Problém optimalizace se stává velmi akutním při vzniku zpomalení systému a přitom je k této oblasti velmi málo dokumentace, zejména v ruském jazyce. Níže se pokusíme rozebrat hlavní aspekty optimalizace PostgreSQL.
Disky a souborové systémy
Pokud má váš server více fyzických disků, můžete rozložit soubory databáze a záznamový soubor transakcí na různé disky:
– Zastavte server.
– Přesuňte adresáře pg_clog a pg_xlog, které se nacházejí v adresáři s databázemi, na jiný disk.
– Vytvořte na původním místě symbolický odkaz.
– Spusťte server.
Konfigurace (úpravou souboru postgresql.conf)
Nastavení zdrojů
shared_buffers
Velikost sdílené paměti mezi procesy PostgreSQL, která je potřebná pro provádění aktivních operací. Nemělo by se zadávat příliš velké množství, protože PostgreSQL také využívá diskovou mezipaměť.
Hodnoty:
Střední objem dat a 256–512 MB dostupné paměti: 16–32 MB
Velký objem dat a 1–4 GB dostupné paměti: 64–256 MB
temp_buffers
Buffer pro dočasné objekty, především pro dočasné tabulky.
Lze nastavit přibližně na 16 MB.
max_prepared_transactions
Počet současně připravovaných transakcí.
Pro provoz 1C tento parametr nemá význam, protože tam se nepoužívá PREPARE TRANSACTION.
Lze ponechat výchozí hodnotu – 5.
work_mem
Speciální paměť používaná pro řazení a mezipaměť tabulek pro jeden dotaz.
Při nastavování tohoto parametru je třeba zohlednit počet konkurenčních dotazů prováděných ve stejný čas.
Při paměti 1–4 GB se doporučuje nastavit 32–128 MB.
maintenance_work_mem
Paměť používaná pro operace VACUUM, CREATE INDEX, ALTER TABLE a FOREIGN KEY.
Mělo by se nastavit vyšší hodnotu než pro work_mem. Příliš vysoké hodnoty mohou vést k používání swapu.
Při paměti 1–4 GB se doporučuje nastavit 128–512 MB.
max_stack_depth
Speciální zásobník pro server, ideálně by měl odpovídat velikosti zásobníku nastavené v jádře OS. Nastavení vyšší hodnoty, než je v jádře, může vést k chybám.
Doporučuje se nastavit 2–4 MB.
max_fsm_relations
Maximální počet tabulek, pro které bude sledováno volné místo v celkové mapě volného místa. Tyto údaje shromažďuje VACUUM.
Nastavte parametr v souladu s počtem tabulek ve vaší databázi s rezervou. (Platí pro verze do 8.4)
max_fsm_pages
Počet bloků, pro které bude uchovávána informace o volném místě. Informace jsou uloženy ve sdílené paměti, pro každý záznam je potřeba 6 bajtů.
Použití tohoto parametru umožňuje vyhnout se používání VACUUM FULL pro databázi, stačí VACUUM. (Platí pro verze do 8.4)
Tento parametr by měl být alespoň 16*max_fsm_relations.
Tento parametr je nastaven automaticky při vytvoření databáze pomocí utility initdb.
Lze jej nastavit i ručně: jako počáteční aproximaci lze vzít polovinu průměrného počtu záznamů, které jsou mezi spuštěními příkazu VACUUM měněny (UPDATE nebo DELETE).
Tuto hodnotu lze odhadnout (databáze by již měla nějakou dobu běžet) provedením:
vacuum full analyze;
NOTICE: number of page slots needed (196272) exceeds max_fsm_pages (153600)
POZNÁMKA: Zvažte zvýšení konfiguračního parametru „max_fsm_pages“ na hodnotu vyšší než 196272.
max_files_per_process
Maximální počet souborů, které může proces a jeho podprocesy otevřít najednou.
Tento parametr snižte, pokud se během provozu objeví zpráva „Too many open files“ (Příliš mnoho otevřených souborů).
Zápis transakčních logů na disk
commit_delay a commit_siblings
Hodnota commit_delay je vyjádřena v mikrosekundách (výchozí hodnota 0). Hodnota commit_siblings je vyjádřena v počtu transakcí (výchozí hodnota 5).
commit_delay určuje zpoždění mezi zápisem záznamu do bufferu transakčního logu a jeho vyprázdněním na disk. Pokud je při úspěšném dokončení transakce aktivních alespoň commit_siblings transakcí, bude záznam zpožděn na dobu commit_delay. Pokud se v této době dokončí další transakce, budou jejich změny vyprázdněny na disk společně pomocí jednoho systémového volání. Tyto parametry umožní zrychlit provoz, pokud je paralelně prováděno mnoho „malých“ transakcí.
fsync
Tento parametr odpovídá za vyprázdnění dat z mezipaměti na disk při dokončování transakcí.
Pokud nastavíte jeho hodnotu
fsync=off
nebudou data zapisována na disková úložiště ihned po dokončení operací. To může výrazně zvýšit rychlost operací insert a update, ale hrozí riziko poškození databáze při selhání (neočekávané vypnutí napájení, selhání operačního systému, selhání diskového subsystemu).
Tuto možnost používejte pouze tehdy, pokud máte spolehlivý zdroj UPS (bezporuchového napájení) a software, který ukončuje provoz systému při nízké úrovni nabití baterií.
Nedoporučuje se vypínat fsync při provozu Postgre SQL? na platformě Windows kvůli nestabilitě systému.
wal_sync_method
Metoda používaná pro vynucení zápisu dat na disk.
Pokud je fsync=off, tento parametr se nepoužívá.
Možné hodnoty:
open_datasync – zápis dat metodou open() s parametrem O_DSYNC
fdatasync – volání metody fdatasync() po každém commitu
fsync_writethrough – volání fsync() po každém commitu s ignorováním paralelních procesů
fsync – volání fsync() po každém commitu
open_sync – zápis dat metodou open() s parametrem O_SYNC
Není všechny metody jsou dostupné na určitých platformách. Ve výchozím nastavení se používá první metoda, která je v systému k dispozici.
full_page_writes
Tento parametr nastavte na off, pokud je fsync=off. Jinak
Když je tento parametr zapnut (on), Postgre SQL? zapisuje obsah každé stránky do transakčního logu při první modifikaci tabulky po kontrolním bodě (checkpoint). Je to nutné, protože stránky mohou být zapsány pouze částečně, pokud během procesu dojde k pádu operačního systému. To vede k tomu, že na disku se objeví nová data smíchaná se starými. Úrovně záznamu do transakčního logu může být nedostatečná k úplnému obnovení dat po „pádu“. full_page_writes zajišťuje správné obnovení za cenu zvýšení objemu zapisovaných dat do transakčního logu. (Protože transakční log vždy začíná od kontrolního bodu. Jediným způsobem snížení objemu zápisu je zvýšení intervalu kontrolních bodů).
wal_buffers
Množství paměti používané v SHARED MEMORY pro vedení transakčních logů.
Při dostupné paměti 1–4 GB se doporučuje nastavit 256–1024 kB.
Optimalizace dotazů
enable_nestloop
Určuje využití plánovače dotazů. V některých případech vypnutí plánovače může snížit dobu provádění jednotlivých dotazů, a to i výrazně. Ve většině případů však plánovač umožňuje snížit dobu provádění dotazů. Nemělo by se jej vypínat, alespoň ne trvale.
random_page_cost
Nastavuje plánovači odhad „nákladnosti“ neprůběžného prohledávání dat. Výchozí hodnota je 4,0. Snížení této hodnoty vzhledem k seq_page_cost způsobí, že plánovač bude upřednostňovat skenování indexu, zvýšení naopak učiní skenování indexu „dražším“. Můžete změnit obě hodnoty, abyste změnili poměr „nákladnosti“ operací disku na čtení/zápis vzhledem k „nákladnosti“ využití procesoru, což bude popsáno následujícími parametry.
Na serverech s rychlými diskovými poli má smysl snížit výchozí nastavení na 3,0, 2,5 nebo dokonce na 2,0. Pokud je aktivní část vaší databáze mnohem větší než velikost operační paměti, zkuste zvýšit hodnotu parametru. K výběru optimální hodnoty lze přistoupit i z hlediska výkonu dotazů. Pokud plánovač dotazů častěji, než je nutné, upřednostňuje sekvenční skeny (sequential scans) před skeny pomocí indexu (index scans), snižujte hodnotu. Naopak, pokud plánovač vybírá sken pomalým indexem, když by to neměl dělat, má smysl nastavení zvýšit. Po změně důkladně otestujte výsledky na co nejširší sadě dotazů. Nikdy nesnižujte hodnotu random_page_cost pod 2,0; pokud se vám zdá, že random_page_cost je třeba ještě snížit, je v tomto případě rozumnější změnit nastavení statistik plánovače.
cpu_tuple_cost
Nastavuje plánovači odhad „nákladnosti“ zpracování každého řádku během provádění dotazu. Výchozí hodnota je 0,01.
cpu_index_tuple_cost
Nastavuje plánovači odhad „nákladnosti“ zpracování každého indexu během operace skenování indexu. Výchozí hodnota je 0,005.
cpu_operator_cost
Nastavuje plánovači odhad „nákladnosti“ provedení každého operátoru nebo funkce během provádění dotazu. Výchozí hodnota je 0,0025.
effective_cache_size
Předává plánovači dotazů informace o objemu paměti, kterou operační systém používá pro ukládání souborů do mezipaměti, pro jeden dotaz.
Tento parametr v OS lze zobrazit v nastavení:
Pro Windows: v Správci úloh, karta Výkon, Fyzická paměť – Systémová mezipaměť.
Pro Linux: zadejte příkaz free, potřebná hodnota ve sloupci cached (v kB)
Tuto hodnotu je třeba vydělit počtem konkurenčních dotazů v jednom okamžiku (průměrný počet připojení k databázi + rezerva).
default_statistics_target
Nastavuje hloubku statistik tabulek. Větší hodnoty mohou zvýšit dobu provádění příkazu ANALYZE, ale zlepší tvorbu plánu dotazu.
Doporučuje se nastavit přibližně na 100.
constraint_exclusion
Zapíná nebo vypíná použití plánovačem omezení CONSTRAINT v tabulkách při tvorbě dotazů.
Doporučuje se nastavit hodnotu on, přičemž pokud měníte CONSTRAINT u tabulek, je nutné aktualizovat jejich statistiky provedením ANALYZE, jinak budou vytvořeny nesprávné plány dotazů.
Sběr statistik
stats_command_string
Předávat statistikovi informace o aktuálně prováděném příkazu a čase jeho zahájení.
Nastavit on
stats_start_collector
Zda zapnout sběr statistik.
Nastavit on
stats_row_level, stats_block_level
Zda sbírat informace o aktivitě na úrovni řádků a bloků.
Nastavit
stats_row_level=on
stats_block_level=off
stats_reset_on_server_start
Resetovat statistiky při restartu serveru.
Nastavit off
Automatický vacuum
VACUUM – „sběr odpadu“. VACUUM obnovuje místo obsazené „mrtvými“ daty. Při běžných operacích s daty PostgreSQL data fyzicky z tabulek neodstraňuje; to se děje pomocí operace VACUUM.
autovacuum
Zda zapnout automatický vacuum (automatické spouštění VACUUM), nastavit on.
autovacuum_naptime
Prodleva mezi spuštěními automatického vacua.
Záleží na tom, jak často se data ve vašich tabulkách aktualizují. Může být řádově 5 minut, výchozí hodnota je 1 minuta.
autovacuum_analyze_threshold
Určuje minimální počet vložení, aktualizací nebo odstranění potřebných k odeslání signálu pro spuštění ANALYZE v libovolné tabulce. Tento parametr lze změnit pro jednotlivé tabulky změnou jejich uložených parametrů.
autovacuum_vacuum_threshold
Určuje minimální počet vložení, aktualizací nebo odstranění potřebných k odeslání signálu pro spuštění VACUUM v libovolné tabulce. Tento parametr lze změnit pro jednotlivé tabulky změnou jejich uložených parametrů.
Zámky
max_locks_per_transaction
Počet zámků za jednu transakci: nastavit přibližně na 250.
deadlock_timeout
Doba života vzájemných zámek (deadlocků). Nastavit přibližně na 2 sekundy.
Možná chyba
Po úpravě konfiguračního souboru se PostgreSQL nemusí spustit a může vyhodit chybu:
FATAL: could not create shared memory segment:...
Failed system call was shmget(key=5432001, size=140075008, 03600).
Tato chyba obvykle znamená, že požadavek PostgreSQL na sdílenou paměť překročil parametr SHMMAX vašeho jádra. Buď zmenšete velikost požadavku, nebo překonfigurujte jádro s větším SHMMAX. Pro zmenšení velikosti požadavku (aktuálně 140075008 bajtů) snižte parametr shared_buffers PostgreSQL (aktuálně 16384) a/nebo parametr max_connections PostgreSQL (aktuálně 10).
Pokud je velikost požadavku již malá, je možné, že je menší než parametr SHMMIN vašeho jádra, v takovém případě je třeba zvýšit velikost požadavku nebo překonfigurovat SHMMIN.
Dokumentace PostgreSQL obsahuje více informací o konfiguraci sdílené paměti.
Pro opravu je nutné zvýšit parametr SHMMAX v systému. Lze to provést následujícím způsobem (pro OS Linux):
Spusťte příkaz:
echo 150829120 > /proc/sys/kernel/shmmax
přičemž zadejte svou novou hodnotu parametru (konkrétní hodnotu lze zjistit z chybového logu PostgreSQL). Tato operace nastaví parametr za běhu, ale po restartu systému budou změny ztraceny. Aby k tomu nedošlo, přidejte do souboru /etc/sysctl.conf následující řádek:
kernel.shmmax = 150829120
přičemž zadejte svou hodnotu.
Příklad konfigurace pro server s 2 GB operační paměti
/var/lib/pgsql/data/postgresql.conf:
to be done
/etc/sysctl.conf:
to be done
Střední nastavení
Střední nastavení pro maximální výkon. Pro konkrétní případ mohou být vhodnější jiná nastavení. Důkladně si prostudujte tento návod a nakonfigurujte PostgreSQL na základě těchto informací.
RAM – velikost paměti
* shared_buffers = 1/8 RAM nebo více (ale ne více než 1/4);
* work_mem = 1/20 RAM;
* maintenance_work_mem = 1/4;
* max_fsm_relations = plánovaný počet tabulek v databázích * 1,5;
* max_fsm_pages = max_fsm_relations * 2000;
* fsync = true;
* wal_sync_method = fdatasync;
* commit_delay = od 10 do 100;
* commit_siblings = od 5 do 10;
* effective_cache_size = 0,9 z hodnoty cached, kterou ukazuje free;
* random_page_cost = 2 pro rychlé CPU, 4 pro pomalé;
* cpu_tuple_cost = 0,001 pro rychlé CPU, 0,01 pro pomalé;
* cpu_index_tuple_cost = 0,0005 pro rychlé CPU, 0,005 pro pomalé;
* autovacuum = on
* autovacuum_vacuum_threshold = 1800
* autovacuum_analyze_threshold = 900
Údržba databáze