přejít na obsah přejít na navigaci

Linux E X P R E S, Optimalizace databáze PostgreSQL

Optimalizace databáze PostgreSQL

postgresql.png

Tímto článkem začínáme seriál o optimalizaci oblíbené open-source databáze PostgreSQL. Dnes začneme nastavením záznamu činnosti databáze a také nastavením jejího automatického čištění.


reklama

Dnešním dílem začínáme seriál o optimalizaci oblíbené open-source databáze PostgreSQL. V seriálu projdeme většinu konfiguračních voleb (platné pro verze 8.1 až 9.0) s cílem vysvětlit, co znamenají, a jak je nastavit pro vysoký výkon celé databáze. Nečekejte ale optimalizaci databázového schématu, budeme se věnovat čistě nastavením databáze na serveru.

Záznam činnosti databáze

Logování událostí a chyb je velmi důležité pro administrátora systému a i když přímo nepatří do ladění výkonnosti, tak dobře nastavený log může pomoci najít slabá místa v konfiguraci a odhalit dlouhotrvající dotazy. V příslušné sekci konfiguračního souboru postgesql.conf (/var/lib/pgsql/data/postgesql.conf) jej mají na starosti parametry log_destination, log_directory, log_filename, které však bývají nastaveny správcem dané distribuce, např.: CentOS 5.5:

log_destination = 'stderr'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Události jsou pak ukládány do souborů v adresáři pg_log v základním adresáři PostgreSQL (například /var/lib/pgsql/data), soubory jsou pojmenovány podle aktuálního data a času.

Předpona řádku logu (log_line_prefix)

Ve výchozím nastavení řádek v logu nemá žádnou předponu, což je nepraktické (není poznat, který uživatel které databáze chybu způsobil a kdy). Pro začátek je dobré nastavit například:

log_line_prefix='%t:%r:%u@%d:[%p]: '

Kde:

%u Uživatelské jméno (přihlášeného uživatele databáze)
%d Jméno databáze
%r Klientský počítač včetně čísla portu
%p PID procesu na straně serveru
%t Časová značka bez milisekund

Více údajů pro záznam do řádku logu je uvedeno v dokumentaci.

Zaznamenávání příkazů jazyka SQL (log_statement)

log_statement = ddl

Možnosti (kompletní dokumentace opět na webu):

none Příkazy SQL nebudou ukládány do logů
ddl Do logu se budou zapisovat pouze příkazy DDL jako CREATE (TABLE, DATABASE), DROP, ALTER.
mod Příkazy, které modifikují data. Tedy například: INSERT, DELETE. Jsou zahrnuty taktéž příkazy DDL.
all Všechny příkazy.

Obvykle není potřeba DDL příkazy zaznamenávat, případně jen z kategorie mod nebo ddl. Typická databáze výrazně více čte, než zapisuje, a těchto příkazů bude tedy minimum.

Na hledání problematických dotazů je jejich logování vynikající pomůcka, zvláště v kombinaci s následující volbou záznamu pouze dlouhých dotazů.

Dlouhé dotazy (log_min_duration_statement)

Příkazy trvající déle než log_min_duration_statement (v milisekundách) se zapíší do logu. Tato volba tak představuje ideální prostředek na hledání pomalých dotazů.

log_min_duration_statement = 200

Za dlouhé dotazy (v případě interaktivní aplikace) se považují dotazy delší než 200 ms. S tímto nastavením je pak možné povolit záznam všech dotazů, v případě optimalizované databáze a aplikace bude log vždy prázdný.

V PostgreSQL 8.4 a novějším je možné také použít modul autoexplain. Jeho použití je flexibilnější (je možné jej volat z klienta a není třeba modifikovat konfiguraci) a také přináší více informací, nejenom dotaz samotný, ale také výsledek příkazu EXPLAIN (výpis prováděcího plánu) v době běhu dotazu. Ten se může radikálně lišit od pozdějšího spuštění po přepočítání statistik apod.

Vakuum a statistiky

PostgreSQL vyžaduje dva druhy pravidelné úklidové práce, které přímo vychází ze způsobu ukládání datových souborů filozofií MVCC: systém ukládání nových verzí řádků místo modifikace původních umožňující souběžné zpracování zápisových a čtecích transakcí a jejich izolací. Datové řádky se tedy nikdy nemění, v případě potřeby změny či přidání dat se vždy vytvoří nový (aktuální) řádek a starý se pouze označí za neplatný (ve skutečnosti celé skupiny datový řádků spadající do jedné transakce). Tyto staré řádky je potřeba pravidelně odstraňovat (odtud výraz vakuum, anglicky vysávání). Také je potřeba udržovat aktuální statistiky datových sloupců pro výběr optimálního prováděcího plánu.

Tyto operace (a také příslušné SQL příkazy) se nazývají VACUUM a ANALYZE. Ve zkratce, příkaz VACUUM uklidí datové soubory a odstraní z nich staré řádky po potvrzených transakcích, řádky smazané příkazem DELETE apod. Příkaz VACUUM FULL také zmenší velikost datových souborů.

Příkaz ANALYZE projde datové řádky a aktualizuje statisticky, což významně napomáhá optimalizátoru dotazů vybrat správný prováděcí plán. Více v dokumentaci. Oba příkazy se spojily do jednoho: VACUUM FULL ANALYZE.

Příklad z praxe: Zákazníkův databázový server běžel (jak to tak bohužel bývá) v základní konfiguraci. Ta je jednak paměťově velmi úsporná, a tak databáze neúměrně zatěžovala disk (nevyužívala cache v paměti a tedy byla dost pomalá), ale také bez aktivního procesu autovacuum (verze PostgreSQL 8.1). Což dále výrazně zpomalovalo chod celé databáze a také významně ukusovalo z volného místa na disku. Po provedení příkazu VACUUM FULL ANALYZE a úpravě konfigurace se velikost datových souborů smrskla na 1/8 (z několika GB nasbíraných asi za čtrnáct dnů provozu na stovky MB) a zpracování SQL dotazů se mnohonásobně zrychlilo.

Proces autovacuum a starší verze PostgreSQL

Ve starších verzích se musel úklid příkazem VACUUM dělat ručně buď z aplikace (tato možnost je i dnes, ale není to vůbec nutné, a v některých speciálních případech ani žádoucí používat automatické spouštění úklidu), případně z nějakého skriptu volaného pravidelně z cronu.

Ve verzi PostgreSQL 8.1 přibyl proces autovacuum, který spouštěl příkaz VACUUM podle potřeby (z analýzy změněných řádků v datových souborech). Tento proces byl však až do 8.3 v základním nastavení vypnutý. Od verze 8.3 je naopak standardně zapnutý. Ve starších verzích je potřeba jej zapnout nastavením v postgresql.conf:

stats_start_collector=true
stats_row_level=true
autovacuum=on

Příkazu VACUUM a procesu autovacuum byla v průběhu vývoje databáze věnována velká péče a v každé nové verzi je výrazně efektivnější a méně omezuje běžný databázový provoz. Je proto vhodné aktualizovat databázový server na vyšší verzi (Pavel Stěhule, český vývojář a popularizátor PostgreSQL, doporučuje v produkčním prostředí udržovat verzi o stupeň starší, než je aktuální. V těchto dnech, po vydání 9.0, je tedy vhodná produkční verze 8.4).

Velikost pracovní paměti

Pracovní paměť je v databázovém serveru potřeba pouze pro několik úkonů, a to konkrétně pro operace typu VACUUM, CREATE INDEX a ALTER TABLE ADD FOREIGN KEY. Tyto tak mohou alokovat velikost paměti maintainance_work_mem nad rámec běžné paměti přidělené klientovi. Navíc je nepravděpodobné, že by takových operací běželo více současně, proto je možné přidělit větší objem paměti než pro standardní klientskou práci.

Je dobré mít na paměti, že tuto paměť použijí i procesy autovacuum (počet těchto procesů se nastavuje pomocí volby autovacuum_max_workers).

Dobré počáteční nastavení pro moderní servery s dostatkem paměti používá 5 % velikosti paměti RAM (tj. 50 MB na každý GB paměti), například pro server s 8 GB RAM je vhodné nastavit:

maintainance_work_mem = 400MB

Nahoru

(Jako ve škole)
Průměr: 1,00 | Hodnotilo: 5
 

Top články z OpenOffice.cz

Příspěvky

Optimalizace databáze PostgreSQL
Štefan Miklošovič 19. 03. 2011, 02:59:23
Odpovědět  Odkaz 
Vyborny clanok, tesim sa na pokracovanie.
Miroslav Hrončok Re:Optimalizace databáze PostgreSQL
Miro Hrončok 19. 03. 2011, 12:34:34
Odpovědět  Odkaz 
Interval bude pravděpodobně 14 dní.
Optimalizace databáze PostgreSQL
rr2 20. 03. 2011, 09:58:51
Odpovědět  Odkaz 
Dobry clanok.

Autovacumm je achilova pata tejto databazy.
Pocas spustenia nie je mozne pracovat s danou tabulkou co absolutne vylucuje nasadenie v zivej prevadzke. Riesenia - manualne vacuovanie. Neskusal som v9...
Avsak pri vhodne zvolenej architekture sa s tym da zit.

Este treba sponenut problematicke indexovanie pri pouziti partitioningu. Databaza este stale nevie pouzit spravny index pri dotazoch ... Samotny partitioning vo v9 som este neskusal, my sme si urobili workaround ktory nam funguje perfektne

Dotiahnut treba multithreading (zatial riesime takisto vlastnym workaroundom).

Loader (mimoriadne rychly) nerobi logy, jedine co dostanete je msg. Preto riesime import dat v jave....

Pgadmin - treba este poriadne dorobit, hlavne "suggesting", taby, odkladanie vysledov dotazov a pod. "Limit" ako standardne nastavenie stale nefunguje a je treba limit davat do sql...

Backup - radsej riesime zipovanim celej databazy...

Security - zatial vyhovuje
Re:Optimalizace databáze PostgreSQL
Pavel Stehule 23. 03. 2011, 08:59:57
Odpovědět  Odkaz 
Tzv. lazy vacuum tabulku nezamyka - a s tabulkou muzete normalne pracovat.

Přidat názor

Nejsou podporovány žádné značky, komentáře jsou jen čistě textové. Více o diskuzích a pravidlech najdete v nápovědě.
Diskuzi můžete sledovat pomocí RSS kanálu rss



 
 

Tomáš Crhonek

Tomáš Crhonek

Pracuji jako administrátor linuxových serverů ve společnosti QCM. Zabývám se především optimalizací DB strojů, zejména PostgreSQL. Hraji si též se storage (od hdd po fs). Vedu si své stránky na www.heronovo.cz.


  • Distribuce: Debian, CentOS, RHEL
  • Grafické prostředí: bash
  • Hodnocení autora: *

| blog



Public Relations

QNAP uvedl novou modelovou řadu NAS TVS-x82T

Společnost QNAP uvedla na trh novou modelovou řadu NAS TVS-x82T, kterou tvoří tři různé modely (TVS-1282T, TVS-882T a TVS-682T). Nová řada je založena na vícejádrových procesorech Intel Core aktuální generace se 14nm výrobním procesem. Díky nim mohou nové NASy nabídnout dostatek výkonu i pro aplikace náročné na CPU.

Pokračování ...


CIO Agenda 2016