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

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: Reakce na: finc

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
avatar © 2005-2021 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.