Optimalizace PostgreSQL – WAL

WAL

V minulém díle jsme lehce naťukli problematiku write ahead logu, v tomto díle ji probereme dále.

wal_buffers

Výchozí nastavení je 64 kB a je zcela dostatečné pro většinu „normálních“ transakcí. Pokud ovšem velikost jedné transakce v typické pracovní zátěži serveru výrazně překračuje tuto hodnotu, je možné wal_buffers zvýšit až na 16 MB (velikost jednoho WAL segmentu), což pro moderní servery s dostatkem RAM nepředstavuje problém. Vyšší hodnoty nemají smysl. Ve verzi PostgreSQL 9.1 bude již nastavení této hodnoty automatizováno (i když ji stále bude možné nastavit manuálně).

wal_sync_method

Nastavení synchronizační metody dat na systém souborů (a disk) je jednou z důležitých optimalizačních voleb a má významný vliv především na bezpečnost databázových dat na disku. Výchozí nastavení synchronizační metody je na rozdíl od všech ostatních nastavení v PostgreSQL serveru závislé na aktuální platformě a liší se tedy od instalace k instalaci. PostgreSQL server automaticky vybere nejbezpečnější nastavení zápisu na základě schopností konkrétního operačního systému.

Obecně tuto hodnotu není nutné měnit (výchozí je dobrá pro bezpečnost dat). Při nastavení vždy pamatujte především na bezpečnost synchronizace a až potom na její rychlost. Nesprávné nastavení synchronizační metody sice může zrychlit zápisy datových souborů, ale také ohrozit vaše data v případě pádu serveru, výpadku napájení apod.

Aktuální používaný způsob synchronizace zápisů lze zjistit pomocí příkazu SHOW:

postgres=# show wal_sync_method;
  wal_sync_method
-----------------
  fdatasync

Na rozdíl od Linuxu je na platformách Microsoft Windows nebo Mac OS X dobré nastavení:

wal_sync_method=fsync_writethrough

Pokud vaše platforma umí tento (fsync_writethrough) způsob synchronizace, máte vyhráno. Zápis databázových dat na disk je bezpečný a tato metoda minimálně zpomaluje ostatní přístupy k disku. Je to bezpečné a rychlé nastavení, mnohem lepší než (bohužel používané) nebezpečné vypnutí zápisových cache. Tento způsob ale není dostupný na všech platformách.

Na Linuxu je situace mnohem komplikovanější. Výchozí metoda fdatasync je bezpečná a pomalá. Je možné zkusit nastavit synchronizované ukládání na disk nastavením wal_sync_method=open_sync.

Což bude ve většině případů mnohem rychlejší. Ale, některé systémy souborů, například výchozí a nejčastěji používaný ext3, nejsou s volbou open_synce příliš bezpečné, což může vést až k poškození datových souborů WAL logu. Tato chyba byla ve verzi jádra 2.6.32 a systému souborů ext4 vyřešena, což je ovšem nový systém souborů a není příliš otestován při provozu velkých enterprise databázových strojů.

V každém případě by měly testy nastavení synchronizační metody vždy obsahovat i test reakce při výpadku napájení (lze nahradit tvrdým resetem hardwaru). Testy by se také měly provádět pro dlouhotrvající intenzívní zápisy.

shared_buffers

Důležitá položka pro rychlost běhu databáze je nastavení velikosti sdílené paměti. Výchozí hodnota je 32 MB, dobrá výchozí hodnota pro systémy s dostatkem paměti je dvacet pět procent celkové paměti RAM. Pro jistou pracovní zátěž je vhodné použít vyšší hodnoty, ty však obecně nemají příliš smysl, protože PostgreSQL využívá také diskovou cache operačního systému (effective_cache_size). Při nastavení vyšší velikosti sdílené paměti je také nutné adekvátně upravit počet segmentů checkpointu (checkpoint_segments).

Pro běh PostgreSQL ve Windows nemají vysoké hodnoty tohoto parametru žádný smysl (lze jej nechat na výchozí hodnotě, nejvíce však 512 MB), je lepší používat systémovou diskovou cache.

Nastavení pro klienta

Všechna nastavení v této sekci lze měnit přímo klientem po připojení k serveru. Je však dobré mít výchozí nastavení v konfiguraci serveru. Individuální klient si je pak může změnit pomocí příkazu SET v rámci svého připojení.

effective_cache_size

PostgreSQL očekává k dispozici jednak svou paměť (shared_buffers, work_mem atp.), ale také cache systému souborů (tedy cache, o kterou pečuje samotný operační systém). Tento parametr je sice možné nastavovat „per client“, ale v praxi se to prakticky nepoužívá (používá se hodnota nastavená v konfiguraci PostgreSQL databáze).

Databázový server na základě této hodnoty nealokuje žádnou paměť. Tato konfigurační hodnota se používá čistě pro výběr strategie vykonání dotazu. Tedy, pokud je nastavená hodnota příliš vysoká (vyšší, než je skutečná dostupná paměť), databáze tak očekává, že se čtená data vlezou do paměti, zatímco se reálně čtou z disku, což je pomalé.

Jestliže výchozí velikost shared_buffers můžeme určit jako dvacet pět procent velikosti RAM, potom pomocí podobného pravidla můžeme jako výchozí hodnotu effective_cache_size vzít padesát procent velikosti dostupné paměti. Přesnější hodnoty získáme z velikosti diskové cache v operačním systému:

• Pro unixové systémy platí: dostupná disková cache = volná paměť + cached. Tyto hodnoty lze zjistit například z programů free nebo top.
• Pro Windows platí podobné pravidlo, velikost systémové cache lze zjistit například ze Správce úloh.

Tyto hodnoty dostupné diskové cache je nutné zjišťovat za běhu databáze a všech dalších případných programů.