Automatické ladění na flexibilním serveru Azure Database for PostgreSQL

PLATÍ PRO: Flexibilní server Azure Database for PostgreSQL

Tento článek obsahuje přehled funkce automatického úklidu pro flexibilní server Azure Database for PostgreSQL a průvodce odstraňováním potíží s funkcemi, které jsou k dispozici pro monitorování bloudů databáze, blokování automatického úklidu a také informace o tom, jak daleko je databáze od nouzové situace nebo jak je zabalit.

Co je autovacuum

Interní konzistence dat v PostgreSQL je založená na mechanismu řízení souběžnosti více verzí (MVCC), který databázovému stroji umožňuje udržovat více verzí řádku a poskytuje větší souběžnost s minimálním blokováním mezi různými procesy.

Databáze PostgreSQL potřebují odpovídající údržbu. Když se například řádek odstraní, neodebere se fyzicky. Místo toho je řádek označený jako "mrtvý". Podobně u aktualizací je řádek označený jako "mrtvý" a vloží se nová verze řádku. Tyto operace opouštějí neaktivní záznamy, označované jako mrtvé řazené řazené kolekce členů, a to i po všech transakcích, které by mohly tyto verze dokončit. Pokud se nevyčistí, zůstanou prázdné řazené kolekce členů, spotřebovávají místo na disku a bloudí tabulky a indexy, což vede k pomalému výkonu dotazů.

PostgreSQL používá k automatickému vyčištění řazených kolekcí členů proces nazvaný autovacuum.

Interní funkce automatického úklidu

Funkce Autovacuum čte stránky, které hledají mrtvé řazené kolekce členů, a pokud se žádná nenajde, funkce autovacuum stránku zahodí. Když autovacuum najde mrtvé řazené kolekce členů, odebere je. Náklady vycházejí z následujících:

• vacuum_cost_page_hit: Náklady na čtení stránky, která už je ve sdílených vyrovnávacích pamětích a nepotřebuje čtení disku. Výchozí hodnota je nastavená na 1.
• vacuum_cost_page_miss: Náklady na načtení stránky, která není ve sdílených vyrovnávacích pamětích. Výchozí hodnota je nastavená na 10.
• vacuum_cost_page_dirty: Náklady na zápis na stránku, když jsou v ní nalezeny mrtvé řazené kolekce členů. Výchozí hodnota je nastavená na 20.

Množství práce automatického úklidu závisí na dvou parametrech:

• autovacuum_vacuum_cost_limit je množství práce autovacuum dělá na jednom místě.
• autovacuum_vacuum_cost_delay počet milisekund, které autovacuum spí po dosažení limitu nákladů určeného parametrem autovacuum_vacuum_cost_limit .

Ve všech aktuálně podporovaných verzích Postgres je výchozí hodnota autovacuum_vacuum_cost_limit 200 (ve skutečnosti je nastavena na hodnotu -1, která se rovná hodnotě běžného vacuum_cost_limit , což je ve výchozím nastavení 200).

autovacuum_vacuum_cost_delayStejně jako u verze Postgres verze 11 je ve výchozím nastavení 20 milisekund, zatímco ve verzi Postgres verze 12 a vyšší je výchozí 2 milisekundy.

Autovacuum se probudí 50krát (50*20 ms=1000 ms) každou sekundu. Pokaždé, když se vzbudí, autovacuum čte 200 stránek.

To znamená, že jednosekundový automatický úklid může provádět:

• ~80 MB/s [ (200 stran/vacuum_cost_page_hit) * 50 × 8 kB na stránku] pokud jsou všechny stránky s mrtvými řazenými kolekcemi členů nalezeny ve sdílených vyrovnávacích pamětích.
• ~8 MB/s [ (200 stran/vacuum_cost_page_miss) * 50 × 8 kB na stránku] pokud se všechny stránky s mrtvými řazenými kolekcemi členů čtou z disku.
• ~4 MB/s [ (200 stran/vacuum_cost_page_dirty) * 50 × 8 kB na stránku] autovacuum může zapisovat až 4 MB/s.

Monitorování automatického úklidu

K monitorování automatického úklidu použijte následující dotazy:

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;

Následující sloupce vám pomůžou určit, jestli autovacuum dochytává aktivitu tabulky:

• dead_pct: procento mrtvých členů v porovnání s živými řazené kolekcemi členů.
• last_autovacuum: Datum posledního automatického úklidu tabulky.
• last_autoanalyze: Datum poslední analýzy tabulky.

Kdy PostgreSQL aktivuje automatické úklidové shrnutí

Akce automatického úklidu ( ANALYZOVAT nebo VAKUO) se aktivuje, když počet řazených kolekcí členů překročí určitý počet závislých na dvou faktorech: celkový počet řádků v tabulce a pevnou prahovou hodnotu. Funkce ANALYZE ve výchozím nastavení aktivuje, když se změní 10 % tabulky plus 50 řádků, zatímco se aktivuje vakuum , když se změní 20 % tabulky plus 50 řádků. Vzhledem k tomu, že prahová hodnota vakua je dvakrát tak vysoká jako prahová hodnota ANALYZE , aktivuje se funkce ANALYZE dříve než vakuum.

Přesné rovnice pro každou akci jsou:

• Automatická analyze = autovacuum_analyze_scale_factor * řazené kolekce členů + autovacuum_analyze_threshold
• Autovacuum = autovacuum_vacuum_scale_factor * řazené kolekce členů + autovacuum_vacuum_threshold

Například analýza aktivačních událostí po 60 řádcích se změní v tabulce, která obsahuje 100 řádků, a vakuové triggery se aktivuje při změně 70 řádků v tabulce pomocí následujících rovnic:

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

Pomocí následujícího dotazu zobrazte seznam tabulek v databázi a identifikujte tabulky, které mají nárok na proces automatického úklidu:

 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;

Poznámka:

Dotaz nebere v úvahu, že funkci automatického úklidu je možné nakonfigurovat pro jednotlivé tabulky pomocí příkazu "alter table" DDL.

Běžné problémy s automatickým úklidem

Projděte si následující seznam možných běžných problémů s procesem automatického úklidu.

Nedržte tempo s zaneprázdněným serverem

Proces automatického úklidu odhaduje náklady na každou vstupně-výstupní operaci, akumuluje celkový součet pro každou operaci, kterou provádí, a pozastaví se po dosažení horního limitu nákladů. autovacuum_vacuum_cost_delay a autovacuum_vacuum_cost_limit jsou to dva parametry serveru, které se v procesu používají.

Ve výchozím nastavení je nastavena hodnota –1, což znamená, autovacuum_vacuum_cost_limit že limit nákladů automatického úklidu je stejná hodnota jako parametr vacuum_cost_limit, který je výchozí hodnota 200. vacuum_cost_limit je cena ručního vakua.

Pokud autovacuum_vacuum_cost_limit je nastavena na -1 hodnotu, použije funkce autovacuum vacuum_cost_limit parametr, ale pokud autovacuum_vacuum_cost_limit je nastavena na hodnotu větší, než -1 je autovacuum_vacuum_cost_limit parametr považován za parametr.

V případě, že autovacuum neudržuje tempo, můžou se změnit následující parametry:

Parametr	Popis
autovacuum_vacuum_scale_factor	Výchozí hodnota: 0.2, rozsah: 0.05 - 0.1. Měřítko je specifické pro úlohy a mělo by se nastavit v závislosti na množství dat v tabulkách. Před změnou hodnoty prozkoumejte zatížení a jednotlivé svazky tabulek.
autovacuum_vacuum_cost_limit	Výchozí hodnota: 200. Může se zvýšit limit nákladů. Využití procesoru a vstupně-výstupních operací v databázi by se mělo monitorovat před provedením změn a po provedení změn.
autovacuum_vacuum_cost_delay	Postgres verze 11 – výchozí: 20 ms. Parametr se může snížit na 2-10 ms. Postgres Verze 12 a vyšší – výchozí: 2 ms.

Poznámka:

Hodnota autovacuum_vacuum_cost_limit se distribuuje úměrně mezi spuštěné pracovní procesy automatického úklidu, takže pokud existuje více než jedna, součet limitů pro každý pracovní proces nepřekračuje hodnotu parametru autovacuum_vacuum_cost_limit .

Automatické úklidové jednotky neustále běží

Průběžné spouštění automatického úklidu může ovlivnit využití procesoru a vstupně-výstupních operací na serveru. Může to být možné z následujících důvodů:

maintenance_work_mem

Proces démon Autovacuum používá autovacuum_work_mem , který je ve výchozím nastavení nastaven na -1 význam autovacuum_work_mem , by měl stejnou hodnotu jako parametr maintenance_work_mem. Tento dokument předpokládá autovacuum_work_mem , že je nastaven na -1 a maintenance_work_mem je používán démonem automatického úklidu.

Pokud maintenance_work_mem je nízká, může se na flexibilním serveru Azure Database for PostgreSQL zvýšit až na 2 GB. Obecným pravidlem je přidělení 50 MB maintenance_work_mem pro každých 1 GB paměti RAM.

Velký počet databází

Funkce Autovacuum se pokusí spustit pracovní proces v každé databázi každých autovacuum_naptime sekund.

Pokud má například server 60 databází a autovacuum_naptime je nastavený na 60 sekund, spustí se pracovní proces automatického úklidu každou sekundu [autovacuum_naptime/počet databází].

Pokud v clusteru existuje více databází, je vhodné ho zvýšit autovacuum_naptime . Současně může být proces automatického úklidu agresivnější zvýšením a snížením autovacuum_cost_limitautovacuum_cost_delay parametrů a zvýšením autovacuum_max_workers výchozí hodnoty 3 až 4 nebo 5.

Chyby Nedostatek paměti

Příliš agresivní maintenance_work_mem hodnoty můžou v systému pravidelně způsobovat chyby nedostatku paměti. Před provedením jakékoli změny parametru je důležité pochopit dostupnou maintenance_work_mem paměť RAM na serveru.

Autovacuum je příliš rušivé

Pokud autovacuum spotřebovává velké množství prostředků, můžete provést následující:

Parametry automatického úklidu

Vyhodnocení parametrů autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit. autovacuum_max_workers Nesprávné nastavení parametrů automatického úklidu může vést ke scénářům, kdy se autovacuum stává příliš rušivým.

Pokud je autovacuum příliš rušivé, zvažte následující:

• Zvýšení autovacuum_vacuum_cost_delay a snížení autovacuum_vacuum_cost_limit , pokud je nastaveno vyšší než výchozí hodnota 200.
• Snižte počet autovacuum_max_workers , pokud je nastaven vyšší než výchozí hodnota 3.

Příliš mnoho pracovních procesů automatického úklidu

Zvýšení počtu pracovníků automatického úklidu nemusí nutně zvýšit rychlost vakua. Nedoporučuje se mít velký počet pracovních procesů automatického úklidu.

Zvýšení počtu pracovních procesů automatického úklidu způsobí větší spotřebu paměti a v závislosti na hodnotě maintenance_work_mem může způsobit snížení výkonu.

Každý pracovní proces automatického úklidu získá (1/autovacuum_max_workers) z celkového autovacuum_cost_limitsoučtu, takže když bude mít vysoký počet pracovních procesů, každý z nich bude pomalejší.

Pokud se zvýší počet pracovníků, autovacuum_vacuum_cost_limit měli byste také zvýšit nebo autovacuum_vacuum_cost_delay snížit, aby byl proces vakua rychlejší.

Pokud jsme však změnili úroveň autovacuum_vacuum_cost_delay tabulky nebo autovacuum_vacuum_cost_limit parametry, pracovní procesy spuštěné v těchto tabulkách jsou vyloučeny z toho, že se považují za algoritmus vyrovnávání [autovacuum_cost_limit/autovacuum_max_workers].

Ochrana zalomení id transakce autovacuum (TXID)

Když databáze narazí na zalamovací ochranu ID transakce, může být zjištěna chybová zpráva podobná této:

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

Poznámka:

Tato chybová zpráva je způsobená dlouhodobým opominutím. Obvykle není nutné přepínat do jednouživatelského režimu. Místo toho můžete spustit požadované příkazy VACUUM a vyladit je tak, aby byly rychlejší. I když nemůžete spustit žádný jazyk pro manipulaci s daty (DML), můžete stále spouštět vakuum.

K problému wraparound dochází v případě, že databáze buď není vakuová, nebo existuje příliš mnoho mrtvých řazených kolekcí členů, které nelze odebrat pomocí automatického úklidu. Důvody pro tyto účely můžou být:

Úlohy s velkým zatížením

Úloha by mohla způsobit příliš mnoho mrtvých řazených kolekcí členů v krátkém období, které znesnadňuje, aby automatické úklidy zachytily. Mrtvé řazené řazené kolekce členů v systému se sčítají za určité období, což vede ke snížení výkonu dotazů a vede k zalomení situace. Jedním z důvodů, proč k této situaci dojít, může být to, že parametry automatického úklidu nejsou dostatečně nastavené a nedrží krok s zaneprázdněným serverem.

Dlouhotrvající transakce

Všechny dlouhotrvající transakce v systému nepovolí odebrání mrtvých řazených kolekcí členů při spuštění automatického úklidu. Jsou překážkou pro vakuový proces. Odebrání dlouhotrvajících transakcí uvolní mrtvé řazené kolekce členů pro odstranění při spuštění automatického úklidu.

Dlouhotrvající transakce lze zjistit pomocí následujícího dotazu:

    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;

Připravené příkazy

Pokud existují připravené příkazy, které nejsou potvrzeny, zabrání odebrání mrtvých řazených kolekcí členů.
Následující dotaz pomáhá najít nepotvrzené připravené příkazy:

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

Pomocí příkazu COMMIT PREPARED nebo ROLLBACK PREPARED potvrďte nebo vraťte zpět tyto příkazy.

Nepoužité sloty replikace

Nepoužité sloty replikace brání automatickému úklidu v deklaraci mrtvých řazených kolekcí členů. Následující dotaz pomáhá identifikovat nevyužité sloty replikace:

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

Slouží pg_drop_replication_slot() k odstranění nepoužívaných slotů replikace.

Když databáze narazí na zalamovací ochranu ID transakce, zkontrolujte všechny blokátory, jak jsme zmínili dříve, a odeberte je ručně, aby automatické úklid pokračovaly a dokončily. Rychlost automatického úklidu můžete také zvýšit nastavením autovacuum_cost_delay na 0 a zvýšením autovacuum_cost_limit hodnoty větší než 200. Změny těchto parametrů se ale nepoužijí u stávajících pracovních procesů automatického úklidu. Pokud chcete použít změny parametrů, restartujte databázi nebo ukončete stávající pracovní procesy ručně.

Požadavky specifické pro tabulky

Parametry automatického úklidu můžou být nastavené pro jednotlivé tabulky. Je to důležité hlavně pro malé a velké stoly. Například u malé tabulky, která obsahuje pouze 100 řádků, aktivuje automatické úklidové operace při změně 70 řádků (jak bylo vypočteno dříve). Pokud se tato tabulka často aktualizuje, může se zobrazit stovky operací automatického úklidu za den. To brání automatickému úklidu v udržování dalších tabulek, u kterých není procento změn tak velké. Případně musí tabulka obsahující miliardu řádků změnit 200 milionů řádků, aby se aktivovaly operace automatického úklidu. Nastavení parametrů automatického úklidu odpovídajícím způsobem zabrání takovým scénářům.

Pokud chcete nastavit nastavení automatického úklidu na tabulku, změňte parametry serveru jako následující příklady:

    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);

Úlohy jen pro vložení

Ve verzích PostgreSQL před 13 se funkce autovacuum nespustí u tabulek s úlohou jen pro vložení, protože pokud nejsou žádné aktualizace nebo odstranění, neexistují žádné prázdné řazené kolekce členů a žádné volné místo, které je potřeba uvolnit. Automatická analýza se ale spustí pro úlohy jen pro vložení, protože existují nová data. Nevýhody tohoto:

• Mapa viditelnosti tabulek se neaktualizuje, a proto výkon dotazů, zejména pokud existují kontroly pouze indexů, začíná v průběhu času trpět.
• Databáze může narazit na ochranu zalamování ID transakce.
• Bity nápovědy se nenastaví.

Řešení

Verze Postgres starší než 13

Pomocí rozšíření pg_cron lze úlohu cron nastavit tak, aby na tabulce naplánovála pravidelnou analýzu vakua. Frekvence úlohy cron závisí na úloze.

Podrobné pokyny k pg_cron najdete v tématu Rozšíření.

Postgres 13 a vyšší verze

Funkce Autovacuum se spustí u tabulek s úlohou jen pro vložení. Dva nové parametry autovacuum_vacuum_insert_threshold serveru a autovacuum_vacuum_insert_scale_factor řízení, kdy je možné automatické úklid aktivovat u tabulek jen pro vložení.

Průvodce řešením potíží

Pomocí průvodců odstraňováním potíží s funkcemi, které jsou k dispozici na portálu flexibilního serveru Azure Database for PostgreSQL, je možné monitorovat bloudání na úrovni databáze nebo jednotlivých schémat spolu s identifikací potenciálních blokujících procesů automatického úklidu. Jako první jsou k dispozici dva průvodci odstraňováním potíží, které je možné použít k monitorování bloudové databáze nebo jednotlivých schémat. Druhým průvodcem odstraňováním potíží jsou blokátory automatického úklidu a wraparound, které pomáhají identifikovat potenciální blokátory automatického úklidu spolu s informacemi o tom, jak daleko databáze na serveru pocházejí z zabalení nebo nouzové situace. Průvodci odstraňováním potíží také sdílejí doporučení ke zmírnění potenciálních problémů. Jak nastavit průvodce odstraňováním potíží, aby je mohli používat, postupujte podle průvodců odstraňováním potíží s nastavením.

Související obsah

• Vysoké využití procesoru
• Vysoké využití paměti
• Parametry serveru