Sdílet prostřednictvím

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

  • Článek

PLATÍ PRO: Flexibilní server Azure Database for PostgreSQL

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

Co je autovacuum

Autovacuum je proces na pozadí PostgreSQL, který automaticky vyčistí mrtvé řazené kolekce členů a aktualizuje statistiky. Pomáhá udržovat výkon databáze automatickým spouštěním dvou klíčových úloh údržby:

  • VAKUUM - Uvolní místo na disku odstraněním mrtvých řazených kolekcí členů.
  • ANALYZE – Shromažďuje statistiky, které vám pomůžou optimalizátoru PostgreSQL zvolit nejlepší cesty provádění pro dotazy.

Aby autovacuum fungovalo správně, měl by být parametr serveru autovacuum vždy nastaven na HODNOTU ON. Když je tato možnost povolená, PostgreSQL se automaticky rozhodne, kdy se má v tabulce spustit vakuum nebo ANALYZOVAT, aby databáze zůstala efektivní a optimalizovaná.

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:

Parametr Popis
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é řazené kolekce členů. Výchozí hodnota je nastavená na 20.

Množství práce, které autovacuum provádí, závisí na dvou parametrech:

Parametr Popis
autovacuum_vacuum_cost_limit 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 nastavená 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:

Parametr Popis
dead_pct Procento mrtvých řazených kolekcí členů ve srovnání s živými řazené kolekcemi členů.
last_autovacuum Datum posledního automatického úklidu tabulky.
last_autoanalyze Datum poslední analýzy tabulky.

Aktivace automatického úklidu

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 funkce VAKUA se aktivuje, 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. Pro verze >PG =13; Funkce ANALYZE ve výchozím nastavení aktivuje, když se vloží 20 % tabulky plus 1 000 řádků.

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

  • Automatickáanalyze = autovacuum_analyze_scale_factor * řazené kolekce členů + autovacuum_analyze_threshold nebo autovacuum_vacuum_insert_scale_factor * řazené kolekce členů + autovacuum_vacuum_insert_threshold (pro verze >PG = 13)
  • Autovacuum = autovacuum_vacuum_scale_factor * řazené kolekce členů + autovacuum_vacuum_threshold

Pokud máme například tabulku s 100 řádky. Následující rovnice pak poskytuje informace o aktivaci analýzy a vakua:

Aktualizace nebo odstranění: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

Analýza aktivačních událostí po změně 60 řádků v tabulce a aktivace vakua při změně 70 řádků v tabulce.

Pro vložení: Autoanalyze = 0.2 * 100 + 1000 = 1020

Analýza triggerů po vložení 1 020 řádků do tabulky

Tady je popis parametrů použitých v rovnici:

Parametr Popis
autovacuum_analyze_scale_factor Procento vložení, aktualizací nebo odstranění, které aktivuje funkci ANALYZE v tabulce.
autovacuum_analyze_threshold Určuje minimální počet vložených/aktualizovaných/odstraněných kolekcí členů pro analýzu tabulky.
autovacuum_vacuum_insert_scale_factor Procento vložení, které aktivuje ANLYZE v tabulce
autovacuum_vacuum_insert_threshold Určuje minimální počet řazených kolekcí členů vložených do tabulky ANALYZE.
autovacuum_vacuum_scale_factor Procento aktualizací/odstranění, které aktivuje funkci VAKUUM v tabulce

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, autovacuum použije vacuum_cost_limit parametr, ale pokud autovacuum_vacuum_cost_limit je sám nastaven na větší než -1 pak autovacuum_vacuum_cost_limit parametr je považován za.

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

Parametr Popis
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 pomě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 .
  • autovacuum_vacuum_scale_factor je další parametr, který by mohl aktivovat vakuum v tabulce na základě akumulace řazené kolekce členů. Výchozí hodnota: 0.2, Povolený 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.

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. Tady jsou některé z možný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á více prostředků, můžete provést následující akce:

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í akce:

  • 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 nezvyšuje 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 však nastavíme parametr na úrovni autovacuum_vacuum_cost_delay tabulky nebo autovacuum_vacuum_cost_limit parametry, budou pracovní procesy spuštěné v těchto tabulkách 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á následující chybě:

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é autovacuum neodebere.

Možné důvody tohoto problému můžou být některé z následujících:

Ú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

Jakákoli dlouhotrvající transakce v systému neumožňuje 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 blokátory 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 nevztahují na stávající pracovní procesy 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 obzvláště důležité pro malé a velké tabulky. 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, což brání automatickému úklidu v udržování dalších tabulek, u kterých procento změn není tak významné. 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 <= 13 se funkce autovacuum nespustí u tabulek s úlohou jen pro vložení, protože neexistují žádné mrtvé řazené kolekce členů a žádné volné místo, které je potřeba uvolnit. Automatická analýza se ale spouští 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 nejsou nastavené.

Řešení

Verze <Postgres = 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.

Pokyny najdete v zvláštních aspektech použití pg_cron na flexibilním serveru Azure Database for PostgreSQL.

Postgres 13 a vyšší verze

Funkce Autovacuum běží na tabulkách 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. Poskytuje také informace o tom, jak daleko databáze na serveru pocházejí z obtékání nebo situace tísňového volání. 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íží tak, aby je používaly, postupujte podle průvodců odstraňováním potíží s nastavením.

Ukončení procesu automatického úklidu – role pg_signal_autovacuum_worker

Autovacuum je velmi důležitý proces na pozadí, protože pomáhá s efektivní údržbou úložiště a výkonu v databázi. V normálním procesu automatického úklidu se zruší po deadlock_timeout. Pokud uživatel spouští příkaz DDL v tabulce, může být nutné počkat na deadlock_timeout interval. To také neumožňuje spouštění čtení a zápisů v tabulce požadované různými požadavky na připojení, což přidává latenci v transakci.

Zavedli jsme roli pg_signal_autovacuum_worker, která umožňuje členům bez superuživatele ukončit probíhající úlohu automatického úklidu. To pomáhá uživatelům získat zabezpečený a řízený přístup k procesu automatického úklidu. Uživatelé, kteří nejsou superuživateli, můžou proces automatického úklidu zrušit, jakmile jim udělíte roli pg_signal_autovacuum_worker pomocí příkazu pg_terminate_backend. V PostgreSQL verze 15 a novějších jsme backportovali roli pg_signal_autovacuum_worker flexibilnímu serveru Azure Database for PostgreSQL.

Poznámka:

Nedoporučujeme ukončit žádný probíhající proces automatického úklidu, protože ukončení procesu automatického úklidu může vést k bloud tabulek a databází, což může dále vést k regresím peformance. V případech, kdy je však potřeba provést naplánované spuštění příkazu DDL, který se shoduje s procesem automatického úklidu, můžeme umožnit, aby nesuživatelé mohli automatické úklid ukončit řízeným a bezpečným způsobem pomocí role pg_signal_autovacuum_worker.

Doporučení Azure Advisoru

Doporučení Azure Advisoru představují proaktivní způsob identifikace, jestli má server vysoký poměr bloat nebo že se server blíží scénáři zabalení transakcí. Pro doporučení můžete také vytvořit upozornění Azure Advisoru.

Doporučení jsou:

  • Vysoký poměr bloat: Vysoký poměr bloat může ovlivnit výkon serveru několika způsoby. Jedním z významných problémů je, že optimalizátor modulu PostgreSQL může mít potíže s výběrem nejlepšího plánu provádění, což vede ke snížení výkonu dotazů. Proto se doporučení aktivuje, když procento na serveru dosáhne určité prahové hodnoty, aby se zabránilo takovým problémům s výkonem.

  • Obtékání transakcí: Tento scénář je jedním z nejdůležitějších problémů, se kterými může server narazit. Jakmile je server v tomto stavu, může přestat přijímat další transakce, což způsobí, že se server stane jen pro čtení. Proto se doporučení aktivuje, když server překročí 1 miliardu prahových hodnot transakcí.

Související obsah