Hari-hari Kepo

MySQL Tuning: Panduan Lengkap Optimasi Performa MySQL (8.0/5.7/MariaDB)

02 Nov 2025 6 menit baca
MySQL Tuning: Panduan Lengkap Optimasi Performa MySQL (8.0/5.7/MariaDB)

Kamu punya server kuat,
RAM 32GB,
SSD NVMe,
tapi kok tetap:

 

❌ Query lambat?
❌ CPU 100% padahal trafik belum tinggi?
❌ User complain "loading terus"?
❌ Locking & deadlock sering muncul?

 

Jangan buru-buru upgrade server.

 

Masalahnya kemungkinan besar bukan hardware — tapi konfigurasi dan struktur query.

 

Inilah saatnya melakukan MySQL tuning secara profesional.

 

Di artikel ini, kamu akan pelajari strategi tuning MySQL dari nol hingga advance, meliputi:

 
  • 🔍 Diagnosa bottleneck
  • 🛠️ Konfigurasi my.cnf yang optimal
  • 💾 Optimasi query & index
  • 📊 Tools monitoring bawaan
  • ✅ Best practices 2025
 

Semua dengan contoh nyata, perintah siap pakai, dan tanpa jargon berlebihan.

 
 

🧭 Roadmap MySQL Tuning

TAHAP
TUJUAN
1. Diagnosa Awal
Temukan penyebab utama lemot
2. Konfigurasi Server
Atur memory, cache, thread
3. Optimasi Query
Perbaiki SQL boros
4. Index Strategy
Buat index yang benar
5. Schema & Storage
Struktur tabel & engine optimal
6. Monitoring
Pantau & cegah sebelum down
 

🔎 Langkah 1: Diagnosa – Temukan Penyebab Utama

Sebelum ubah apapun, cari dulu apa yang salah.

 

✅ Gunakan SHOW PROCESSLIST

Lihat query yang sedang jalan:

 
sql
 
SHOW FULL PROCESSLIST;
 

Cari:

  • Query dengan status Sending data, Copying to tmp table, Locked
  • Durasi (Time) lama (> 10 detik)
 

💡 Tips: Jalankan berkala atau gunakan pt-query-digest dari Percona Toolkit.

 
 

✅ Aktifkan Slow Query Log

Catat semua query yang lebih dari X detik.

 

Edit my.cnf:

ini
 
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
 

Restart MySQL:

bash
 
sudo systemctl restart mysql
 

Setelah 1 jam, analisis:

bash
 
mysqldumpslow /var/log/mysql/slow.log
 

Atau pakai pt-query-digest:

bash
 
pt-query-digest /var/log/mysql/slow.log > report.txt
 
 

🧩 Langkah 2: Konfigurasi Server (my.cnf / my.ini)

File konfigurasi utama: /etc/mysql/my.cnf atau /etc/my.cnf

 

Berikut nilai rekomendasi untuk server 16–32GB RAM:

 
ini
 
[mysqld]
# --- MEMORY & CACHE ---
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M
 
# --- THREAD & CONNECTION ---
max_connections = 500
thread_cache_size = 50
table_open_cache = 4000
table_definition_cache = 2000
 
# --- QUERY OPTIMIZER ---
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
tmp_table_size = 256M
max_heap_table_size = 256M
 
# --- LOGGING ---
slow_query_log = 1
long_query_time = 2
log_error = /var/log/mysql/error.log
 
# --- INNODB SPECIFIC ---
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_stats_on_metadata = OFF
 
# --- OPTIONAL: PERFORMANCE SCHEMA ---
performance_schema = ON
 

🔍 Penjelasan Kunci:

PARAMETER
FUNGSI
innodb_buffer_pool_size
Cache data & index — set70-80% RAMjika dedicated DB
innodb_log_file_size
Semakin besar, semakin jarang flush ke disk (tapi recovery lebih lama)
innodb_flush_log_at_trx_commit = 2
Balance antara speed & durability (cocok untuk non-banking)
tmp_table_size&max_heap_table_size
Batas tabel temporer di memori (hindari disk-based temp table)

⚠️ Setelah ubah innodb_log_file_size, hapus file ib_logfile* di folder data sebelum restart.

 
 

🧠 Langkah 3: Optimasi Query & Index

✅ Gunakan EXPLAIN untuk Analisis

Contoh:

sql
 
EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2025-01-01';
 

🔍 Cek:

  • type: hindari ALL (full scan)
  • key: pastikan pakai index
  • rows: semakin kecil, semakin baik
  • Extra: hindari Using temporary, Using filesort
 
 

✅ Strategi Index yang Efektif

1. Index Kolom WHERE & JOIN

sql
 
CREATE INDEX idx_orders_date ON orders(created_at);
CREATE INDEX idx_orders_user ON orders(user_id);
 

2. Composite Index untuk Multi-Kondisi

sql
 
-- Jika query sering filter by user + status + date
CREATE INDEX idx_orders_filter ON orders(user_id, status, created_at);
 

💡 Rule: Urutan kolom penting! Yang paling selektif dulu.

 

3. Covering Index – Hindari Lookup ke Tabel

sql
 
-- Jika query hanya butuh kolom ini
CREATE INDEX idx_covering ON orders(user_id, created_at, total);

→ InnoDB bisa ambil semua data dari index saja (index-only scan)

 
 

✅ Hindari Ini!

❌ SELECT * → ambil hanya kolom yang dibutuhkan
❌ Function di kolom WHERE → WHERE YEAR(created_at) = 2025 → tidak pakai index
✅ Ganti dengan: WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

 
 

💾 Langkah 4: Schema & Storage Optimization

✅ Pilih Engine yang Tepat

ENGINE
KAPAN DI PAKAI
InnoDB
Default — transaksional, foreign key, crash-safe
MyISAM
Hanya untuk read-heavy, statik (tidak direkomendasikan)
Memory
Temporary lookup table (data hilang saat restart)

Pastikan semua tabel pakai InnoDB:

sql
 
SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = 'your_db' AND engine != 'InnoDB';
 
 

✅ Partisi Tabel Besar

Untuk tabel > 10 juta baris.

Contoh: partisi by bulan

sql
 
CREATE TABLE sales (
id INT AUTO_INCREMENT,
amount DECIMAL(10,2),
sale_date DATE,
PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE COLUMNS(sale_date) (
PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
PARTITION p202502 VALUES LESS THAN ('2025-03-01')
);
 

Manfaat:

  • Query lebih cepat (partition pruning)
  • Maintenance lebih ringan (hapus data lama: DROP PARTITION)
 
 

🔐 Langkah 5: Kendali Concurrency & Locking

✅ Kurangi Deadlock

  • Minimalkan transaksi panjang
  • Akses tabel dalam urutan yang sama
  • Gunakan FOR UPDATE hanya jika perlu
 

✅ Monitor Lock

sql
 
-- Cek locking aktif
SELECT * FROM performance_schema.data_locks;

Atau di MySQL 5.7+:

sql
 
SHOW ENGINE INNODB STATUS\G

→ Cari bagian TRANSACTIONS dan LATEST DETECTED DEADLOCK

 
 

📈 Langkah 6: Monitoring & Preventif

✅ Gunakan Performance Schema

Aktifkan dan pantau aktivitas real-time:

 
sql
 
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
 

Contoh query:

sql
 
-- Top 10 query paling boros
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
 
 

✅ Tools Eksternal (Gratis)

TOOL
FUNGSI
phpMyAdmin / Adminer
GUI dasar
MySQL Workbench
Profiling, ERD, tuning
Percona Monitoring and Management (PMM)
Dashboard grafana + alert
Prometheus + Grafana + mysqld_exporter
Monitoring custom
 

🏁 Penutup: Tuning Itu Proses, Bukan Sekali Jalan

MySQL yang cepat bukan datang dari:

  • Server mahal
  • SSD tercepat
  • Atau versi terbaru
 

Tapi dari:

  • 🔍 Diagnosa yang tepat
  • 🛠️ Konfigurasi yang bijak
  • 📊 Monitoring yang konsisten
  • ✅ Budaya optimasi berkelanjutan
 

Ingat:

“Database yang sehat adalah database yang diam.
Tidak ada yang complain.
Tidak ada yang panic.
Dan itu hasil dari kerja diam-diam di balik layar.”

 

Mulai hari ini:

  1. Aktifkan slow query log
  2. Jalankan EXPLAIN untuk 3 query terlambat
  3. Optimasi buffer pool
  4. Dan buat jadwal review bulanan
 

Karena performa bukan keberuntungan — tapi disiplin.

BAGIKAN WhatsApp Facebook X Telegram

Baca Juga

Sebelumnya
Advanced Tuning Oracle Database: Strategi Pro untuk Performa Maksimal (19c/21c)

Komentar

2
ZAP 09 Aug 2026 14:05
Zaproxy dolore alias impedit expedita quisquam.
ZAP 09 Aug 2026 14:05
Zaproxy dolore alias impedit expedita quisquam.

Tinggalkan Komentar

Kembali ke Blog