PgAnalyze

Při provozování webových aplikací nad PostgreSQL, je užitečné monitorovat SQL dotazy průběžně, tak aby bylo možné odhalit pomalé / nedostatečně optimalizované dotazy co nejdříve. Tento nástroj by vám měl dát zajímavé informace o každém z podezřelých dotazů.

Tento projekt se zrodil poměrně nenápadně - před několika lety jsme nad PostgreSQL provozovali několik středně velkých webových aplikací (napsaných v PHP) a čím dál častěji jsme naráželi na výkonnostní problémy. Evidentně souvisely s databází, ale bylo velmi obtížné zjistit které SQL dotazy nad kterou databází a ze které aplikace jsou vlastně příčinou problémů. Otázkou také bylo zda je špatný výkon důsledkem rostoucí návštěvnosti, nebo jsou dotazy prostě nevhodně formulované.

Nakonec jsme zapnuli logování pomalých dotazů v konfiguraci postgresql.conf, cca pod týdnu jsme prošli výsledný log a ručně jsme vytipovali nejproblematičtější SQL dotazy. Po jejich analýze jsme některé SQL dotazy přeformulovali a upravili jsme některé elementy architektury aplikací, v důsledku čehož výkonnostní problémy téměř zmizely - rychlost odezvy ve špičce se znatelně snížila, výrazně se zmenšil rozptyl rychlosti odpovědi, atd.

Řekli jsme si, že by nebylo na škodu podobný monitoring provádět pravidelně, a to nejen na produkčním serveru. Napsali jsme si skript který z logu vyzobal zalogované dotazy, a vygeneroval poměrně komplexní HTML report. Tak se zrodila první verze nástroje pganalyze.

Současnost

Během používání projektu se ukázalo že ačkoliv PostgreSQL umožňuje zapisovat pomalé SQL dotazy přímo do vlastního logu, má to i své nevýhody:

  • formát položek v logu se liší stroj od stroje - záleží na tom zda je využíván syslog, a také v závislosti na konfiguraci v postgresql.conf
  • záznamy v logu (a tedy i zaznamenané SQL dotazy) mohou být rozděleny na více řádků a promíchány mezi sebou - to vyžaduje poměrně složité parsování logu a identifikaci řádků které patří k sobě. To navíc za určitých okolností nemusí být možné (například v případě nevhodného nastavení logovaných údajů).
  • podobný nástroj lze s úspěchem použít i pro další databáze, což je ale těsným provázáním s PostgreSQL znemožněno
  • v PostgreSQL není možné nastavit některé sofistikované parametry logování (například pravděpodobnost zalogování dotazu)

Vzhledem k těmto nevýhodám jsem logování v PostgreSQL přestal používat k monitorování pomalých dotazů, a přesunul jsem ho do PHP knihovny zajišťující databázové operace. Zalogované dotazy mají unifikovaný jednoduchý formát (řádek na dotaz, atd.) takže jejich parsování je daleko jednodušší, a nezáleží zda byly zalogovány z PostgreSQL nebo jiné databáze.