autovacuum در PostgreSQL؛ تشخیص عقب‌افتادگی پاک‌سازی و خطر wraparound

داخل دیتابیس مورد بررسی، سراغ جدول‌ها و materialized viewهایی می‌ریم که واقعاً داده ذخیره می‌کنن؛ کاتالوگ‌ها هم در این فهرست هستن. مقدارهای بزرگ ممکنه در جدول TOAST نگه‌داری بشن و سن آن جدول از جدول اصلی بیشتر باشه. نام schema و جدول رو با هم ثبت کن تا شیء هم‌نامی رو اشتباه نگیری.

این فهرست اول XID رو مرتب می‌کنه. اگر مشکل از MXID است، سن multixact جدول اصلی و TOAST رو هم جدا دنبال کن و بر اساسشان اولویت بده. والد یک جدول پارتیشن‌بندی‌شده دادهٔ عادی خودش رو نداره؛ باید پارتیشن‌های واقعی رو بررسی کنیم. قدیمی‌بودن کاتالوگ یا چند رابطه، برنامهٔ نگهداری سراسری دیتابیس لازم داره و با کار تک‌جدولی آخر آموزش بسته نمی‌شه.

SELECT n.nspname, c.relname,
       age(c.relfrozenxid) AS heap_xid_age,
       age(t.relfrozenxid) AS toast_xid_age,
       greatest(age(c.relfrozenxid),age(t.relfrozenxid)) AS oldest_xid_age,
       mxid_age(c.relminmxid) AS heap_mxid_age,
       mxid_age(t.relminmxid) AS toast_mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       c.reloptions
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
LEFT JOIN pg_class t ON t.oid=c.reltoastrelid
WHERE c.relkind IN ('r','m') AND c.relfrozenxid <> '0'::xid
ORDER BY greatest(age(c.relfrozenxid),age(t.relfrozenxid)) DESC
LIMIT 30;

حالا می‌بینیم حجم تغییرات جدول چه نسبتی با نگهداری انجام‌شده داره. n_dead_tup تخمینه؛ نه تعداد دقیق ردیف قابل حذف رو می‌گه و نه فضای قابل آزادشدن رو. ممکنه snapshot قدیمی هنوز به نسخه‌های قبلی ردیف نیاز داشته باشه. از طرف دیگه، کم‌بودن این عدد دلیل نمی‌شه جدول به freeze نیاز نداشته باشه.

چند مشاهده رو همراه با بار کاری و زمان reset آمار مقایسه کن. جدول پر از INSERT هم ممکنه vacuum بخواد. از این شمارنده‌ها به‌تنهایی نمی‌شه نتیجه گرفت زمان‌بندی خراب شده یا باید VACUUM FULL اجرا کنیم.

SELECT schemaname,relname,n_live_tup,n_dead_tup,
       last_vacuum,last_autovacuum,vacuum_count,autovacuum_count,
       n_ins_since_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

vacuum نمی‌تونه نسخه‌ای از ردیف رو دور بریزه که یک snapshot قدیمی هنوز ممکنه لازم داشته باشه. قدیمی‌ترین نیازِ باقی‌مونده، مرزی برای جلوآمدن پاک‌سازی می‌گذاره. تراکنش باز قدیمی، تراکنش prepared، replication slot و بازخورد standby از چیزهایی هستن که برای پیدا کردن این مانع بررسی می‌کنیم. اول هویت و سن این موارد رو می‌گیریم؛ لازم نیست متن کامل SQL دیگران رو که ممکنه دادهٔ خصوصی داشته باشه چاپ کنیم.

برای تراکنش قدیمی با مسئول برنامه هماهنگ کن. تراکنش prepared زیر نظر هماهنگ‌کنندهٔ commit دو مرحله‌ای است و تصمیم خودسرانه درباره‌اش می‌تونه سازگاری کار برنامه رو بشکنه. slot هم ممکنه تاریخچهٔ موردنیاز یک مصرف‌کنندهٔ خاموش رو حفظ کرده باشه. حذفش گاهی به راه‌اندازی دوبارهٔ مصرف‌کننده یا از دست‌رفتن تاریخچهٔ لازم منتهی می‌شه. از روی این فهرست، نشست‌ها رو قطع نکن و تراکنش prepared یا slot رو حذف نکن.

SELECT pid,usename,application_name,backend_type,state,
       clock_timestamp()-xact_start AS transaction_duration,
       age(backend_xid) AS xid_age,age(backend_xmin) AS xmin_age,
       wait_event_type,wait_event
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST LIMIT 30;
SELECT gid,prepared,owner,database,age(transaction) AS xid_age
FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
SELECT slot_name,slot_type,database,active,active_pid,
       age(xmin) AS xmin_age,age(catalog_xmin) AS catalog_xmin_age,
       wal_status
FROM pg_replication_slots ORDER BY age(catalog_xmin) DESC NULLS LAST;

وضعیت vacuum همین دیتابیس رو می‌خونیم. اگر blocking_pids خالی نباشه، باید قفل و نگه‌دارنده‌اش رو بررسی کنیم. خالی‌بودن کل خروجی هم می‌تونه فقط یعنی همین لحظه vacuumی در حال اجرا نیست؛ یک اجرای کوتاه ممکنه بین دو مشاهده تمام شده باشه.

اسکن heap فقط یک بخش از کاره. تعداد بلوک اسکن‌شده درصد زمان کل رو نشون نمی‌ده و پردازش index ممکنه چند دور تکرار بشه. مرحله، انتظار و چند مشاهدهٔ پشت‌سرهم رو کنار هم بخون. worker جلوگیری از wraparound رو فقط به خاطر هزینه‌اش لغو نکن؛ فشار I/O یا کمبود ظرفیت شاهد جدا لازم داره.

SELECT p.pid,p.datname,p.relid,p.phase,
       p.heap_blks_total,p.heap_blks_scanned,p.heap_blks_vacuumed,
       p.index_vacuum_count,a.backend_type,a.wait_event_type,a.wait_event,
       pg_blocking_pids(p.pid) AS blocking_pids
FROM pg_stat_progress_vacuum p
LEFT JOIN pg_stat_activity a USING(pid)
WHERE p.datid=(SELECT oid FROM pg_database WHERE datname=current_database())
ORDER BY p.pid;

در فرم مقادیر نمونه، schema و یک جدول عادی یا materialized view موجود رو انتخاب و اعمال کن. مقدارهای اولیه فقط جای نام جدول خودت هستن؛ آموزش جدول نمونه‌ای نمی‌سازه. نتیجهٔ فقط‌خواندنی پایین رو همراه درخواست نگهداری نگه دار.

قبل از کار اختیاری بعدی، با مسئول سرویس دربارهٔ فشار قابل‌قبول I/O و WAL، اثر روی replica، فضای آزاد و زمان نگهداری توافق کن. اگر افق قدیمی رو پیدا کردی، اول از مسیر مسئول خودش رسیدگی کن. vacuum یک جدول، سن دیتابیس رو پایین نمی‌آره اگر کاتالوگ یا پارتیشن یا رابطهٔ قدیمی‌تری باقی مونده باشه.

\set target_schema 'public'
\set target_table 'orders'
SELECT c.oid,c.relkind,n.nspname,c.relname,c.reloptions,
       age(c.relfrozenxid) AS xid_age,mxid_age(c.relminmxid) AS mxid_age,
       age(t.relfrozenxid) AS toast_xid_age,
       mxid_age(t.relminmxid) AS toast_mxid_age
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
LEFT JOIN pg_class t ON t.oid=c.reltoastrelid
WHERE n.nspname=:'target_schema' AND c.relname=:'target_table';

فقط وقتی ادامه بده که مسئول سرویس این جدول مشخص رو تأیید کرده و کلاستر در وضعیت توقف اضطراری نیست. در ترمینال دوم، پروفایل نگهداری رو باز کن؛ مطمئن شو به همان دیتابیس و سرور primary رسیده. ترمینال مشاهده رو برای دیدن پیشرفت باز نگه دار.

نام پروفایل به‌تنهایی مقصد یا مجوز رو ثابت نمی‌کنه. اگر فقط حساب فقط‌خواندنی داری، نتیجهٔ تشخیص رو تحویل مسئول دیتابیس بده تا اقدام مشخص رو انجام بده.

/usr/pgsql-18/bin/psql -X -v ON_ERROR_STOP=1 'service=academy_vacuum_maintain'