MySQL nebezpečí průtokových tabulek, zamyšlení nad insert into ... select from

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.