Dnešním dílem začínáme seriál o optimalizaci oblíbené open-source databáze PostgreSQL. V seriálu projdeme většinu konfiguračních voleb (platné pro verze 8.1 až 9.0) s cílem vysvětlit, co znamenají, a jak je nastavit pro vysoký výkon celé databáze. Nečekejte ale optimalizaci databázového schématu, budeme se věnovat čistě nastavením databáze na serveru.
Záznam činnosti databáze
Logování událostí a chyb je velmi důležité pro administrátora systému a i když přímo nepatří do ladění výkonnosti, tak dobře nastavený log může pomoci najít slabá místa v konfiguraci a odhalit dlouhotrvající dotazy. V příslušné sekci konfiguračního souboru postgesql.conf (/var/lib/pgsql/data/postgesql.conf) jej mají na starosti parametry log_destination, log_directory, log_filename, které však bývají nastaveny správcem dané distribuce, např.: CentOS 5.5:
log_destination = 'stderr' log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
Události jsou pak ukládány do souborů v adresáři pg_log v základním adresáři PostgreSQL (například /var/lib/pgsql/data), soubory jsou pojmenovány podle aktuálního data a času.
Předpona řádku logu (log_line_prefix)
Ve výchozím nastavení řádek v logu nemá žádnou předponu, což je nepraktické (není poznat, který uživatel které databáze chybu způsobil a kdy). Pro začátek je dobré nastavit například:
log_line_prefix='%t:%r:%u@%d:[%p]: '
Kde:
%u Uživatelské jméno (přihlášeného uživatele databáze)
%d Jméno databáze
%r Klientský počítač včetně čísla portu
%p PID procesu na straně serveru
%t Časová značka bez milisekund
Více údajů pro záznam do řádku logu je uvedeno v dokumentaci.
Zaznamenávání příkazů jazyka SQL (log_statement)
log_statement = ddl
Možnosti (kompletní dokumentace opět na webu):
none Příkazy SQL nebudou ukládány do logů
ddl Do logu se budou zapisovat pouze příkazy DDL jako CREATE (TABLE, DATABASE), DROP, ALTER.
mod Příkazy, které modifikují data. Tedy například: INSERT, DELETE. Jsou zahrnuty taktéž příkazy DDL.
all Všechny příkazy.
Obvykle není potřeba DDL příkazy zaznamenávat, případně jen z kategorie mod nebo ddl. Typická databáze výrazně více čte, než zapisuje, a těchto příkazů bude tedy minimum.
Na hledání problematických dotazů je jejich logování vynikající pomůcka, zvláště v kombinaci s následující volbou záznamu pouze dlouhých dotazů.
Dlouhé dotazy (log_min_duration_statement)
Příkazy trvající déle než log_min_duration_statement (v milisekundách) se zapíší do logu. Tato volba tak představuje ideální prostředek na hledání pomalých dotazů.
log_min_duration_statement = 200
Za dlouhé dotazy (v případě interaktivní aplikace) se považují dotazy delší než 200 ms. S tímto nastavením je pak možné povolit záznam všech dotazů, v případě optimalizované databáze a aplikace bude log vždy prázdný.
V PostgreSQL 8.4 a novějším je možné také použít modul autoexplain. Jeho použití je flexibilnější (je možné jej volat z klienta a není třeba modifikovat konfiguraci) a také přináší více informací, nejenom dotaz samotný, ale také výsledek příkazu EXPLAIN (výpis prováděcího plánu) v době běhu dotazu. Ten se může radikálně lišit od pozdějšího spuštění po přepočítání statistik apod.
Vakuum a statistiky
PostgreSQL vyžaduje dva druhy pravidelné úklidové práce, které přímo vychází ze způsobu ukládání datových souborů filozofií MVCC: systém ukládání nových verzí řádků místo modifikace původních umožňující souběžné zpracování zápisových a čtecích transakcí a jejich izolací. Datové řádky se tedy nikdy nemění, v případě potřeby změny či přidání dat se vždy vytvoří nový (aktuální) řádek a starý se pouze označí za neplatný (ve skutečnosti celé skupiny datový řádků spadající do jedné transakce). Tyto staré řádky je potřeba pravidelně odstraňovat (odtud výraz vakuum, anglicky vysávání). Také je potřeba udržovat aktuální statistiky datových sloupců pro výběr optimálního prováděcího plánu.
Tyto operace (a také příslušné SQL příkazy) se nazývají VACUUM a ANALYZE. Ve zkratce, příkaz VACUUM uklidí datové soubory a odstraní z nich staré řádky po potvrzených transakcích, řádky smazané příkazem DELETE apod. Příkaz VACUUM FULL také zmenší velikost datových souborů.
Příkaz ANALYZE projde datové řádky a aktualizuje statisticky, což významně napomáhá optimalizátoru dotazů vybrat správný prováděcí plán. Více v dokumentaci. Oba příkazy se spojily do jednoho: VACUUM FULL ANALYZE.
Příklad z praxe: Zákazníkův databázový server běžel (jak to tak bohužel bývá) v základní konfiguraci. Ta je jednak paměťově velmi úsporná, a tak databáze neúměrně zatěžovala disk (nevyužívala cache v paměti a tedy byla dost pomalá), ale také bez aktivního procesu autovacuum (verze PostgreSQL 8.1). Což dále výrazně zpomalovalo chod celé databáze a také významně ukusovalo z volného místa na disku. Po provedení příkazu VACUUM FULL ANALYZE a úpravě konfigurace se velikost datových souborů smrskla na 1/8 (z několika GB nasbíraných asi za čtrnáct dnů provozu na stovky MB) a zpracování SQL dotazů se mnohonásobně zrychlilo.
Proces autovacuum a starší verze PostgreSQL
Ve starších verzích se musel úklid příkazem VACUUM dělat ručně buď z aplikace (tato možnost je i dnes, ale není to vůbec nutné, a v některých speciálních případech ani žádoucí používat automatické spouštění úklidu), případně z nějakého skriptu volaného pravidelně z cronu.
Ve verzi PostgreSQL 8.1 přibyl proces autovacuum, který spouštěl příkaz VACUUM podle potřeby (z analýzy změněných řádků v datových souborech). Tento proces byl však až do 8.3 v základním nastavení vypnutý. Od verze 8.3 je naopak standardně zapnutý. Ve starších verzích je potřeba jej zapnout nastavením v postgresql.conf:
stats_start_collector=true stats_row_level=true autovacuum=on
Příkazu VACUUM a procesu autovacuum byla v průběhu vývoje databáze věnována velká péče a v každé nové verzi je výrazně efektivnější a méně omezuje běžný databázový provoz. Je proto vhodné aktualizovat databázový server na vyšší verzi (Pavel Stěhule, český vývojář a popularizátor PostgreSQL, doporučuje v produkčním prostředí udržovat verzi o stupeň starší, než je aktuální. V těchto dnech, po vydání 9.0, je tedy vhodná produkční verze 8.4).
Velikost pracovní paměti
Pracovní paměť je v databázovém serveru potřeba pouze pro několik úkonů, a to konkrétně pro operace typu VACUUM, CREATE INDEX a ALTER TABLE ADD FOREIGN KEY. Tyto tak mohou alokovat velikost paměti maintainance_work_mem nad rámec běžné paměti přidělené klientovi. Navíc je nepravděpodobné, že by takových operací běželo více současně, proto je možné přidělit větší objem paměti než pro standardní klientskou práci.
Je dobré mít na paměti, že tuto paměť použijí i procesy autovacuum (počet těchto procesů se nastavuje pomocí volby autovacuum_max_workers).
Dobré počáteční nastavení pro moderní servery s dostatkem paměti používá 5 % velikosti paměti RAM (tj. 50 MB na každý GB paměti), například pro server s 8 GB RAM je vhodné nastavit:
maintainance_work_mem = 400MB
