Vyčištění

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í:

• FULL provede plné vyčištění tabulek, dočasně bude PgSQL potřebovat dostatek volného místa na discích i v  paměti, po dobu provádění tohoto čištění budou tabulky exkluzivně zamčeny.
• FREEZE je odstranění všech duplicit „hrubou silou“. Tato varianta se vylučuje s předchozí.
• VERBOSE vám vypíše vše co PgSQL s databází dělá.
• tabulka je, jako všechny ostatní, volitelným parametrem. Nebude-li uveden název tabulky, provede se VACUUM na celou aktuální databázi, bude-li uveden, provede se vyčištění jen této tabulky.

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řerovnání tabulky

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.

Analýza příkazů

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í:

• ANALYZE bez tohoto parametru je udělána jen analýza průběhu příkazu, s tímto parametrem je příkaz i proveden, leč při analýze SELECTU výsledek neuvidíte.
• VERBOSE bude vypsán úplný strom prováděných operací místo jejich pouhého souhrnu.

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)

Závěr

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.