Desatero

Z PostgreSQL
Verze z 29. 10. 2011, 07:59, kterou vytvořil Pavel (diskuse | příspěvky) (Analyzujte)

(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)
Přejít na: navigace, hledání

Autor: Pavel Stěhule, 1.11.2007 (aktualizováno 29.9.2011)

Dobře nakonfigurovaná a dobře provozovaná databáze se Vám odvděčí perfektním, stabilním a spolehlivým výkonem. To ostatně platí pro každou aplikaci. Následující desatero obsahuje doporučení a odkazy, co a jak dělat (nastavit) v PostgreSQL, aby databáze mohla běžet rychle a se stabilním výkonem.

Vacuujte

S mírnou nadsázkou by se dalo konstatovat, že příkaz VACUUM je v PostgreSQL nejdůležitějším příkazem. Orientačně se doporučuje příkaz VACUUM spouštět jednou denně a VACUUM FULL jednou měsíčně. To "orientačně" znamená, že přibližně dojde ke změně 10% řádků tabulky. Lehčí varianta příkazu (pouhé VACUUM) zajistí rychlý přístup k platným záznamům v tabulce a uvolní prostor v datových souborech, který byl alokován již neplatnými záznamy. Nedochází k fyzickému přeuspořádání záznamů na disku a také vacuovaná tabulka není uzamčena. Vždy je důležité si ověřit, zda tento příkaz byl zpracován úplně. Pokud nestačí vyhrazená paměť pro evidenci volných stránek příkaz se neprovede. (proměnné MAX_FSM_PAGES a MAX_FSM_RELATIONS v postgresql.conf).

Přibližně jednou za měsíc se doporučuje spustit plné VACUUM (VACUUM FULL). V případě, že nestačí paměť pro evidenci volných stránek, pak je opět nutné použít VACUUM FULL. Jelikož VACUUM FULL provádí fyzické přeuspořádání záznamu na disku (setřesení), dochází k odstranění alokovaných ale prázdných stránek datového souboru. Tím se jednak zmenší velikost datových souborů, jednak dojde k "zahuštění" uložených dat a tím i k rychlejšímu přístupu. Analogií je defragmentace souborů na disku. Příkaz VACUUM FULL zamyká tabulku.

Frekvence volání těchto příkazů je úměrná frekvenci a účinku příkazů UPDATE a DELETE v databázi. Pokud máte stabilní databázi, kde nedochází ke změnám dat, tak není nutné spouštět VACUUM často. Naopak, čím jsou data v databázi dynamičtější, tím častěji je nutné spouštět VACUUM. Novější verze PostgreSQL obsahují tzv. autovacuum, což je proces, který sleduje počet změněných řádků, a v případě, že tato hodnota přesáhne nastavený limit, spustí VACUUM tabulky. Příkaz VACUUM lze aplikovat na databázi nebo tabulku. Nemá smysl vacuuovat stabilní tabulky. Lépe je spouštět VACUUM o něco dříve než je nutné než příliš pozdě. Velký počet mrtvých záznamů v tabulce (nad 100%) vede k citelné degradaci výkonu databáze a naopak ke gradaci zátěže serveru.

Dostatek údajů pro rozhodování Vám poskytne "upovídanější" varianta příkazu - VACUUM VERBOSE a výstup z diagnostické funkce pgstattuple (ze stejnojmenného rozšiřujícího modulu (contrib). Orientačně, 10-20% mrtvých záznamů - VACUUM, využito méně než 30% alokovaného prostoru na disku - VACUUM FULL.

U novějších verzí (8.4 a vyšší) se o správné načasování spuštění VACUUM stará automatický proces (autovacuum).

Příkaz VACUUM FULL můžete urychlit odstraněním indexů. Po dokončení příkazu indexy znovu vytvoříte.

Komerční odkaz: Všeobecné školení PostgreSQL

Analyzujte

Pro každý SQL příkaz existuje tzv. prováděcí plán. Je to posloupnost nízkoúrovňových příkazů, jejichž provedení zajistí korektní naplnění výsledné množiny. Ke každému netriviálnímu SQL příkazu existují desítky až tisíce různě efektivních prováděcích plánů. Výběr jednoho konkrétního, který se bude provádět, je úkol tzv. optimalizátoru. Optimalizátor, použitý v PostgreSQL, vychází ze statistik. Ke každému sloupci se udržuje histogram hodnot a orientační počet řádek. Pak se relativně snadno určí efekt podmínky. Pokud ale statistiky neodpovídají reálným datům, pak optimalizátor 100% nevybere optimální prováděcí plán. Tento problém se identifikuje snadno. V detailu prováděcího plánu EXPLAIN ANALYZE se rozchází odhady počtu zpracovávaných řádků a skutečného počtu zpracovávaných řádků.

O aktualizaci statistik se postará příkaz ANALYZE. Ten by se měl spouštět při změně 20 % řádků (samozřejmě, že vždy po každém importu). Stejně jako VACUUM i příkaz ANALYZE sekvenčně prochází tabulku. Celkem logicky se spojily do jednoho příkazu VACUUM ANALYZE. Přesnost odhadu závisí na počtu tříd v histogramu. Výchozí hodnota 100 tříd (default_statistics_target) nemusí být pro některá data dostatečná a doporučuje se zvýšit na 100-200 tříd (maximum je 10000). Naopak se nedoporučuje tuto hodnotu zvyšovat více než je nutné, dochází ke zpomalení optimalizace (optimalizátor musí zpracovat větší objem vstupních dat).

Pozn. V 8.3 a starších verzích byla výchozí hodnota default_statistics_target rovna 10, což bylo častokrát nedostačující a doporučovalo se nastavení této hodnoty na minimálně 100.

U novějších verzí (8.4 a vyšší) se o správné načasování spuštění ANALYZE stará automatický proces (autovacuum).

Optimalizujte

Správně navržená aplikace nesmí generovat pomalé dotazy! Každý pomalý dotaz alokuje zdroje serveru a zpomaluje provádění ostatních dotazů. Za pomalý dotaz se považuje dotaz, jehož zpracování trvá déle než 200 ms (pro interaktivní aplikace z důvodu uživatelského komfortu). Věřte, že to není až tak nereálné kritérium. Samozřejmě, že existují pomalejší dotazy, ale ty by se neměly vyskytovat v interaktivních aplikacích a v pracovní době. Jelikož také tady platí pravidlo 80/20, které v tomto případě znamená, že 20 % příkazů generuje 80 % zátěže, stačí se zaměřit na 20 % dotazů, které generuje aplikace. S jejich identifikací nám pomůže pgfouine. V případě typických www aplikací je zásadní použití cache. Optimalizace dotazu spočívá ze 70 % přidáním indexů, z 20 % přepsáním dotazu (aby se aplikoval index) a zbylých 10 % představuje změnu přístupu (např. ochranu proti vygenerování pomalých dotazů vynucením zadání dostatečného počtu kritérií, omezením výpisů na určité měsíce, LIKE nahradit fulltextem, atd). Využívejte podmíněné a funkcionální indexy.

Vývojovou databázi nadimenzujte na předpokládaný objem po 5-10 letech provozu systému. Indexy přidávejte uvážlivě. Každý index může zásadně urychlit provádění dotazu, ovšem za cenu mírného zpomalení ostatních operací (aktualizace indexu má také režii). Index nad primárním klíčem je automaticky, indexem nad cizím klíčem zpravidla nic nezkazíme (pokud má tabulka alespoň 1000 řádků). Z výpisu detailu prováděcího plánu zjistíme, které indexy se použily, které tabulky se čtou sekvenčně, atd. Výpis získáme příkazem EXPLAIN ANALYZE. Před detailní analýzou nezapomeňte spustit ANALYZE. S optimalizací souvisí normalizace. Nenavrhujte široké tabulky - je to jedna se začátečnických chyb.

Hodnota efective_cache_size je ve výchozí konfiguraci zbytečně nízká. Na základě této hodnoty se optimalizátor rozhoduje mezi použitím sekvenčního čtení a čtením s pomocí indexu. Použití indexů je náročnější, a proto systém upřednostní sekvenční čtení, pokud je jen malá naděje (nízká hodnota efective_cache_size), že se index udrží v cache. efective_cache_size by měla odpovídat velikosti paměti použité v systému jako disková cache. Pro dedikované servery se hrubě doporučuje 2/3 RAM.

Bez dobré znalosti SQL není možné psát rychlé dotazy, tudíž i rychlé aplikace - tedy naučte se dobře SQL:

Vyvarujte se ORM. Pokud jej již používáte, ověřte si, jakou skutečnou zátěž generuje:

Pro primární data nepoužívejte EAV (Entity-Attribute-Value) tabulky nebo také key/value model - pokud potřebujete pracovat s neznámým počtem atributů použijte modul hstore.

Komerční odkaz: Optimalizace a ladění databáze, Školení PostgreSQL efektivně - Základy SQL

Používejte cache

SQL příkaz, který, díky cache, ušetříte je nejrychlejší a nejméně zatěžuje databázi. Do cache ukládejte krátkodobá netransakční data - např - http sessions, do cache ukládejte agregovaná data, v případě zatižených aplikací do cache ukládejte vše, co lze uložit do cache. Snažší (ohledně správy cache) je plnění cache ještě před vlastním požadavkem na obsah - změní li se data, změním obsah cache. Jiný přístup - invalidace cache je většinou náročnější na implementaci - aplikace musí podporovat oba přístupy - po restartu je cache prázdná. Aplikace jako je např. wikipedie používá 160GB memcached.

Přidělte PostgreSQL dostatek paměti

Paměti není nikdy dost. To platí i pro PostgreSQL. Pravidlo č. 1. Server, na kterém běží PostgreSQL, nesmí nikdy swapovat. Pravidlo č. 2. Čím je více paměti, tím větší může být vyrovnávající paměť používaná na načtená data z disků. Použití vyrovnávající paměti má zásadní význam při víceuživatelském režimu - je docela pravděpodobné, že uživatelé budou chtít stejná data, a čtení z vyrovnávající paměti je řádově rychlejší než čtení z disku. Jelikož operační paměť je dost omezená, nelze udržovat v paměti veškerá přečtená data. Existují sofistikované algoritmy, které určují, které stránky vyrovnávající paměti se uvolní a použijí pro nově načtená data. Na rozdíl od operačního systému, který také používá vyrovnávající paměť, tyto algoritmy v PostgreSQL jsou optimalizovány pro databázové operace. Doporučuje se zhruba 1/3 operační paměti vyhrazené pro PostgreSQL použít jako vyrovnávající paměť (shared buffers).

Zbývající paměť (work_mem) vyhrazená pro PostgreSQL se používá pro zpracování db operací (třídění, slučování). Opět platí, že čím větší je přidělená operační paměť, tím je pravděpodobnější, že se úloha bude moci zpracovat výlučně v operační paměti, čímž se několikanásobně zrychlí. Hledáme rozumný kompromis mezi maximálním počtem klientů a maximální velikostí operační paměti vyhrazené jednomu klientu. Tyto parametry nesmí překračovat fyzické limity. Je to pojistka proti swapování operačního systému, a pokud neodpovídají realitě, tak se systém nezhroutí, ale začne swapovat. Zákonitě se zvýší několikanásobně odezva. Na zatíženém systému se začnou hromadit nezpracované požadavky a posléze může systém přestat komunikovat.

Příklad: Dedikovaný server s 8G operační paměti. Počáteční konfigurace:

operační systém .... 0.5G
vyrovnávací paměť .. 4.0G
maximální počet klientů .... 200
pracovní paměť pro klienta . 17.5M 

Ukázka chybné konfigurace - v důsledku použití chybného návrhu aplikace došlo k neuvolňování spojení. Na tuto situaci administrátor reagoval zvednutím maximálního počtu klientů:

operační systém .... 0.5G
vyrovnávací paměť .. 4.0G
maximální počet klientů .... 400
pracovní paměť pro klienta . 17.5M 

V důsledku tohoto zásahu se situace jenom razantně zhoršila. PostgreSQL vytvořil dalších 200 spojení, pro které už nezbývala fyzická paměť (systém swapoval 3.5G). Posléze došlo k zahlcení serveru, následoval vynucený restart.

Korekce - a) oprava návrhu aplikace, b) realitě odpovídající konfigurace (zvýšení počtu klientů musí odpovídat snížení pracovní paměti):

operační systém .... 0.5G
vyrovnávací paměť .. 4.0G
maximální počet klientů .... 400
pracovní paměť pro klienta . 8.75M 

Rozdělení paměti lze popsat rovnicí:

    maximalni pocet klientu * pracovni pamet + vyrovnavaci pamet = vyhrazena pamet pro PostgreSQL

Optimální hodnoty musí vycházet z konkrétní aplikace, hw vybavení, objemu dat.

Doporučení, která naleznete na internetu, si ověřte testováním. Ne všechno, co je na internetu k dispozici, je pravdivé, správné a aktuální. Při vývoji dochází k odlišnostem v chování, citlivosti na parametrech, limitech různých verzích PostgreSQL. To, co platilo pro PostgreSQL 7.4, neplatí v některých bodech pro verzi 8.2. Navíc některá doporučení jsou spojena s určitou aplikací, určitým charakterem zpracování dat, a mohou být zavádějící (nesmyslná) ve Vaší aplikaci nebo na Vašem hw.

Výkon PostgreSQL závisí na výkonu celého systému. Nelze předpokládat, že pokud "přidusíte" o.s. (např. jej přinutíte swapovat), že PostgreSQL poběží rychle. Všechny parametry systému musí být v rovnováze. Paměť není jediná hw komponenta ovlivňující rychlost. Významnou roli hrají rychlosti disků a vlastnosti diskového řadiče. Mám empiricky ověřeno, že čím je hw sofistikovanější (obvykle i dražší), tím důležitější je jeho správná konfigurace. Kromě vlatních testů můžete použít program pgbench.

Pro o.s. Windows neplatí doporučení maximalizovat shared_buffers! Naopak, doporučuje se nastavit shared_buffers na co možná nejnižší hodnotu (s 4GB RAM cca 64-128MB).

A aby to nebylo tak jednoduché, existuje další výjimka. Pokud provozujete databázi, kde dochází k intenzivním změnám (opravdu intenzivním, např. od víc než stovky připojených uživatelů atd), a používáte PostgtreSQL 8.2 a starší, tak zvýšení shared_buffers může způsobit problémy se zvýšenou zátěží způsobenou checkpointy. Tento problém se již nevyskytuje u verze 8.3. O tomto více a o správné konfiguraci v tomto případě se dočtete ve článku http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm.

Komerční odkaz: Školení administrace PostgreSQL

Reindexujte

Změny v databázi vedou jak ke fragmentaci datových souborů tak ke fragmentaci indexů. Odstraněním a opětovným vytvořením indexu dojde k defragmentaci a optimalizaci indexu. Toto je přesně smyslem příkazu REINDEX. Stejně jako u příkazu VACUUM FULL doporučuje se jej spouštět 1x měsíčně, v závěsu za příkazem VACUUM FULL.

Diagnostiku defragmentace indexu poskytne funkce getstatindex z modulu pgstattuple. Spouštění reindexace má smysl, pokud hodnota u avg_leaf_density je menší než 50%, a pokud je index větší než 10 datových stránek (80KB).

Aktualizujte PostgreSQL (upgrade)

Na databázovém jádru PostgreSQL se stále pracuje. Každá verze přináší novou funkcionalitu a zatím i vyšší výkon. Tým vývojářů uvolňuje každý rok novou verzi. Pro reálné nasazení se doporučuje používat verzi půl roku až rok starou, a migrovat každé dva roky. Při častější migraci se zmenšují rozdíly mezi verzí, kterou používáte a cílovou verzí.

Při častější migraci je větší naděje, že ve vaší organizaci stále pracuje autor aplikace nebo někdo, kdo má zkušenosti z předchozí migrace. Naopak, čím déle je provozovaný systém zakonzervovaný, tím pravděpodobnější jsou problémy s migrací. Pokud stáří systému přesáhne kritickou hranici, pak náklady na migraci odpovídají aktualizaci celého systému, v horším případě se systém odstaví. Po 3-4 letech jsou to ty systémy, kolem kterých se chodí po špičkách a do kterých se kdokoliv bojí sáhnout.

Používejte uložené procedury a předpřipravené dotazy

Stále se vedou diskuze, zda-li používat nebo nepoužívat uložené procedury. Osobně si nedovedu představit složitější aplikaci, kde by použití uložených procedur bylo na škodu. Pokud používáte ORM (technika, jak se v SQL databázi obejít bez SQL), pak pravděpodobně procedury nepoužijete. Bez diskuzí je jejich přínos k bezpečnosti aplikace. Předpřipravené dotazy jsou jedinou 100% ochranou proti SQL injektáži. A nezanedbatelné je i zrychlení určitých operací. Pokud dokážete udržet zpracování úlohy na db. serveru odpadá režie na udržení komunikace mezi klientem a serverem, na nezbytné konverze, atd. Díky rychlejšímu zpracování úlohy, server drží kratší dobu alokované zdroje na úlohu, dříve je uvolní a dříve je bude moci použít pro ostatní úlohy.

Komerční odkaz: Školení pro pokročilé - návrh a vývoj uložených procedur v jazyce PL/pgSQL

Udržte pořádek v databázi

Co nejdříve odstraňujte všechny nepoužívané databázové objekty (samozřejmě až po té, co se skutečně ujistíte, že jsou nepoužívané). Rušené objekty zálohujte. Pokud úloha, která se spouští jednou měsíčně vyžaduje index, který se jindy nepoužije, přemýšlejte o tom, zda-li by nebylo možné vytvořit index před provedením úlohy a poté jej opět odstranit.

Nepoužívané tabulky a indexy nejsnáze identifikujeme analýzou provozních statistik z tabulek pg_stat_user_tables(seq_scan, idx_scan) a pg_stat_user_indexes(idx_scan). Až do verze 8.3 bylo nutné aktualizaci provozních statistik explicitně povolit stats_start_collector a stats_row_level).

Udržujte aktuální dokumentaci db schématu. Změny v databázovém schématu neprovádějte přímo, ale pomocí skriptů. Změny konfiguračních souborů archivujte pomocí systémů správy verzí. Používejte wiki, blog .. systémy, které vám umožní dokumentovat změny, aniž by obtěžovaly.

Použijte partitioning

Pro opravdu velké tabulky (nad jeden milión záznamů) požívejte partitioning.

Pozor na hardware

Výkon hw je kritický. Problematická síťová karta může způsobovat pomalé připojení k databázi. Špatný výběr typu diskových polí může úplně zazdít výkon databáze bez ohledu na cenu a kvalitu serveru. Nepoužívejte RAID 5!. Jedno z doporučení (pro o.s. Linux) je: dva disky v RAID 1 pro pg_xlog (ext2), pro vlastní data RAID 10 (ext3). Výkon diskových polí je nutné vždy otestovat. Zdrojem potíží může být i firmware řadiče diskových polí.

Používejte správné nástroje

Databáze PostgreSQL je navržena a optimalizována pro tzv. podnikové prostředí. To se vyznačuje požadavkem na maximální možnou spolehlivost a dobrý výkon při souběžném zpracování komplikovanějších SQL příkazů bez ohledu na objem dat. Kromě toho PostgreSQL nabízí více než obvyklý komfort a široký repertoár funkcí. Nelze nezmínit vynikající bezplatnou podporu (IRC, elektr. konference). Na druhou stranu existuje celá řada dalších open source databází, které jsou pro určité aplikace vhodnější. Namátkou: MySQL, Firebird, SQLite a nyní třeba i 602SQL Open Server. Je proto třeba volit uvážlivě a brát v potaz celou řadu ať technických nebo netechnických faktorů. Pokud nemusíte řešit analýzu nad dočasnými daty, pak tato data neukládejte do databáze (např. správa http sessions). Pro taková data použijte memcached.