Belajar Database Administrator - Incident & Troubleshooting

#Pendahuluan

Semua yang kita bangun — HA, backup, monitoring, IaC — berujung pada satu momen: incident. Database produksi melambat, aplikasi error, halaman pembayaran macet, dan kalian yang dipanggil. Di momen itu, pengetahuan saja tidak cukup: butuh metode, kecepatan, dan mental yang tenang.

Kabar baiknya: incident database hampir selalu jatuh ke beberapa pola yang bisa dipelajari. Episode ini memetakan tiga pola paling umum (deadlock, lock contention, connection exhaustion) beserta alat diagnosanya, lalu menutup dengan budaya post-mortem yang mengubah kegagalan menjadi perbaikan.

#Prinsip Diagnosa: Gejala vs Akar Masalah

Aturan pertama DBA saat incident: jangan menyembuhkan gejala, temukan akarnya. Query lambat adalah gejala; penyebabnya bisa index hilang, lock menahan tabel, atau disk I/O penuh — tiga perlakuan berbeda.

Alur diagnosa yang disiplin:

1. Stabilkan: jika layanan sekarat, stabilkan dulu (misal batasi koneksi, pindah beban baca ke replica) — tanpa memperburuk.
2. Kumpulkan bukti: pg_stat_activity, pg_locks, log, dashboard episode 7.
3. Hipotesis: pilih penyebab paling mungkin berdasarkan bukti.
4. Uji kecil: satu tindakan, ukur dampak.
5. Selesaikan & verifikasi: hingga metrik kembali normal.
6. Dokumentasikan: sebelum pindah ke incident berikutnya.

Tool utama kalian di PostgreSQL:

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS durasi,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY durasi DESC;

Kolom wait_event_type adalah emas: Lock, IO, Client, CPU menunjukkan di mana proses sedang tersangkut — inilah kompas diagnosa pertama.

#Lock Contention: Tabel yang Terkunci

Konspirasi paling umum: query panjang memegang lock, query lain mengantre di belakangnya. Contoh nyata: ALTER TABLE atau VACUUM FULL menahan AccessExclusiveLock, sementara aplikasi menunggu SELECT.

Identifikasi pengantre:

SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY granted DESC;

Perhatikan granted = false — itu proses yang menunggu; yang granted = true dengan mode eksklusif adalah pemegang lock (tersangka utama). Langkah penyelesaian:

1. Identifikasi query pemegang lock (pg_stat_activity).
2. Apakah itu query sesat (episode 6) atau migration yang melanggar expand-contract (episode 17)?
3. Jika tidak bisa menunggu, batalkan proses dengan pg_terminate_backend(pid) — tindakan terakhir, bukan pertama.
4. Perbaiki akarnya: index, lock_timeout, atau migration expand-contract.

ALTER ROLE app_user SET lock_timeout = '5s';
ALTER ROLE app_user SET statement_timeout = '30s';

lock_timeout mencegah aplikasi menggantung tanpa batas saat antre lock — salah satu jaring pengaman termurah yang pernah ada.

#Deadlock: Dua Arah Saling Menunggu

Deadlock terjadi saat dua transaksi saling memegang resource yang dibutuhkan yang lain. PostgreSQL mendeteksi dan membatalkan salah satunya, lalu menulis log:

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 555;
         blocked by process 12346. Process 12346 waits for
         ShareLock on transaction 444; blocked by process 12345.

Penyebab paling umum di produksi: urutan update tidak konsisten. Transaksi A meng-update orders lalu users; transaksi B meng-update users lalu orders — suatu saat keduanya menabrak. Perbaikannya di level aplikasi: selalu update dalam urutan yang sama (misal berdasarkan id), atau persingkat transaksi (semakin singkat, semakin kecil jendela deadlock). Pencarian bukti historis: grep deadlock /var/log/postgresql/*.log — log adalah saksi terbaik.

#Connection Exhaustion: Habisnya Koneksi

Gejala klasik: aplikasi error "connection limit exceeded" / "remaining connection slots are reserved for superuser". Artinya max_connections tercapai. Penyebab biasanya bukan "traffic naik", melainkan:

1. Aplikasi membocorkan koneksi — tidak pernah menutup connection pool.
2. Query lambat menahan koneksi — tiap query lambat = koneksi tersita lama (pola episode 6).
3. Tanpa pooler — ingat episode 3: PostgreSQL satu koneksi satu proses.

Diagnosa cepat:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- idle in transaction = tersangka kebocoran transaksi
SELECT pid, state, now() - xact_start AS xact_dur
FROM pg_stat_activity
WHERE state = 'idle in transaction' ORDER BY xact_dur DESC;

Perhatikan idle in transaction — koneksi yang sudah selesai tapi transaksinya belum di-commit/rollback; ini klasik bocor dari kode aplikasi. Penyelesaian: fix aplikasi, tambah pooler (episode 3), dan jika darurat: pg_terminate_backend untuk koneksi idle-in-transaction tua.

#Runbook: Sahabat di Malam 3 Pagi

Incident bukan waktu untuk berimprovisasi — adalah waktu untuk mengeksekusi runbook yang sudah disiapkan. Runbook yang baik per incident:

• Gejala yang jelas (metrik apa yang abnormal).
• Langkah diagnosa nomor 1-5 (query apa yang dijalankan).
• Langkah penyelesaian per akar masalah.
• Esokasi: kapan harus memanggil siapa (vendor, atasan, tim aplikasi).
• Komunikasi: template status ke manajemen (jangan terlalu teknis).

Tulis runbook untuk tiga pola di atas, simpan di repositori (bukan di kepala), dan latih lewat drill (gaya episode 15). Saat incident nyata datang, kalian tinggal mengeksekusi dengan tenang.

#Post-Mortem: Kegagalan yang Menjadi Pelajaran

Setiap incident ditutup dengan post-mortem — tapi post-mortem yang benar bukan untuk mencari siapa yang salah, melainkan apa yang gagal di sistem sehingga manusia bisa salah. Budaya blameless ini yang membedakan tim matang.

Struktur post-mortem yang baik:

1. Timeline faktual: apa terjadi jam berapa, keputusan apa diambil.
2. Dampak: berapa lama, berapa banyak pengguna terdampak (angka, bukan drama).
3. Akar masalah: 5 why sampai ke penyebab sistemik.
4. Action items: perbaikan yang jelas — dan harus diselesaikan, bukan ditulis lalu dilupakan.
5. Kepemilikan & deadline tiap action item.

Dari pola incident kita: perbaikan yang sering lahir — index baru (episode 6), lock_timeout dipasang, migration dipisah (episode 17), monitoring ditambah (episode 7), runbook ditulis (episode ini). Setiap insiden menjadikan sistem lebih kuat — itulah siklus SRE yang sebenarnya.

#Pitfall Umum

1. Panik & restart seluruh server: restart menutup gejala tapi menghapus bukti — dan downtime bertambah. Diagnosa dulu, kecuali benar-benar darurat.
2. Mengabaikan wait_event: menebak tanpa melihat kolom ini seperti mengobati tanpa diagnosa.
3. pg_terminate_backend membabi buta: membunuh query yang tidak bersalah bisa membuat situasi lebih buruk — bidik yang jelas menunggu lock atau idle-in-transaction.
4. Post-mortem tanpa action item: dokumen tanpa tindak lanjut = kegagalan yang akan terulang.
5. Menyembunyikan incident: budaya takut salah membuat masalah disembunyikan — dan membesar. Laporkan, pelajari, perbaiki.

#Penutup

Inti yang harus dibawa pulang:

• Diagnosa sistematis: stabilkan → bukti (pg_stat_activity, pg_locks) → hipotesis → uji → verifikasi.
• Lock contention: cari granted = false, bidik pemegang lock; pasang lock_timeout.
• Deadlock: urutan update konsisten + transaksi singkat.
• Connection exhaustion: cek idle in transaction, pakai pooler, terminasi yang jelas bocor.
• Post-mortem blameless dengan action item bertanggung jawab — setiap incident menguatkan sistem.

Di episode 21 selanjutnya kita mendorong performansi ke level lanjutan: performance engineering lanjutan — lapisan cache in-memory, table partitioning, materialized views, dan tuning index tingkat tinggi, semuanya diuji dengan benchmark yang jujur. Sampai jumpa di episode 21!