Diagnosis Pertanyaan Perlahan PostgreSQL dan Amalan Rollback Selamat untuk Operasi SRE

Senario

Semasa operasi SRE rutin, pangkalan data PostgreSQL tiba-tiba menunjukkan jumlah pertanyaan perlahan yang tinggi, menyebabkan peningkatan latensi aplikasi dan potensi kegagalan berantai. Peranan: Jurutera SRE. Persekitaran: PostgreSQL 15 yang digunakan dalam Kubernetes melalui StatefulSet.

Gejala

• Kadar ralat aplikasi meningkat (cth., HTTP 5xx)
• Masa respons pertanyaan melonjak dari 50ms ke lebih 5s
• Penggunaan CPU pangkalan data hampir 100%
• Kehabisan kolam sambungan

Diagnosis

1. Dayakan Log Pertanyaan Perlahan

-- Log sementara pertanyaan melebihi 1 saat
ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

2. Kenal pasti Pertanyaan yang Berjalan

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT LIKE '%pg_stat%'
ORDER BY duration DESC LIMIT 5;

3. Kesan Sekatan dan Kunci

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.granted;

4. Analisis dengan EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM orders WHERE customer_id = 12345;

Cari imbasan jadual penuh, indeks yang hilang, atau susunan sambungan yang tidak optimum.

Kawalan Risiko Semasa Diagnosis

• Elakkan membunuh sesi secara langsung (boleh menyebabkan rollback panjang).
• Guna pg_cancel_backend(pid) untuk membatalkan pertanyaan daripada pg_terminate_backend jika boleh.
• Jika penamatan perlu, catat ID transaksi dan sahkan tiada kunci baris kritikal dipegang.

Rollback Selamat

Jika isu disebabkan oleh perubahan skema terkini (cth., penciptaan indeks, pengubahsuaian pertanyaan), rollback seperti berikut:

1. Rollback Penciptaan Indeks

DROP INDEX IF EXISTS idx_orders_customer_id_new;

2. Rollback Perubahan DDL

Guna transaksi DDL jika disokong atau balikkan operasi secara manual.

ALTER TABLE orders DROP COLUMN IF EXISTS temp_column;

3. Rollback Kod Aplikasi

Kembali ke versi pertanyaan sebelumnya atau guna pg_hint_plan untuk memaksa pelan lama.

Pengesahan

• Pantau log pertanyaan perlahan: sahkan tiada pertanyaan melebihi ambang.
• Periksa metrik prestasi: CPU, IO, sambungan kembali ke garis dasar.
• Jalankan pertanyaan ujian:

SELECT * FROM orders WHERE customer_id = 12345; -- jangkaan <100ms

• Pemeriksaan kesihatan aplikasi: sahkan penurunan kadar ralat.

Bila Hantar Tiket OpsGlobal

• Punca utama tidak jelas; memerlukan analisis pakar.
• Isu berterusan selepas rollback; perlu penyahpepijatan mendalam (cth., isu VACUUM, konfigurasi).
• Masalah peringkat kluster: kependaman replikasi, kerosakan cakera.
• Operasi berisiko tinggi diperlukan (cth., pembaikan manual jadual sistem).

OpsGlobal menyediakan sokongan DBA jarak jauh 24/7 dengan masa respons 30 minit.