Optimalizace PostgreSQL – Statistiky a checkpointy

Statistiky

Každý optimalizátor prováděcího plánu, PostgreSQL nevyjímaje, potřebuje údaje o datech v tabulkách. K tomu mu slouží statistiky počítané z těchto dat (viz dokumentace příkazu ANALYZE, který slouží právě k aktualizaci statistik nad tabulkou). Tento příkaz (resp. jeho variantu VACUUM ANALYZE, která jednak uklidí datový soubor a také přepočítá statistiky) též spouští proces autovacuum. Objem informací (resp. a konkrétněji histogram hodnot ve sloupcích), které se při analýze počítají, je dán parametrem default_statistics_target. Vyšší hodnoty způsobí delší analýzu tabulek, ovšem také přinesou (někdy výrazně) lepší prováděcí plán, dotazy budou výrazně rychlejší.

Ve starších verzích (8.3 a starší) byla výchozí hodnota parametru default_statistics_target velmi nízká (10). Od verze 8.4 a vyšší je pak výchozí hodnota 100. Pro některé typy dotazů (obsahující operátor LIKE používající index) jsou vhodné ještě vyšší hodnoty objemu statistických dat. Maximální hodnota pro verze 8.4 a vyšší je pak 10000.

Ovšem pozor. Nepomůže jen slepě zvětšit globální hodnotu v konfiguračním souboru. Příliš vysoká hodnota by znamenala zpomalení údržbových operací a nepřinesla by (až na výjimky) lepší provádění dotazů. Což je také důvod, pro některé benchmarky verzí ukazují pro verzi PostgreSQL DB 8.4 nižší hodnoty než pro verzi 8.3, která používala výrazně menší objem statistik (jak již bylo uvedeno – 10, oproti novějším 100).

Pokud tedy některé dotazy potřebují mít lepší statistiky, je možné upravit nastavení pro jednotlivé sloupce, pomocí příkazu ALTER TABLE SET STATISTICS na vyšší hodnoty (maximálně 10000) a globální nastavení mít na rozumně nízké úrovni.

Checkpointy

Všechna data zapsaná do transakčního logu je potřeba zapsat do datových souborů. PostgreSQL zapisuje data dvakrát. Poprvé sekvenčně do velmi rychlého (a malého, relativně k velikost datových souborů) logu a po nějaké době pak do datových souborů (událost checkpoint). Checkpoint tak zaručuje, že všechna data zapsána do logu budou v pořádku zapsána do datových souborů. Nastavení a umístění transakčního logu je tak důležité pro rychlost zápisu do databáze.

Zápis checkpointu představuje obrovskou zátěž především pro diskový subsystém a je tak rozprostřen v čase. Velký transakční log přispívá jednak k vysoké rychlosti databáze (díky tomu, že je možné do něj zapisovat sekvenčně), na druhou stranu výrazně zpomaluje proces zotavení po pádu (vhledem k jeho architektuře je nutné jej přečíst a zpracovat celý do stránek datových souborů). Toto lze řídit parametry v souboru postgresql.conf:

checkpoint_segments

Počet segmentů logu. Společně s dalším parametrem v podstatě definuje maximální velikost logu na disku. Každý WAL segment zabírá na disku maximálně 16 MB prostoru. Skutečný počet těchto segmentů na disku lze určit podle rovnice:

(2 + checkpoint_completion_target) * checkpoint_segments + 1

PostgreSQL 8.3 a starší ještě neměl zápis checkpointů rozprostřený v čase, pro starší verze pak tato rovnice bude platit s dosazením checkpoint_completion_target=0.

Nejjednodušší cesta pro nastavení parametrů je určení velikosti místa na disku, a tím pádem také délku zotavení po případném pádu stroje, které chceme pro transakční log použít (například podle velikosti vyhrazeného rychlého pole). Očekávaná velikost místa pro pg_xlog adresář, kam se transakční log ukládá, je potom:

Zjednodušeně lze psát, že pro každých 32 segmentů je potřeba přibližně jeden gigabajt místa. Přesně tolik dat je potřeba přečíst v případě pádu databázového stroje a lze tak dopředu odhadnout čas nutný pro zotavení. Základní nastavení jsou tři segmenty, což je velmi málo. Dobrý začátek je nastavení checkpoint_segments = 10, tedy přibližně půl gigabajtu místa. Většinou není potřeba na běžném serveru nastavovat počet segmentů na více než 32.

checkpoint_timeout

Výchozí hodnota pro ukládání dat z logu do datových souborů je pět minut a je vhodná pro většinu systémů. Pokud systém nestíhá zapisovat stránky do datových souborů, je možné zvýšit počet segmentů logu a také timeout.

Datové soubory mohou být na jiném typu úložiště, např. na RAID 5, než soubory s transakčními logy (adresář pg_xlog), které mohou mít vyhrazeny rychlé a drahé disky a RAID 10. Snadno se tak stane, že úložiště trvalých datových souborů má pomalejší zápis než úložiště pro transakční log. Pro rychlost dotazů SELECT je nutná pouze rychlost čtení datových a indexových souborů, pro zápisové operace (INSERT, UPDATE, ALTER), pak rychlost zápisu transakčního logu.

Toto zvýšení není nijak nebezpečné, data jsou po potvrzené (COMMIT) transakci vždy uložena na disk (nejprve do WAL logu a poté do datových souborů), ovšem prodlouží dobu zotavení databáze po pádu (je nutné zpracovat větší transakční log).

checkpoint_completion_target

Udává rozprostření zápisů v čase, což s sebou přináší minimální zatížení diskového IO (není nárazové). Výchozí hodnota je 0.5, což znamená, že data budou zapsána do datových souborů v polovině času, před dalším zápisem checkpointu. Maximální hodnota je 1.0 (zápis checkpointu je zcela rozprostřen na maximální čas mezi událostmi), ovšem hrozí riziko, že další zápis začne dříve, než se stihne na disk uložit celý předchozí checkpoint (což zápis naopak výrazně zpomalí). Vhodná maximální hodnota je tak 0.9. Výchozí hodnota je dobrá pro většinu serverů a není většinou potřeba ji měnit. Nižší hodnoty než 0.5 nemají příliš smysl a přinášejí s sebou nárazový zápis do datových souborů.

checkpoint_warning

Příliš časté ukládání checkpointů do datových souborů může znamenat příliš malý transakční log (a pomalou databázi, resp. pomalejší zápis, než by mohl být) pro danou pracovní zátěž. Pomocí volby checkpoint_warning lze nastavit čas mezi checkpointy (výchozí hodnota je třicet sekund). Pokud jsou dvě události zápisu checkpointu od sebe vzdáleny méně než checkpoint_warning sekund, je o této skutečnosti zapsaná hláška do chybového logu PostgreSQL serveru. V případě mnoha častých zápisů (hlášek v logu) je vhodné zvážit zvýšení počtu segmentů transakčního logu a nebo zrychlení úložiště datových souborů. Toto nastavení je pouze informativní a nemá vliv na rychlost zápisu checkpointů. Nastavení checkpoint_warning = 0 pak zcela vypíná zaznamenávání této události do chybového logu.