Diagnosa Cepat dan Isolasi Saat Insiden Berlangsung

Saat lonjakan beban query memicu saturasi CPU atau lonjakan koneksi (connection pool exhaustion), tindakan pertama bukan langsung merancang indeks permanen, melainkan mengidentifikasi dan mengisolasi query runaway agar database tidak runtuh.

1. Identifikasi Query Penyebab Bottleneck

Gunakan sistem katalog bawaan untuk menemukan query terlama yang memblokir worker:

-- PostgreSQL: Cek query aktif yang berjalan lebih dari 10 detik
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' 
  AND (now() - pg_stat_activity.query_start) > interval '10 seconds'
ORDER BY duration DESC;

-- MySQL: Cek processlist aktif yang memakan waktu lama
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 10
ORDER BY time DESC;

2. Isolasi Runaway Queries

Hentikan query bermasalah secara selektif sebelum memodifikasi skema:

-- PostgreSQL: Batalkan satu query spesifik tanpa memutus koneksi klien
SELECT pg_cancel_backend(<PID>);
-- Paksa pemutusan koneksi jika query menolak terminasi biasa
SELECT pg_terminate_backend(<PID>);

-- MySQL: Hentikan thread query
KILL QUERY <ID>;

Cegah query serupa menumpuk kembali selama masa triage dengan memberlakukan statement_timeout sementara di level session atau role:

-- PostgreSQL: Terapkan batas waktu eksekusi query darurat (5 detik)
ALTER ROLE api_user SET statement_timeout = '5000';

-- MySQL: Terapkan batas eksekusi maksimum (satuan milidetik)
SET GLOBAL max_execution_time = 5000;

Trik Indexing Darurat Tanpa Downtime

Menjalankan CREATE INDEX standar akan mengambil kunci eksklusif (exclusive lock) yang memblokir operasi WRITE, memperburuk status antrean transaksi. Gunakan operasi non-blocking.

1. PostgreSQL: CREATE INDEX CONCURRENTLY

Opsi CONCURRENTLY membutuhkan dua kali pemindaian tabel dan menunggu transaksi yang sedang berjalan selesai, tetapi tidak memblokir perintah INSERT, UPDATE, atau DELETE.

-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at);

2. MySQL: Online DDL via ALGORITHM=INPLACE

Pada InnoDB, manfaatkan Online DDL untuk mengizinkan operasi DML paralel selama pembuatan indeks:

-- MySQL
ALTER TABLE orders 
ADD INDEX idx_orders_status_created_at (status, created_at),
ALGORITHM=INPLACE, LOCK=NONE;
Penting: CREATE INDEX CONCURRENTLY di PostgreSQL tidak bisa dijalankan di dalam blok transaksi (BEGIN ... COMMIT). Pastikan autocommit aktif pada klien eksekusi.

Trik Efisiensi I/O: Partial Index untuk Dataset Aktif

Membangun indeks pada tabel berukuran ratusan juta baris di tengah insiden berisiko memperparah I/O bottleneck. Trik mitigasi darurat tercepat adalah membuat Partial Index (indeks kondisional) yang hanya mencakup baris data aktif penahan beban saat itu.

Jika lonjakan query hanya menyasar pesanan berstatus pending atau data 7 hari terakhir:

-- PostgreSQL: Membangun indeks hanya untuk data aktif
CREATE INDEX CONCURRENTLY idx_orders_pending_urgent
ON orders (created_at)
WHERE status = 'pending';

Ukuran indeks ini jauh lebih kecil, waktu build selesai dalam hitungan detik, dan penggunaan buffer cache sangat minimal dibandingkan indeks penuh.

Verifikasi Query Plan dengan EXPLAIN ANALYZE

Sebelum menganggap insiden selesai, verifikasi bahwa query optimizer menggunakan indeks baru tanpa melakukan Sequential Scan / Full Table Scan.

-- PostgreSQL
EXPLAIN ANALYZE
SELECT id, user_id FROM orders 
WHERE status = 'pending' AND created_at >= NOW() - INTERVAL '1 day';

Periksa output eksekusi: pastikan node eksekusi menunjukkan Index Scan atau Bitmap Index Scan, bukan Seq Scan, dan nilai actual time sesuai target latensi SLA aplikasi.

Kriteria Rollback dan Pemulihan Indeks Gagal

Jika metrik I/O disk mencapai 100% atau latensi replikasi (replication lag) melonjak tajam saat pembuatan indeks berlangsung, segera hentikan proses:

  1. Batalkan query pembangunan indeks menggunakan pg_cancel_backend(pid) atau KILL QUERY <id>.
  2. Di PostgreSQL, pembatalan CONCURRENTLY meninggalkan indeks berstatus tidak valid (invalid index). Cek dan hapus indeks tersebut agar tidak membebani operasi penulisan selanjutnya:
-- PostgreSQL: Cek indeks invalid
SELECT indisvalid, indexrelid::regclass 
FROM pg_index 
WHERE indisvalid = false;

-- Hapus indeks invalid tanpa memblokir pembacaan
DROP INDEX CONCURRENTLY idx_orders_status_created_at;