Tuning Performa MariaDB & Galera Cluster untuk Beban Transaksi Tinggi

1. Intisari Kilat: Fondasi dalam Sekali Lirik

Pukul 00.01 saat peluncuran tiket konser atau flash sale akbar, skenario horor di ruang operasional hampir selalu seragam: CPU server web Nginx dan backend Go/PHP masih santai di angka 30%, namun latensi aplikasi melompat dari 40ms ke 12 detik. Begitu kita intip terminal MariaDB via mytop atau SHOW FULL PROCESSLIST, ribuan koneksi menumpuk dengan status Locked, Waiting for table metadata lock, atau wsrep: waiting for certification. Di titik ini, menambah core CPU database ibarat menuang bensin ke kobaran api—hanya mempercepat lock contention.

Galera Cluster bukan sekadar replikasi master-slave biasa; ia adalah sistem virtually synchronous berbasis sertifikasi (certification-based replication). Jika Anda memperlakukannya seperti MySQL standalone standar, bersiaplah menghadapi badai aborted transactions dan node yang terlempar keluar dari cluster (split-brain).

Berikut contekan mental arsitektur yang wajib terpatri di kepala Anda sebelum menyentuh file konfigurasi:

[!IMPORTANT] Tiga Hukum Abadi Transaksi Tinggi MariaDB + Galera:

1. RAM adalah Penguasa Mutlak: 70–80% RAM server khusus database wajib didekasikan untuk innodb_buffer_pool_size. Jika database Anda mulai melakukan swapping ke disk, performa seketika runtuh 1000x lipat.
2. Tulis Lokal, Verifikasi Global: Transaksi dieksekusi secara optimistik di node lokal, namun commit harus melewati sertifikasi quorum di semua node. Semakin besar ukuran satu transaksi, semakin tinggi peluangnya ditolak (certification failure).
3. Semua Tabel Wajib Punya Primary Key: Menjalankan Galera tanpa explicit primary key pada setiap tabel adalah resep instan menuju full table scans saat sertifikasi replikasi, yang berujung pada kelumpuhan total node.

[Client Apps] 
      │
      ▼
[HAProxy / ProxySQL] ── (Layer 7 Routing: Split Read/Write)
      │
      ├── Write Traffic ──► [Node 1 (Writer Master)] ──┐
      │                                               │ (wsrep sync group)
      └── Read Traffic  ──► [Node 2 & 3 (Read-Only)] ◄─┘

Mengapa routing di atas krusial? Karena meski Galera berlabel Multi-Master, menulis ke semua node secara simultan (multi-node writing) pada baris data yang berdekatan adalah pintu gerbang bencana write skew. Untuk memahami apakah Anda benar-benar membutuhkan topologi ini atau cukup arsitektur yang lebih sederhana, mari kita telaah kompas keputusannya.

2. Kompas Keputusan: Kapan Memilih Solusi Ini vs Alternatif Lain

Banyak insinyur terjebak tren arsitektur: langsung memasang Galera Cluster 3 node untuk aplikasi yang beban tulisnya sebenarnya hanya 200 QPS (Queries Per Second). Akibatnya, mereka menanggung kompleksitas jaringan, overhead sertifikasi, dan penanganan split-brain tanpa mendapatkan benefit nyata. Gunakan pohon keputusan berikut untuk menentukan nasib arsitektur data Anda:

Diagram Alur Keputusan Arsitektur Basis Data

Apakah aplikasi Anda memerlukan Zero RPO (Recovery Point Objective) tanpa toleransi kehilangan data?
 ├── TIDAK ──► Gunakan MariaDB Standalone + Asynchronous Replica (Read Replica pool biasa).
 └── YA
      │
      ▼
 Apakah rasio beban tulis (WRITE) lebih tinggi daripada baca (READ)? (Misal: > 40% Write)
      ├── YA ──► Evaluasi ulang: Galera MEMPERLAMBAT write throughput global demi konsistensi.
      │           Pertimbangkan Sharding (Vitess / Citus) atau Message Queue decoupling (Kafka/RabbitMQ).
      └── TIDAK (Read-heavy, toleransi zero-lag data)
           │
           ▼
      Apakah tim siap mendisiplinkan aplikasi: semua tabel ber-PK, tidak ada lock manual (GET_LOCK),
      dan transaksi berukuran kecil (< 5000 rows/commit)?
           ├── TIDAK ──► Gunakan Semi-Synchronous Replication (Loss-less semi-sync).
           └── YA ──► PILIH: Galera Cluster 3-Node dengan Front-End Redis Caching Layer.

Tabel Komparasi: Evaluasi Kebutuhan Riil di Lapangan

Jika evaluasi Anda mantap menjatuhkan pilihan pada MariaDB Galera Cluster yang diperkuat Redis, langkah berikutnya adalah membedah organ internal engine InnoDB dan parameter replikasi wsrep agar tidak kolaps di tengah jalan.

3. Contekan Konfigurasi & Parameter Paling Sering Dicari

Mari kita bedah langsung isi dapur /etc/mysql/mariadb.conf.d/50-server.cnf dan konfigurasi cluster. File konfigurasi default bawaan distribusi Linux (seperti Ubuntu atau Debian) disiapkan untuk server kecil berkapasitas 2 GB RAM. Menjalankan konfigurasi default di server enterprise (misal 64 GB RAM dengan NVMe SSD) sama saja mengendarai supercar dengan rem tangan terkunci.

1. Optimasi Mesin Inti (InnoDB & Thread Engine)

Letakkan konfigurasi ini pada blok [mysqld]:

[mysqld]
# --- PENGATURAN DASAR & JARINGAN ---
user                            = mysql
default_storage_engine          = InnoDB
socket                          = /run/mysqld/mysqld.sock
pid-file                        = /run/mysqld/mysqld.pid
max_connections                 = 2500       # Jangan set ke 10000; memori per-thread bisa memicu OOM Killer
max_connect_errors              = 1000000
open_files_limit                = 65535
back_log                        = 1024

# --- THREAD MANAGEMENT: Menghilangkan Overhead Buka-Tutup Koneksi ---
# Hitung rasio: Status 'Threads_created' / 'Connections' harus mendekati 0
thread_handling                 = pool-of-threads # Gunakan Thread Pool pada beban transaksi tinggi concurrency ekstrim
thread_pool_size                = 16              # Samakan atau 1.5x jumlah physical CPU cores
thread_pool_max_threads         = 2000
thread_cache_size               = 128             # Fallback jika thread_handling = one-thread-per-connection

# --- MEMORI & INNODB BUFFER POOL (Asumsi Server Dedikasi 64 GB RAM) ---
# Alokasikan 75% dari total RAM untuk Buffer Pool
innodb_buffer_pool_size         = 48G
# Pecah pool menjadi instances agar CPU cores tidak antre mutex yang sama (1 instance per 1-4 GB pool)
innodb_buffer_pool_instances     = 16
innodb_buffer_pool_dump_at_shutdown = ON          # Warm-up cache otomatis pasca restart
innodb_buffer_pool_load_at_startup  = ON

# --- I/O & NVMe DISK ACCELERATION ---
innodb_file_per_table           = 1
innodb_flush_method             = O_DIRECT        # Bypass page cache OS kernel, hindari double buffering
innodb_io_capacity              = 5000            # Sesuaikan IOPS baseline SSD (NVMe: 5000 - 10000)
innodb_io_capacity_max          = 15000           # Lonjakan darurat saat dirty pages menumpuk
innodb_read_io_threads          = 8
innodb_write_io_threads         = 8
innodb_flush_neighbors          = 0               # WAJIB 0 untuk SSD/NVMe (jangan cari blok tetangga)

# --- LOGGING & DURABILITAS TRANSAKSI ---
# Trade-off durabilitas vs throughput: 
# 1 = Full ACID (disk flush tiap commit). 2 = Flush ke OS cache tiap commit, flush disk tiap 1 detik.
innodb_flush_log_at_trx_commit  = 2               # Di Galera, redundansi dijamin quorum; nilai 2 aman & dongkrak I/O 300%
innodb_log_file_size            = 4G              # Write buffer besar mencegah checkpointing mendadak
innodb_log_buffer_size          = 64M

2. Galera Replication Tuning (Mitigasi Deadlock & Write Skew)

Lanjutkan dengan konfigurasi replikasi di /etc/mysql/mariadb.conf.d/60-galera.cnf:

[galera]
wsrep_on                        = ON
wsrep_provider                  = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name              = "production_core_cluster"
wsrep_cluster_address           = "gcomm://192.168.10.11,192.168.10.12,192.168.10.13"
wsrep_node_address              = "192.168.10.11"
wsrep_node_name                 = "node-db-01"

# --- SST (State Snapshot Transfer) via Mariabackup (Non-blocking) ---
wsrep_sst_method                = mariabackup
wsrep_sst_auth                  = "sst_user:SangatRahasia123!"

# --- PARALLEL REPLICATION SLAVE THREADS ---
# Jika node lambat mengejar writer, cert queue membengkak. Berikan worker thread yang cukup.
# Rumus: (2 s/d 4) x CPU Core khusus untuk apply replication stream
wsrep_slave_threads             = 32

# --- INTEGRITAS DATA & MITIGASI WRITE SKEW ---
wsrep_certify_nonPK             = ON              # Larang transaksi tabel tanpa PK dieksekusi diam-diam
wsrep_auto_increment_control    = ON              # Auto handle auto-increment offset agar tidak collision
wsrep_retry_autocommit          = 5               # Auto-retry transaksi autocommit yang terbentur sertifikasi

# --- PENGATURAN BUFFER BUFFER WRITE-SET ---
wsrep_provider_options          = "gcache.size=16G; gcs.fc_limit=256; gcs.fc_factor=0.8; evs.suspect_timeout=PT5S; evs.inactive_timeout=PT15S"

[!TIP] Apa maksud gcache.size=16G? GCache adalah memory/disk ring-buffer penampung transaksi Galera. Jika Node 2 offline selama 30 menit (misal kernel upgrade), Node 1 dan 3 akan mencatat semua diff data di GCache. Saat Node 2 online kembali, ia cukup mengambil IST (Incremental State Transfer) dari GCache alih-alih melakukan SST (State Snapshot Transfer) yang mengunci I/O dan menyalin seluruh raw file ratusan gigabyte.

3. Arsitektur Redis Caching Layer: Menghentikan Beban Sebelum ke Engine

Jangan biarkan hot row (misalnya: data sisa stok produk flash sale) menjadi medan perang pessimistic lock di MariaDB (SELECT ... FOR UPDATE). Tangani atomic operation di Redis menggunakan pendekatan Cache-Aside termodifikasi, seperti implementasi backend Node.js / Go berikut:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"github.com/redis/go-redis/v9"
	_ "github.com/go-sql-driver/mysql"
)

var ctx = context.Background()

// DecoupleStockReservation memproses stok di memory Redis terlebih dahulu
// untuk menjamin sub-millisecond response tanpa menyentuh Galera Cluster locks
func DecoupleStockReservation(rdb *redis.Client, db *sql.DB, itemID string, qty int64) error {
	// Lua Script: Atomic check-and-decrement stok di Redis
	var checkAndDecr = redis.NewScript(`
		local current = redis.call('GET', KEYS[1])
		if not current then
			return -1
		end
		if tonumber(current) >= tonumber(ARGV[1]) then
			return redis.call('DECRBY', KEYS[1], ARGV[1])
		else
			return -2
		end
	`)

	cacheKey := fmt.Sprintf("item:stock:%s", itemID)
	res, err := checkAndDecr.Run(ctx, rdb, []string{cacheKey}, qty).Result()
	if err != nil {
		return fmt.Errorf("redis transaction failed: %w", err)
	}

	status := res.(int64)
	if status == -1 {
		// Cache Miss: Ambil dari MariaDB, isi kembali Redis (Cache Warm-up flow)
		return fmt.Errorf("cache expired or not found, fallback to synced read")
	} else if status == -2 {
		// Stok Habis secara real-time tanpa membebani database
		return fmt.Errorf("out of stock")
	}

	// Sukses di RAM! Lempar pembaruan permanen ke Galera via Background Async Worker / Queue
	// Ini membebaskan MariaDB dari lock contention brutal ratusan ribu user secara bersamaan
	go func() {
		_, err := db.ExecContext(context.Background(), 
			"UPDATE product_stocks SET stock = stock - ? WHERE product_id = ?", 
			qty, itemID)
		if err != nil {
			// Log error ke DLQ (Dead Letter Queue) untuk recovery kompensasi
			fmt.Printf("CRITICAL: Failed async persist to Galera: %v\n", err)
		}
	}()

	return nil
}

Setelah konfigurasi tertanam dan layer caching terpasang, bagaimana cara seorang architect memantau status sistem di terminal saat traffic spike berlangsung? Inilah saatnya membuka laci berisi mantra perintah ringkas.

4. Koleksi Mantra One-Liner Siap Salin untuk Keseharian

Simpan baris perintah ini di direktori ~/.bash_aliases atau catatan tempel engineer on-call Anda. Tidak perlu repot mengingat sintaks rumit di saat panik produksi.

1. Intip Kesehatan Kluster Galera (Quorum & Flow Control)

Periksa apakah ada node yang tertinggal atau memicu jeda replikasi (Flow Control paused):

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment'; SHOW STATUS LIKE 'wsrep_cluster_size'; SHOW STATUS LIKE 'wsrep_flow_control_paused'; SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';"

Indikator Bahaya: Jika wsrep_flow_control_paused bernilai > 0.00 (misal 0.25), artinya node ini menghambat 25% waktu tulis seluruh cluster karena I/O disk tidak mampu mengejar transaksi masuk.

2. Deteksi Certification Failure & Local Aborts (Write Skew Tracker)

Lihat seberapa sering transaksi lokal dibunuh secara paksa oleh transaksi dari node lain:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_local_bf_aborts'; SHOW STATUS LIKE 'wsrep_local_cert_failures';"

Indikator Bahaya: Jika angka wsrep_local_bf_aborts (Brute Force Aborts) naik puluhan digit per detik, aplikasi Anda sedang melakukan multi-master write ke baris data yang sama. Arahkan semua traffic tulis ke satu node master!

3. Cek Efisiensi InnoDB Buffer Pool Hit Ratio

Pastikan data Anda dibaca langsung dari memori RAM, bukan dari piringan storage:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%s';" | awk '{data[$1]=$2} END {hit_rate = (1 - (data["Innodb_buffer_pool_reads"] / data["Innodb_buffer_pool_read_requests"])) * 100; printf "Buffer Pool Hit Rate: %.4f%%\n", hit_rate}'

Target: Wajib berada di angka >= 99.8000%. Bila di bawah 95%, buffer pool Anda kekecilan atau ada kueri liar melakukan full table scan.

4. Tangkap Kueri yang Sedang Menyandera Lock

Cari tahu transaksi mana yang sedang mengunci thread lain sebelum database hang:

mysql -u root -p -e "SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, trx_query FROM information_schema.innodb_trx ORDER BY duration_sec DESC LIMIT 5;"

5. Pantau Redis Hit Rate & Memory Pressure

Ketahui seketika apakah beban transaksi berhasil diredam oleh layer cache:

redis-cli info stats | grep -E 'keyspace_hits|keyspace_misses' | awk -F: '{val[$1]=$2} END {total=val["keyspace_hits"]+val["keyspace_misses"]; printf "Redis Hit Ratio: %.2f%%\n", (val["keyspace_hits"]/total)*100}'

Mantra terminal ini memberi Anda visibilitas instan. Namun, pemahaman parameter dan kueri diagnostik tidak akan menyelamatkan Anda jika Anda masih melakukan kekeliruan fatal yang sering disepelekan berikut ini.

5. Jalur Bahaya: Dosa Besar yang Wajib Dihindari di Produksi

Dalam puluhan audit post-mortem insiden database skala masif, kami menemukan bahwa kegagalan Galera hampir tidak pernah disebabkan oleh bug kode MariaDB itu sendiri, melainkan oleh "dosa arsitektur" yang diizinkan melenggang ke lingkungan produksi.

       [ DOSA 1: Multi-Master Writing ke Hot-Row ]
App 1 (Node 1)                     App 2 (Node 2)
UPDATE stock SET qty = qty - 1     UPDATE stock SET qty = qty - 1
WHERE product_id = 100;            WHERE product_id = 100;
          │                                 │
          ▼                                 ▼
  [Commit Lokal OK]                 [Commit Lokal OK]
          │                                 │
          └───► [ WSREP CERTIFICATION ] ◄───┘
                           │
               CONFLICT DETECTED (Write Skew)!
            Node 2 Transaction dibatalkan paksa!
          (wsrep_local_bf_aborts / Deadlock 1213)

[!WARNING] 1. Menggunakan Multi-Master Sebagai Multi-Writer Tanpa Routing Terpusat Galera memang mendukung multi-master, tetapi bukan berarti Anda bebas menulis ke semua node secara bersamaan untuk tabel yang sama. Ketika Node A dan Node B memperbarui row yang sama di milidetik yang sama, kedua transaksi lolos validasi lokal, namun salah satunya AKAN DIBUNUH saat proses sertifikasi global. Hasilnya? Aplikasi backend Anda memuntahkan galat Deadlock: WSREP detected deadlock/conflict and aborted the transaction (Error 1213). Solusi Tegas: Gunakan ProxySQL atau HAProxy di depan Galera. Konfigurasikan aturan L7: Arahkan 100% trafik WRITE ke satu node primer (Primary Writer). Manfaatkan node lain murni hanya untuk READ.

[!WARNING] 2. Menjalankan DDL Besar Secara Langsung (ALTER TABLE Bloat) Menjalankan perintah ALTER TABLE transaksi ADD COLUMN notes VARCHAR(255); pada tabel berukuran puluhan gigabyte menggunakan mode default (wsrep_OSU_method = TOI / Total Order Isolation) akan membekukan seluruh node di cluster! Tidak ada kueri read maupun write yang bisa diproses di seluruh cluster sampai alter selesai. Solusi Tegas: Gunakan tool online schema migration seperti pt-online-schema-change atau gh-ost. Alternatif lain, gunakan mode Rolling Schema Upgrade (RSU) dengan isolasi manual per node keluar dari cluster.

[!WARNING] 3. Mengabaikan SWAP Memory Paging di Host Database Mengatur innodb_buffer_pool_size terlalu mepet dengan kapasitas RAM fisik (misal menyetel 30 GB di server ber-RAM 32 GB) menyisakan ruang yang sangat sempit untuk sistem operasi, buffer koneksi klien (sort_buffer_size, read_buffer_size), dan proses mysqld. Begitu RAM habis, kernel Linux akan memindahkan memori InnoDB ke swap partition disk. Solusi Tegas: Turunkan agresivitas swapping kernel Linux secara permanen. Tambahkan nilai ini di /etc/sysctl.conf:

vm.swappiness = 1
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5

Nilai vm.swappiness = 1 memaksa kernel mempertahankan InnoDB buffer di memori fisik semaksimal mungkin tanpa mematikan fitur swap darurat OS.

[!WARNING] 4. Transaksi Raksasa Tanpa Paginasi (Batch Mutation Terlalu Besar) Perintah seperti DELETE FROM session_logs WHERE created_at < NOW() - INTERVAL 30 DAY; yang menghapus 2 juta baris dalam satu perintah tunggal akan membuat paket write-set membesar hingga ratusan megabyte. Ini langsung menyumbat gcs.fc_limit, memicu cluster flow control, dan membuat semua node cluster freeze seketika. Solusi Tegas: Pecah transaksi ke ukuran kecil yang aman (chunking): hapus maksimal 5.000 baris per commit di dalam loop aplikasi atau cron batch scheduler.

Dengan mendisiplinkan topologi melalui Primary Writer, mengamankan InnoDB Buffer Pool di RAM murni, mereduksi lonjakan IOPS via Redis, dan menghormati batasan replikasi Galera, basis data relasional Anda kini siap menopang jutaan transaksi tanpa berkeringat dingin di malam peluncuran sistem.