Neaktuální statistiky

Když databáze sestavuje execution plan, postupuje tak že sestaví execution planů několik, a následně spočítá jejich "cenu" tj. počítá náklady (CPU operace, I/O operace, apod.) potřebné k vyhodnocení SQL dotazu pomocí daného execution planu. Z těchto planů následně vybere ten "nejlevnější" a použije ho k vyhodnocení dotazu.

Při určování ceny daného execution planu jsou používány statistiky - informace o velikosti tabulek, počtu řádků, histogram hodnot ve sloupcích, apod. Důsledkem toho je že pokud jsou statistiky neaktuální, může se databáze při výběru nejlevnějšího execution planu zmýlit (protože si například myslí že tabulka má výrazně méně řádek, apod).

Jak poznáte že statistiky neodpovídají skutečnosti? Celkem jednoduše z execution planu, získaného příkazem EXPLAIN ANALYZE který obsahuje jak odhady parametrů (např. počty výsledných řádek) vypočtené při sestavování execution planu, tak i hodnoty získané při skutečném spuštění. Pokud se hodnoty výrazně liší, je to neklamná známka že statistiky jsou nepřesné - zastaralé nebo nedostatečně podrobné.

Náprava je celkem jednoduchá - stačí statistiky aktualizovat. V PostgreSQL to lze jednorázově provést příkazem ANALYZE, pro průběžné automatické aktualizování je vhodnější nastavit autovacuum démona. V Oracle se zhruba to samé provádí procedurami v balíčku DBMS_STATS.

Příklad: Vytvořme jednoduchou tabulku se dvěma sloupci (ID a VALUE) a naplňme ji náhodnými daty (1.000.000 řádek) přičemž statistiky jsou spočteny po vložení prvního řádku, a to vše samozřejmě při vypnutém autovacuum démonovi.

CREATE TABLE test_table (id INTEGER, value VARCHAR(255));
CREATE INDEX test_table_idx ON test_table(id);
INSERT INTO test_table SELECT mod(i, 100) + 1, REPEAT(MD5(i::text), 5)
                         FROM generate_series(1,1) AS s(i);
ANALYZE test_table;
INSERT INTO test_table SELECT mod(i, 100) + 1, REPEAT(MD5(i::text), 5)
                         FROM generate_series(2,1000000) AS s(i);

Pokud nyní zkusíme načíst řádky s ID = 20, tj. 1% řádků (10.000 řádek), dostaneme z příkazu EXPLAIN ANALYZE následující explain plan:

db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id = 20;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using test_table_idx on test_table
    (cost=0.00..8.39 rows=1 width=168)
    (actual time=0.087..131.211 rows=10000 loops=1)
   Index Cond: (id = 20)
 Total runtime: 142.704 ms
(3 rows)

Jak vidíte, zatímco dle původního odhadu by výsledný počet řádek měl být 1, ve skutečnosti jich je 10.000 - což je markantní rozdíl. Rozdílem hodnot "cost" a "actual time" se nenechte zmást - tyto hodnoty jsou nesrovnatelné; zatímco "cost" označuje v podstatě abstraktní cenu daného kroku (inicializaci a celkový čas), "actual time" udává čas potřebný k provedení daného kroku, změřený během skutečného vyhodnocování dotazu.

Proveďme aktualizaci statistik a spusťme dotaz znovu:

db=# ANALYZE test_table;
db=# EXPLAIN ANALYZE SELECT * FROM test_table WHERE id = 20;

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on test_table
    (cost=198.81..18609.48 rows=9604 width=168)
    (actual time=6.582..118.196 rows=10000 loops=1)
   Recheck Cond: (id = 20)
   ->  Bitmap Index Scan on test_table_idx
          (cost=0.00..196.41 rows=9604 width=0)
          (actual time=3.313..3.313 rows=10000 loops=1)
         Index Cond: (id = 20)
 Total runtime: 129.664 ms
(5 rows)

Jak je vidět, statistiky se výrazně zpřesnily - obecně vzato, odhadované a skutečné hodnoty nikdy nebudou stejné, problém je pouze pokud se výrazně liší (například o řád). Aktualizace statistik dokonce měla za následek volbu jiného execution planu, byť na rychlost dotazu to velký vliv nemělo - v případě složitějších dotazů (několik joinů, více indexů, více podmínek, složitější podmínky, apod.) to ale může rozhodovat zda dotaz poběží vteřinu nebo celý den.

Mimochodem - pro analýzu explain planů existuje výborný webový nástroj na explain.depesz.com který zvýrazní problematické části vloženého execution planu.

Nabobtnalé (bloated) tabulky a indexy

Jestliže neaktuálnost statistik je potenciálním problémem v podstatě ve všech databázových systémech, bobtnající tabulky a indexy poměrně úzce souvisí s MVCC architekturou daného systému. Zatímco v Oracle tento problém v podstatě neexistuje, zatímco v PostgreSQL se může jednat o značný problém.

PostgreSQL totiž při úpravě (UPDATE) řádku vytvoří "kopii" s novými hodnotami a původní řádek označí jako smazaný (a poznačí si ID transakce). Jinými slovy - původní řádky v tabulce (a v indexech) ponechává, čímž zajistí jejich dostupnost z ostatních transakcí (READ COMMITED) a možnost ROLLBACKu. Problém je že tímto způsobem tabulky a indexy bobtnají - pokud aktualizujete celou tabulku, její velikost se zhruba zdvojnásobí (a to samé platí i pro indexy).

Tabulky a indexy je tedy třeba pravidelně čistit - odstraňovat mrtvé řádky (dead tuples) které již nebudou potřeba pro žádné běžící transakce. V PostgreSQL k tomu slouží příkaz VACUUM [FULL] [ANALYZE] který provede "jednorázové" čištění, a ještě lépe autovacuum démon pro průběžné automatické čištění.

Jak zjistíte že tabulky nebo indexy nabobtnaly? Na to není lehká odpověď - PostgreSQL sice poskytuje mnoho zajímavých statistických systémových katalogů s informacemi o velikostech tabulek, počtu jejich řádek, počtu mrtvých řádek apod. čili přesně ty informace které potřebujete. Problém je že sběr a aktualizace těchto hodnot mohou být vypnuty - proto se přesvědčte že máte v konfiguraci zapnutý tzv. statistics collector (pozor, jeho konfigurace se liší mezi verzemi, zejména mezi 8.2 a 8.3). Nicméně pokud potřebné statistiky jsou sbírány, není nic jednoduššího než napsat skript který načte data z pohledu pg_stat_user_tables a pokud poměr živých vs. mrtvých řádek pro některou z tabulek překročí zvolený poměr (například 1:1), provede se definovaná akce - například se odešle e-mail administrátorovi, apod.

Optimálním řešením je samozřejmě autovacuum démon, ale i v případě jeho použití může docházet k problémům s nevhodným nastavením (například při pravidelných aktualizacích velkého množství dat, apod.). Jeho konfigurace je ale zcela mimo téma tohoto článku.