Horký tip jak optimalizovat PostreSQL

Pracuji v Heurece v týmu, který má na starosti několik datově velmi intenzivních služeb. Jednou z nich je OfferRank, služba, která zajišťuje, že se na Heurece zobrazí nabídky ve správném pořadí. Její databáze drží necelou miliardu řádků. Druhá služba, PriceService, která dodává informace o aktuálních slevách a historickém průběhu cen, má databázi, která po naplnění čítá několik miliard. To už nejsou úplně malá čísla.

Nejde ale jen o to, kolik dat v databázi je. Jde hlavně o to, co se s nimi děje. Je velký rozdíl, jestli se z dabáze primárně jen čte a zapíše se tam jednou za hodinu pár nových záznamů, nebo jestli se data velmi rapidně přepisují. Naše servisy jsou součástí komplexu mikroslužeb, které si predávájí data skrze RabbitMQ a denně k nám přitečou miliony záznamů, které je potřeba zpracovat, tedy stará data updatovat a na jejich základě přepočítat stovky a tisice dalších záznamů. Prakticky při každém updatu jedněch dat se spouští kaskáda jobů, které mění data jiná. Jinými slovy workflow na našich databázích by se dalo charakterizovat jako silně "update-heavy".

PostreSQL nativně bohužel není úplně nejlepší volbou pro takovéto workflow. 

Při standardním updatu PostgreSQL na pozadí vytvoří nový řádek a starý označí pro budoucí transakce jako neplatný. Důvodem je speciální implementace Multi-Version Concurrency Control, kterou Postgre využívá. MVCC funguje jedoduše řečeno tak, že každá transakce vidí jakoby svou 'vlastní' verzi databáze. Při aktualizaci řádku Postgre vytvoří její novou verzi, zatímco stará verze zůstává viditelná pro transakce, které byly zahájeny před touto změnou. Toto je možné díky použití systému transakčních ID (xmin a xmax), která říkají, kdy byl řádek vytvořen a kdy byl neplatný. Každý řádek v databázi tedy obsahuje meta informace o tom, kdy byl vytvořen a kdy přestal být platný. Více verzí řádku tedy exisuje současně bez konfliktu. 

To na jedné straně zvyšuje izolaci, zlepšuje paralelní zpracování a zabraňuje problémům, jako je kupříkladu dirty read. Na straně druhé to ale vede k větší fragmentaci dat, protože nové řádky se musí ukládat na nové stránky a zároveň se zvyšují nároky na I/O operace, protože více řádků musí být přečteno, než se najde ten správný. 

Další nevýhodou je i nutnost častějšího vacuumingu, což je proces, při kterém Postgre odstraňuje staré a nepotřebné řádky, tzv. dead tuples – je to vlastně taková analogie garbage collectoru na úrovni databáze. Jsou-li nějaké tabulky updatovány často a hodně, vede to k tomu, že se vacuují v podstatě non-stop, což pochopitelně bere nějaký výkon a databázi zpomaluje. 

V neposlední řadě, co je důležité si uvědomit, je, že jsou-li na tabulce nějaké indexy, tak se při standardním updatu řádku musí updatovat i tyto indexy. U malých tabulek to nevadí, ale u velkých, které mají velké indexy, to může zabírat dost času.

Co tedy dělat, když máte PostgreSQL databázi v aplikaci, kde se tabulky velmi často a intenzivně updatují?

Jednou větou?

Vyměňte Postgre.

MySQL, například, takový tok operací zvládá lépe.

Pokud však výměna databáze není možná, jak to bylo v našem případě, existuje jeden horký tip, tzv. HOT update.

Co je HOT update?

HOT není od slova "horký", ale je to zkratka pro Heap Only Tuple update. HOT update je optimalizační technika, která – jak už název napovídá – umožňuje aktualizaci řádku pouze na heapu, bez nutnosti aktualizovat index. To je možné samozřejmě za předpokladu, že data, která se na řádku aktualizují, nejsou součástí indexového klíče. Pokud se aktualizuje sloupec, který je indexován, HOT update nebude aplikovatelný, protože je nutné aktualizovat i samotný index. Tím, že se po každém updatu řádku nemusí aktualizovat i index, dochází k redukci I/O operací, které jsou v mnoha případech velkým bottleneckem.

Zároveň HOT update umožňuje, aby se nová verze řádku uložila na stejnou stránku, kde byla verze původní, pokud na této stránce zůstalo dostatečné místo (což je ovlivněno nastavením fillfactor). Toto snižuje nároky na vacuuming, protože moderní verze PostgreSQL umí provádět vacuuming pouze konkrétních stránek, bez nutnosti vacuumovat celou tabulku. Tím se také snižuje fragmentace dat, protože související záznamy zůstávají blíže k sobě, což zvyšuje rychlost sekvenčního vyhledávání.

Aby bylo možné vložit nový řádek na stejnou stránku jako řádek původní, je vhodné nastavit fillfactor nižší než defaultních 100%. Toto nastavení poskytuje rezervní prostor na stránce pro možné budoucí aktualizace, které tak mohou být prováděny efektivněji a s nižšími nároky na zdroje.

Co je fillfactor a jaký má vliv?

PostgreSQL ukládá řádky do bloků, které se nazývají stránky. Každá stránka má pevně definovanou velikost (defaultně je to 8 KB, ale lze přenastavit) a může tedy pojmout určitý počet řádků. Hodnota fillfactor udává, kolik procent z kapacity dané stránky bude PostgreSQL využívat pro uložení nově příchozích záznamů. Defaultně je fillfactor nastaven na 100%, což znamená, že se bude využívat celá kapacita stránky a žádné místo nebude volné.

Pokud se ale nastaví fillfactor na nižší hodnotu, například na 70%, pak PostgreSQL na každé stránce nechá 30% volného místa pro nové inserty anebo právě pro aktualizace v rámci HOT update. Nově aktualizovaný řádek tak může vložit přímo vedle toho starého původního. Pokud by tam místo nebylo, nový řádek by musel být uložen na novou stránku. Zvolit správně velký fillfactor je důležité: příliš nízký vede k tomu, že se zbytečně plýtvá diskovým prostorem, ale příliš vysoký může omezit efektivitu HOT updatů. V našich databázích obvykle volíme hodnotu kolem 70 až 80 procent, aby bylo dosaženo optimální rovnováhy mezi využitím prostoru a efektivitou aktualizací.

Jak se fillfactor nastavuje

Fillfactor lze nastavit již při vytváření tabulky pomocí SQL příkazu:

CREATE TABLE my_table (

id SERIAL PRIMARY KEY,

sometextcol VARCHAR(100),

someintcol INT

) WITH (FILLFACTOR = 70);

Pokud ho chcete nastavit na existující tabulce, použijte následující příkaz:

ALTER TABLE my_table SET (FILLFACTOR = 80);

Nicméně, pokud provedete změnu fillfactor na již existující tabulce, změna nebude mít okamžitý efekt. Aby se nové nastavení projevilo, je nutné provést VACUUM FULL na dané tabulce, nebo počkat, dokud nebude smazán dostatečný počet záznamů, který umožní reorganizaci dat podle nového nastavení, což prakticky ale nikdy nemužete vědět, kdy vlastně bude. VACUUM FULL přeuspořádá data v tabulce na disku, takže se může HOT využívat okamžitě. Důležité je také vědět, že VACUUM FULL je blokující operace, která během svého provádění vyžaduje exkluzivní zámek. Bere si access-exclussive lock, což je nejagresivnější lock vůbec. Jinými slovy to znamená, že tabulka nebude během provádění této operace vůbec dostupná pro jiné transakce, což může ovlivnit provoz vaší aplikace.

Jak ověřit, že se updatuje prostřednictvím HOT update

Pokud jste nastavili možnost HOT updates, pravděpodobně budete chtít občas zkontrolovat, jak efektivně je tato funkce využívána. Pro tento účel slouží dotazy na provozní statistiky databáze, které poskytují přehled o tom, kolik aktualizací bylo provedeno jako HOT updates. Následující SQL dotaz vám umožní získat potřebné údaje.

SELECT

relname AS table_name,
n_tup_upd AS updates,
n_tup_hot_upd AS hot_updates

FROM

pg_stat_user_tables

WHERE

relname = 'nazev_tabulky';

Tento dotaz zobrazí počet běžných aktualizací (n_tup_upd) a počet HOT updates (n_tup_hot_upd) pro zvolenou tabulku. V ideálním případě by mělo dojít k tomu, že čísla pro updates a hot_updates budou velmi blízká nebo dokonce stejná, což by indikovalo, že většina aktualizací, resp. všechny, se provedly prostřednictvím HOT mechanismu.

Pro širší přehled o využití HOT updates napříč databází, můžete použít rozšířenější dotaz. To se hodí především v případech, že máte na tabulkách implementovaný partitioning jako je to v našem případě.

SELECT

relname AS table_name,
seq_scan AS sequential_scans,
idx_scan AS index_scans,
n_tup_ins AS inserts,
n_tup_upd AS updates,
n_tup_hot_upd AS hot_updates

FROM

pg_stat_user_tables

ORDER BY

hot_updates DESC;

Tento dotaz poskytuje kompletní přehled o aktivitě databáze, včetně počtu sekvenčních a indexových scanů, insertů, updatů a HOT updatů pro všechny uživatelské tabulky. Výsledky vám umožní identifikovat, které tabulky nejvíce využívají HOT a mohou tak indikovat, zda jsou fillfactor a další relevantní nastavení optimálně konfigurovány pro vaše potřeby.