Optimasi Performa Query Kompleks dan Sharding pada MariaDB Enterprise Cluster

1. Konteks Industri & Analisis Akar Masalah

Pertumbuhan volume data pada arsitektur web modern dengan jutaan transaksi harian sering kali membawa sistem basis data relasional ke ambang batas kapasitasnya. Dalam ekosistem yang melibatkan backend berbasis Go/Node.js, web server Nginx, serta caching layer menggunakan Redis, MariaDB Enterprise Cluster sering kali menjadi titik pusat bottleneck ketika query kompleks tidak dioptimalkan atau ketika topologi replikasi tidak dirancang untuk menangani beban write dan read yang tinggi secara bersamaan.

Di dunia nyata, aplikasi enterprise sering mengalami lonjakan latensi (latency spikes), peningkatan penggunaan CPU hingga 100%, dan kegagalan replikasi (replication lag) pada Galera Cluster. Akar masalah utama biasanya bukan terletak pada spesifikasi perangkat keras (RAM/CPU/NVMe), melainkan pada:

1. Penggunaan query yang melakukan full table scan karena kurangnya pemahaman terhadap struktur execution plan.
2. Desain indeks yang buruk (mengabaikan aturan leftmost prefix pada composite index).
3. Kesalahan dalam arsitektur read-write splitting, di mana operasi tulis dan baca berat dieksekusi pada node yang sama tanpa mekanisme load balancing yang tepat.

Sebelum vs Sesudah Penerapan

• Sebelum Optimasi: Query analitik kompleks memakan waktu hingga 12.4 detik, menyebabkan koneksi backend (PHP-FPM/Go) menumpuk (connection pool exhaustion), dan menyebabkan deadlock pada Galera Cluster saat terjadi transaksi tulis bersamaan.
• Sesudah Penerapan: Melalui pembacaan EXPLAIN FORMAT=JSON yang mendalam, penerapan covering index, restrukturisasi query, serta implementasi read-write splitting berbasis ProxySQL, waktu eksekusi query turun drastis menjadi 18 milidetik, sementara throughput transaksi meningkat 450% tanpa menambah kapasitas node fisik.

2. Konsep Inti & Deep Dive Arsitektur

Untuk memahami cara kerja optimasi query dan klasterisasi pada MariaDB, kita harus melihat apa yang terjadi di balik layar (under the hood) pada tingkat mesin penyimpanan InnoDB dan mesin konsistensi Galera Cluster.

+-----------------------------------------------------------------------------------+
|                           Client Request (Go / Node.js)                           |
+-----------------------------------------------------------------------------------+
                                          |
                                          v
+-----------------------------------------------------------------------------------+
|                           ProxySQL (Read-Write Splitter)                          |
+-----------------------------------------------------------------------------------+
             | (Writes / Mutations)                       | (Reads / Analytics)
             v                                            v
+---------------------------+              +----------------------------------------+
|   MariaDB Node 1 (Master) | --(Galera)-- | MariaDB Node 2 & Node 3 (Read Replicas)|
+---------------------------+              +----------------------------------------+

Alur Eksekusi Query dan Galera Replication

1. Query Parsing & Optimization: Ketika query dikirim, MariaDB melakukan parsing sintaks, memeriksa izin, dan memanggil Cost-Based Optimizer (CBO). CBO menggunakan statistik tabel untuk memilih jalur akses terbaik (apakah menggunakan indeks atau table scan).
2. InnoDB Storage Engine: Data dibaca dari buffer pool (jika ada di RAM) atau diambil dari disk melalui file .ibd. Kunci dari performa tinggi adalah memaksimalkan Innodb Buffer Pool Hit Rate.
3. Galera Certification-based Replication: Dalam klaster MariaDB Enterprise, replikasi tidak menggunakan binlog konvensional berbasis asynchronous, melainkan protokol virtually synchronous berbasis certification. Setiap transaksi dikirim ke seluruh node via multicast/unicast. Node memvalidasi apakah ada konflik kunci baris. Jika ada konflik, transaksi dibatalkan pada node non-pemrakarsa untuk mencegah split-brain.

3. Perbandingan Komparatif / Analisis Pendekatan

Tabel berikut membandingkan pendekatan pengelolaan beban kerja basis data untuk enterprise:

[!WARNING] Jangan pernah melakukan write secara paralel ke semua node Galera tanpa mekanisme antrean atau ProxySQL yang mengatur routing, karena hal ini akan memicu certification failure dan lonjakan deadlock secara masif.

4. Prasyarat Sistem & Persiapan Lingkungan

Sebelum masuk ke implementasi teknis, pastikan environment Anda memenuhi standar enterprise berikut:

• Sistem Operasi: Ubuntu Server 22.04 LTS / RHEL 9 (Minimum kernel 5.15+ dengan pengaturan sysctl yang dioptimalkan untuk jaringan berkecepatan tinggi).
• Spesifikasi Hardware per Node: Minimal 8 vCPU, 32 GB RAM, SSD NVMe dengan IOPS tinggi.
• Konektivitas Jaringan: Latensi antar node klaster < 2ms (Private Network / VPC).
• Paket Software: MariaDB Enterprise Server 10.11+, ProxySQL 2.5+.

Konfigurasi dasar sistem operasi (/etc/sysctl.conf):

fs.file-max = 2097152
net.core.somaxconn = 65535
vm.swappiness = 1
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10

Terapkan perubahan dengan perintah:

sysctl -p

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Konfigurasi MariaDB Enterprise Server (Node 1, 2, 3)

Buat file konfigurasi khusus Galera di /etc/mysql/mariadb.conf.d/99-galera.cnf:

[mysqld]
bind-address = 0.0.0.0
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size = 20G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Galera Cluster Configuration
wsrep_on = ON
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://192.168.10.11,192.168.10.12,192.168.10.13"
wsrep_cluster_name = "mariadb_enterprise_cluster"
wsrep_node_address = "192.168.10.11"
wsrep_node_name = "node1"
wsrep_sst_method = rsync
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2

Langkah 2: Analisis Query Menggunakan EXPLAIN FORMAT=JSON

Jalankan perintah SQL berikut untuk menganalisis query kompleks pada tabel transaksi:

EXPLAIN FORMAT=JSON
SELECT t.id, t.user_id, t.amount, t.created_at 
FROM transactions t 
JOIN users u ON t.user_id = u.id 
WHERE t.status = 'completed' 
  AND t.created_at >= '2023-01-01 00:00:00'
ORDER BY t.created_at DESC 
LIMIT 50;

Langkah 3: Pembuatan Strategi Indexing Lanjutan

Berdasarkan hasil analisis EXPLAIN, buat composite covering index untuk menghindari filesort dan table scan:

CREATE INDEX idx_status_created_user_amount 
ON transactions (status, created_at DESC, user_id, amount);

Untuk pencarian teks penuh pada deskripsi transaksi, gunakan InnoDB Full-Text Search:

ALTER TABLE transactions ADD FULLTEXT INDEX fx_description (description);

SELECT id, description 
FROM transactions 
WHERE MATCH(description) AGAINST('+enterprise +billing' IN BOOLEAN MODE);

Langkah 4: Konfigurasi ProxySQL untuk Read-Write Splitting

Konfigurasikan ProxySQL (/etc/proxysql.cnf atau melalui CLI ProxySQL):

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES 
(0, '192.168.10.11', 3306),
(1, '192.168.10.12', 3306),
(1, '192.168.10.13', 3306);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES 
(1, 1, '^SELECT.*FOR UPDATE', 0, 1),
(2, 1, '^SELECT', 1, 1);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO RUNTIME;

6. Bedah Kode & Penjelasan Detail Baris Kunci

• innodb_flush_log_at_trx_commit = 2: Parameter ini sangat krusial untuk performa tulis. Log transaksi disalin ke disk setiap detik, bukan pada setiap transaksi commit. Ini meningkatkan performa I/O secara drastis dengan risiko kehilangan data maksimal 1 detik jika terjadi kegagalan daya total.
• innodb_autoinc_lock_mode = 2: Mengaktifkan interleaved lock mode pada Galera Cluster. Ini memungkinkan pembangkitan nilai AUTO_INCREMENT secara konkuren tanpa mengunci seluruh tabel, yang esensial untuk beban tulis tinggi.
• idx_status_created_user_amount: Urutan kolom pada indeks komposit sangat menentukan. Karena klausa WHERE status = ... menggunakan operator kesamaan (=), kolom status diletakkan pertama. Diikuti oleh created_at DESC agar sesuai dengan klausa ORDER BY, sehingga optimiser dapat membaca indeks secara berurutan tanpa proses pengurutan tambahan di memori (filesort).

7. Skenario Produksi & Studi Kasus Dunia Nyata

Sebuah platform e-commerce skala besar mengalami lonjakan trafik hingga 50.000 requests per second (RPS) saat event flash sale. Backend Go yang terhubung ke MariaDB mengalami kegagalan karena lonjakan koneksi.

Solusi Arsitektural yang Diterapkan:

1. Query Offloading: Query laporan analitik dialihkan sepenuhnya ke hostgroup baca (Node 2 & Node 3) melalui ProxySQL.
2. Connection Pooling: Aplikasi Go menggunakan pustaka database/sql dengan pengaturan SetMaxOpenConns(100) dan SetMaxIdleConns(20) untuk mencegah pembuatan koneksi baru yang berlebihan ke MariaDB.
3. Caching Layer: Data master produk yang jarang berubah di-cache di Redis dengan TTL 300 detik, mengurangi beban pembacaan ke database sebesar 70%.

8. Common Pitfalls, Anti-Patterns & Solusi

• Anti-Pattern 1: Menggunakan fungsi pada kolom yang diindeks dalam klausa WHERE. Contoh Buruk: WHERE DATE(created_at) = '2023-10-01' Solusi: Ubah menjadi rentang waktu agar indeks dapat digunakan: WHERE created_at >= '2023-10-01 00:00:00' AND created_at <= '2023-10-01 23:59:59'.
• Anti-Pattern 2: Melakukan transaksi besar secara masif (Mega Transactions). Dampak: Menyebabkan konsumsi memori berlebih pada Galera dan memicu wsrep_max_ws_rows error. Solusi: Pecah transaksi besar menjadi batch kecil (misal: 1.000 baris per iterasi).

[!TIP] Selalu pantau metrik wsrep_local_send_queue dan wsrep_local_recv_queue. Jika nilainya konsisten di atas 0, berarti node Anda mengalami hambatan jaringan atau kapasitas CPU yang tidak memadai untuk memproses replikasi.

9. Benchmark, Metrik Kinerja & Tuning Performa

Lakukan pengujian beban menggunakan sysbench untuk memvalidasi performa klaster sebelum dan sesudah optimasi:

sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=6033 \
  --mysql-user=bench_user \
  --mysql-password=secure_password \
  --mysql-db=enterprise_db \
  --tables=10 \
  --table-size=1000000 \
  --threads=64 \
  --time=300 \
  --report-interval=10 \
  run

Interpretasi Hasil Benchmark:

• Perhatikan metrik transactions per second (TPS) dan 95th percentile latency.
• Jika latensi tinggi pada beban baca, pastikan ProxySQL mendistribusikan beban secara merata ke seluruh node read replica.

10. Checklist Penerapan Produksi & Kesimpulan

Gunakan checklist berikut sebelum merilis perubahan ke lingkungan produksi:

• Seluruh query kompleks telah dianalisis menggunakan EXPLAIN FORMAT=JSON.
• Indeks komposit dan covering index telah diterapkan dan diverifikasi tidak ada unused index.
• Parameter innodb_buffer_pool_size diset ke 70-80% dari total RAM fisik.
• ProxySQL dikonfigurasi dengan benar untuk memisahkan trafik read dan write.
• Pemantauan otomatis (Prometheus + Grafana) aktif untuk metrik Galera (wsrep_cluster_status, wsrep_connected, wsrep_local_state_comment).

Dengan menerapkan arsitektur klaster yang tangguh, strategi indexing yang presisi, dan pemisahan beban kerja melalui ProxySQL, sistem basis data enterprise Anda siap menangani jutaan transaksi harian dengan latensi minimal dan ketersediaan tinggi (High Availability).