Optimasi Performa Kueri MariaDB Skala Besar untuk Aplikasi Web High-Traffic

1. Konteks Industri & Analisis Akar Masalah

Dalam lanskap aplikasi web modern berkinerja tinggi—mulai dari platform e-commerce flash sale, gerbang pembayaran digital, hingga aplikasi SaaS analitik multi-tenant—lapisan basis data relasional (relational database layer) hampir selalu menjadi titik jenuh utama (bottleneck). Ketika lonjakan lalu lintas (traffic spikes) terjadi, infrastruktur frontend web server (Nginx/Node.js/Go) dapat dengan mudah dinaikkan skalanya secara horizontal (auto-scale out). Namun, basis data relasional stateful seperti MariaDB menghadapi batasan fisika: konkurensi I/O disk, perebutan mutex latch, serta keterbatasan memori utama.

Pada kondisi default instalasi bawaan sistem operasi (seperti paket mariadb-server di Ubuntu atau RHEL), MariaDB dikonfigurasi secara sangat konservatif untuk memastikan ia dapat berjalan di Virtual Private Server (VPS) dengan RAM sekecil 512 MB hingga 1 GB. Ketika konfigurasi out-of-the-box ini dibebani ribuan queries per second (QPS) dari server aplikasi web, anomali berikut langsung bermunculan:

1. I/O Thrashing yang Melumpuhkan: Buffer Pool yang terlalu kecil memaksa MariaDB melakukan operasi pembacaan blok data acak (random reads) langsung dari penyimpanan disk (bahkan NVMe tercepat pun memiliki latensi urutan mikrodetik, dibanding nanodetik pada DRAM).
2. Connection Starvation & Thread Overhead: Pembuatan satu thread OS per koneksi klien (one-thread-per-connection) menguras memori virtual dan memaksa CPU melakukan context switching masif saat menerima ribuan koneksi konkuren dari PHP-FPM atau Node.js workers.
3. Lock Contention & Cascade Stall: Desain kueri yang buruk tanpa indeks penutup (covering index) memicu penguncian baris (row-level locks) yang meluas menjadi table scan, menahan transaksi lain dalam antrean lock wait timeout, dan akhirnya memuntahkan status HTTP 504 Gateway Timeout ke pengguna akhir.

Transformasi performa ini tidak diraih melalui spekulasi acak, melainkan melalui pemahaman mendalam atas arsitektur internal MariaDB: interaksi antara Query Optimizer, Storage Engine InnoDB, alokasi buffer memori, dan orkestrasi topologi klaster.

2. Konsep Inti & Deep Dive Arsitektur

Untuk mengoptimalkan MariaDB, arsitek sistem harus memahami siklus hidup kueri dari saat paket TCP diterima pada socket hingga data diekstraksi dari B-tree pages di storage disk.

                             [ HTTP Client Traffic ]
                                        │
                                        ▼
                            [ Edge Cloudflare / WAF ]
                                        │
                                        ▼
                     [ Reverse Proxy Nginx / Ingress ]
                                        │
                                        ▼
                  [ Web Application Pool (PHP-FPM / Go) ]
                                        │
                                        ▼
                     ┌────────────────────────────────────┐
                     │  ProxySQL L7 Layer / Load Balancer │
                     └──────────────────┬─────────────────┘
                                        │ (Read/Write Split)
                 ┌──────────────────────┴──────────────────────┐
                 ▼ (Writes: Master)                            ▼ (Reads: Slaves)
       ┌───────────────────┐                         ┌───────────────────┐
       │   MariaDB Master  │                         │   MariaDB Slave   │
       │ ┌───────────────┐ │   GTID Semi-Sync Repl   │ ┌───────────────┐ │
       │ │  Thread Pool  │ │ ──────────────────────> │ │  Thread Pool  │ │
       │ └───────┬───────┘ │                         │ └───────┬───────┘ │
       │         ▼         │                         │         ▼         │
       │ ┌───────────────┐ │                         │ ┌───────────────┐ │
       │ │Query Optimizer│ │                         │ │Query Optimizer│ │
       │ └───────┬───────┘ │                         │ └───────┬───────┘ │
       │         ▼         │                         │         ▼         │
       │ ┌───────────────┐ │                         │ ┌───────────────┐ │
       │ │ InnoDB Engine │ │                         │ │ InnoDB Engine │ │
       │ │┌─────────────┐│ │                         │ │┌─────────────┐│ │
       │ ││ Buffer Pool ││ │                         │ ││ Buffer Pool ││ │
       │ │└─────────────┘│ │                         │ │└─────────────┘│ │
       │ │┌─────────────┐│ │                         │ │┌─────────────┐│ │
       │ ││  Redo Log   ││ │                         │ ││  Redo Log   ││ │
       │ │└─────────────┘│ │                         │ │└─────────────┘│ │
       │ └───────┬───────┘ │                         │ └───────┬───────┘ │
       └─────────┼─────────┘                         └─────────┼─────────┘
                 ▼                                             ▼
       [ NVMe Storage (PCIe) ]                       [ NVMe Storage (PCIe) ]

Alur Eksekusi Kueri MariaDB (Query Execution Flow)

1. Connection Handling & Authentication: Permintaan kueri masuk melalui TCP port 3306. Pada mode thread pool, kueri diantrikan ke dalam thread group yang sesuai alih-alih melahirkan thread sistem operasi baru.
2. Parsing & Query Rewriting: Kueri diuraikan secara leksikal (Lexical Parse) untuk memvalidasi sintaks SQL. Parser menghasilkan Abstract Syntax Tree (AST).
3. Query Optimization: MariaDB Cost-Based Optimizer (CBO) menganalisis AST. CBO membaca metadata statistik indeks tabel untuk mengestimasi biaya eksekusi (cost estimate) dari berbagai jalur eksekusi alternatif (misalnya: menggunakan Index Scan vs Full Table Scan, atau menentukan urutan join tabel).
4. Storage Engine Interface: Optimizer menyerahkan instruksi ke antarmuka InnoDB. InnoDB mencari halaman data (pages, ukuran standar 16 KB) di dalam InnoDB Buffer Pool.
   • Buffer Hit: Data ditemukan langsung di RAM. Tidak ada latensi disk.
   • Buffer Miss: InnoDB memicu operasi asinkron I/O untuk memuat blok 16 KB dari berkas .ibd di NVMe ke dalam Buffer Pool, mendepak halaman usang (eviction) berbasis algoritma Modified Least Recently Used (LRU).
5. Transaction Logging & Flushing:
   • Operasi modifikasi (DML: INSERT/UPDATE/DELETE) menulis perubahan terlebih dahulu ke struktur memori (Buffer Pool) dan secara sekuensial ke InnoDB Redo Log (berkas ib_logfile*).
   • Penulisan Redo Log bersifat append-only sequential write, yang ribuan kali lebih cepat daripada melakukan in-place random write ke seluruh berkas tabel disk.
   • Latar belakang thread InnoDB (Page Flush Thread) kemudian menyinkronkan halaman kotor (dirty pages) dari Buffer Pool ke disk secara bertahap.

3. Perbandingan Komparatif Pendekatan Arsitektur

Sebelum melakukan modifikasi skala besar, arsitek sistem harus mengevaluasi trade-off dari beberapa strategi skalabilitas basis data:

[!NOTE] Keputusan Arsitektur: Selalu mulai dengan memaksimalkan performa satu node (InnoDB Tuning & Index Optimization). Jangan beralih ke Master-Slave jika single node belum disetel optimal. Gunakan Master-Slave Replication untuk membaca skala besar, dan simpan Sharding hanya sebagai jalan terakhir saat ukuran tabel individual melampaui ratusan juta baris dan batas hardware NVMe tertinggi telah tercapai.

4. Prasyarat Sistem & Persiapan Lingkungan

Untuk mereplikasi panduan ini di lingkungan produksi skala enterprise, siapkan infrastruktur dengan spesifikasi minimum berikut:

Spesifikasi Lingkungan Produksi

• OS: Ubuntu Server 22.04 LTS / 24.04 LTS atau AlmaLinux 9 Enterprise.
• MariaDB Version: MariaDB Community Server versi 10.11 LTS atau 11.4 LTS.
• CPU: 16 Core vCPU / Fisik (Disarankan x86_64 dengan instruksi AVX2).
• RAM: 64 GB ECC Registered Memory.
• Storage: Enterprise NVMe U.2/U.3 dalam susunan RAID 10 (Direct Attached Storage), sistem berkas ext4 atau XFS.
• Network: Minimal 10 Gbps antarmuka jaringan terisolasi untuk jalur replikasi.

Verifikasi Awal Konfigurasi Kernel & OS

Sebelum mengonfigurasi MariaDB, kernel Linux harus dituning untuk beban kerja basis data transaksional tinggi. Jalankan perintah verifikasi berikut di terminal server:

# Periksa ketersediaan Direct I/O dan jenis filesystem
df -Th /var/lib/mysql

# Verifikasi elevator I/O scheduler (Untuk NVMe, wajib 'none' atau 'mq-deadline')
cat /sys/block/nvme0n1/queue/scheduler

# Verifikasi batas resource open files saat ini
ulimit -n

Terapkan penyesuaian parameter kernel secara permanen pada berkas /etc/sysctl.d/99-mariadb-enterprise.conf:

# Nonaktifkan agresivitas swapping memori RAM ke disk
vm.swappiness = 1

# Tingkatkan kapasitas penampungan antrean koneksi TCP socket
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535

# Izinkan pemakaian port lokal yang lebih banyak untuk koneksi keluar
net.ipv4.ip_local_port_range = 1024 65535

# Cegah kehabisan memory mappings untuk instance InnoDB Buffer Pool besar
vm.max_map_count = 1048576

Muat parameter baru tanpa reboot:

sudo sysctl --system

Konfigurasi batasan file descriptor sistem pada /etc/security/limits.d/mysql.conf:

mysql    soft    nofile    1048576
mysql    hard    nofile    1048576
mysql    soft    nproc     65535
mysql    hard    nproc     65535

5. Panduan Implementasi Langkah demi Langkah

Langkah 1: Analisis Query Execution Plan Menggunakan EXPLAIN

Sebelum memodifikasi konfigurasi server, diagnosa kueri yang mengalami degradasi performa. Aktifkan penulisan kueri lambat (Slow Query Log) secara dinamis dari MariaDB CLI:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.05; -- Kueri di atas 50 milidetik dicatat
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';

Ambil salah satu kueri transaksional bermasalah dari tabel order web skala besar:

EXPLAIN EXTENDED
SELECT 
    o.id AS order_id, 
    o.customer_id, 
    o.total_amount, 
    p.payment_status
FROM orders o
JOIN payments p ON o.id = p.order_id
WHERE o.merchant_id = 452
  AND o.status = 'COMPLETED'
  AND o.created_at >= '2024-01-01 00:00:00'
ORDER BY o.created_at DESC
LIMIT 20;

Untuk evaluasi presisi matematis, manfaatkan format JSON MariaDB yang menyediakan pembacaan engine cost dan evaluasi kondisi data:

EXPLAIN FORMAT=JSON
SELECT 
    o.id AS order_id, 
    o.customer_id, 
    o.total_amount, 
    p.payment_status
FROM orders o
JOIN payments p ON o.id = p.order_id
WHERE o.merchant_id = 452
  AND o.status = 'COMPLETED'
  AND o.created_at >= '2024-01-01 00:00:00'
ORDER BY o.created_at DESC
LIMIT 20\G

Output JSON Execution Plan:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "128450.40"
    },
    "ordering_operation": {
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "o",
            "access_type": "ALL",
            "possible_keys": ["PRIMARY"],
            "rows_examined_per_scan": 489201,
            "rows_produced_per_join": 12450,
            "filtered": "2.54",
            "cost_info": {
              "read_cost": "112000.00",
              "eval_cost": "2490.00",
              "prefix_cost": "114490.00",
              "data_read_per_join": "3M"
            },
            "attached_condition": "((`o`.`merchant_id` = 452) and (`o`.`status` = 'COMPLETED') and (`o`.`created_at` >= '2024-01-01 00:00:00'))"
          }
        },
        {
          "table": {
            "table_name": "p",
            "access_type": "eq_ref",
            "possible_keys": ["idx_order_id"],
            "key": "idx_order_id",
            "key_length": "8",
            "ref": ["o.id"],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 12450,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "11470.40",
              "eval_cost": "2490.00",
              "prefix_cost": "128450.40",
              "data_read_per_join": "972K"
            }
          }
        }
      ]
    }
  }
}

Analisis Masalah:

1. access_type: ALL membuktikan terjadinya Full Table Scan. MariaDB memindai 489.201 baris secara mentah.
2. using_filesort: true menandakan MariaDB harus mengurutkan data di RAM (Sort Buffer) atau menumpahkannya ke disk temporer karena indeks tidak mencakup urutan kolom created_at.
3. Nilai query_cost mencapai 128.450 unit komputasi.

Solusi Optimasi Indeks Komposit (Covering Index Indexing Strategy): Kita buat indeks komposit dengan urutan selektivitas terbaik: Kolom Equality (merchant_id, status) diletakkan di depan, diikuti kolom Range & Sorting (created_at), dan sertakan kolom proyeksi untuk menghindari Secondary Index Lookup ke clustered index PK.

ALTER TABLE orders 
ADD INDEX idx_perf_opt (
    merchant_id, 
    status, 
    created_at DESC, 
    customer_id, 
    total_amount
);

Setelah indeks dibuat, jalankan kembali EXPLAIN FORMAT=JSON. Hasil menunjukkan transformasi drastis:

• access_type: berubah menjadi range.
• key: menggunakan idx_perf_opt.
• using_filesort: hilang. Data dibaca langsung terurut dari B-Tree.
• query_cost: anjlok dari 128450.40 menjadi 14.20.

Langkah 2: Tuning File Konfigurasi Tingkat Produksi (my.cnf)

Buka berkas /etc/mysql/mariadb.conf.d/50-server.cnf atau /etc/my.cnf.d/server.cnf pada server fisik 64 GB RAM. Terapkan konfigurasi enterprise berikut baris demi baris:

[client]
port                           = 3306
socket                         = /run/mysqld/mysqld.sock
default-character-set          = utf8mb4

[mysqld]
# ----------------------------------------------------------------------
# 1. Identitas Server & Direktori Operasional
# ----------------------------------------------------------------------
user                           = mysql
pid-file                       = /run/mysqld/mysqld.pid
socket                         = /run/mysqld/mysqld.sock
port                           = 3306
basedir                        = /usr
datadir                        = /var/lib/mysql
tmpdir                         = /dev/shm
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_520_ci

# ----------------------------------------------------------------------
# 2. Skalabilitas Koneksi & Thread Pool Architecture
# ----------------------------------------------------------------------
max_connections                = 4000
max_user_connections           = 3800
back_log                       = 1024
thread_handling                = pool-of-threads
thread_pool_size               = 16
thread_pool_max_threads        = 1000
thread_pool_idle_timeout       = 60
interactive_timeout            = 300
wait_timeout                   = 60

# ----------------------------------------------------------------------
# 3. Alokasi Memori Per-Koneksi (Buffers Lokal)
# ----------------------------------------------------------------------
sort_buffer_size               = 2M
join_buffer_size               = 2M
read_buffer_size               = 1M
read_rnd_buffer_size           = 2M
tmp_table_size                 = 64M
max_heap_table_size            = 64M

# ----------------------------------------------------------------------
# 4. Engine Storage InnoDB: Buffer Pool & I/O Subsystem (Host 64GB RAM)
# ----------------------------------------------------------------------
default_storage_engine         = InnoDB
innodb_buffer_pool_size        = 48G
innodb_buffer_pool_instances  = 8
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup  = 1

# Redo Logging & Flusing Durability (ACID vs Performance)
innodb_log_file_size           = 4G
innodb_log_files_in_group      = 2
innodb_log_buffer_size         = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method            = O_DIRECT

# Disk I/O Concurrency (Disesuaikan untuk Enterprise NVMe RAID 10)
innodb_io_capacity             = 15000
innodb_io_capacity_max         = 30000
innodb_read_io_threads         = 8
innodb_write_io_threads        = 8
innodb_page_cleaners           = 8
innodb_purge_threads           = 4

# Optimasi Mutex & Table Space
innodb_file_per_table          = 1
innodb_open_files              = 20000
table_open_cache               = 10000
table_definition_cache         = 4000

# ----------------------------------------------------------------------
# 5. Binary Logging & Replikasi Global Transaction ID (GTID)
# ----------------------------------------------------------------------
server_id                      = 101
log_bin                        = /var/log/mysql/mariadb-bin
log_bin_index                  = /var/log/mysql/mariadb-bin.index
binlog_format                  = ROW
expire_logs_days               = 7
max_binlog_size                = 1G
sync_binlog                    = 1
gtid_strict_mode               = 1
log_slave_updates              = 1

[!WARNING] Nilai innodb_flush_log_at_trx_commit = 2 menulis redo log ke OS file cache setiap transaksi dan melakukan flush ke disk fisik setiap 1 detik. Jika server crash mendadak akibat kegagalan listrik, data transaksi 1 detik terakhir dapat hilang (RPO = 1 detik). Jika integritas absolut anti-kehilangan data diwajibkan untuk sektor perbankan, ganti nilai ini menjadi 1 (Full ACID compliance).

Restart MariaDB untuk memvalidasi konfigurasi:

sudo systemctl restart mariadb
sudo systemctl status mariadb

Langkah 3: Setup Replikasi Master-Slave Semi-Sinkron Berbasis GTID

Replikasi asinkron tradisional rentan terhadap kehilangan data jika master padam sebelum binlog terkirim. Gunakan replikasi Semi-Synchronous dengan GTID (Global Transaction Identifier).

[ Client Write ] ──> (Master) ───[ Redo Log Local Commit ]
                         │
                         ├─── Binlog Sync via Network ───┐
                         │                               ▼
                         │                       (Slave Engine)
                         │                               │
                         │   [ ACK Packet: Log Written ] │
                         │ <─────────────────────────────┘
                         ▼
             [ Return OK to Client ]

Pada Server Master (Node A - IP: 10.0.10.101):

Masuk ke MariaDB root CLI:

-- 1. Pasang plugin Semi-Sync Master
INSTALL SONAME 'semisync_master';
SET GLOBAL rpl_semi_sync_master_enabled = ON;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- Fallback ke asinkron jika slave tidak merespon dalam 1 detik

-- 2. Buat akun replikasi dengan otentikasi aman
CREATE USER 'repl_agent'@'10.0.10.%' IDENTIFIED BY 'KredensialReplikasiSangatKuat_99!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_agent'@'10.0.10.%';
FLUSH PRIVILEGES;

-- 3. Periksa status posisi GTID Master saat ini
SELECT @@GLOBAL.gtid_binlog_pos;

Pada Server Slave (Node B - IP: 10.0.10.102):

Buka /etc/mysql/mariadb.conf.d/50-server.cnf di Slave, pastikan nilai unik:

server_id          = 102
read_only          = 1
log_slave_updates  = 1

Restart MariaDB di Slave, lalu konfigurasi replikasi secara dinamis via SQL CLI:

-- 1. Pasang plugin Semi-Sync Slave
INSTALL SONAME 'semisync_slave';
SET GLOBAL rpl_semi_sync_slave_enabled = ON;

-- 2. Hentikan slave thread lama (jika ada)
STOP SLAVE;

-- 3. Hubungkan ke Master menggunakan Master Current GTID Pos
CHANGE MASTER 'master_cluster' TO
  MASTER_HOST='10.0.10.101',
  MASTER_PORT=3306,
  MASTER_USER='repl_agent',
  MASTER_PASSWORD='KredensialReplikasiSangatKuat_99!',
  MASTER_USE_GTID=slave_pos,
  MASTER_CONNECT_RETRY=5;

-- 4. Jalankan Thread Replikasi
START SLAVE 'master_cluster';

-- 5. Verifikasi Keberhasilan Jalur Replikasi
SHOW SLAVE 'master_cluster' STATUS\G

Parameter Kunci yang Wajib Divalidasi pada Output:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0 (Menandakan replikasi sinkron tanpa delay)

Langkah 4: Implementasi ProxySQL untuk Read/Write Splitting Otomatis

Daripada mengotori logika kode backend web dengan koneksi multi-host, tempatkan ProxySQL di depan cluster. ProxySQL mengarahkan SELECT ke kumpulan node Slave dan kueri modifikasi (INSERT/UPDATE/DELETE) ke node Master secara transparan.

Pasang ProxySQL di server gateway/aplikasi:

sudo apt-get install -y proxysql
sudo systemctl start proxysql

Akses antarmuka administratif ProxySQL (Port default 6032, password default: admin):

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> '

Jalankan provisioning klaster berikut di dalam ProxySQL Admin:

-- 1. Definisikan Hostgroups (Hostgroup 10: Master-Writer, Hostgroup 20: Slaves-Reader)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) 
VALUES 
  (10, '10.0.10.101', 3306, 1000, 2000),
  (20, '10.0.10.102', 3306, 1000, 2000);

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

-- 2. Daftarkan User Database Aplikasi yang Akan Terhubung Lewat Proxy
INSERT INTO mysql_users (username, password, default_hostgroup) 
VALUES ('app_production', 'PasswordAplikasiSuperKetat_88!', 10);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- 3. Definisikan Routing Rules (Query Rules) Berbasis Regex
-- Rule 1: Kunci semua transaksi kueri FOR UPDATE ke Master
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10, 1);

-- Rule 2: Arahkan seluruh pembacaan SELECT standar ke Hostgroup Reader (Slaves)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT', 20, 1);

-- Rule 3: Seluruh query mutasi (INSERT, UPDATE, DELETE, CREATE) jatuh ke Hostgroup Master
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (3, 1, '.*', 10, 1);

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

Langkah 5: Strategi Sharding Horizontal (Partitioning & Sharded Cluster)

Ketika volume data pada tabel transaksi tunggal melampaui 500 juta baris (skala multi-terabyte), B-Tree index leaf pages tidak lagi muat seluruhnya di dalam RAM (Buffer Pool). Pada kondisi ini, bahkan partisi tabel native MariaDB tidak mencukupi; Anda memerlukan Application-Level Sharding menggunakan algoritma Consistent Hashing atau Key-Based Sharding.

Berikut implementasi Sharding Connection Manager kelas produksi dalam bahasa Go yang membagi trafik secara horizontal ke berbagai instans MariaDB independen secara deterministik:

package main

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/binary"
	"fmt"
	"log"
	"time"

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

// ShardManager mengelola koneksi database pool terdistribusi
type ShardManager struct {
	shards []*sql.DB
	count  uint32
}

// NewShardManager menginisialisasi koneksi ke seluruh node shard independen
func NewShardManager(dsns []string) (*ShardManager, error) {
	var pools []*sql.DB
	for _, dsn := range dsns {
		db, err := sql.Open("mysql", dsn)
		if err != nil {
			return nil, fmt.Errorf("gagal menginisialisasi connection pool: %w", err)
		}

		// Parameter Connection Pool Level Aplikasi
		db.SetMaxOpenConns(200)
		db.SetMaxIdleConns(50)
		db.SetConnMaxLifetime(10 * time.Minute)
		db.SetConnMaxIdleTime(2 * time.Minute)

		ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
		defer cancel()

		if err := db.PingContext(ctx); err != nil {
			return nil, fmt.Errorf("node shard tidak merespon: %w", err)
		}

		pools = append(pools, db)
	}

	return &ShardManager{
		shards: pools,
		count:  uint32(len(pools)),
	}, nil
}

// GetShardIndex menghitung indeks database tujuan berdasarkan Sharding Key (MurmurHash/SHA256)
func (sm *ShardManager) GetShardIndex(shardingKey string) uint32 {
	hasher := sha256.New()
	hasher.Write([]byte(shardingKey))
	hashBytes := hasher.Sum(nil)
	// Ekstrak 4 byte pertama sebagai integer untuk operasi modulo
	hashInt := binary.BigEndian.Uint32(hashBytes[:4])
	return hashInt % sm.count
}

// ResolveNode mengembalikan koneksi pool spesifik sesuai identitas user / tenant
func (sm *ShardManager) ResolveNode(shardingKey string) *sql.DB {
	index := sm.GetShardIndex(shardingKey)
	return sm.shards[index]
}

func main() {
	// Daftar DSN Master untuk tiap Cluster Shard
	shardEndpoints := []string{
		"app_user:StrongPass_1@tcp(10.0.20.11:3306)/tenant_db?charset=utf8mb4&parseTime=true",
		"app_user:StrongPass_2@tcp(10.0.20.12:3306)/tenant_db?charset=utf8mb4&parseTime=true",
		"app_user:StrongPass_3@tcp(10.0.20.13:3306)/tenant_db?charset=utf8mb4&parseTime=true",
		"app_user:StrongPass_4@tcp(10.0.20.14:3306)/tenant_db?charset=utf8mb4&parseTime=true",
	}

	manager, err := NewShardManager(shardEndpoints)
	if err != nil {
		log.Fatalf("Fatal error inisialisasi sharding: %v", err)
	}

	// Simulasi eksekusi kueri terarah berdasarkan Customer UUID
	customerUUID := "usr_c920f124-7b83-4a12-881c-99d81d2aefaa"
	targetDB := manager.ResolveNode(customerUUID)

	var totalSpending float64
	query := "SELECT COALESCE(SUM(total_amount), 0) FROM customer_orders WHERE customer_uuid = ?"
	err = targetDB.QueryRow(query, customerUUID).Scan(&totalSpending)
	if err != nil {
		log.Fatalf("Kueri eksekusi pada shard gagal: %v", err)
	}

	fmt.Printf("Total belanja untuk akun %s pada shard target: $%.2f\n", customerUUID, totalSpending)
}

6. Bedah Kode & Penjelasan Detail Baris Kunci

Mari kita bedah secara komprehensif parameter krusial dari konfigurasi my.cnf dan arsitektur di atas:

1. innodb_buffer_pool_size = 48G:

   • Fungsi: Mendefinisikan ukuran area memori RAM utama tempat InnoDB menyimpan cache struktur index tree dan data halaman tabel.
   • Formula Standard: Ditetapkan pada angka 70% hingga 80% total memori host fisik pada server yang didedikasikan secara eksklusif untuk MariaDB.
   • Dampak Fatal: Jika disetel terlalu besar (misal: 62 GB pada server 64 GB), sistem operasi akan kehabisan RAM untuk kernel dan thread lokal, memicu Linux OOM Killer yang akan mematikan proses mysqld seketika saat traffic tinggi.

2. innodb_buffer_pool_instances = 8:

   • Fungsi: Memecah buffer pool utama menjadi 8 wilayah memori independen.
   • Mengapa Dibutuhkan: Pada arsitektur multi-core CPU dengan konkurensi tinggi, satu buffer pool raksasa akan menimbulkan perebutan kunci internal (mutex contention) saat ratusan thread mencoba membaca/menulis halaman secara bersamaan. Dengan 8 instans, thread mengakses latch terpisah, meminimalisir waktu tunggu CPU stall.

3. thread_handling = pool-of-threads & thread_pool_size = 16:

   • Fungsi: Mengubah model konkurensi dari tradisional one-thread-per-connection menjadi arsitektur thread pool dinamis MariaDB.
   • Rekomendasi: Nilai thread_pool_size diselaraskan dengan jumlah core CPU fisik host. Ini mencegah CPU melakukan jutaan context switches yang sia-sia di kernel scheduler ketika 3.000 klien web terhubung bersamaan.

4. innodb_flush_method = O_DIRECT:

   • Fungsi: Memerintahkan MariaDB untuk mengabaikan OS Page Cache (Filesystem Cache) saat menulis berkas data InnoDB ke disk, langsung membaca/menulis ke blok hardware I/O.
   • Dampak: Mencegah fenomena Double Buffering (di mana halaman data disimpan dua kali: satu kali di Buffer Pool MariaDB dan satu kali di RAM OS Linux), membebaskan gigabyte memori nyata untuk sistem.

5. innodb_io_capacity = 15000 & innodb_io_capacity_max = 30000:

   • Fungsi: Mengontrol batas throughput IOPS yang diizinkan untuk proses latar belakang InnoDB saat melakukan flushing halaman kotor ke NVMe storage.
   • Dampak: Konfigurasi default (200 IOPS) dirancang untuk harddisk putar mekanik (HDD). Pada SSD/NVMe modern, menetapkan nilai rendah akan mengakibatkan penumpukan dirty pages di RAM yang memicu kondisi darurat Sync Flush Stall (seluruh kueri write dibekukan sementara).

7. Skenario Produksi & Studi Kasus Dunia Nyata

Kasus: Flash Sale E-Commerce Marketplace Nasional

• Trafik Masuk: 45.000 Requests Per Second (RPS) pada layer HTTP Nginx.
• Beban Database: 12.000 QPS penulisan (checkout dan update stok) dan 65.000 QPS pembacaan katalog produk.

Kegagalan Sebelum Re-arsitektur

Server MariaDB Master tunggal mengalami load average melonjak hingga 140 pada server 32-core. Disk I/O Wait mencapai 85%. Nginx mulai memuntahkan error HTTP 502 Bad Gateway karena thread MariaDB macet menunggu kunci baris transaksi pada baris stok barang yang sama (hotspot row lock contention).

[Klien Melakukan Transaksi] ──> [Update Stock: WHERE product_id = 9999]
                                           │ (Row Locked!)
[Klien Lain Menunggu...]   ──> [Menunggu Baris 9999 Terbuka (Lock Wait)]
                                           │
                               [Antrean Mengular: Max Connections Tercapai (4000)]
                                           ▼
                              [CRASH: MariaDB Unresponsive]

Solusi & Langkah Remediasi Arsitektur

1. Atomic In-Memory Caching untuk Hotspots: Stok produk yang diperebutkan dipindahkan ke Redis Cluster menggunakan manipulasi atomik DECRBY. Kueri langsung ke tabel products di MariaDB Master ditiadakan sepenuhnya saat event berlangsung.

2. Kompensasi Transaksi Asinkron via Message Queue: Proses pembuatan order tidak lagi melakukan penulisan langsung secara serentak ke MariaDB. Server web Go memvalidasi payload dan menaruh event OrderCreated ke antrean RabbitMQ/Kafka.

3. Optimasi Table DDL & Indeks Partisi: Tabel transaksi dipecah (Range Partitioning) berdasarkan rentang mingguan menggunakan fitur partisi bawaan MariaDB. Partisi lawas yang tidak lagi aktif dimutasikan ke mode ROW_FORMAT=COMPRESSED untuk menghemat konsumsi Buffer Pool:

ALTER TABLE customer_orders 
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p_2024_w1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-08 00:00:00')),
    PARTITION p_2024_w2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-15 00:00:00')),
    PARTITION p_2024_w3 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-22 00:00:00')),
    PARTITION p_future  VALUES LESS THAN MAXVALUE
);

4. Hasil Pasca Remediasi:
   • Load average CPU stabil di angka 12 - 18 pada master.
   • Database Master menangani penulisan batch secara teratur dari worker antrean tanpa kegagalan lock wait.
   • Latensi query agregasi pembacaan turun menjadi 3.2 ms via ProxySQL read replicas.

8. Common Pitfalls, Anti-Patterns & Solusi

Anti-Pattern 1: Pagination Masif Menggunakan OFFSET Besar

Banyak developer web menulis fitur pagination tabel antarmuka pengguna seperti ini:

-- SALAH: Membaca dan membuang 500.000 baris sebelum mengambil 20 baris
SELECT id, title, created_at 
FROM articles 
ORDER BY created_at DESC 
LIMIT 500000, 20;

Akar Masalah: Engine InnoDB wajib memindai dan membaca 500.020 baris indeks dari disk, mengalokasikannya ke RAM, lalu membuang 500.000 baris pertama. Latensi kueri ini bisa mencapai lebih dari 8 detik!

Solusi Elegan: Keyset Pagination (Seek Method): Gunakan kursor nilai terakhir dari halaman sebelumnya (ID atau Timestamp):

-- BENAR: Menggunakan index b-tree traversal instan (0.001 detik)
SELECT id, title, created_at 
FROM articles 
WHERE created_at < '2024-01-20 14:22:10' 
ORDER BY created_at DESC 
LIMIT 20;

Anti-Pattern 2: Pemanggilan Fungsi pada Kolom yang Terindeks di Klausul WHERE

-- SALAH: Fungsi DATE() melumpuhkan pemakaian B-Tree Index (Full Table Scan)
SELECT id, total_amount 
FROM invoices 
WHERE DATE(created_at) = '2024-05-01';

Akar Masalah: MariaDB tidak dapat memanfaatkan indeks B-Tree pada kolom created_at karena nilai kolom harus dievaluasi terlebih dahulu melalui fungsi baris per baris (sargability violation).

Solusi Elegan: Ubah Menjadi Rentang Terbuka:

-- BENAR: Tetap sargable, memanfaatkan index range scan presisi
SELECT id, total_amount 
FROM invoices 
WHERE created_at >= '2024-05-01 00:00:00' 
  AND created_at < '2024-05-02 00:00:00';

Anti-Pattern 3: Penggunaan SELECT * yang Membatalkan Covering Index

Meminta seluruh kolom memaksa MariaDB melakukan Clustered Key Lookup tambahan untuk mengambil data biner panjang (seperti TEXT atau BLOB), yang secara otomatis mematikan kemampuan InnoDB untuk melayani kueri langsung dari memori indeks sekunder (Secondary Index Only Scan).

[!TIP] Definisikan hanya kolom yang dieksekusi oleh API response (SELECT id, status, amount). Ini memungkinkan arsitektur basis data memanfaatkan fitur Index-Covered Queries secara 100% tanpa menyentuh tabel primer di disk.

9. Benchmark, Metrik Kinerja & Tuning Performa

Untuk menguji efektivitas penyesuaian parameter konfigurasi dan desain indeks, lakukan stresstest beban produksi menggunakan utilitas resmi Sysbench.

Langkah Pengujian Beban Konkurensi Tinggi

Pasang sysbench pada host terpisah (jangan menjalankan tool benchmark di server MariaDB target agar alokasi CPU tidak terdistorsi):

sudo apt-get install -y sysbench

Inisialisasi tabel pengujian dengan 10 juta baris data:

sysbench oltp_read_write \
  --mysql-host=10.0.10.101 \
  --mysql-port=3306 \
  --mysql-user=app_production \
  --mysql-password='PasswordAplikasiSuperKetat_88!' \
  --mysql-db=benchmark_db \
  --tables=10 \
  --table-size=1000000 \
  prepare

Jalankan simulasi beban transaksi ekstrim dengan 256 thread konkuren selama 10 menit:

sysbench oltp_read_write \
  --mysql-host=10.0.10.101 \
  --mysql-port=3306 \
  --mysql-user=app_production \
  --mysql-password='PasswordAplikasiSuperKetat_88!' \
  --mysql-db=benchmark_db \
  --tables=10 \
  --table-size=1000000 \
  --threads=256 \
  --time=600 \
  --report-interval=10 \
  --histogram=on \
  run

Interpretasi Hasil Metrik Kinerja

SQL statistics:
    queries performed:
        read:                            14820120
        write:                            4234320
        other:                            2117160
        total:                           21171600
    transactions:                        1058580 (1764.21 per sec.)
    queries:                             21171600 (35284.23 per sec.)

Latency (ms):
         min:                                    0.82
         avg:                                    7.25
         max:                                   48.12
         95th percentile:                        9.41
         99th percentile:                       14.28

Evaluasi Metrik Produksi Internal MariaDB

Jalankan kueri berikut di MariaDB untuk menghitung rasio efisiensi cache InnoDB pasca benchmark:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'

Hitung rasio Buffer Pool Hit Ratio dengan formula: $$\text{Hit Ratio} = \left(1 - \frac{\text{Innodb_buffer_pool_reads}}{\text{Innodb_buffer_pool_read_requests}}\right) \times 100$$

Jika angka hit ratio berada di atas 99.0%, berarti hampir seluruh siklus pembacaan data transaksi dilayani langsung oleh RAM tanpa memicu beban I/O blok disk fisik.

10. Checklist Penerapan Produksi & Kesimpulan

Sebelum meluncurkan pembaruan konfigurasi MariaDB ke lingkungan live production, tinjau checklist audit enterprise berikut:

Checklist Evaluasi Sistem & Kesiapan Produksi

• Alokasi Memori: innodb_buffer_pool_size disetel pada rentang 70-80% total RAM fisik server.
• Thread Architecture: thread_handling = pool-of-threads telah aktif dan diverifikasi via SHOW VARIABLES LIKE 'thread_handling';.
• Bypass OS Buffer: innodb_flush_method tervalidasi menggunakan O_DIRECT pada filesystem yang didukung (ext4/XFS).
• Ukuran Redo Log: Total innodb_log_file_size × innodb_log_files_in_group mencukupi untuk menampung minimal 1-2 jam penulisan data kontinu.
• Storage Tuning: Nilai innodb_io_capacity dan innodb_io_capacity_max disesuaikan dengan spek IOPS hardware NVMe RAID.
• Eliminasi Full Table Scan: Seluruh kueri pada Slow Query Log telah dianalisis dengan EXPLAIN FORMAT=JSON dan memiliki covering index.
• Replikasi GTID: Status replikasi Master-Slave semi-sinkron menunjukkan status IO/SQL thread running tanpa gap error.
• Monitoring & Health Check: Node Exporter, Prometheus mysqld_exporter, dan alerting latensi disk I/O telah terpasang aktif di Grafana dashboard.

Melalui perencanaan arsitektur yang cermat—mulai dari penyusunan struktur indeks yang efisien, penyelarasan alokasi memori InnoDB, penanganan konkurensi thread pool native, hingga implementasi replikasi semi-sinkron dan routing cerdas via ProxySQL—MariaDB mampu menopang puluhan ribu transaksi konkuren per detik secara stabil dengan latensi sub-milidetik, mengamankan fondasi infrastruktur web high-traffic Anda.