Autovacuum-hangolás az Azure Database for PostgreSQL-ben – rugalmas kiszolgáló

A következőkre vonatkozik: Azure Database for PostgreSQL – Rugalmas kiszolgáló

Ez a cikk áttekintést nyújt a rugalmas Azure Database for PostgreSQL-kiszolgáló autovacuum szolgáltatásáról, valamint az adatbázisblobok és az autovacuum-blokkolók monitorozásához elérhető funkciók hibaelhárítási útmutatóiról. Azt is ismerteti, hogy az adatbázis milyen messze van a vészhelyzettől vagy a sortörési helyzettől.

Mi az autovacuum?

Az Autovacuum egy PostgreSQL-háttérfolyamat, amely automatikusan megtisztítja a halott rekordokat, és frissíti a statisztikákat. Két fő karbantartási feladat automatikus futtatásával segít fenntartani az adatbázis teljesítményét:

• VÁKUUM – Felszabadítja a lemezterületet az elhalt kukák eltávolításával.
• ANALYZE – Statisztikákat gyűjt, amelyek segítenek a PostgreSQL-optimalizálónak kiválasztani a lekérdezések legjobb végrehajtási útvonalait.

Az autovacuum megfelelő működésének biztosítása érdekében az autovacuum kiszolgálóparamétert mindig ON értékre kell állítani. Ha engedélyezve van, a PostgreSQL automatikusan dönti el, hogy mikor futtassa a VACUUM vagy az ANALYZE parancsot egy táblán, biztosítva, hogy az adatbázis hatékony és optimalizált maradjon.

Autovacuum internals

Az Autovacuum felolvassa azokat a lapokat, amelyek halott upleseket keresnek, és ha nem található, az autovacuum elveti az oldalt. Ha az autovacuum halott üstöket talál, eltávolítja őket. A költség alapja:

Paraméter	Leírás
vacuum_cost_page_hit	A megosztott pufferekben már meglévő és lemezolvasást nem igénylő lapok olvasásának költsége. Az alapértelmezett érték 1.
vacuum_cost_page_miss	A nem megosztott pufferekben lévő lapok beolvasásának költsége. Az alapértelmezett érték 10.
vacuum_cost_page_dirty	A lapra való írás költsége, ha halott csuplok találhatók benne. Az alapértelmezett érték 20.

Az autovacuum által végzett munkamennyiség két paramétertől függ:

Paraméter	Leírás
autovacuum_vacuum_cost_limit	Az autovacuum munkamennyisége egy lépésben történik.
autovacuum_vacuum_cost_delay	Azon ezredmásodpercek száma, amelyek után az autovacuum elalszik, miután elérte a autovacuum_vacuum_cost_limit paraméter által megadott költségkorlátot.

A Postgres összes jelenleg támogatott verziójában az alapértelmezett érték autovacuum_vacuum_cost_limit 200 (valójában -1 értékre van állítva, ami egyenlő a normál vacuum_cost_limitértékével, amely alapértelmezés szerint 200).

Ami a autovacuum_vacuum_cost_delayPostgres 11-es verziójában alapértelmezés szerint 20 ezredmásodpercet, míg a Postgres 12-es és újabb verzióiban alapértelmezés szerint 2 ezredmásodperc.

Az Autovacuum másodpercenként 50-szer (50*20 ms=1000 ms) ébred fel. Minden alkalommal, amikor felébred, az autovacuum 200 oldalt olvas.

Ez azt jelenti, hogy az egy másodperces autovacuum a következőt teheti:

• ~80 MB/s [ (200 oldal/vacuum_cost_page_hit) * 50 * 8 KB oldalanként], ha az összes elhalt csonkot tartalmazó lap megosztott pufferekben található.
• ~8 MB/s [ (200 oldal/vacuum_cost_page_miss) * 50 * 8 KB/oldal] ha az összes elhalt kukacot tartalmazó lap lemezről van beolvasva.
• ~4 MB/s [ (200 oldal/vacuum_cost_page_dirty) * 50 * 8 KB /oldal] Az autovacuum legfeljebb 4 MB/s-ot írhat.

Autovacuum monitorozása

Az autovacuum figyeléséhez használja az alábbi lekérdezéseket:

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

Az alábbi oszlopok segítenek megállapítani, hogy az autovacuum felzárkózni fog-e a táblatevékenységhez:

Paraméter	Leírás
dead_pct	Az elhalt tuplok százalékos aránya az élő kukákhoz képest.
last_autovacuum	A tábla legutóbbi automatikus létrehozásának dátuma.
last_autoanalyze	A tábla utolsó automatikus elemzésének dátuma.

Mikor aktiválja a PostgreSQL az autovacuumot?

Az autovacuumművelet (VAGY ANALYZE vagy VACUUM) akkor aktiválódik, ha az elhalt üstök száma túllép egy bizonyos számot, amely két tényezőtől függ: egy tábla sorainak teljes száma és egy rögzített küszöbérték. Az ANALYZE alapértelmezés szerint akkor aktiválódik, ha a tábla 10%-a és 50 sor változik, míg a VACUUM akkor aktiválódik, ha a tábla 20%-a és 50 sor változik. Mivel a VÁKUUM küszöbértéke kétszer akkora, mint az ANALYZE küszöbérték, az ANALYZE a VACUUM-nál korábban aktiválódik. PG-verziókhoz >=13; AZ ANALYZE alapértelmezés szerint akkor aktiválódik, ha a tábla 20%-a plusz 1000 sor beszúrása történik.

Az egyes műveletek pontos egyenletei a következők:

• Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold vagy autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (PG-verziókhoz >= 13)
• Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

Ha például van egy 100 sort tartalmazó táblázatunk. Az alábbi egyenlet ezután az elemzés és a vákuum eseményindítóinak időpontjáról ad információt:

Frissítések/törlések esetén: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

Elemezheti az eseményindítókat, miután egy táblán 60 sor módosult, a vákuum pedig akkor aktiválódik, ha egy táblán 70 sor módosul.

Beszúrások esetén: Autoanalyze = 0.2 * 100 + 1000 = 1020

Eseményindítók elemzése 1020 sor táblázatba való beszúrása után

Az egyenletben használt paraméterek leírása:

Paraméter	Leírás
autovacuum_analyze_scale_factor	Az ANALYZE táblát aktiváló beszúrások/frissítések/törlések százalékos aránya.
autovacuum_analyze_threshold	A táblázat ELEMZÉSéhez beszúrt/frissített/törölt kukák minimális számát adja meg.
autovacuum_vacuum_insert_scale_factor	Az ANLYZE-t a táblán aktiváló beszúrások százalékos aránya.
autovacuum_vacuum_insert_threshold	Megadja a táblák ELEMZÉSéhez beszúrt üstök minimális számát.
autovacuum_vacuum_scale_factor	A táblázatban a VACUUM-t aktiváló frissítések/törlések százalékos aránya.

Az alábbi lekérdezéssel listázhatja az adatbázis tábláinak listáját, és azonosíthatja az autovacuum-folyamatnak megfelelő táblákat:

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Feljegyzés

A lekérdezés nem veszi figyelembe, hogy az autovacuum táblázatonkénti alapon konfigurálható az "alter table" DDL paranccsal.

Az autovacuum gyakori problémái

Tekintse át az autovacuum folyamatával kapcsolatos lehetséges gyakori problémák alábbi listáját.

Nem tart lépést az elfoglalt kiszolgálóval

Az autovacuum-folyamat megbecsüli az egyes I/O-műveletek költségeit, összegeket halmoz fel az egyes végrehajtott műveletekhez, és a költség felső korlátjának elérése után szünetelteti azt. autovacuum_vacuum_cost_delay és autovacuum_vacuum_cost_limit a folyamat során használt két kiszolgálóparaméter.

Alapértelmezés szerint autovacuum_vacuum_cost_limit –1 értékre van állítva, ami azt jelenti, hogy az autovacuum költségkorlátja ugyanaz az érték, mint a paraméteré vacuum_cost_limit, amely alapértelmezés szerint 200. vacuum_cost_limit a manuális vákuum költsége.

Ha autovacuum_vacuum_cost_limit be van állítva -1, akkor az autovacuum a paramétert vacuum_cost_limit használja, de ha autovacuum_vacuum_cost_limit maga nagyobb, mint -1 akkor autovacuum_vacuum_cost_limit a paramétert veszi figyelembe.

Ha az autovacuum nem tart fenn, a következő paraméterek módosulhatnak:

Paraméter	Leírás
autovacuum_vacuum_cost_limit	Alapértelmezett: 200. A költségkorlát növelhető. Az adatbázis cpu- és I/O-kihasználtságát a módosítások végrehajtása előtt és után kell figyelni.
autovacuum_vacuum_cost_delay	Postgres 11-es verzió – Alapértelmezett: 20 ms. Előfordulhat, hogy a paraméter értéke a következőre 2-10 mscsökken: . Postgres 12- és újabb verziók – Alapértelmezett: 2 ms.

Feljegyzés

• Az autovacuum_vacuum_cost_limit érték arányosan oszlik el a futó autovacuum-feldolgozók között, így ha több is van, az egyes feldolgozók korlátainak összege nem lépi túl a autovacuum_vacuum_cost_limit paraméter értékét.
• autovacuum_vacuum_scale_factor egy másik paraméter, amely vákuumot válthat ki egy táblázatban az elhalt halmozódás alapján. Alapértelmezett: 0.2, Engedélyezett tartomány: 0.05 - 0.1. A méretezési tényező munkaterhelés-specifikus, és a táblákban lévő adatok mennyiségétől függően kell beállítani. Az érték módosítása előtt vizsgálja meg a számítási feladatot és az egyes táblaköteteket.

Autovacuum folyamatosan fut

Az autovacuum folyamatos futtatása hatással lehet a kiszolgáló processzor- és I/O-kihasználtságára. Íme néhány lehetséges ok:

maintenance_work_mem

Az Autovacuum démon alapértelmezés autovacuum_work_mem szerint jelentésre -1 autovacuum_work_mem van állítva, és ugyanaz az érték, mint a paraméter maintenance_work_mem. Ez a dokumentum feltételezi autovacuum_work_mem , hogy -1 az autovacuum démon értéke és maintenance_work_mem használata.

Ha maintenance_work_mem alacsony, akár 2 GB-ra is növelhető a rugalmas Azure Database for PostgreSQL-kiszolgálón. A hüvelykujj általános szabálya, hogy 1 GB RAM-ra 50 MB-ot kell lefoglalni maintenance_work_mem .

Nagy számú adatbázis

Az Autovacuum minden másodpercben megpróbál egy feldolgozót elindítani az egyes adatbázisokon autovacuum_naptime .

Ha például egy kiszolgáló 60 adatbázissal rendelkezik, és autovacuum_naptime 60 másodpercre van állítva, akkor az autovacuum-feldolgozó másodpercenként elindul [autovacuum_naptime/Adatbázisok száma].

Érdemes növelni autovacuum_naptime , ha több adatbázis található egy fürtben. Ugyanakkor az autovacuum-folyamat agresszívabbá tehető a autovacuum_cost_limit paraméterek növelésével és csökkentésével autovacuum_cost_delay , valamint az autovacuum_max_workers alapértelmezett 3-ról 4-re vagy 5-re való növelésével.

Memóriahiány miatti hibák

A túlzottan agresszív maintenance_work_mem értékek időnként memóriakihasználtságot okozhatnak a rendszerben. A paraméter módosítása maintenance_work_mem előtt fontos megérteni a kiszolgálón elérhető RAM-ot.

Az Autovacuum túl zavaró

Ha az Autovacuum több erőforrást használ fel, a következő műveleteket hajthatja végre:

Autovacuum-paraméterek

A paraméterek autovacuum_vacuum_cost_delaykiértékelése , autovacuum_vacuum_cost_limit. autovacuum_max_workers Az autovacuum paramétereinek helytelen beállítása olyan helyzetekhez vezethet, amikor az autovacuum túl zavaróvá válik.

Ha az autovacuum túl zavaró, fontolja meg a következő műveleteket:

• A 200-nál magasabb beállítás esetén növelje autovacuum_vacuum_cost_delay és csökkentse autovacuum_vacuum_cost_limit a 200-nál magasabb értéket.
• Csökkentse az autovacuum_max_workers alapértelmezett 3-nál magasabb értékeket.

Túl sok autovacuum-feldolgozó

Az autovacuum-feldolgozók számának növelése nem növeli a vákuum sebességét. Az autovacuum-feldolgozók nagy száma nem ajánlott.

Az autovacuum-feldolgozók számának növelése több memóriahasználatot eredményez, és az értéktől maintenance_work_mem függően teljesítménycsökkenést okozhat.

Minden autovacuum-feldolgozó folyamat csak az összeg autovacuum_cost_limit(1/autovacuum_max_workers) lesz, ezért a nagy számú feldolgozó miatt mindegyik lassabban halad.

Ha a munkavállalók száma megnő, autovacuum_vacuum_cost_limit növelni kell és/vagy autovacuum_vacuum_cost_delay csökkenteni kell a vákuumfolyamat felgyorsítása érdekében.

Ha azonban a paramétert táblaszinten autovacuum_vacuum_cost_delay vagy autovacuum_vacuum_cost_limit paraméterekben állítjuk be, akkor az ezeken a táblákon futó feldolgozók mentesülnek a kiegyensúlyozási algoritmus [autovacuum_cost_limit/autovacuum_max_workers] alól.

Autovacuum tranzakcióazonosító (TXID) wraparound protection

Amikor egy adatbázis tranzakcióazonosító-körbefuttatás elleni védelemmel fut, az alábbihoz hasonló hibaüzenet figyelhető meg:

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Feljegyzés

Ez a hibaüzenet hosszú ideje fennálló felügyelet. Általában nem kell egyfelhasználós üzemmódra váltania. Ehelyett futtathatja a szükséges VÁKUUM parancsokat, és hangolást végezhet a VÁKUUM gyors futtatásához. Bár nem futtathat adatmanipulációs nyelvet (DML), továbbra is futtathatja a VACUUM-t.

A körbefuttatási probléma akkor fordul elő, ha az adatbázist vagy nem porszívózták ki, vagy túl sok elhalt üstök van, amelyeket nem távolít el az autovacuum. A probléma okai a következőek lehetnek:

Nagy munkaterhelés

A számítási feladat rövid idő alatt túl sok halott tuplest okozhat, ami megnehezíti az autovacuum számára a felzárkózást. A rendszer elhalt csonkjai egy olyan időszakban jelennek meg, amely a lekérdezési teljesítmény romlásához és a sortörési helyzethez vezet. Ennek a helyzetnek az egyik oka az lehet, hogy az autovacuum paraméterei nincsenek megfelelően beállítva, és nem tart lépést egy foglalt kiszolgálóval.

Hosszú ideig futó tranzakciók

A rendszerben futó hosszan futó tranzakciók nem teszik lehetővé az elhalt csuplok eltávolítását az autovacuum futtatása közben. Blokkolják a vákuumfolyamatot. A hosszú ideig futó tranzakciók eltávolítása az autovacuum futtatásakor felszabadítja a törléshez szükséges elhalt pályákat.

A hosszú ideig futó tranzakciók a következő lekérdezéssel észlelhetők:

    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;

Előkészített utasítások

Ha vannak olyan előkészített utasítások, amelyek nem véglegesítettek, megakadályozhatják az elhalt csuplok eltávolítását.
A következő lekérdezés segít megtalálni a nem véglegesített előkészített utasításokat:

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

A VÉGLEGESÍTÉS KÉSZ vagy a ROLLBACK PREPARED használatával véglegesítse vagy visszaállítsa ezeket az utasításokat.

Nem használt replikációs pontok

A nem használt replikációs pontok megakadályozzák, hogy az autovacuum halott csuplokat követeljen. A következő lekérdezés segít azonosítani a nem használt replikációs pontokat:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

A nem használt replikációs pontok törlésére használható pg_drop_replication_slot() .

Amikor az adatbázis tranzakcióazonosító-körbefuttatás elleni védelemmel fut, ellenőrizze a korábban említett blokkolókat, és távolítsa el manuálisan a blokkolókat, hogy az autovacuum folytatódjon és befejeződjön. Az autovacuum sebességét úgy is növelheti, hogy 0-ra állítja autovacuum_cost_delay , és 200-nál nagyobb értékre növeli autovacuum_cost_limit . A paraméterek módosításai azonban nem vonatkoznak a meglévő autovacuum-feldolgozókra. Indítsa újra az adatbázist, vagy törölje a meglévő dolgozókat manuálisan a paramétermódosítások alkalmazásához.

Táblázatspecifikus követelmények

Az autovacuum paraméterei egyéni táblákhoz is beállíthatók. Ez különösen fontos a kis és nagy táblák esetében. Ha például egy kis tábla csak 100 sort tartalmaz, az autovacuum a VÁKUUM műveletet aktiválja 70 sor módosításakor (a korábban kiszámított módon). Ha ezt a táblázatot gyakran frissítik, naponta több száz autovacuum-művelet jelenhet meg, megakadályozva, hogy az autovacuum más táblákat tartson fenn, amelyeken a módosítások százalékos aránya nem olyan jelentős. Másik lehetőségként egy milliárd sort tartalmazó táblának 200 millió sort kell módosítania az autovakuumműveletek aktiválásához. Az autovacuum paramétereinek megfelelő beállítása megakadályozza az ilyen eseteket.

Az autovacuum beállítás táblázatonkénti beállításának beállításához módosítsa a kiszolgáló paramétereit az alábbi példák szerint:

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);

Csak beszúrási számítási feladatok

A PostgreSQL <= 13 verzióiban az autovacuum nem csak beszúrásos számítási feladatokkal rendelkező táblákon fut, mivel nincsenek elhalt gócpontok, és nincs szabad hely, amelyet vissza kell igényelni. Az automatikus elemzés azonban csak beszúrási számítási feladatokhoz fut, mivel új adatok vannak. Ennek hátrányai a következők:

• A táblák láthatósági térképe nem frissül, így a lekérdezési teljesítmény , különösen ahol csak indexvizsgálatok vannak, idővel szenvedni kezd.
• Az adatbázis tranzakcióazonosítós wraparound védelemmel is futtatható.
• A tippbitek nincsenek beállítva.

Megoldások

Postgres-verziók <= 13

A pg_cron bővítmény használatával egy cron-feladat beállítható úgy, hogy rendszeres vákuumelemzést ütemezzen a táblán. A cron-feladat gyakorisága a számítási feladattól függ.

A pg_cron használatával kapcsolatos részletes útmutatásért tekintse át a bővítményeket.

Postgres 13 és újabb verziók

Az Autovacuum csak beszúrásos számítási feladatokkal rendelkező táblákon fut. Két új kiszolgálóparaméter autovacuum_vacuum_insert_threshold , és autovacuum_vacuum_insert_scale_factor segít szabályozni, hogy az autovacuum mikor aktiválható csak beszúrási táblákon.

Hibaelhárítási útmutatók

Az Azure Database for PostgreSQL rugalmas kiszolgálói portálján elérhető szolgáltatás hibaelhárítási útmutatók segítségével az adatbázis vagy az egyéni séma szintjén is monitorozhat bloatokat, valamint azonosíthatja az autovacuum-folyamat lehetséges blokkolóit. Elsőként két hibaelhárítási útmutató érhető el, az egyik az autovacuum monitorozása, amely a bloat adatbázis- vagy sémaszinten történő figyelésére használható. A második hibaelhárítási útmutató az autovacuum blokkolók és a wraparound, amely segít azonosítani a lehetséges autovacuum blokkolók. Arról is információt nyújt, hogy a kiszolgálón lévő adatbázisok milyen messze vannak a sortöréstől vagy a vészhelyzettől. A hibaelhárítási útmutatók a lehetséges problémák megoldására vonatkozó javaslatokat is megosztanak. A hibaelhárítási útmutatók beállítása a beállítási hibaelhárítási útmutatók követésére.

Azure Advisor-javaslatok

Az Azure Advisor javaslatai proaktív módon azonosítják, hogy egy kiszolgáló magas bloatarányú-e, vagy a kiszolgáló megközelíti a tranzakcióburkoló forgatókönyvet. Riasztásokat is beállíthat a javaslatokhoz az Azure Portalon az új javaslatokra vonatkozó Azure Advisor-riasztások létrehozásával

A javaslatok a következők:

• Magas bloatarány: A magas bloatarány számos módon befolyásolhatja a kiszolgáló teljesítményét. Az egyik jelentős probléma az, hogy a PostgreSQL Engine Optimizer esetleg nehezen választja ki a legjobb végrehajtási tervet, ami a lekérdezési teljesítmény romlásához vezet. Ezért egy javaslat akkor aktiválódik, ha egy kiszolgálón a bloat százalékos aránya elér egy bizonyos küszöbértéket az ilyen teljesítményproblémák elkerülése érdekében.

• Tranzakciók körbefuttatása: Ez a forgatókönyv az egyik legsúlyosabb probléma, amelyet a kiszolgáló tapasztalhat. Ha a kiszolgáló ebben az állapotban van, előfordulhat, hogy a kiszolgáló nem fogad el további tranzakciókat, így a kiszolgáló írásvédetté válik. Ezért egy javaslat akkor aktiválódik, ha azt látjuk, hogy a kiszolgáló átlépte az 1 milliárd tranzakciós küszöbértéket.

Kapcsolódó tartalom

• Magas cpu-kihasználtság
• Magas memóriakihasználtság
• Kiszolgálóparaméterek