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.

Podělte se s ostatními:

  • Digg
  • del.icio.us
  • Technorati
  • Diigo
  • DZone
  • FriendFeed
  • Google Bookmarks
  • LinkedIn
  • Reddit
  • RSS
  • StumbleUpon
  • Twitter

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:
Takovéhle články už radši ne!Nic nového pod sluncem.Průměr - obsahuje zajímavé střípky informací.Hodnotný článek - lecos nového jsem se dozvěděl.Skvělý článek - informace se mi dost hodí. (doposud bez hlasů)
Loading ... Loading ...

-2 Responses to “MySQL nebezpečí průtokových tabulek, zamyšlení nad insert into … select from”

  1. milos says:

    Resili jsme podobne problemy. Dost casto se zpracovani zrychlilo, kdyz jsme selecty (i updaty) rozdelili a ukladali mezivysledky.

    Zda se ale, ze vsechny enginy musi resit stejne problemy a reseni se lisi jen v detailech. Takze zpusob ukladani na stranky a politika zamku bude podobna. Delam na Sybase a tzv. Deleted rows (ale i Forwarded rows – radky, ktere se nevesly po update a nachazeji se na jine strance, nez by mely byt) se nam objevuji take. U kmenovych tabulek jsme provedli po jedne reorganizaci, to je ale narocne na misto.

    Nejlepsi asi bude mit casovou ulohu, ktera v klidove dobe provozu udela truncate. Ten je navic vyhodny i z hlediska indexu.

    INSERT .. SELECT se navic v Sybase chova jeste lepe, nez SELECT…. INTO tabulka (nevim, zda to v MySQL jde).

  2. Otec Fura says:

    Nepodporuje – má alternativní zápisy, ale takhle to přímo použít nejde:

    http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

    Mě se to celé naštěstí podařilo předělat na vnořené selecty, které zdá se jsou i výkonnější než původní řešení – hádám právě pro to, že se to celé dokáže odehrát v paměti a nemusí se to fyzicky dostat do té odkládací tabulky, což by zahrnovalo IO operace.

Leave a Reply