Optimalizace PostgreSQL DB – testy výkonu

V dnešním posledním a více praktickém dílu si ukážeme ucelený konfigurační soubor a databázi otestujeme standardním testem TPC-B. Uvidíte, že parametrů, které databázový administrátor musí znát a umět nastavit zase tolik není. (Článek vyšel též na portále LinuxExpres.)

Dostatek paměti je základ

Výchozí konfigurace databáze PostgreSQL je velmi úsporná (například v Debianu 6 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 5 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 OS, 10 GB budeme chtít přidělit databázovému serveru. Bude se k němu připojovat ne více než 16 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 = 8192MB

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ň 4 GB volné paměti (parametr effective_cache_size žádnou paměť přímo nealokuje, je to nápověda pro optimalizátor, kolik paměti má DB k disposici ať již jako vnitřní buffery, nebo jako cache os — toto je důvod, proč PostgreSQL nepotřebuje alokovat veškerou paměť, spolehá se na cache os).

Toto nastavení platí pro Linux; u Windows nemá smysl použít tak velký shared_buffer, tam se spoléhá na velkou IO cache OS.

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 1000 klientů po 1MB).

A je to. Celkem 5 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 5 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 32b 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ň 15 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 servříku, 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 64b, verze PostgreSQL 8.4 (ze stable repositář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 na LinuxSoftu.

Parametry samotného testu: pgbench -c 4 -M prepared -T 900 (4 klienti, bude využito připravených dotazů, běh testu po 900s).

Výsledek: 107tps (transakcí za sekundu).

Optimalizované nastavení DB 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 vlézt 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: 1262tps. Což je 11x více, než v případě výchozího nastavení. Celá tabulka accounts se vlezla 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 vleze do paměti celá (ne, že by ta data byly 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 DB 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: 44168tps. Pěkné.

Opravdu velká databáze

Abychom splnili požadavky na TPC test, kdy se nepředpokládá, že se tabulka accounts vleze 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 (200 milionů řádků, 25GB).

Výsledek testu: 150tps. Takže jsme se dostali zpět na začátek, kdy se DB nevleze 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).

Závěr

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.