Základní konfigurace PostgreSQL
Autor: Pavel Stěhule
Pokusím se vám ukázat, že konfigurace Postgresu je směšně jednoduchá. V první části se budu věnovat základnímu nastavení, které určuje, kolik zdrojů (RAM, IO) si může pro sebe vzít databáze.
max_connection udává, kolik mohu mít naráz běžících dotazů – zpracování každého dotazu běží v samostatném procesu. Při velkém množství procesů a malém množství procesorových jader vznikne velká režie z důvodu přepínání procesů. Při velkém množství procesů může vzniknout velký tlak na sdílenou paměť a naroste režie spin locků (zámků, u kterých se předpokládá extrémně krátká doba zamknutí – pokud se na uvolnění těchto zámků čeká trochu déle, tak extrémně roste zátěž CPU). max_connection je pojistka proti přetížení serveru. Je bezpečnější, když Postgres bude odmítat spojení a zabrání se přetížení serveru než když dojde k přetížení serveru a nucenému tvrdému resetu (to už hrozí poškození souborového systému). Pro dedikovaný server používaný jako OLTP databáze je doporučená hodnota pro max_connection cca 10–20 násobek počtu jader CPU.
Při velkém množství procesů na každý proces zbyde méně operační paměti (databázový server by neměl swapovat) – plánovač pak nemůže používat rychlé, ale paměťově náročné algoritmy – a musí používat pomalejší, paměťově úspornější algoritmy, které zatěžují IO a jejich rychlost je silně závislá na aktuálním zatížení IO. Navíc musíme mít na paměti, že u klasických disků dost rychle klesá rychlost čtení a zápisu při větším počtu paralelně prováděných operací.
Počet běžících dotazů musí odpovídat hw zdrojům, a je nutné nějakým způsobem omezit aktivní dotazy. Postgres používá nejstarší a nejjednodušší techniku – dalším uživatelům se nedovolí přihlásit. MySQL, kde zpracování dotazů běží ve vláknech, používá thread pool (pouze omezený počet vláken je aktivní). Analytické databáze, jako je např. Vertica, používají query pool – dotaz čeká na zahájení zpracování, dokud na serveru není dostatek volných zdrojů (operační paměť, vlákna, …).
U typické webové aplikace cca 30 souběžných spojení obslouží deset tisíc přihlášených uživatelů do databáze. Výchozí stovka by tedy měla stačit většině uživatelů. Problémy mohou být u starších nebo špatně napsaných aplikací, kde každý klientský proces si alokuje připojení na startu a drží si jej až do svého konce. Situaci může zhoršit, když je klient špatně napsaný a neuvolní spojení při svém zániku. Postgres sám o sobě spojení aktivně netestuje, a pokud neposílá výsledky na klienta, tak se nedozví, že klient už neexistuje – nicméně na straně serveru žije stále obslužný proces a čerpá svou jednotku z počtu max_connection. Kdykoliv jsem viděl nějakou šílenou hodnotu max_connection, tak se jednalo o chybu aplikace – connection leaks, neuzavírání transakcí, … Jedná se o problém, který nelze vyřešit na straně databáze – oprava na straně klienta je naprosto nezbytná (a z dlouhodobého hlediska optimální).
Některé problémy s klienty lze vyřešit nasazením PgBounceru – což je proxy aplikace mezi Postgresem a klientem. Tato aplikace odstíní server od klienta – vytváří logická spojení, která pak mapuje na fyzická spojení do serveru. Použitím PgBounceru můžeme potlačit negativní efekty některých chyb klienta (ne všechny). Pokud klient například neukončuje transakce, tak mi PgBouncer nepomůže. Otevřená transakce zabraňuje znovu použití spojení – a navíc může snižovat účinnost příkazu VACUUM – což má zase negativní efekt na vnitřní uspořádání souborů (klesá informační hustota, roste velikost, roste doba na sekvenční čtení, je potřeba více cache pro méně informací, …). Výsledkem je téměř nefunkční aplikace. Příznaky jsou ale naprosto jasné – požadavek na velký počet max_connection, velký počet neaktivních spojení případně spojení dlouhodobě ve stavu idle in transaction. Menší problémy s neodhlašujícími klienty může pomoci vyřešit nastavení keep alive packetů – tcp_keepalives_idle. Díky nim se Postgres (server) může výrazně dříve dozvědět, že je klient neaktivní (pro aplikace, které používají vlastní connection pool, to ale nemusí nic znamenat – klient je dole, ale obsluha connection poolu je stále živá a komunikuje).
PgBouncer umí i refresh spojení (sezení). Postgres (stejně jako všechny ostatní aplikace v Linuxu) vrací alokovanou paměť operačnímu systému až v době zániku procesu (v Postgresu po odhlášení uživatele). Pokud klient používá dlouhodobé připojení k databázi, nebo se připojení používá opakovaně a nezahazuje se, tak roste alokovaná paměť uvnitř obslužných procesů a ve výsledku se paměť nemusí používat efektivně. Když použiji PgBouncer, mohu nastavit maximální stáří fyzického spojení na server, a pokud se tohoto stáří dosáhne, tak se toto spojení zavře a dále nepoužívá (rozumná hodnota je cca mezi 5 min a 1 hod).
Výchozí hodnota 100 je pro typické použití v pohodě a většinou není důvod měnit nastavení max_connection.
Konfigurační proměnná shared_buffers určuje, kolik paměti se bude používat jako sdílená cache datových stránek (read/write). Tato paměť se alokuje při startu Postgresu a je sdílená všemi procesy Postgresu. Do určité velikosti platí, že čím víc, tím lépe – pokud data najdu ve sdílené paměti, nemusím skrze systémové služby načítat data z disku (případně z cache souborového systému). Pokud mám volnou cache datových stránek mohu zapisovat do souborů s větším zpožděním (write cache může být efektivnější) Přístup do sdílené paměti je výrazně rychlejší než systémové volání. Na druhou stranu je správa sdílené paměti poměrně jednoduchá, a pokud je sdílená paměť opravdu velká (větší jednotky, desítky gigabajtů), tak některé operace mohou být relativně pomalé (a je nutné ladit několik dalších parametrů). Typické nastavení u dedikovaného serveru je 1/4 RAM (a to do velikosti cca 15 GB).
Většinou výsledek záleží na celé souhře několika faktorů a kolikrát se nedá dost dobře odhadnout. Zvětšením sdílené paměti může dojít k zrychlení interních operací Postgresu, přičemž ale zvětšení sdílené paměti znamená snížení velikosti cache souborového systému a zpomalení IO operací. Co převáží a jaký bude výsledný efekt, se vůbec nedá odhadnout (zvlášť pokud se skutečně naráží na limity hw).
V každém případě je výchozí hodnota pro dedikované servery pro dnešní typické servery nízká a téměř vždy se zvyšuje.
work_mem
Konfigurační parametr work_mem určuje, kolik paměti může být použito pro paměťově náročnou operaci při zpracování dotazů. V některých případech to znamená limit pro optimalizátor, jindy je to používaný limit v executoru, a po jeho dosažení se vypočítaná data začnou zapisovat do dočasných souborů. Plánování dotazů je v podstatě volba mezi čtením dat sekvenčně a čtením dat náhodným přístupem, a volbou mezi paměťově náročným (a rychlým) algoritmem (např. hashjoin, quicksort) nebo paměťově úsporným (a pomalejším) algoritmem (mergejoin, external sort). Čím je větší work_mem, tím je větší pravděpodobnost, že se použijí paměťově náročné (ale rychlé) algoritmy. Zde platí, že čím víc, tím lépe. Limitem je ale velikost operační paměti. Pokud nastavíme work_mem na příliš vysokou hodnotu, může server začít swapovat a tím silně degradovat výkon serveru.
Opět – výchozí hodnota work_mem je velice nízká a obvykle se zvětšuje. Pro některé dotazy může být ještě dočasně navýšená příkazem SET.
Musí platit
Shared buffers + max_connection * work_mem * 2 + file system cache + operation system <= RAM
V rámci jednoho dotazu se může použít víc bloků work_mem – proto ta bezpečnostní konstanta 2. Všimněte si – čím je větší max_connection, tím menší musí být work_mem nebo shared_buffers.
Pro konfiguraci work_mem mohou být zajímavé hodnoty temp_files a temp_bytes ze systémové tabulky pg_stat_database. Pokud jsou nulové (nebo blízké nule), tak je hodnota work_mem dostatečná a není třeba ji měnit.
maintenance_work_mem
Víc paměti se může hodit i pro příkazy jako je VACUUM, ANALYZE nebo CREATE INDEX. Jelikož tyto příkazy obvykle nepoužívají všichni uživatelé (také v rámci jednoho příkazu alokují paměť pouze jednou), je pro ně speciální nastavení paměti – maintanance_work_mem. Tato konfigurační proměnná se může nastavit výrazně víc, než je work_mem (nemusím tady násobit max_connection), musíme mít ale stále na paměti, že databázový server nesmí aktivně swapovat. Může se nastavit jako 10násobek work_mem, můžeme zkoušet rychlost příkazu VACUUM nebo CREATE INDEX u velkých tabulek a zvolit rozumný kompromis.
V novějších verzích PostgreSQL je ještě proměnná autovacuum_work_mem, která se váže k příkazu VACUUM spuštěného obslužným procesem autovacuum. Výkonných procesů může být více než několik málo – při zvětšení hodnoty autovacuum_max_workers by pak hodnota maintenance_work_mem mohla způsobovat problémy, a tudíž můžeme nastavit nižší hodnotu do autovacuum_work_mem.
random_page_cost, seq_page_cost
random_page_cost a seq_page_cost jsou parametry optimalizátoru. Jejich poměr vyjadřuje penalizaci náhodného čtení – výchozí hodnota je 4/1 – tj náhodné čtení (použití indexu) je 4 krát dražší než sekvenční čtení. Čím je tato penalizace větší, tím menší část tabulky se musí číst, aby se použil index.
Při optimalizaci dotazů (vynucování si indexů) vývojáři dělají jednu zásadní chybu – testují výkonnost na nezatíženém serveru. Použití indexu nemusí vždy znamenat výhodu – index si vynutí čtení dat s náhodným přístupem, které je pomalejší a které víc zatěžuje IO. Na nezatíženém vývojářském (testovacím) serveru může všechno vypadat jinak než na produkčním severu. Pokud vývojář nebo administrátor neadekvátně změní poměr cen, tak si na produkčním systému způsobí větší zátěž IO a následně zpomalení celého systému.
Nicméně existují dvě situace, kdy je změna poměru cen náhodného a sekvenčního čtení na místě. První situací je použití SSD disků – pro tyto disky už čtení s náhodným přístupem neznamená takový problém jako u klasických rotačních disků. Druhou situací je dostatek RAM, kdy prakticky všechna živá data se udrží v cache souborového systému nebo v shared buffers Postgresu. Tato technika se dost často používá v cloud prostředích, kde výkon IO je často dost nepredikovatelný (a paměť docela levná). Pokud máte alespoň o 1/3 více RAM než je velikost databáze, tak by se fyzické IO mělo používat téměř pouze pro zápis. V těchto dvou případech se mění poměr cen na 2/1 nebo 4/2.
Při produkčním použití Postgresu je opravdu dobré si rozběhnout monitoring serveru – nejdůležitějšími metrikami jsou využití paměti (použití swapu), utilizace IO (IO waits, iops, čtení, zápisy), počet spojení do databáze (aktivní, neaktivní, neaktivní v transakci). Z těchto hodnot se dá pak vyčíst, kde a v čem jsou limity serveru, případně zda server má rezervy výkonu.