O optimalizaci dat v tabulkách jsme již v tomto seriálu psali, nyní se podíváme, jaké nástroje nám PostgreSQL server nabízí pro optimalizaci databáze a dotazů.
7.8.2006 09:00 | MaReK Olšavský | přečteno 16581×
PgSQL manipuluje se svými datovými soubory stejným způsobem, jaký je v souborových databázích dbf a SQLite. Upravené nebo vymazané věty nejsou ve skutečnosti odstraněny, ale označeny jako neplatné a v případě updatů přibyde v souborech další věta, která je vracena. Dalším „nepořádkem“, který se v databázi hromadí jsou zrušené transakce. To znamená, že databáze, která je používána aktivně, nejen pro čtení, postupně zvětšuje své nároky na diskový prostor i přesto, že v ní je defakto konstantní počet záznamů. Proto je zapotřebí ji občas takzvaně vyčistit, k čemuž server nabízí příkaz VACUUM.
Syntaxe je následující: VACUUM [ FULL | FREEZE ] [ VERBOSE ] [tabulka], přičemž význam parametrů je následující:
Použití VACUUM je dosti náročné na výkon stroje, což poznáte především, když máte v tabulkách větší množství dat, proto je vhodné pečlivě zvážit, kdy se bude tento příkaz provádět a vhodně jej načasovat, například krátkým skriptem v Pythonu/Perlu/PHP, který bude spouštěn cronem. Pokud je databáze opravdu aktivně a masivně používána, přičemž drtivě nepřevažjí výběry, je vhodné tuto údržbu provádět denně v době, kdy není žádný, nebo je naprosto minimální provoz. Pokud je situace jiná, je mnoho výběrů, ale minimum, mazání a updatů vět je možné zvolit delší intervaly, například týden, měsíc, 3 měsíce. Mnohem obvyklejší postup je provádět VACUUM denně na opravdu živých tabulkách a na těch, ve kterých dochází ke změnám dat v dlouhých intervalech provádět tento příkaz v delších intervalech.
Příkaz VACUUM má ještě jednu modifikaci, která provede nejen vyčištění databáze, ale i sesbírá statistiky, které mohou urychlit, obvykle ve větších databázích opravdu urychlí, výkonnost prováděných příkazů. Syntaxe příkazu je VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ tabulka [(sloupec1 [, ...] )]]. Ten modifikátor, který provede i tuto analýzu se jmenuje ANALYZE, oproti předchozí variantě je možné udělat analýzu cíleně vůči určitým sloupcům.
Příklady:
-- vycisteni a analyza tabulky dealers podle sloupce active VACUUM VERBOSE ANALYZE dealers(active); -- proste vycisteni cele databaze s vystupem informaci VACUUM FULL VERBOSE;
Pokud nechcete psát skript, který byste spouštěli cronem, můžete spouštět přímo konzolový/terminálový příkaz vacuumdb, který má podobné parametry, jako VACUUM, ale navíc mu předáte patřičné připojovací řetězce k databázi. Jeho parametry naleznete buď v původní dokumentaci, nebo je získáte zadáním vacuumdb --help, či s podrobnějším popisem pomocí man vacuumdb. Použití této možnosti je vázáno na přístup k možnosti spouštět programy na straně serveru, kteroužto možnost na většině běžných webhostingů mít nebudete.
Přidávání dat do tabulek, případně jejich mazání, pokud jsou indexovány, provádí server v několika krocích. Napřed přidá novou větu do tabulky, případně ji z tabulky vymaže, a poté občerství všechny indexy, jichž se tato změna dotkla. O významu indexů jsme již v seriálu měli 12. díl. V indexech jsou data uspořádána rozumným způsobem, v tabulkách jsou data za sebou tak, jak byla vkládána, mazána a aktualizována, což je velice neefektivní.
Data v tabulce lze přerovnat podle jednoho indexu, k tomu slouží příkaz CLUSTER. Název tohoto příkazu může svádět k tomu, že s jeho pomocí se spojují PgSQL servery do clusterů pro rozdělení potřebné zátěže mezi více strojů, ale není tomu tak, k tomu jsou, na rozdíl od MySQL, zapotřebí externí nástroje.
Syntaxe příkazu CLUSTER je CLUSTER index ON tabulka, kde index je ten, podle něhož chcete přerovnat tabulku. Příkaz existuje i ve formě CLUSTER tabulka, kdy jsou data přerovnána podle indexu, který byl naposledy k dané tabulce pro přerovnávání použit. Příkaz CLUSTER použitý samostatně přerovná všechny již v minulosti rovnané tabulky a indexy použije stejným způsobem, jako CLUSTER tabulka.
Opět, stejně jako u VACUUM, existuje i pro tuto funkci konzolový ekvivalent clusterdb, jehož parametry naleznete buď na stránkách s dokumentací PostgreSQL, nebo v un*xech obvyklým způsobem clusterdb --help, či man clusterdb.
PgSQL je standardní databázový server ovládaný příkazy v SQL. Co se děje při provádění jednotlivých příkazů, a s jejich následnou analýzou a optimalizací, nám pomůže příkaz, opravdu jen SQL, takže použitelných v psql konzoli, aplikaci či jiném nástroji pro tuto databázi, EXPLAIN, který zobrazí posloupnost operací příkazu uvedeného na EXPLAIN. Příkaz má syntaxi EXPLAIN [ANALYZE] [VERBOSE] SQLPŘÍKAZ, význam parametrů je následující:
V zobrazení výstupu tohoto uvidíte nejen časovou náročnost prováděného příkazu, ale i použité indexy a v případě spojování několika tabulek uvidíte i použité způsoby spojení. Tento příkaz prokáže veliké služby při tvorbě databáze a návrhu dotazů, kterými získáváte data. Ve výstupu uvidíte hodnoty cost, které vám ukáží časovou náročnost prováděných příkazů, rows ukazuje počet řádků, které se mohou aktuáně vrátit z databáze a width jejich délky v bytech.
EXPLAIN SELECT * FROM lsoft.dealers AS t1 LEFT JOIN lsoft.orders AS t2
ON t1.dealers_id=t2.dealers_id WHERE t1.active=TRUE;
--vysledek:
QUERY PLAN
----------------------------------------------------------------------
Hash Left Join (cost=1.09..2.21 rows=4 width=356)
Hash Cond: ("outer".dealers_id = "inner".dealers_id)
-> Seq Scan on dealers t1 (cost=0.00..1.06 rows=4 width=323)
Filter: active
-> Hash (cost=1.07..1.07 rows=7 width=33)
-> Seq Scan on orders t2 (cost=0.00..1.07 rows=7 width=33)
(6 řádek)
V tomto díle jsme se seznámili s nástroji, které nám umožní optimalizovat uspořádání dat v databázích a zjistit, kde máme slabá místa v tvorbě dotazů a indexaci tabulek. V příštím díle se podíváme na to, co jsou to pohledy, které nám při větším množství dat, nebo dlouhých větách mohou významně urychlit práci s databází, případně jednotlivým uživatelům omezit přístup pouze k některým datům.