Postgres: bloat, pg_repack a odložená omezení

Efekt nadýmavých tabulek a indexů (bloat) je všeobecně známý a je přítomen nejen v Postgresu. Existují způsoby, jak se s tím vypořádat „z krabice“, jako je VACUUM FULL nebo CLUSTER, ale zamykají stoly během provozu, a proto je nelze vždy použít.

Článek bude obsahovat nějakou teorii o tom, jak dochází k nadýmání, jak se s ním můžete vypořádat, o odložených omezeních a problémech, které přináší používání rozšíření pg_repack.

Tento článek je založen na můj projev na PgConf.Russia 2020.

Proč tam je nadýmání

Postgres je založen na modelu více verzí (MVCC). Jeho podstatou je, že každý řádek v tabulce může mít několik verzí, přičemž transakce nevidí více než jednu z těchto verzí, ale nemusí nutně stejnou. To umožňuje, aby několik transakcí fungovalo současně a navzájem se neovlivnily téměř vůbec.

Je zřejmé, že všechny tyto verze je třeba zachovat. Postgres pracuje s pamětí stránku po stránce a stránka je minimální množství dat, které lze číst z disku nebo zapisovat. Podívejme se na malý příklad, abychom pochopili, jak se to děje.

Řekněme, že máme tabulku, do které jsme přidali několik záznamů. Na první stránce souboru, kde je tabulka uložena, jsou nová data. Jedná se o živé verze řádků, které jsou dostupné pro ostatní transakce po potvrzení (pro jednoduchost budeme předpokládat, že úroveň izolace je Read Committed).

Poté jsme aktualizovali jeden ze záznamů a starou verzi jsme tak označili jako zastaralou.

Krok za krokem, aktualizací a mazáním verzí řádků, jsme dostali stránku, na které je asi polovina dat „odpad“. Tato data nejsou viditelná pro žádnou transakci.

Postgres má mechanismus VACUUM, který vyčistí zastaralé verze a uvolní prostor pro nová data. Pokud však není nakonfigurován dostatečně agresivně nebo je zaneprázdněn prací v jiných tabulkách, pak „odpadní data“ zůstávají a pro nová data musíme použít další stránky.

V našem příkladu se tedy v určitém okamžiku bude tabulka skládat ze čtyř stránek, ale bude v ní pouze polovina živých dat. Výsledkem je, že při přístupu k tabulce načteme mnohem více dat, než je nutné.

I když VACUUM nyní odstraní všechny nepodstatné verze řádků, situace se dramaticky nezlepší. Budeme mít volné místo ve stránkách nebo i celých stránkách pro nové řádky, ale i tak přečteme více dat, než potřebujeme.
Mimochodem, pokud by na konci souboru byla úplně prázdná stránka (druhá v našem příkladu), pak by ji VACUUM mohl odříznout. Teď je ale uprostřed, takže se s ní nedá nic dělat.

Když se počet takových prázdných nebo silně řídkých stránek zvětší, což se nazývá nadýmání, začne to ovlivňovat výkon.

Vše popsané výše je mechanika výskytu nadýmání v tabulkách. V indexech se to děje téměř stejným způsobem.

Mám nadýmání?

Existuje několik způsobů, jak zjistit, zda máte nadýmání. Myšlenkou prvního je použití interní statistiky Postgres, která obsahuje přibližné informace o počtu řádků v tabulkách, počtu „živých“ řádků atd. Na internetu existuje mnoho variant hotových skriptů. Vzali jsme jako základ skript od PostgreSQL Experts, která dokáže vyhodnotit table bloat spolu s indexy toast a bloat btree. Podle našich zkušeností je jeho chyba 10-20%.

Dalším způsobem je použití rozšíření pgstattuple, která vám umožní nahlédnout do stránek a získat odhadovanou i přesnou hodnotu nadýmání. Ale ve druhém případě budete muset naskenovat celou tabulku.

Malé množství nadýmání, do 20 %, je přijatelné. Lze jej považovat za obdobu faktoru plnění pro tabulky и indexy. Při 50 % a více mohou začít problémy s výkonem.

Způsoby, jak se vypořádat s nadýmáním

Existuje několik nestandardních způsobů, jak se vypořádat s nadýmáním v Postgresu, ale zdaleka ne vždy a nemusí vyhovovat každému.

Nastavte AUTOVAKUUM tak, aby nedocházelo k nadýmání. A přesněji řečeno, aby to bylo na pro vás přijatelné úrovni. Vypadá to jako „kapitánova“ rada, ale ve skutečnosti toho není vždy snadné dosáhnout. Máte například aktivní vývoj s pravidelnou změnou datového schématu nebo probíhá nějaká migrace dat. V důsledku toho se váš profil zatížení může často měnit a má tendenci se lišit pro různé tabulky. To znamená, že musíte být neustále o něco napřed a přizpůsobovat AUTOVAKUUM měnícímu se profilu každého stolu. Ale je zřejmé, že to není snadné.

Dalším častým důvodem, proč AUTOVACUUM selhává při zpracování tabulek, je přítomnost dlouhotrvajících transakcí, které mu brání v čištění dat, protože je pro tyto transakce k dispozici. Doporučení je zde také nasnadě – zbavit se „visících“ transakcí a minimalizovat dobu aktivních transakcí. Pokud je však zatížení vaší aplikace hybridem OLAP a OLTP, můžete mít současně mnoho častých aktualizací a krátkých dotazů a také zdlouhavé operace, jako je vytváření sestavy. V takové situaci byste měli myslet na rozložení zátěže na různé základny, které vám umožní doladit každou z nich.

Další příklad - i když je profil homogenní, ale databáze je velmi zatížena, pak ani ten nejagresivnější AUTOVACUUM nemusí zvládnout a dojde k nadýmání. Měřítko (vertikální nebo horizontální) je jediným řešením.

Jak se dostat do situace, kdy jste nakonfigurovali AUTOVACUUM, ale nadýmání stále roste.

Tým VAKUUM PLNÝ znovu sestaví obsah tabulek a indexů a ponechá v nich pouze aktuální data. Pro eliminaci bloatu to funguje perfektně, ale při jeho provádění je zachycen exkluzivní zámek na stole (AccessExclusiveLock), který nedovolí dotazy na tuto tabulku, dokonce vybírá. Pokud si můžete dovolit zastavit službu nebo její část na nějakou dobu (od desítek minut až po několik hodin v závislosti na velikosti databáze a vašem hardwaru), pak je tato možnost nejlepší. Bohužel nemáme čas spustit VACUUM FULL během plánované údržby, takže tento způsob nám nevyhovuje.

Tým CLUSTER znovu sestaví obsah tabulek stejným způsobem jako VACUUM FULL, přičemž umožňuje zadat index, podle kterého budou data fyzicky řazena na disku (pořadí však není zaručeno pro nové řádky v budoucnu). V určitých situacích je to dobrá optimalizace pro řadu dotazů – při čtení několika záznamů v indexu. Nevýhoda příkazu je stejná jako u VACUUM FULL - zamyká stůl během provozu.

Tým REINDEX podobné předchozím dvěma, ale znovu sestaví konkrétní index nebo všechny indexy v tabulce. Zámky jsou o něco slabší: ShareLock na tabulce (zabraňuje úpravám, ale umožňuje výběr) a AccessExclusiveLock na přestavitelném indexu (blokuje dotazy pomocí tohoto indexu). Postgres 12 však tuto možnost zavedl SOUČASNĚ, která umožňuje znovu sestavit index bez blokování souběžného přidávání, úprav nebo odstraňování záznamů.

V dřívějších verzích Postgresu můžete dosáhnout výsledku podobného REINDEXU SOUČASNĚ s VYTVOŘTE INDEX SOUČASNĚ. Umožňuje vytvořit index bez silného zámku (ShareUpdateExclusiveLock, který nezasahuje do paralelních dotazů), poté nahradit starý index novým a starý index odstranit. To vám umožní eliminovat nadýmání indexu, aniž byste zasahovali do vaší aplikace. Je důležité vzít v úvahu, že při přestavbě indexů dojde k dodatečnému zatížení diskového subsystému.

Pokud tedy existují způsoby, jak indexy eliminovat nadýmání, pak neexistují žádné pro tabulky. Zde přicházejí do hry externí rozšíření: pg_repack (dříve pg_reorg), pgcompact, pgcompacttable a další. V rámci tohoto článku je nebudu srovnávat a budu hovořit pouze o pg_repacku, který po určité úpravě používáme doma.

Jak funguje pg_repack

Řekněme, že máme docela obyčejnou tabulku – s indexy, omezeními a bohužel i s nadýmáním. Jako první krok pg_repack vytvoří tabulku protokolů, která bude sledovat všechny změny, zatímco je spuštěn. Spouštěč replikuje tyto změny na každé vložení, aktualizaci a odstranění. Poté se vytvoří tabulka, která je strukturou podobná originálu, ale bez indexů a omezení, aby nezpomalovala proces vkládání dat.

Dále pg_repack přenese data ze staré tabulky do nové tabulky, automaticky odfiltruje všechny irelevantní řádky a poté vytvoří indexy pro novou tabulku. Během provádění všech těchto operací se změny shromažďují v tabulce protokolu.

Dalším krokem je přenesení změn do nové tabulky. Migrace se provádí v několika iteracích, a když v tabulce protokolu zbývá méně než 20 položek, pg_repack získá silný zámek, migruje nejnovější data a nahradí starou tabulku novou v tabulkách systému Postgres. Toto je jediný a velmi krátký okamžik, kdy nebudete moci pracovat se stolem. Poté se stará tabulka a tabulka s protokoly odstraní a uvolní se místo v systému souborů. Proces dokončen.

Teoreticky vše vypadá skvěle, ale co v praxi? Testovali jsme pg_repack bez zátěže a pod zátěží a zkontrolovali jeho činnost v případě předčasného zastavení (jinými slovy Ctrl+C). Všechny testy byly pozitivní.

Šli jsme do produ - a pak se všechno pokazilo, jak jsme očekávali.

První palačinka v prodeji

V prvním clusteru jsme dostali chybu o jedinečném porušení omezení:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Toto omezení mělo automaticky generovaný název index_16508, vytvořený pg_repack. Podle atributů obsažených v jeho složení jsme určili „naše“ omezení, které mu odpovídá. Problém se ukázal být v tom, že se nejedná o zcela běžné omezení, ale o zpožděné (odložené omezení), tj. jeho ověření se provádí později než příkaz sql, což vede k neočekávaným následkům.

Odložená omezení: proč jsou potřebná a jak fungují

Trochu teorie o odložených omezeních.
Vezměme si jednoduchý příklad: máme tabulku adresáře aut se dvěma atributy – jménem a pořadím vozu v adresáři.

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique
);

Předpokládejme, že potřebujeme prohodit první a druhé auto na místech. Přímým řešením je aktualizovat první hodnotu na druhou a druhou na první:

begin;
  update cars set ord = 2 where name = 'audi';
  update cars set ord = 1 where name = 'bmw';
commit;

Ale když spustíme tento kód, očekává se, že dojde k porušení omezení, protože pořadí hodnot v tabulce je jedinečné:

[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.

Jak to udělat jinak? Možnost jedna: přidejte další nahrazení hodnoty příkazem, který v tabulce zaručeně neexistuje, například „-1“. V programování se tomu říká „výměna hodnot dvou proměnných za třetí“. Jedinou nevýhodou této metody je dodatečná aktualizace.

Druhá možnost: Upravte návrh tabulky tak, aby pro hodnotu exponentu místo celých čísel používala datový typ s plovoucí desetinnou čárkou. Poté, když aktualizujete hodnotu z 1, například na 2.5, první položka se automaticky „staví“ mezi druhou a třetí. Toto řešení funguje, ale má dvě omezení. Za prvé, nebude vám fungovat, pokud je hodnota použita někde v rozhraní. Za druhé, v závislosti na přesnosti datového typu budete mít před přepočtením hodnot všech záznamů omezený počet možných vložení.

Možnost tři: odložte omezení, aby bylo zkontrolováno pouze v době potvrzení:

create table cars
(
  name text constraint pk_cars primary key,
  ord integer not null constraint uk_cars unique deferrable initially deferred
);

Vzhledem k tomu, že logika našeho původního požadavku zajišťuje, že všechny hodnoty jsou v době provedení odevzdání jedinečné, odevzdání bude úspěšné.

Výše diskutovaný příklad je samozřejmě velmi syntetický, ale odhaluje myšlenku. V naší aplikaci používáme odložená omezení k implementaci logiky, která je zodpovědná za řešení konfliktů, když uživatelé současně interagují se sdílenými objekty widgetu na desce. Použití takových omezení nám umožňuje trochu zjednodušit kód aplikace.

Obecně platí, že v závislosti na typu omezení v Postgresu existují tři úrovně granularity jejich ověřování: řádek, transakce a výraz.

Zdroj: žebráci

CHECK a NOT NULL jsou vždy zaškrtnuty na úrovni řádku, pro ostatní omezení, jak je vidět z tabulky, jsou různé možnosti. Můžete si přečíst více zde.

Stručně shrnuto, odložená omezení v řadě situací vedou k čitelnějšímu kódu a menšímu počtu příkazů. Za to však musíte zaplatit zkomplikováním procesu ladění, protože okamžik výskytu chyby a okamžik, kdy se o ní dozvíte, jsou časově odděleny. Dalším možným problémem je, že plánovač nemusí být vždy schopen sestavit optimální plán, pokud je v dotazu zahrnuto zpožděné omezení.

Vylepšení pg_repack

Probrali jsme, co jsou odložená omezení, ale jak souvisí s naším problémem? Připomeňme si chybu, kterou jsme dostali dříve:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Dochází k němu, když jsou data zkopírována z tabulky protokolu do nové tabulky. Vypadá to divně, protože data v tabulce protokolu jsou potvrzena spolu s daty v původní tabulce. Pokud splňují omezení původní tabulky, jak mohou porušit stejná omezení v nové?

Jak se ukázalo, kořen problému spočívá v předchozím kroku pg_repack, který vytváří pouze indexy, ale nikoli omezení: stará tabulka měla jedinečné omezení a nová místo toho vytvořila jedinečný index.

Zde je důležité poznamenat, že pokud je omezení normální a není odloženo, pak jedinečný index vytvořený místo něj je ekvivalentní tomuto omezení, protože jedinečná omezení v Postgresu jsou implementována vytvořením jedinečného indexu. Ale v případě zpožděného omezení není chování stejné, protože index nelze odložit a je vždy zkontrolován v okamžiku, kdy je spuštěn příkaz sql.

Podstata problému tedy spočívá v „odložené“ kontrole: v původní tabulce k ní dochází v době odevzdání a v nové v době provádění příkazu sql. Musíme se tedy ujistit, že kontroly probíhají v obou případech stejným způsobem: buď vždy se zpožděním, nebo vždy okamžitě.

Jaké jsme tedy měli nápady.

Vytvořte index podobný jako odložený

První nápad je provést obě kontroly v okamžitém režimu. To může vést k několika falešným pozitivům omezení, ale pokud je jich málo, nemělo by to mít vliv na práci uživatelů, protože takové konflikty jsou pro ně normální. Objevují se například tehdy, když dva uživatelé začnou upravovat stejný widget současně a klient druhého uživatele nestihne obdržet informaci, že widget je již zablokován pro úpravy prvním uživatelem. V takové situaci server odpoví druhému uživateli odmítnutím a jeho klient vrátí změny a uzamkne widget. O něco později, když první uživatel dokončí úpravy, druhý dostane informaci, že widget již není blokován, a bude moci svou akci zopakovat.

Abychom zajistili, že kontroly budou vždy v neodloženém režimu, vytvořili jsme nový index podobný původnímu odloženému omezení:

CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;

V testovacím prostředí jsme zaznamenali pouze několik očekávaných chyb. Úspěch! Znovu jsme spustili pg_repack na prod a za hodinu práce jsme dostali 5 chyb na prvním clusteru. To je přijatelný výsledek. Již na druhém clusteru se však počet chyb výrazně zvýšil a museli jsme zastavit pg_repack.

Proč se to stalo? Pravděpodobnost výskytu chyby závisí na tom, kolik uživatelů současně pracuje se stejnými widgety. Zřejmě v tu chvíli došlo k mnohem méně konkurenčním změnám s daty uloženými na prvním clusteru než na zbytku, tzn. máme prostě "štěstí".

Nápad nevyšel. V tu chvíli jsme viděli dvě další řešení: přepsat náš aplikační kód, abychom opustili odložená omezení, nebo „naučit“ pg_repack s nimi pracovat. Vybrali jsme to druhé.

Nahraďte indexy v nové tabulce odloženými omezeními z původní tabulky

Účel revize byl zřejmý – pokud má původní tabulka odložené omezení, pak pro novou je nutné takové omezení vytvořit, a ne index.

Abychom otestovali naše změny, napsali jsme jednoduchý test:

• tabulka s odloženým omezením a jedním záznamem;
• do smyčky vložíme data, která jsou v konfliktu s existujícím záznamem;
• provést aktualizaci - data již nejsou v konfliktu;
• provést změny.

create table test_table
(
  id serial,
  val int,
  constraint uk_test_table__val unique (val) deferrable initially deferred 
);

INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    COMMIT;
  END;
END LOOP;

Původní verze pg_repack spadla vždy při prvním vložení, upravená verze fungovala bez chyb. Skvělý.

Přejdeme na prod a znovu dostaneme chybu ve stejné fázi kopírování dat z tabulky protokolu do nové:

$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed: 
    ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL:  Key (id, index)=(100500, 42) already exists.

Klasická situace: vše funguje na testovacích prostředích, ale ne na produkci?!

APPLY_COUNT a spojení dvou dávek

Začali jsme analyzovat kód doslova řádek po řádku a zjistili jsme důležitý bod: data se přenášejí z tabulky protokolu do nové v dávkách, konstanta APPLY_COUNT udávala velikost dávky:

for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);

if (num > MIN_TUPLES_BEFORE_SWITCH)
     continue;  /* there might be still some tuples, repeat. */
...
}

Problém je v tom, že data původní transakce, ve které může několik operací potenciálně porušit omezení, se mohou během přenosu dostat na křižovatku dvou dávek - polovina příkazů bude potvrzena v první dávce a druhá polovina ve druhém. A tady, jaké štěstí: pokud týmy v první dávce nic neporuší, pak je vše v pořádku, ale pokud ano, dojde k chybě.

APPLY_COUNT se rovná 1000 záznamům, což vysvětluje, proč byly naše testy úspěšné – nepokryly případ „dávkového spojení“. Použili jsme dva příkazy - insert a update, takže vždy bylo v dávce umístěno přesně 500 transakcí dvou příkazů a nezaznamenali jsme problémy. Po přidání druhé aktualizace přestala naše úprava fungovat:

FOR i IN 1..10000 LOOP
  BEGIN
    INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
    UPDATE test_table set val = i where id = v_id;
    UPDATE test_table set val = i where id = v_id; -- one more update
    COMMIT;
  END;
END LOOP;

Dalším úkolem je tedy zajistit, aby se data z původní tabulky, která byla změněna v jedné transakci, dostala do nové tabulky také v rámci jedné transakce.

Odmítnutí dávkování

A měli jsme opět dvě řešení. Za prvé: odmítněme se rozdělovat do dávek úplně a udělejme z přenosu dat jedinou transakci. Ve prospěch tohoto rozhodnutí byla jeho jednoduchost – požadované změny kódu jsou minimální (mimochodem, ve starších verzích tehdy pg_reorg takto fungoval). Ale je tu problém - vytváříme dlouhodobou transakci, a to, jak již bylo zmíněno, je hrozbou pro vznik nového nadýmání.

Druhé řešení je složitější, ale pravděpodobně správnější: vytvořte v tabulce protokolů sloupec s identifikátorem transakce, která přidala data do tabulky. Poté je při kopírování můžeme seskupit podle tohoto atributu a zajistit, aby se související změny přenesly společně. Dávka bude tvořena z několika transakcí (nebo jedné velké) a její velikost se bude lišit v závislosti na tom, kolik dat bylo v těchto transakcích změněno. Je důležité si uvědomit, že jelikož data různých transakcí vstupují do tabulky protokolů v náhodném pořadí, nebude již možné je číst postupně, jako tomu bylo dříve. seqscan na každý požadavek filtrovaný pomocí tx_id je příliš drahý, potřebujete index, ale také to zpomalí metodu kvůli režii na její aktualizaci. Obecně, jako vždy, musíte něco obětovat.

Rozhodli jsme se tedy začít s první možností, jako jednodušší. Nejprve bylo nutné pochopit, zda dlouhá transakce bude skutečným problémem. Vzhledem k tomu, že hlavní přenos dat ze staré tabulky do nové probíhá také v jedné dlouhé transakci, byla otázka transformována na „o kolik tuto transakci navýšíme?“ Doba trvání první transakce závisí především na velikosti stolu. Doba trvání nového závisí na tom, kolik změn se v tabulce nahromadí během přenosu dat, tzn. na intenzitě zátěže. Ke spuštění pg_repack došlo v době minimálního zatížení služby a množství změn bylo nesrovnatelně malé ve srovnání s původní velikostí tabulky. Rozhodli jsme se, že čas nové transakce můžeme zanedbat (pro srovnání průměr je 1 hodina a 2-3 minuty).

Pokusy byly pozitivní. Uvedení také do prodeje. Pro názornost je zde obrázek s velikostí jedné ze základen po běhu:

Jelikož nám toto řešení zcela vyhovovalo, nezkoušeli jsme implementovat druhé, ale zvažujeme možnost projednat jej s vývojáři rozšíření. Naše aktuální revize bohužel ještě není připravena k publikaci, protože jsme problém vyřešili pouze s jedinečnými zpožděnými omezeními a pro plnohodnotný patch je třeba vytvořit podporu pro další typy. Doufáme, že se nám to v budoucnu podaří.

Možná máte otázku, proč jsme se do tohoto příběhu vůbec zapojili s vylepšením pg_repack a nepoužili jsme například jeho analogy? V určitém okamžiku jsme o tom také uvažovali, ale pozitivní zkušenost s jeho dřívějším používáním, na stolech bez opožděných omezení, nás motivovala k tomu, abychom se pokusili pochopit podstatu problému a opravit jej. Použití jiných řešení navíc vyžaduje čas na provedení testů, proto jsme se rozhodli, že se nejprve pokusíme problém vyřešit v něm, a pokud zjistíme, že to v rozumném čase nezvládneme, začneme zvažovat analogy.

Závěry

Co můžeme na základě vlastní zkušenosti doporučit:

1. Sledujte své nadýmání. Na základě monitorovacích dat budete schopni pochopit, jak dobře je autovakuum naladěno.
2. Nastavte AUTOVACUUM, abyste udrželi nadýmání na přijatelné úrovni.
3. Pokud nadýmání stále roste a vy si s ním nevíte rady s out-of-the-box nástroji, nebojte se použít externí rozšíření. Hlavní je vše dobře otestovat.
4. Nebojte se upravovat externí řešení podle svých potřeb – někdy to může být efektivnější a dokonce jednodušší než změna vlastního kódu.

Zdroj: www.habr.com