Pár tipů k parametrům geodatabáze PostgreSQL
Výchozí nastavení parametrů databáze PostgreSQL je velmi „opatrné“ z hlediska limitů pro využití paměti RAM a nemusí být vždy optimální z hlediska odezvy.
Tyto limity pro dostupnou paměť jsou ve výchozím nastavení raději velmi přísné, aby databáze fungovala stabilně i na strojích s malou RAM, a tedy nemusí být zcela optimální z hlediska odezvy databáze v případě, že stroj má jinak volné paměti dostatek.
Když databáze objekt poptávaný SQL dotazem (tabulku či použitý index) nenajde ve své databázové cache, načte si jej z datových souborů. Pomineme-li případné načítání datových souborů do cache operačním systémem, které na pozadí také probíhá, je čtení z disku vždy řádově výrazně pomalejší než čtení z cache (čili z paměti RAM).
Při nedostatku místa v cache nově načtené objekty postupně vytlačují objekty načtené dříve. Čím je databázová cache menší a počet často dotazovaných velkých databázových objektů větší, stoupá riziko, že požadovaný objekt v cache nebude a tabulka či index se bude muset znovu a pomalu načíst do cache z disku. Velikost databázové cache, kam se objekty po prvním načtení z disku uloží a odkud je pak PostgreSQL může rychleji načíst, je dána parametrem shared_buffers v souboru postgresql.conf. (Výchozí hodnota je 128 MB.)
V případě, že jste tedy PostgreSQL pouze nainstalovali, výchozí parametry neměnili, a přitom víte, že databázový stroj má dostatek RAM a v databázi máte mnoho velkých tabulek (data GIS jsou z podstaty často objemná), měli byste uvažovat, zda nezkusit parametr shared_buffers upravit.
Jako velmi dobrý ukazatel, zda je parametr shared_buffers nastaven adekvátně, lze zkontrolovat, v kolika procentech došlo při načítání datových bloků databází ke čtení z disku. Tento je možné zjistit porovnáním celkové hodnoty blks_read a blks_hit v pohledu pg_stat_database. U již nějakou dobu běžící a využívané databáze mohou být hodnoty pod 95% příznakem, že parametr shared_buffers není nastaven na adekvátní velikost.
Jak je databáze z hlediska parametru shared_buffers nastavena, je možné zjistit dotazem do pg_catalog.pg_settings:
select name, setting, unit from pg_catalog.pg_settings where name like 'shared_buffers';
Výsledná velikost je zde hodnota v sloupci setting vynásobená velikosti datového bloku (obvykle 8 kb). Nastavení lze také zkontrolovat přímo v samotném souboru postgresql.conf.
Jako vhodná výchozí hodnota parametru shared_buffers se uvádí 25–50 % RAM dostupné pro PostgreSQL. Pro změnu tohoto parametru je potřeba databázi zastavit, upravit hodnotu v souboru postgresql.conf a databázi znovu nastartovat.
Jako další základní parametry paměti, které mohou ovlivnit odezvu, je možné uvést i effective_cache_size a work_mem.
Effective_cache_size není vyloženě limit, ale slouží pro optimalizátor PostgreSQL spíše jako odhad, kolik má v běžném provozu serveru celkově k dispozici dostupné paměti RAM pro využívání cache pro optimalizaci dotazů do databáze. Jako doporučovaná hodnota se uvádí rozmezí 50–75 % v běžném provozu dostupné RAM.
Na rozdíl od parametru shared_buffers, který ovlivňuje velikost cache využitelné pro dotazy ze všech připojení dohromady, je work_mem parametr, který ovlivňuje paměť dostupnou pro zpracování dotazů pro každé připojení zvlášť. (Databáze tuto paměť využívá např. při vyhodnocování operací ORDER BY, DISTINCT, MERGE a HASH JOIN.) Z tohoto důvodu je nutné brát v úvahu, že roste s počtem připojení (u paralelně vyhodnocovaných dotazů násobně), a tím může paměť alokovaná pro PostgreSQL dost rychle nabobtnat. K jeho navyšování je proto vhodné přistupovat s určitým respektem. Ale u databáze s dostatkem RAM, kam směřuje omezený počet komplexnějších dotazů od menšího počtu uživatelů, je možné nastavit tento parametr na násobky výchozích 4 MB.
Pro prvotní nástřel výše uvedených parametrů a pro případné další ladění databáze PostgreSQL lze vedle oficiální dokumentace PostgreSQL doporučit například i stránky enterprisedb či „kalkulačku“ parametrů pgtune.
Po každé změně nastavení parametrů, zejména work_mem, je vhodné databázi a její odezvu nějakou dobu při zátěži sledovat, a to i co se týče prostředků (v uvedeném případě hlavně paměti), které si databáze (postgres procesy) na stroji alokuje.