Belajar Database Administrator - Performance Tuning & Query Optimization

#Pendahuluan

Backup (episode 5) melindungi data; tuning menjaga data itu tetap cepat. Query lambat adalah keluhan nomor satu yang diterima DBA — dan kabar baiknya, hampir semua kasus bisa dipecahkan dengan metode, bukan tebakan. Kuncinya satu: jangan pernah menebak; baca rencana eksekusi.

PostgreSQL dan MySQL menyediakan alat baca rencana eksekusi: EXPLAIN. Episode ini mengajarkan kalian membaca plan itu, memahami angka-angka penting, dan mengikuti alur tuning yang terbukti: dari menemukan query lambat, menganalisis plan, menambah index/parameter yang tepat, sampai memverifikasi perbaikan.

#Membaca EXPLAIN

EXPLAIN menunjukkan rencana eksekusi tanpa menjalankan query; EXPLAIN ANALYZE menjalankan query sekaligus menampilkan waktu nyata:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.email, o.total_cents
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

Output khas sebelum ada index yang tepat:

Sort  (cost=2500.12..2505.30 rows=2072 width=40)
  Sort Key: o.created_at DESC
  ->  Nested Loop  (cost=0.00..2400.00 rows=2072 width=40)
        ->  Seq Scan on orders o  (cost=0.00..1600.00 rows=2072 ...)
              Filter: (status = 'pending'::text)
        ->  Index Scan using users_pkey on users u
              Index Cond: (id = o.user_id)

Perhatikan kata kunci Seq Scan pada tabel besar — itu tanda bahaya yang artinya database memindai semua baris orders. Ini juga alasan mengapa query ini lambat di produksi.

#Operator Rencana yang Wajib Dikenali

Kunci membaca: ikuti alur dari bawah ke atas; perhatikan rows= (estimasi) vs baris sebenarnya — jika estimasi meleset jauh dari kenyataan, masalahnya sering statistik usang (ANALYZE belum jalan) atau parameter planner.

#Studi Kasus: Turun dari 40 Detik ke Milidetik

Query di atas menjalankan Seq Scan pada orders (2 juta baris). Urutan perbaikannya:

Langkah 1 — buat index yang sesuai pola query (dari pelajaran episode 4: compound (status, created_at)):

CREATE INDEX idx_orders_status_created
    ON orders (status, created_at DESC);

Langkah 2 — analisis ulang:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, u.email, o.total_cents
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

Index Scan using idx_orders_status_created on orders o
  Index Cond: (status = 'pending'::text)
  ->  Nested Loop  (cost=...) rows=2072 ...

Perhatikan: Seq Scan berubah menjadi Index Scan, dan Sort hilang karena index sudah menyediakan urutan created_at DESC. Waktu eksekusi turun dari puluhan detik ke beberapa milidetik — hanya dengan satu index yang pas.

Langkah 3 — jika masih lambat, cek work_mem: hash join dan sort besar memakai work_mem per operasi. Jika plan menunjukkan "spilled to disk", naikkan:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders
GROUP BY user_id;

GroupAggregate ... Sort Method: external merge  Disk: 1400kB

Sort Method: external merge Disk berarti sort terlempar ke disk — naikkan work_mem di postgresql.conf (ingat aturan episode 3: nilai dikali operasi paralel).

#Parameter Buffer: Mana yang Berpengaruh

Tiga parameter yang paling sering diutak-atik DBA, beserta panduannya:

Parameter lain seperti max_parallel_workers_per_gather hanya membantu query paralel tertentu. Prinsip tuning yang jujur: ubah satu parameter, ukur, baru ubah yang lain. Mengubah lima parameter sekaligus membuat kalian tidak tahu mana yang bekerja.

#Alur Tuning yang Terbukti

Ini runbook yang bisa kalian pakai untuk query lambat apa pun:

1. Temukan query-nya — lewat log slow query, pg_stat_statements, atau keluhan (episode 7).
2. Baca plan — EXPLAIN ANALYZE; catat operator dominan dan rows estimasi vs nyata.
3. Perbaiki yang murah dulu — index yang pas, join yang salah arah, statistik (jalankan ANALYZE).
4. Geser parameter hanya jika terbukti — ada disk spill? baru sentuh work_mem.
5. Verifikasi — ukur ulang waktu, buffer, dan beban server sebelum/ sesudah.
6. Dokumentasikan — simpan query, plan, dan keputusan; ini jadi bekal incident di episode 20.

#Pitfall Umum

1. EXPLAIN tanpa ANALYZE untuk menilai performa: rencana bisa terlihat sama untuk query yang sangat lambat — selalu pakai EXPLAIN ANALYZE saat menilai waktu nyata.
2. Index berlebihan untuk mengejar semua query: setiap index memperlambat write; fokus pada query yang benar-benar sering.
3. Menabrak work_mem terlalu besar: dialokasikan per operasi per koneksi; di sistem 200 koneksi, nilai gila bisa menghabiskan RAM.
4. Melupakan ANALYZE setelah load besar: statistik usang = rencana buruk; jadwalkan ANALYZE otomatis via autovacuum.
5. Mengubah banyak parameter sekaligus: tak bisa diukur efeknya — disiplin satu-per-satu.

#Penutup

Inti yang harus dibawa pulang:

• EXPLAIN ANALYZE adalah mata DBA: baca operator (Seq Scan vs Index Scan), rows, dan tanda disk spill.
• Satu index compound yang tepat bisa mengubah sequential scan + sort menjadi index scan tanpa sort.
• Parameter berpengaruh: shared_buffers, effective_cache_size, work_mem — ubah dengan bukti, bukan tebakan.
• Alur tuning: temukan → baca plan → perbaiki murah → geser parameter → verifikasi → dokumentasikan.

Di episode 7 selanjutnya kita membangun monitoring & alerting — metrik yang harus dipantau (latency, throughput, koneksi, I/O), memanfaatkan slow query log dan pg_stat_statements, serta membangun dashboard Prometheus + Grafana dan alerting pganalyze. Data tuning kalian butuh data — dan monitoring adalah sumbernya. Sampai jumpa di episode 7!