Dostatek paměti je základ
Výchozí konfigurace databáze PostgreSQL je velmi úsporná (například v Debianu Squeeze si ukousne méně než 100 MB RAM). Pro produkční provoz je to velmi málo a proto je nutné (v podstatě jako jediné nastavení) přidělit databázi o něco více prostředků.
Velikost přidělené paměti ovlivňuje těchto pět parametrů:
-
max_connections
-
work_mem
-
shared_buffers
-
maintainance_work_mem
-
effective_cache_size
Konkrétní hodnoty pro tyto parametry lze určit pomocí vzorce:
RAM > max_connections * work_mem + shared_buffers + + maintainance_work_mem + effective_cache_size
Tedy zcela konkrétně, mějme server s 12 GB paměti RAM, 2 GB necháme pro operační systém, 10 GB budeme chtít přidělit databázovému serveru. Bude se k němu připojovat ne více než šestnáct klientů, kteří mohou volat složité dotazy s velkým výsledkem a budou vyžadovat velkou pracovní paměť. Konkrétní nastavení v konfiguračním souboru postgresql.conf:
max_connections = 16 work_mem = 128MB shared_buffers = 4096MB maintainance_work_mem = 512MB effective_cache_size = 3072MB
Dle výše uvedeného vzorce si takto nastavený server ukousne 6,5 GB paměti (16 * 128 MB + 4096 MB + 512 MB = 6,5 GB) a bude předpokládat, že pro IO cache operačního systému zůstane alespoň 3 GB volné paměti (parametr effective_cache_size žádnou paměť přímo nealokuje, je to nápověda pro optimalizátor).
Toto nastavení platí pro Linux; u Windows nemá smysl použít tak velký shared_buffer, tam se spoléhá na velkou IO cache operačního systému.
Vhodná velikost pracovní paměti (work_mem) přímo závisí na typu dotazů. U běžného webového serveru s rychlými a jednoduchými dotazy budeme chtít naopak nastavit pracovní paměť na nižší hodnoty a zároveň počet klientů bude dramaticky vyšší (například tisíc klientů po 1 MB).
A je to. Celkem pět parametrů s hodnotami přibližně určenými podle jednoduchého vzorce. Nic víc není potřeba nastavovat (snad jen formát logování událostí a u starších verzí PostgreSQL je také nutné zapnout proces autovacuum), ostatní parametry jsou ve výchozím stavu nastaveny dobře pro bezpečnost dat a není nutné je jakkoliv měnit.
Do budoucna můžeme počítat s automatickým nastavením těchto parametrů, tak jak jej mají vyspělé komerční databáze (MSSQL, DB2, Oracle). Myslím si však, že by administrátor serveru měl znát alespoň rámcově nastavení služeb a že pět parametrů by nemělo být pro správce překážkou.
V čem se často chybuje
Velice často administrátor zvýší počet připojení (max_connections), bez ohledu na velikost pracovní paměti. U databázového serveru s MySQL (což ale není podstatné) jsem v praxi viděl i nastavení paměti přidělující databázi několik desítek GB (právě kvůli velké hodnotě pracovní paměti v kombinaci s povolením několika tisíc připojení). A to vše na 32bitové platformě, kde jeden proces může mít nejvýše 2 GB paměti. Na to je potřeba pamatovat, v praxi se potom může stát (a stává se to), že takto nakonfigurovaný server po několika dnech bezproblémového běhu bez jasné příčiny „spadne“. Databázový server zkrátka vyčerpal veškerou paměť a alokace pro dalšího klienta již neprojde. Řešením je přidat do serveru další paměť a nebo upravit konfiguraci na smysluplné hodnoty.
Testy výkonu
U každého testu výkonu je nutné se zamyslet nad tím, co vlastně chceme testovat a čeho chceme daným nastavením dosáhnout. V následujících testech budou používat nástroj pgbench, který nad databází provádí standardní test TPC-B, což je transakční test, při kterém se ve velké míře zapisuje. V praxi (a zejména webové) se s takovou pracovní zátěží ale nesetkáme, tam naopak převládá čtení. Proto tento test nemusí dávat vždy smysl a výkon databáze muže být v praktickém nasazení zcela odlišný (oběma směry). Ideální je testovat přímo pomocí aplikace, která potom poběží v produkčním provozu (ovšem přepsat aplikaci na benchmark využívající reálná data není lehké).
Test TPC má běžet nad připravenou databází, která má velikost více než 1 GB a doba běhu musí být alespoň patnáct minut. První požadavek je dnes již poněkud zastaralý (test byl schválen v roce 1990), takto malá databáze se dnes vejde bez problémů do paměti, což ale neodpovídá záměru otestovat i diskový výkon.
Testovací prostředí
Testoval jsem na svém domácím serveru, který slouží především jako síťový souborový server. Procesor Intel i3 540, paměť 12 GB DDR3, diskové úložiště RAID5 (pro provoz databáze vyloženě nevhodný) nad čtyřmi 7200 rpm disky. Souborový systém ext4. Operační systém Debian 6.0 64bit, verze PostgreSQL 8.4 (ze stable repozitáře).
Výchozí nastavení databázového serveru
Vytvoření testovací databáze: pgbench -i -s 100.
Velikost tabulky accounts je 1,2 GB. Pro bližší informace o testu si můžete přečíst článek o implementaci TPC-B testu.
Parametry samotného testu: pgbench -c 4 -M prepared -T 900 (čtyři klienti, bude využito připravených dotazů, běh testu po devíti stech sekundách).
Výsledek: 107 tps (transakcí za sekundu).
Optimalizované nastavení databázového serveru
Testovací databáze zůstala stejná (tedy 1,2 GB), nastavení serveru je shodné s ukázkovým nastavením výše (6,5 GB). Databáze by se měla vejít do paměti, což je v praxi ideální stav.
Parametry samotného testu zůstaly nezměněny: pgbench -c 4 -M prepared -T 900.
Výsledek: 1262 tps. Což je jedenáctkrát více než v případě výchozího nastavení. Celá tabulka accounts se vešla do paměti, z disku se tedy příliš nečetlo, jen se zapisovaly transakce. Což někdy odpovídá praxi, databáze se často vejde do paměti celá (ne, že by ta data byla tak malá, spíše je dnes levná paměť a server s několika desítkami gigabajtů paměti není výjimkou, zatímco takto velká databáze ano).
Optimalizované nastavení serveru, test čtení
Nedalo mi to a když už byla databáze pěkně v paměti, pustil jsem i test, při kterém se provádějí pouze dotazy SELECT, nezapisuje se. Což ale také realitě neodpovídá.
Parametry testu: pgbench -c 4 -M prepared -S -T 900.
Výsledek testu: 44168 tps. Pěkné.
Opravdu velká databáze
Abychom splnili požadavky na TPC test, kdy se nepředpokládá, že se tabulka accounts vejde celá do paměti, učiňme na závěr ještě test s velkou databází. Výsledek už tak hezký nebude.
Vytvoření testovací databáze: pgbench -i -s 2000 (dvě stě milionů řádků, 25 GB).
Výsledek testu: 150 tps. Takže jsme se dostali zpět na začátek, kdy se databáze nevejde celá do paměti a diskový systém se musí vyrovnat se současným čtením a zápisem (a to ještě na nevhodné RAID5).
Odlaďte nastavení podle svých potřeb
Dnešním praktičtějším dílem uzavíráme seriál o optimalizaci databázového serveru PostgreSQL. Ukázali jsme si více či méně smysluplné testy výkonu a vliv nastavení (výchozí versus optimalizované) na něj. Z výsledků si lze udělat obrázek v jakých mezích lze očekávat zlepšení výkonu databáze pouze změnou nastavení serveru.
