Rychlost vkládání do InnoDB tabulek
Školení, která pořádám 
Pro ukládání dat do MySQL databáze rád používám úložiště InnoDB. Dvěmi klíčovými výhodami proti MyISAM tabulkám je funkčnost transakcí a podpora cizích klíčů.
Výkon InnoDB se také postupně zvyšuje, ale rychlost vkládání záznamů je zoufale nízká. Provedl jsem několik jednoduchých testů a přináším jejich srovnání. Vytvořil jsem tabulku s jedním sloupcem typu int NOT NULL bez indexů a vložil do ní tisíc záznamů. Výsledky jsou šokující.
| Kód | MyISAM | InnoDB |
|---|---|---|
<?php for ($i=0; $i < 1000; $i++) { mysql_query("INSERT INTO tab VALUES ($i)"); } ?> | 0.082 s | 26.448 s |
<?php mysql_query("START TRANSACTION"); // MyISAM: LOCK TABLES tab WRITE for ($i=0; $i < 1000; $i++) { mysql_query("INSERT INTO tab VALUES ($i)"); } mysql_query("COMMIT"); // MyISAM: UNLOCK TABLES ?> | 0.076 s | 0.126 s |
<?php $values = array(); for ($i=0; $i < 1000; $i++) { $values[] = "($i)"; } mysql_query("INSERT INTO tab VALUES " . implode(", ", $values)); ?> | 0.004 s | 0.035 s |
Bez transakcí jsou InnoDB tabulky při vkládání záznamů o dva řády pomalejší než MyISAM. Příčinou je nastavení direktivy innodb_flush_log_at_trx_commit na výchozí hodnotu 1, které způsobí synchronizaci dat na disku po každé transakci, což je v autocommit režimu po každém příkazu. Při uzavření všech vložení do jedné transakce se výkon dramaticky zlepší, pořád je ale zhruba dvakrát horší než u MyISAM tabulek. Další výrazné zlepšení přináší extended insert, ten ale zhruba dvacetkrát zvýší výkon i u MyISAM tabulek.
Přijďte si o tomto tématu popovídat na školení Výkonnost webových aplikací.
Diskuse
Michal Krajcirovic:
osobne spatruji naprosto zasadni nevyhodu innodb v mensi stabilite pri vypadku serveru - samozrejme se da ibdata1 rozdelit na vice souboru, samostatnych pro kazdou db, nicmene rada hostingu to prave tak nema a je tam riziko toho, ze tento soubor zkolabuje a databazi uz neda nikdo dohromady.
pokud to neni reseno samostatnyma ibdata souborama pro kazdou db, je to take pomerne problematicky migrovatelne, myisam snadno premigruji pouhym presunem slozky s datovymi soubory a neni problem. obecne tedy, z pozice server admina, vyrazne preferuji myisam pred innodb.
Radek:
Jojo, a to se právě stalo mně. Sice mám transakce, ale, přiznávám sice mou chybou, ale přesto, přišel jsem o ibdata1 a tím o všechny innodb tabulky ze všech databází.
krteQ:
To je ale vic nez syntetickej test....
Hadam zes to delal na nezatizenym serveru a v jednom vlakne - MyISAM zapisuje kazdej dotaz na disk, v tvym pripade nulovy konkurencnosti doslo k mergnuti zapisu na disk do jedinyho souvislyho zapisu, kterej je samozrejme rychlej. Zkus to ale na zatizenym produkcni serveru :) Tam pak jsou to vsechno random writes.
Napriklad, ja jsem provedl ten tvuj test s 10K radkama na serveru s bezici aplikaci a vysledek:
MyISAM:88.545 s
InnoDB *bez* transakci:40.486 s
A jak mas nastavenej mysqld? Spousta lidi rika ze InnoDB je pomalejsi, ale vetsinou je to konfiguraci :)
V pripade insertu zalezi treba na tehle promennych:
# flush do OS cache - pro vetsinu webu dostacujici
innodb_flush_log_at_trx_commit=2
# transaction log
innodb_log_file_size
innodb_log_buffer_size
krteQ: Miloslav Ponkrác:
Ano, to mě také napadlo, že test v článku je velmi k ničemu. Živil jsem se hodně databázemi, a podle testu v článku bych se rozhodně neřídil ani v nejmenším. Udělat seriózní databázový test je velmi složité až nemožné.Navíc, když insert do myisam tabulky s jednou konekcí je v zásadě sekvenční zápis na konec souboru, ale do innodb už je to jinak. Navíc rychlost insertu do tabulky bez indexů je v podstatě pouze akademická věc prakticky k ničemu se v praxi nehodící. A indexy zpomalují inserty.
V případě konkurenčního zápisu už jsme jinde, atd.. Nicméně výkon mysql je mizerný tak jako tak celkově. Nicméně na velmi mnoho účelů stačí.
Jakub Vrána 
:
Uznávám, že článek nemá parametry důkladného testu. Je to spíš jen takové jednoduché upozornění na to, že InnoDB bez transakcí je významně pomalejší a že víceřádkové inserty jsou významně rychlejší nezávisle na úložišti.
Nechtěl jsem uvádět kompletní konfiguraci a parametry testovacího stroje, uznávám ale, že alespoň hodnota innodb_flush_log_at_trx_commit=1 zmíněna být měla, protože má na výsledek zásadní vliv.
K hromadným insertům typicky dochází u importů, které se obvykle provádějí při co nejmenším zatížení stroje. Proto mi přišlo v pořádku spouštět kód na serveru bez další zátěže.
Za zjednodušení interpretace výsledků se omlouvám.
Jakub Vrána : zipi:
jojo, tohle bych snad, Jakube, radeji smazal :-)Martin:
InnoDB si vytvori vlastni PK, pokud v tabulce zadny neni. MyISAM ne. Takz pri vkladani do InnoDB se provadi operace s indexem. V prvnim pripade navic je nejspis nastaveny autocommit, takze transakcni rezije je spojena s kazdym insertem u InnoDB.Zipi to vystihl spravne. Jakube, co kdyz tomu opravdu nekdo uveri! A jeste pod timhle mit odkaz na "školení o výkonnosti webových aplikací"...
finc:
Musim se pripojit k diskutujicim. Takovy test je k nicemu.
I kdyz je pravda, ze MyISAM je rychlejsi pri praci s daty, neni to tak znacne, jak je videt z tveho testu.
Kde InnoDB skutecne pokulhava oproti MyISAM, jsou ruzne agregacni funkce. Znama to vec, kterou snad Falcon vyresi :)
Jinak, jak uz tady nekdo psal, je to o nastaveni. A takove "vytunene" my.cnf je casto pekna magie (pocet procesoru, predpoklada velikost DB, velikost RAM, rychlost disku, pocet threadu, atd atd atd....).
LLook:
Zrovna jsem u jednoho projektu zvolil InnoDB. Jednak kvůli již zmíněným transakcím, jednak kvůli výkonu.
Inserty a updaty se zas tak často neprovádí a většinou beztak jejich provedení zdrží buďto mě nebo crona a tak je pro mě důležitější rychlost selectů, protože ty zdržují mé uživatele.
No a v této kategorii prozměnu InnoDB docela dostává MyISAM, vizte http://www.mysqlperformanceblog.com/2007/…-benchmarks-part-1/
David Grudl:
Mimochodem - mnohanásobný INSERT lze významně zrychlit otevřením transakce i v SQLite.
David Grudl: Martin:
Tak zrovna pro mě je tenhle článek nesmírně zajímavý (i když z mého pohledu pozdě). Kdysi jsem dělal redakční systém a dost dlouho se divil, že se stránka generuje tak dlouho (cca 0,7s). Pak jsem postupným vypínáním funkcí zjistil, že to dělá ukládání logovacích informací a to ještě jen v případě, že jde o InnoDB. Po změně tabulky na MyISAM se rychlost generování stránky zmenšila na 0,1s! Takže tenhle článek je minimálně pro začátečníky oborovsky ušetřený čas... Díky.doser:
Musím říct, že innodb je při insertu velmi pomalé. Myslím, že v článku jde skutečně více o postřeh než o test. Koho z vás napadne, že změnou úložiště se změní rychlost v řádu stovek procent?
Mám zkušenosti z projektu, kdy při importu do db vkládám řádově milióny řádek (stovky MB dat), a musím říct, že zde je to opravdu propastný rozdíl...
Ale to je trochu extrém, nicméně i při běžném vkládání např. 100 řádek byl rozdíl znatelný (několik sekund). A to už je při načítání stránky poměrně nepříjemný čas...
Marťas:
Takže nějaké výsledné srovnání? Na co se hodí víc nebo nehodí dané typy? Popříadě nějaké návrhy v závislosti na výkonu stroje, velikosti DB ?Petr:
Článek a případné postřehy jsou velice užitečné děkuji.Jan Svoboda:
Dekuji predalaval jsem tabluku z MyISAM na innodb a mel jsem nad dni radove 100K dotazu update (synchronizace dat) a zabalit to do jedne trancakce to radove 10-50x zrychlilo ... DikyDiskuse je zrušena z důvodu spamu.
© 2005-2024 