[MARIADB] innodb tuning - fourslickz/notes GitHub Wiki

⚙️ MariaDB InnoDB Tuning — VM 16 Core / 32 GB RAM

🧭 Tujuan

Optimasi performa InnoDB buffer pool agar mengurangi Innodb_buffer_pool_wait_free (menunggu flush karena buffer pool penuh), meningkatkan efisiensi memori, dan mempercepat query.


💾 1. Buffer Pool Size

Kondisi Server Rekomendasi Catatan
Dedicated DB server innodb_buffer_pool_size = 24G ~75% total RAM
DB + App (shared) innodb_buffer_pool_size = 16G ~50% total RAM

🧠 Gunakan 60–75% RAM untuk buffer pool jika MariaDB adalah satu-satunya service utama.


🧩 2. Buffer Pool Instances

Buffer Pool Size Instances Ideal Contoh
1–4 GB 2 Kecil
8–16 GB 4 Sedang
24–64 GB 8 Besar (cocok untuk 24G)

Setiap instance bekerja paralel di multi-core. Hindari terlalu banyak karena menambah overhead memori.

innodb_buffer_pool_instances = 8

⚙️ 3. Disk I/O Capacity

Jenis Disk innodb_io_capacity innodb_io_capacity_max
HDD 200 400
SSD / NVMe 2000 4000

Untuk VM modern (SSD/NVMe) gunakan nilai tinggi agar flush halaman kotor bisa cepat.

innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

🔧 4. Parameter Pelengkap

innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_max_dirty_pages_pct = 75
innodb_lru_scan_depth = 2048

Penjelasan:

  • O_DIRECT → Hindari double buffering (RAM OS & MariaDB).
  • flush_neighbors=0 → Lebih efisien di SSD (tidak flush tetangga).
  • max_dirty_pages_pct=75 → Flush lebih dini, hindari penumpukan.
  • lru_scan_depth → Kontrol agresivitas pembersihan cache.

🧠 5. Contoh Konfigurasi Lengkap (/etc/mysql/my.cnf)

[mysqld]
# InnoDB Memory & Cache
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_max_dirty_pages_pct = 75
innodb_lru_scan_depth = 2048

# I/O Optimization
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Threads (opsional, untuk 16 core)
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4

# Log & Flush Settings
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 1

# Monitoring
innodb_monitor_enable = all

🔍 6. Verifikasi

Cek status buffer pool:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Jalankan dua kali (selang beberapa menit):

  • Jika Innodb_buffer_pool_wait_free tidak bertambah signifikan → konfigurasi sudah ideal ✅
  • Jika naik cepat → tambahkan innodb_buffer_pool_size atau optimalkan disk I/O.

📊 7. Tips Monitoring

Buat interval check sederhana:

watch -n 60 "mysql -e \"SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';\""

Atau gunakan Prometheus + Grafana untuk visualisasi tren buffer pool metrics.


🧩 8. Quick Summary

Parameter Nilai Ideal Fungsi
innodb_buffer_pool_size 24G Cache data & index
innodb_buffer_pool_instances 8 Parallel buffer access
innodb_io_capacity 2000 Flush rate
innodb_flush_method O_DIRECT Hindari double cache
innodb_flush_neighbors 0 Optimasi SSD
innodb_max_dirty_pages_pct 75 Flush balance
innodb_lru_scan_depth 2048 Cache cleaning depth