Jak už bylo uvedeno, princip statspacku spočívá ve vytváření snapshotů interních informací dostupných ve V$ pohledech, přičemž vytvořením snapshotu je míněno uložení dat z pohledu do k tomu určených tabulek. Během analýzy jsou poté načtena data příslušná počátečnímu a koncovému snapshotu a vypočte se jejich rozdíl, který je případně dále zpracován a zobrazen uživateli.

Statspack vs. pgstatspack

Pgstatspack funguje v zásadě na totožném principu jako statspack, tj. ukládá "snapshoty" dat ze zajímavých systémových pohledů (pg_stat_* a pg_statio_*) do k tomu určených tabulek. Existují ale pochopitelně i některé rozdíly - podívejme se na dva nejpodstatnější. První se týká úrovně na které jsou údaje sbírány - totiž zatímco statspack sbírá data o celé instanci (i jednotlivých objektech), pgstatspack data sbírá primárně na úrovni jednotlivých databázových objektů (tabulky, indexy, sekvence, ...). Druhý se týká šíře sbíraných informací, a (pomineme-li různé úrovně sběru dat) lze konstatovat že v Oracle jsou sbírány zhruba následující obecné typy informací:

• rollback segment - PostgreSQL vzhledem ke své MVCC architektuře nic jako rollback segment nemá, tato statistika tedy nemá smysl
  
• row cache - na úrovni databáze jsou k dispozici informace o tom kolik bloků bylo načteno z disku a kolik jich bylo načteno z cache
• eventy (system, background, session) - eventy v PostgreSQL bohužel nejsou
• systémové statistiky - základní systémové statistiky k dispozici jsou, a to jednak na úrovni databáze, jednak na úrovni bgwiter procesů (ale bgwriter statistiky nejsou zatím v pgstatspacku podporovány)
• wait statistiky - statistiky "čekání" v PostgreSQL bohužel také nejsou (i když změnu možná přinese dtrace nebo obdobná technologie)
• statistiky zámků - v PostgreSQL bohužel nejsou sbírány
• statistiky latchí - latche jsou v Oracle používány pro synchronizaci přístupu ke sdíleným zdrojům (např. query plan cache v shared poolu apod.), ale jejich statistika v PostgreSQL bohužel není k dispozici
• SQL statementy, query plany - bohužel nedostupné
• statistiky na úrovni segmentů, tj. DB objektů (logical/physical reads, row lock, waits, buffer busy waits) - ano, částečně jsou k dispozici (některé typy statistik ale v PostgreSQL sbírány nejsou - viz. výše)

Tolik k rozdílům mezi statspackem a pgstatspackem, a nyní už se podívejme na použití.

Něco málo o snapshotech

Instalaci pgstatspacku tu popisovat nebudu - je dobře popsaná v README souboru, a v podstatě se nejedná o nic jiného než o vytvoření několika tabulek a procedury která do nich uloží snapshot. Podívejme se tedy na praktické použití ale nejdříve ještě trochu teorie o snapshotech.

Optimální interval mezi snapshoty

Vzhledem k tomu že podstatou pgstatspacku jsou rozdíly hodnot mezi snapshoty, jsou vždy nutné dva snapshoty - pokud máte jediný snapshot nemůžete spočíst rozdíl a máte smůlu. Otázkou zůstává jak dlouhý by interval mezi snapshoty měl být. Optimální hodnota silně závisí na aktivitě databáze, ale zkušenosti ukazují že naprosto nemá smysl dělat snapshoty na úrovni dní - jako optimální se ukazují intervaly o délce jednotek minut (např. 5 až 15 minut), v některých případech lze uvažovat i o cca hodině (ale to už je skutečně mezní hodnota).

Totiž pgstatspack přijde vhod zejména v případě řešení (krátkodobých či  dlouhodobých) výkonnostních problémů, indikovaných skokovou změnou ve sbíraných datech. Příkladem může být například situace kdy databáze přestane na některé velké tabulce používat index, v důsledku čehož skokově naroste počet sekvenčních skenů tabulky (a množství sekvenčně načtených bloků), a naopak skokově poklesne počet index skenů, počet bloků načtených z indexu apod.

V případě krátkodobých problémů sběrem snapshotu za příliš dlouhé časové období tento "výkyv" rozmělníte a bude daleko složitější ho identifikovat, čímž je popřen samotný smysl snapshotů. Naopak pokud jsou problémy dlouhodobé (např. pokud trvají po celý interval), potom je celkem jedno jestli data sbíráte po dobu 5 minut nebo 5 hodin - stejně je třeba je nějakým způsobem normalizovat (přepočítat např. na hodnoty "za vteřinu" - viz. dále). V případě dlouhodobých problémů je tak jedno jak byl interval dlouhý.

Je tedy zřejmé že příliš dlouhé intervaly vám vlastně žádné výhody nepřináší - v případě dlouhodobých problémů je délka intervalu celkem šumák, a naopak v případě krátkodobých problémů ztěžuje vaši snahu o identifikaci příčin problémů.

Snapshoty z doby problému nestačí

Řekněme že právě řešíte nějaký akutní výkonnostní problém - ještě před 15 minutami bylo všechno v pohodě, ale z ničeho nic se něco pokazilo a databáze mele z posledního což uživatelům (např. webové aplikace která k databázi přistupuje) působí nemalé problémy. Je třeba co nejdříve identifikovat problém a opravit ho, ale vy absolutně netušíte co se pokazilo a kde začít, takže se rozhodnete použít pgstatspack a doufáte že ukáže na problematickou tabulku apod. Uděláte tedy snapshoty v intervalu 5 minut a díváte se na jejich rozdíl - ale jak proboha poznáte které hodnoty jsou normální a které se skokově změnily? Jistě, spousta věcí jde odhadnout, ale ne vždy.

To znamená že pokud řešíte výkonnostní problém, jsou pro vás snapshoty z doby kdy bylo všechno v pořádku stejně důležité jako snapshoty vytvořené v době kdy se problém projevuje. Nejlepší způsob jak to zajistit je pravidelné vytváření snapshotů, např. pomocí cronu.

Použití pgstatspacku v praxi

A nyní již k praktické ukázce použití pgstatspacku - předem upozorňuji že se jedná o poněkud umělý příklad, sloužící jen a pouze k ilustračním účelům. Uvažujme jednoduchou tabulku

CREATE TABLE pgstatspack_test (
    id INTEGER,
    value TEXT
);
CREATE UNIQUE INDEX pgstatspack_test_idx ON pgstatspack_test(id);

kterou naplníme 100.000 řádky (cca 35MB tabulka a 2MB index):

INSERT INTO pgstatspack_test SELECT i, repeat(md5(i::text), 10)
                               FROM generate_series(1,100000) s(i);
ANALYZE pgstatspack_test;

Řekněme že z tabulky chceme načíst 1000 řádek na základě náhodné hodnoty ve sloupci "id" pomocí následující procedury

CREATE OR REPLACE FUNCTION pgstatspack_test_proc() RETURNS void AS $$
DECLARE
    v_text TEXT;
BEGIN
    FOR i IN 1..1000 LOOP
        SELECT value INTO v_text FROM pgstatspack_test
         WHERE id = (SELECT (99999*random())::int + 1);
    END LOOP;
END;
$$ language plpgsql;

Zkusme ji spustit (a vytvořit snapshot před a po spuštění):

SELECT pgstatspack_snap('index-before');
SELECT pgstatspack_test_proc();
SELECT pgstatspack_snap('index-after');

a nyní pomocí skriptu pgstatspack_report.sh vygenerujte report - příklad vygenerovaný na mém počítači najdete zde: pgstatreport_index_scan.txt.

Nyní proveďme to samé ale s vypnutým index scanem (a bitmap index scanem).

SET enable_indexscan = off;
SET enable_bitmapscan = off;

-- na tomto místě je nutné znovu vytvořit testovací proceduru, jinak
-- se použije původní query plan (s indexem)

SELECT pgstatspack_snap('index-before');
SELECT pgstatspack_test_proc();
SELECT pgstatspack_snap('index-after');

Opět je třeba vygenerovat report - můj report najdete zde: pgstatreport_seq_scan.txt.

A nyní přichází samotná analýza reportů, jehož cílem je "zjistit" proč druhý běh procedury trvá zhruba 10x déle (36 vteřin oproti 4 vteřinám při prvním spuštění).

Database statistics

První zajímavá čísla najdete v sekci "database statistics" - pokud máte v PostgreSQL clusteru několik databází, tato sekce může zajímat protože vám pomůže zjistit které databáze generují největší část I/O operací a podobně.

Vzhledem k tomu že my víme že problémy jsou v databázi "pgstatspack" takže vypíšeme jen příslušný řádek (a to samé platí pro tabulky / indexy v dalších sekcích).

Při spuštění s využitím indexu dostanete tato čísla

zatímco bez indexu (tj. se sekvenčním skenem) dotatnete toto

Už z této sekce reportu je zcela jasně vidět že výrazně narostl počet I/O operací, a to jak logických (60x) tak i fyzických (20x). To pochopitelně nemusí znamenat nějakou chybu - klidně se může jednat o krátkodobý nárust zátěže (více uživatelů a tedy více načtených dat).

Podívejme se tedy na další sekce které do analýzy vnesou více světla - zajímavá čísla najdeme až v sekci "Tables ordered by percentage of tuples scanned" (tabulky setříděné dle poměru načtených řádků) a "Indexes ordered by scans" (indexy setříděné dle počtu skenů).

Tables ordered by percentage of tuples scanned

Podívejme se tedy na statistiku tabulek - s indexem to vypadá takto

zatímco bez indexu takto

Jak sami vidíte, bez indexu případě výrazně narostl počet načtených bloků z tabulky a naopak poklesl počet index hitů, což je neklamným znamením že se používá sekvenční sken namísto index skenu, stejně jako nárust počtu načtených řádek na 99%.

Indexes ordered by scans

Používání sekvenčního namísto index skenu si můžeme potvrdit v sekci věnované indexům - zatímco s indexem vypadá takto

bez indexu se v ní náš index (pgstatspack_test_idx) pochopitelně vůbec nevyskytuje (protože se index vůbec nepoužívá).

Závěrem

Doufám že se mi podařilo dostatečně demonstrovat jak užitečným nástrojem může pgstatspack být. Rozhodně se ale nejedná o nástroj pro uživatele kteří o databázích nic neví - v případě že se ale databázím věnujete a pojmy jako sekvenční sken, index sken, cache, apod. vám nedělají problém, potom je pgstatspack nástroj pro vás.