Myšlenky dne otce Fura

Je to asi rok co jsem psal článek o implicitních commitech při provádění DDL příkazů. Řešil jsem tehdy problém velmi složitého selectu, který se výrazně zjednodušil, pokud jsem jej rozdělil na dvě části s uložením mezivýsledků. Jelikož jsem potřeboval zachovat transakčnost, nemohl jsem využít temporárních tabulek a šel jsem cestou stálé tabulky s aplikačním hashem rozlišující mezivýsledky jednotlivých transakcí mezi sebou. To jsem ještě netušil, jaké mi to přinese komplikace …

Jak jsem nedávno zjistil není ukládání mezivýsledků to odkládací tabulky vůbec dobrý nápad. Zvlášť, když se počty záznamů v mezivýsledcích pohybují v řádech tisíců. Jednak tento mechanismus vyžaduje I/O operace na disku a jednak velikost tabulky neustále roste, přestože se správně čistí a reálně v ní nejsou žádné záznamy. Zní to zvláštně, ale tabulka, která pro select count(*) from TABULKA vrátí výsledek 0 záznamů může představovat několik giga místa na disku.

To co teď píšu je spíš empirická zkušenost, než reálná znalost toho jak to v InnoDB skutečně uvnitř chodí. Obraz jsme si skládal ze střípků v různých článcích a dokumentaci, co jsem našel. InnoDB má alokovaný prostor pro tabulku, ve kterém se pohybuje. Při insertech si doalokovává nový potřebný prostor, nicméně při deletech prostor automaticky neuvolňuje. Vzniká tak fragmentace tabulky – stejná jako známe v případě fragmentace souborů v souborovém systému. InnoDB by se mělo snažit díry s novými inserty zaplňovat, nicméně v některých případech se tak neděje moc efektivně.

Rychlost odezev SQL dotazů je přímo úměrná reálné velikosti tabulky a jeji fragmentaci. Tj. příkaz select count(*) from TABULKA bude trvat znatelně pomaleji na prázdné tabulce zabírající 1GB oproti prázdné tabulce zabírající pouhý 1kB. A v tom je celé jádro pudla.

V mém případě proudilo skrz odkládací tabulku velké množství dat (pomocí insert into … select from … příkazu), které (přestože ve finally bloku odmazávaly) neustále nafukovaly objem této odkládací tabulky a postupně degradovaly výkon systému.

Čištění zaneřáděné tabulky je možné – buď příkazem DROP + CREATE TABLE (respektive TRUNCATE TABLE, které v konkrétním případě dělá totéž) nebo příkazem OPTIMIZE TABLE. Oba dva způsoby však vyřadí tabulku na nějakou dobu z provozu (díky uzamčení celé tabulky) a tudíž jsou pro pravidelné používání v rámci práce nějaké knihovny dost diskutabilní.

Z výše uvedného vyplývá, že použití principu průtokové tabulky na MySQL InnoDB při větší zátěži nepoužitelné. Osobně jsem algoritmus přepsal tak, abych si vystačil s jedním SQL dotazem s dalšími subselecty a problém jsem za cenu drsnější aplikační logiky obešel.

Zvláštní na tom celém je to, že když jsem si dělal odděleně testy s cílem navodit fragmentaci tabulky, tak se mi to na lokálním systému vůbec nepodařilo (a to i v případě, že jsem v paralelních threadech současně vkládal i vymazával řádky z tabulky s průtokem 1 mil. řádků v tabulce). Tedy velikost tabulky se zmenšovala s počtem záznamů v ní. Z toho vyvozuji, že problém tkvěl právě v použití insert into … select from …, který zároveň vkládal a odmazával celé bloky řádků. Popřípadě odlišnému chování MySQL na Windows a Linuxu.

Insert into … select from … má ještě další nevýhody. Není to na první pohled vidět, ale tento příkaz vytváří celou řadu zámků – jednak write zámky v tabulce, do které zapisuje a jednak read zámky v tabulce, ze které čte. V případě, že v selectu nemůže využít indexů, ale jedná se o fulltable scan, tak to při standardní isolaci transakce (REPEATABLE READ) v podstatě uzamkne zdrojovou tabulku proti změnám (opět mne neberte 100% za slovo, tohle už bylo na mě trošku husté čtení a třeba jsem něco z dokumentace vyčetl špatně). Výsledkem bylo, že při konkurentním přístupu mě z kódu, který tyto statementy používá začaly vybíhat deadlock vyjímky, v některých případech timeout vyjímky při čekání na zámek.

Pokud mám mezi čtenáři nějakého MySQL experta budu velmi rád, když do komentářů napíše svůj názor na celou věc, nebo ještě lépe – víc odkryje pozadí toho všeho.

Tyto informace samozřejmě nejsou přenositelné na jiné DB enginy – každý pracuje se zámky trochu odlišně. Tohle jsou prostě jen empirické zkušenosti z MySQL 5.1 + InnoDB.

Související články:

1. Import velkých dat do MySQL
2. MySQL temporary tables inside Transaction
   and the magic of implicit commit
3. Jak nainstalovat MySQL server na Windows Vista

Ohodnoťte článek:

(doposud bez hlasů)

 Loading ...

Článek byl odeslán 02.02.2010 v 20:45 a je zařazen do kategorie Databáze, Programování. Reakce na tento článek můžete sledovat skrze RSS 2.0 feed. Můžete vložit reakci, nebo vložit odkaz na vaše stránky.