Mengatasi perlindungan wraparound ID transaksi (TXID)

Halaman ini menjelaskan hal yang dapat Anda lakukan saat database menjalankan program perlindungan Wraparound ID Transaksi di PostgreSQL. Hal ini ditunjukkan sebagai pesan ERROR, seperti berikut:

database is not accepting commands to avoid wraparound data loss in database
dbname.

Stop the postmaster and vacuum that database in single-user mode.

You might also need to commit or roll back old prepared transactions, or drop
stale replication slots.

Atau, pesan WARNING seperti berikut mungkin muncul:

database dbname must be vacuumed within 10985967 transactions.

To avoid a database shutdown, execute a database-wide VACUUM in that database.

Ringkasan langkah

  • Cari tahu database dan tabel mana yang menyebabkan wraparound.
  • Periksa apakah ada sesuatu yang menghambat (AUTO)VACUUM (misalnya, ID transaksi macet).
  • Ukur kecepatan AUTOVACUUM. Jika lambat, Anda dapat mencoba mempercepatnya (opsional).
  • Jalankan beberapa perintah VACUUM lagi secara manual jika diperlukan.
  • Cari tahu cara lain untuk mempercepat proses vacuum. Terkadang, cara tercepat adalah dengan menghapus tabel atau beberapa indeks.

Ada banyak rekomendasi nilai flag yang sengaja dibuat tidak akurat karena bergantung pada banyak parameter database. Baca dokumen yang ditautkan di akhir halaman untuk analisis yang lebih mendalam tentang topik ini.

Temukan database dan tabel yang menyebabkan wraparound

Menemukan database

Untuk mengetahui database mana yang berisi tabel penyebab wraparound, jalankan kueri berikut:

SELECT datname,
       age(datfrozenxid),
       2^31-1000000-age(datfrozenxid) as remaining
  FROM pg_database
 ORDER BY 3

Database dengan nilai remaining mendekati 0 lah yang menyebabkan masalah.

Menemukan tabel

Hubungkan ke database tersebut dan jalankan kueri berikut:

SELECT c.relnamespace::regnamespace as schema_name,
       c.relname as table_name,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age,
       2^31-1000000-greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as remaining
  FROM pg_class c
  LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
 WHERE c.relkind IN ('r', 'm')
 ORDER BY 4;

Kueri ini menampilkan tabel yang menyebabkan masalah.

Untuk tabel SEMENTARA

Jika schema_name dimulai dengan pg_temp_, maka satu-satunya cara untuk menyelesaikan masalah ini adalah dengan menghapus tabel tersebut. Hal ini karena PostgreSQL tidak mengizinkan Anda melakukan VACUUM kepada tabel sementara yang dibuat di sesi lain. Terkadang, Anda dapat melakukan vacuum kepada tabel yang ada di sana jika sesi tersebut terbuka dan dapat diakses. Namun, hal semacam ini jarang terjadi. Gunakan pernyataan SQL berikut untuk menghapus tabel sementara:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
DROP TABLE pg_temp_<N>.<tablename>;

Jika hanya itu masalahnya, maka autovacuum akan mengambil perubahan ini dan memindahkan datfrozenxid ke depan pg_databasedalam satu menit. Tindakan ini menyelesaikan masalah status baca saja pada perlindungan wraparound.

Tabel normal

Untuk tabel normal (yang tidak bersifat sementara), lanjutkan dengan langkah berikutnya di bawah ini untuk melihat apakah ada yang menghalangi proses penghapusan, apakah proses VACUUM berjalan cukup cepat, dan apakah tabel yang paling penting sedang menjalani proses vacuum.

Periksa ID transaksi yang macet

Ada beberapa kemungkinan alasan mengapa sistem bisa kehabisan ID transaksi. Salah satunya adalah karena begitu transaksi terlama yang sedang berjalan dimulai, PostgreSQL tidak dapat freeze (yaitu, menandai dapat dilihat oleh semua transaksi) satu pun ID transaksi yang dibuat setelahnya. Hal ini terjadi karena aturan kontrol konkurensi multiversi (MVCC). Dalam kasus yang ekstrem, transaksi semacam ini dapat memakan waktu lama sehingga tidak memungkinkan VACUUM untuk menghapus seluruh batas wraparound ID transaksi yang jumlahnya mencapai 2 miliar. Hal ini pun menyebabkan seluruh sistem berhenti menerima DML baru. Biasanya Anda juga melihat peringatan di file log yang bertuliskan WARNING: oldest xmin is far in the past.

Anda harus beralih ke pengoptimalan hanya setelah ID transaksi yang macet selesai diperbaiki.

Berikut empat kemungkinan penyebab terjadinya ID transaksi macet, beserta informasi tentang cara memitigasinya:

  • Transaksi yang berjalan lama: Identifikasi dan batalkan atau hentikan backend untuk berhenti memblokir vacuum.
  • Transaksi penyiapan usang: Roll back transaksi ini.
  • Slot replikasi yang ditinggalkan: Hapus slot yang ditinggalkan.
  • Transaksi yang berjalan lama pada replika, dengan hot_standby_feedback = on: Identitifikasi dan batalkan atau hentikan backend untuk berhenti memblokir vacuum.

Untuk skenario ini, kueri berikut menampilkan usia transaksi terlama dan jumlah transaksi yang tersisa hingga wraparound:

 WITH q AS (
SELECT
  (SELECT max(age(backend_xmin))
      FROM pg_stat_activity  WHERE state != 'idle' )       AS oldest_running_xact_age,
  (SELECT max(age(transaction)) FROM pg_prepared_xacts)    AS oldest_prepared_xact_age,
  (SELECT max(greatest(age(catalog_xmin),age(xmin))) FROM pg_replication_slots)        AS oldest_replication_slot_age,
  (SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS oldest_replica_xact_age
)
SELECT *,
       2^31 - oldest_running_xact_age AS oldest_running_xact_left,
       2^31 - oldest_prepared_xact_age AS oldest_prepared_xact_left,
       2^31 - oldest_replication_slot_age AS oldest_replication_slot_left,
       2^31 - oldest_replica_xact_age AS oldest_replica_xact_left
FROM q;

Kueri ini mungkin menampilkan salah satu nilai *_left yang dilaporkan mendekati atau kurang dari 1 juta dari wraparound. Nilai ini merupakan batas perlindungan wraparound protection saat PostgreSQL berhenti menerima perintah penulisan baru. Dalam hal ini, lihat Menghapus pemblokir VACUUM atau Menyesuaikan VACUUM.

Misalnya, kueri sebelumnya mungkin menampilkan:

┌─[ RECORD 1 ]─────────────────┬────────────┐
│ oldest_running_xact_age      │ 2146483655 │
│ oldest_prepared_xact_age     │ 2146483655 │
│ oldest_replication_slot_age  │ ¤          │
│ oldest_replica_xact_age      │ ¤          │
│ oldest_running_xact_left     │ 999993     │
│ oldest_prepared_xact_left    │ 999993     │
│ oldest_replication_slot_left │ ¤          │
│ oldest_replica_xact_left     │ ¤          │
└──────────────────────────────┴────────────┘

di mana oldest_running_xact_left dan oldest_prepared_xact_left berada dalam batas perlindungan wraparound 1 juta. Dalam kasus ini, Anda harus terlebih dahulu menghapus pemblokir agar proses VACUUM dapat dilanjutkan.

Menghapus pemblokir VACUUM

Transaksi jangka panjang

Pada kueri sebelumnya, jika oldest_running_xact sama dengan oldest_prepared_xact, buka bagian Transaksi penyiapan usang, karena versi terkini yang dijalankan juga mencakup transaksi yang telah disiapkan.

Sebagai pengguna postgres, Anda mungkin perlu menjalankan perintah berikut terlebih dahulu: 

GRANT pg_signal_backend TO postgres;

Jika transaksi yang melanggar aturan adalah milik salah satu pengguna sistem (dimulai dengan cloudsql...), Anda tidak dapat membatalkannya secara langsung. Anda harus memulai ulang database untuk membatalkannya.

Jika ingin mengidentifikasi kueri jangka panjang dan membatalkan atau menghentikannya guna membuka blokir vacuum, pertama-tama Anda harus memilih beberapa kueri terlama. Baris LIMIT 10 membantu menyesuaikan hasil di layar. Anda mungkin perlu mengulanginya setelah menyelesaikan kueri terlama yang sedang berjalan.

SELECT pid,
       age(backend_xid) AS age_in_xids,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       state,
       query
 FROM pg_stat_activity
 WHERE state != 'idle'
 ORDER BY 2 DESC
 LIMIT 10;

Jika age_in_xids kembali sebagai NULL, ini berarti transaksi tersebut belum diberi ID transaksi permanen dan dapat diabaikan dengan aman.

Batalkan kueri yang xids_left_to_wraparoundnya mendekati 1M.

Jika state adalah active, kueri dapat dibatalkan menggunakan SELECT pg_cancel_backend(pid);. Jika tidak, Anda harus menghentikan seluruh koneksi menggunakan SELECT pg_terminate_backend(pid);, di mana pid merupakan pid dari kueri sebelumnya

Transaksi penyiapan usang

Buatlah daftar semua transaksi yang telah disiapkan:

DB_NAME=> SELECT age(transaction),* FROM pg_prepared_xacts ;
┌─[ RECORD 1 ]┬───────────────────────────────┐
│ age         │ 2146483656                    │
│ transaction │ 2455493932                    │
│ gid         │ trx_id_pin                    │
│ prepared    │ 2021-03-03 16:54:07.923158+00 │
│ owner       │ postgres                      │
│ database    │ DB_NAME                       │
└─────────────┴───────────────────────────────┘

Roll back transaksi penyiapan usang paling lama dengan menggunakan gid dari kueri terakhir (dalam hal ini, trx_id_pin) sebagai ID transaksi:

ROLLBACK PREPARED trx_id_pin;

Atau, lakukan commit: 

COMMIT PREPARED trx_id_pin;

Lihat dokumentasi SQL ROLLBACK PREPARED untuk penjelasan selengkapnya.

Slot replikasi yang ditinggalkan

Jika slot replikasi ditinggalkan karena replika yang ada dihentikan, dijeda, atau bermasalah, Anda dapat menghapus replika dari gcloud atau konsol Google Cloud.

Pertama, pastikan replika tidak dinonaktifkan seperti yang dijelaskan dalam Mengelola replika baca. Aktifkan kembali replikanya jika sebelumnya dinonaktifkan. Jika lag masih tetap tinggi, hapus saja replikanya,

Slot replikasi dapat dilihat dalam tampilan sistem pg_replication_slots.

Kueri berikut mengambil informasi yang relevan:

SELECT *, age(xmin) AS age FROM pg_replication_slots;
┌─[ RECORD 1 ]────────┬─────────────────────────────────────────────────┐
│ slot_name           │ cloudsql_1_355b114b_9ff4_4bc3_ac88_6a80199bd738 │
│ plugin              │ ¤                                               │
│ slot_type           │ physical                                        │
│ datoid              │ ¤                                               │
│ database            │ ¤                                               │
│ active              │ t                                               │
│ active_pid          │ 1126                                            │
│ xmin                │ 2453745071                                      │
│ catalog_xmin        │ ¤                                               │
│ restart_lsn         │ C0/BEF7C2D0                                     │
│ confirmed_flush_lsn │ ¤                                               │
│ age                 │ 59                                              │
└─────────────────────┴─────────────────────────────────────────────────┘

Berdasarkan contoh di atas, nilai pg_replication_slots termasuk ke dalam kategori sehat (usia == 59). Jika usianya mendekati 2 miliar, Anda perlu menghapus slot tersebut. Tidak ada cara mudah untuk mengetahui replika mana yang dimaksud jika kueri menampilkan beberapa kumpulan data. Jadi, periksa semuanya untuk berjaga-jaga jika ada transaksi jangka panjang pada replika mana pun.

Transaksi jangka panjang pada replika

Periksa replika untuk transaksi terlama yang berjalan dengan mengatur hot_standby_feedback ke on dan menonaktifkannya pada replika.

Kolom backend_xmin dalam tampilan pg_stat_replication memiliki TXID terlama yang diperlukan pada replika.

Untuk melanjutkannya, hentikan kueri yang menahannya pada replika. Untuk mengetahui kueri mana yang menahannya, gunakan kueri tersebut dalam Transaksi jangka panjang. Namun, kali ini, jalankan transaksinya di replika.

Opsi lainnya adalah memulai ulang replika.

Mengonfigurasi VACUUM

Atur dua flag berikut:

  • autovacuum_vacuum_cost_delay = 0
  • autovacuum_work_mem = 1048576

Flag pertama menonaktifkan throttling disk untuk di-vacuum oleh PostgreSQL sehingga proses VACUUM dapat berjalan dengan kecepatan penuh. Secara default, autovacuum di-throttle agar tidak menggunakan seluruh IO disk pada server yang paling lambat.

Flag kedua, yaitu autovacuum_work_mem, mengurangi jumlah pembersihan indeks yang terlewat. Jika memungkinkan, nilainya harus cukup besar untuk menyimpan semua ID dari baris yang mati dalam tabel yang akan dibersihkan oleh VACUUM. Saat mengatur nilai tersebut, pertimbangkan bahwa ini adalah jumlah maksimum memori lokal yang dapat dialokasikan oleh setiap VACUUM yang sedang berjalan. Pastikan Anda tidak mengizinkan lebih dari yang tersedia, dengan beberapa yang tersedia sebagai cadangan. Jika Anda membiarkan database berjalan dalam mode baca saja, pertimbangkan juga memori lokal yang digunakan untuk kueri baca saja tersebut.

Pada sebagian besar sistem, gunakan nilai maksimum (1 GB atau 1048576 kB, seperti yang tertera dalam contoh). Nilai ini dapat memuat hingga sekitar 178 juta tuple mati. Terlebih lagi, masih banyak yang menyebabkan beberapa pemindaian indeks terlewati.

Flag ini beserta flag lainnya dijelaskan secara lebih rinci dalam Mengoptimalkan, memantau, dan memecahkan masalah pengoperasian VACUUM di PostgreSQL.

Setelah mengatur flag ini, mulai ulang database agar autovacuum dapat dimulai dengan nilai yang baru.

Anda dapat menggunakan tampilan pg_stat_progress_vacuum untuk memantau progres VACUUM yang dimulai secara otomatis. Tampilan ini menunjukkan VACUUM yang berjalan di semua database dan untuk tabel (relasi) dari database lain yang namanya tidak dapat Anda cari menggunakan kolom tampilan relid.

Untuk mengidentifikasi database dan tabel yang perlu di-vacuum berikutnya, gunakan kueri dari Mengoptimalkan, memantau, dan memecahkan masalah pengoperasian VACUUM di PostgreSQL. Jika VM server cukup kuat dan memiliki bandwidth untuk lebih banyak proses VACUUM paralel daripada yang dimulai oleh autovacuum, maka Anda dapat mulai melakukan beberapa vacuum manual.

Periksa kecepatan VACUUM

Bagian ini menjelaskan cara memeriksa kecepatan VACUUM dan cara mempercepatnya, jika diperlukan.

Memeriksa autovacuums yang sedang berjalan

Semua backend yang menjalankan VACUUM dapat dilihat di tampilan sistem pg_stat_progress_vacuum.

Jika fase saat ini adalah scanning heap, Anda dapat memantau progres dengan mengamati perubahan di kolom heap_blks_scanned. Sayangnya, tidak ada cara mudah untuk menentukan kecepatan pemindaian pada fase lainnya.

Memperkirakan kecepatan pemindaian VACUUM

Untuk memperkirakan kecepatan pemindaian, Anda harus menyimpan nilai dasar terlebih dahulu, lalu menghitung perubahan yang terjadi dari waktu ke waktu untuk memperkirakan waktu penyelesaian. Pertama, Anda harus menyimpan snapshot heap_blks_scanned bersama dengan stempel waktu dengan menggunakan kueri snapshot berikut:

SELECT set_config('save.ts', clock_timestamp()::text, false),
       set_config('save.heap_blks_scanned', heap_blks_scanned::text, false)
FROM pg_stat_progress_vacuum
WHERE datname = 'DB_NAME';

Karena kita tidak dapat menyimpan apa pun di tabel yang sudah berada dalam wraparound, sebaiknya gunakan set_config(flag, value) untuk mengatur dua flag yang ditentukan pengguna - save.ts dan save.heap_blks_scanned - ke nilai saat ini dari pg_stat_progress_vacuum.

Pada kueri berikutnya, kami menggunakan keduanya sebagai dasar perbandingan untuk menentukan kecepatan dan memperkirakan waktu penyelesaian.

CATATAN: WHERE datname = DB_NAME membatasi investigasi pada satu database dalam satu waktu. Jumlah tersebut cukup jika hanya ada satu autovacuum yang berjalan di database ini, dengan lebih dari satu baris per database. Kondisi filter tambahan ('AND relid= …'') perlu ditambahkan ke WHERE untuk menunjukkan baris autovacuum tunggal. Hal ini juga berlaku untuk kueri berikutnya.

Setelah menyimpan nilai-nilai dasarnya, Anda dapat menjalankan kueri berikut:

with q as (
    SELECT datname,
           phase,
           heap_blks_total,
           heap_blks_scanned,
           clock_timestamp() - current_setting('save.ts')::timestamp AS ts_delta,
           heap_blks_scanned - current_setting('save.heap_blks_scanned')::bigint AS scanned_delta
     FROM pg_stat_progress_vacuum
     WHERE datname = DB_NAME
), q2 AS (
SELECT *,
       scanned_delta / extract('epoch' FROM ts_delta) AS pages_per_second
  FROM q
)
SELECT *,
       (heap_blks_total - heap_blks_scanned) / pages_per_second AS remaining_time
  FROM q2
;

┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname           │ DB_NAME          │
│ phase             │ scanning heap    │
│ heap_blks_total   │ 9497174          │
│ heap_blks_scanned │ 18016            │
│ ts_delta          │ 00:00:40.30126   │
│ as_scanned_delta  │ 11642            │
│ pages_per_second  │ 288.87434288655  │
│ remaining_time    │ 32814.1222418038 │
└───────────────────┴──────────────────┘

Kueri ini membandingkan nilai terkini dengan nilai dasar penyimpanan dan menghitung pages_per_second dan remaining_time, yang memungkinkan kami memutuskan apakah VACUUM berjalan cukup cepat atau apakah kami ingin mempercepatnya. Nilai remaining_time hanya untuk fase scanning heap. Fase lainnya juga memerlukan waktu, bahkan terkadang lebih lama. Anda dapat membaca lebih lanjut tentang proses vacuum dan melihat postingan blog di internet yang membahas beberapa aspek kompleks vacuum.

Mempercepat proses VACUUM

Cara termudah dan tercepat untuk mempercepat pemindaian VACUUM adalah dengan mengatur autovacuum_vacuum_cost_delay=0. Cara ini dapat dilakukan melalui konsol Google Cloud.

Sayangnya, VACUUM yang telah berjalan tidak dapat menerima nilai ini. Anda mungkin perlu memulai ulang database.

Setelah memulai ulang, Anda mungkin akan melihat hasil seperti berikut:

┌─[ RECORD 1 ]──────┬──────────────────┐
│ datname           │ DB_NAME          │
│ phase             │ scanning heap    │
│ heap_blks_total   │ 9497174          │
│ heap_blks_scanned │ 222382           │
│ ts_delta          │ 00:00:21.422615  │
│ as_scanned_delta  │ 138235           │
│ pages_per_second  │ 6452.76031894332 │
│ remaining_time    │ 1437.33713040171 │
└───────────────────┴──────────────────┘

Dalam contoh ini, kecepatan meningkat dari <300 halaman per detik menjadi ~6500 halaman per detik, dan perkiraan waktu yang tersisa untuk fase pemindaian heap menurun dari 9 jam menjadi 23 menit.

Kecepatan pemindaian fase lainnya tidak mudah diukur, tetapi seharusnya menunjukkan kecepatan yang serupa.

Pertimbangkan juga untuk membuat autovacuum_work_mem sebesar mungkin untuk menghindari terlewatnya indeks beberapa kali. Indeks yang terlewat terjadi setiap kali memori diisi dengan pointer tuple yang telah mati.

Jika database tidak sedang digunakan, atur autovacuum_work_mem agar memiliki ~80% memori kosong setelah mengizinkan jumlah yang diperlukan untuk shared_buffers. Ini merupakan batas maksimum untuk setiap proses VACUUM yang dimulai secara otomatis. Jika Anda ingin terus menjalankan workload baca saja, sebaiknya gunakan lebih sedikit memori.

Cara lain untuk meningkatkan kecepatan

Menghindari indeks proses vacuum

Untuk tabel yang sangat besar, VACUUM menghabiskan sebagian besar waktunya untuk membersihkan indeks.

PostgreSQL 14 memiliki pengoptimalan khusus untuk menghindari pembersihan indeks jika sistem berada dalam bahaya wraparound.

Di PostgreSQL 12 dan 13, Anda dapat menjalankan pernyataan berikut secara manual:

VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) <tablename>;

Pada versi 11 dan versi lain yang lebih lama, Anda dapat DROP indeks sebelum menjalankan proses vacuum dan membuatnya kembali nanti.

Menghapus indeks saat autovacuum sudah berjalan pada tabel tersebut akan memerlukan pembatalan vacuum yang sedang berjalan. Setelah itu, segera jalankan perintah penghapusan indeks sebelum autovacuum berhasil memulai lagi proses vacuum pada tabel tersebut.

Pertama, jalankan pernyataan berikut untuk menemukan PID dari proses autovacuum yang perlu Anda hentikan:

SELECT pid, query
  FROM pg_stat_activity
 WHERE state != 'idle'
   AND query ilike '%vacuum%';

Kemudian, jalankan pernyataan berikut untuk menghentikan proses vacuum yang sedang berjalan dan menghapus satu indeks atau lebih:

SET cloudsql.enable_maintenance_mode = 'on'; /* get extra transaction ids */
SELECT pg_terminate_backend(<pid>);DROP INDEX <index1>;DROP INDEX <index2>; ...

Menghapus tabel yang melanggar aturan

Dalam beberapa kasus yang jarang terjadi, Anda dapat menghapus tabel. Misalnya, jika tabel tersebut mudah dipulihkan dari sumber lain seperti cadangan atau database lain.

Anda masih harus menggunakan cloudsql.enable_maintenance_mode = 'on' dan mungkin juga menghentikan proses VACUUM pada tabel tersebut seperti yang ditunjukkan di bagian sebelumnya.

VACUUM FULL

Dalam kasus yang jarang terjadi, akan lebih cepat untuk menjalankan VACUUM FULL FREEZE, saat tabel hanya memiliki sebagian kecil tuple aktif. Hal ini dapat diperiksa melalui tampilan pg_stat_user_tables (kecuali jika telah terjadi kerusakan yang menghapus statistik).

Perintah VACUUM FULL akan menyalin tuple aktif ke file baru sehingga tersedia cukup ruang untuk file baru tersebut beserta indeksnya.

Langkah selanjutnya