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ódMyISAMInnoDB
<?php
for ($i=0; $i < 1000; $i++) {
    mysql_query("INSERT INTO tab VALUES ($i)");
}
?>
0.082 s26.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 s0.126 s
<?php
$values = array();
for ($i=0; $i < 1000; $i++) {
    $values[] = "($i)";
}
mysql_query("INSERT INTO tab VALUES " . implode(", ", $values));
?>
0.004 s0.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í.

Jakub Vrána, Řešení problému, 16.5.2008, diskuse: 15 (nové: 0)

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

ikona 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

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čí.

ikona Jakub Vrána OpenID:

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.

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/

ikona David Grudl:

Mimochodem - mnohanásobný INSERT lze významně zrychlit otevřením transakce i v SQLite.

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 ... Diky

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.