Import velkých dat do MySQL

6. 10. 2009

Ještě něž jsem zahájil svou dovolenou, jsme při dokončování projektu narazili na výkonnostní problém při velkém importu dat do MySQL databáze. V našem případě se jednalo o cca 30 tisíc záznamů do tří tabulek navzájem provázaných cizími klíči. Úvodní verze importního algoritmu trvala cirka 50 minut, po dvou dnech jsme se dostali na jednotky minut. Nedalo mi to, a udělal jsem pár testů, které snaží tento problém rozkrýt do většího detailu, tak abych pro příště věděl, co a především jak významně ovlivňuje rychlost importu takto rozsáhlých dat.

Pro svůj test jsem použil pouze dvě tabulky provázané přes cizí klíč, první tabulka má navíc jeden unikátní index. V tabulkách je pouze minimum sloupců. Tyto dvě tabulky jsou vytvořeny jednou pro InnoDB a jednou pro MyISAM engine (pro MyISAM cizí klíč chybí, jelikož tento engine cizí klíče nepodporuje). Zde je jejich deklarace:


CREATE TABLE T_RECIPIENT_INNO(
id INTEGER NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
created DATETIME NOT NULL,
PRIMARY KEY (id),
UNIQUE UQ_RECIPIENT_EMAIL_INNO(email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE T_RECIPIENT_PROPS_INNO(
idProp INTEGER NOT NULL AUTO_INCREMENT,
idRcpt INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (idProp),
KEY (idRcpt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE T_RECIPIENT_PROPS_INNO ADD CONSTRAINT FK_T_RECIPIENT_PROPS_INNO
FOREIGN KEY (idRcpt) REFERENCES T_RECIPIENT_INNO (id)
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE T_RECIPIENT_ISAM(
id INTEGER NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
created DATETIME NOT NULL,
PRIMARY KEY (id),
UNIQUE UQ_RECIPIENT_EMAIL_ISAM(email)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE T_RECIPIENT_PROPS_ISAM(
idProp INTEGER NOT NULL AUTO_INCREMENT,
idRcpt INTEGER NOT NULL,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (idProp),
KEY (idRcpt)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Před každým testem se provede drop všech tabulek a jejich znovu vytvoření. Každý test se jinou strategií snaží vložit 100 tisíc provázaných řádků do obou tabulek (tj. 200 tisíc řádků celkem). Všechny testy používají jedinou konekci (JDBC session, která se nikdy nezavírá).

testNaiveInnoImport: provádí insert jednoho řádku po druhém bez použití transakcí (tj. autocommit = true)
testMultiRowInnoImportLongChunks: použití vícenásobného insertu (specialita MySQL) ve větších dávkách (po 10tisících záznamech v insertu) bez zakrytí transakcí
testMultiRowInnoImportShortChunks: použití vícenásobného insertu (specialita MySQL) v menších dávkách (po 2tisících záznamech v insertu) bez zakrytí transakcí
testMultiRowInnoImportLongChunksUnderTransaction: použití vícenásobného insertu (specialita MySQL) ve větších dávkách (po 10tisících záznamech v insertu) s použitím transakcí
testMultiRowInnoImportShortChunksUnderTransaction: použití vícenásobného insertu (specialita MySQL) v menších dávkách (po 2tisících záznamech v insertu) s použitím transakcí
testInnoImportUnderTransaction: zakrytí všech 200 tisíc insertů jedinou transakcí
testInnoImportUnderTransactionLongChunks: zakrytí větší dávky insertů (po 20tisících záznamech) transakcí
testInnoImportUnderTransactionSmallChunks: zakrytí menší dávky insertů (po 4tisících záznamech) transakcí

Dále jsem ještě vytvořil klony těchto testů s následujícími modifikacemi:

Locking testy: před zavoláním každého testu provede zamčení používaných tabulek pro WRITE a na konci testů je opět odemkne
Disable foreign key testy: před zavoláním každého testu nastaví proměnnou MySQL, která řídí kontrolu referenční integrity na false a na konci testů ji opět zapne
Disable unique key testy: před zavoláním každého testu nastaví proměnnou MySQL, která řídí kontrolu unique indexů na false a na konci testů ji opět zapne
Combied testy: kombinují všechny tři výše uvedené operace

Všechny tyto kombinace se volají zvlášť pro InnoDB a zvlášť pro MyISAM tabulky.

Výsledky testů

A nyní se již podívejme na statistiky výkonnosti jednotlivých testů:

[caption id="attachment_632" align="aligncenter" width="461" caption="Výsledky testů"][/caption]

Update k 7/10/09 V tabulce ještě není znázorněn poslední test, který jsem dopracoval na námět Lukáše Drbala (viz. komentáře), který testuje další funkci MySQL Load data infile. Import dat s využitím této funkce, trval:

MyIsam: 8.55s
InnoDB: 6.46s

Zdrojové kódy jsem aktualizoval, takže si funkci můžete vyzkoušet sami.

Závěry

MyISAM je v daném případě srovnatelně rychlý jak InnoDB pod transakcí
multi-row inserty zaznamenaly v případě MyISAM engine cca. 2.5x zrychlení, v případě InnoDB engine cca. 15x zrychlení oproti autocommit přístupu a 2x zrychlení oproti transakčnímu přístupu
uzamčení tabulek pro zápis znamenalo v případě MyISAM cca 20-25% zrychlení, v případě InnoDB nemělo prakticky vliv
vypnutí kontroly cizích a unikátních klíčů nemělo na rychlost importu v podstatě vliv pro oba enginy
zakrytí importu transakcí v případě InnoDB enginu znamenalo 8x zrychlení importu
využití funkce LOAD DATA INFILE je pro tyto účely nejoptimálnější - znamenalo v případě MyISAM zrychlední cca. 5.5x a v případě InnoDB cca. 42x zrychlení oproti autocommit přístupu a 5.5x zrychlení oproti transakčnímu přístupu

Z výše uvedeného vyplývají následující doporučení:

pokud je to možné použijte LOAD DATA INFILE funkci - zde můžete narazit na problém provázání cizích klíčů - nicméně kombinace s dodatečným selectem by stále mohla být poměrně efektivní
v libovolném případě preferujte multi-row inserty po pravidelných segmentech (velikost segmentu nehraje zase až tak velikou roli)
v případě, že nechcete použít multi-row inserty:
- v případě MyISAM si zkuste zamknout tabulky pro zápis
- v případě InnoDB vždy používejte transakce a segmentujte

Out of memory při dotazování rozsáhlého resultsetu

V kombinaci s rozsáhlými daty mi také docházelo k OutOfMemoryError. Prováděl jsem stránkování na úrovni ResultSetů - tj. skip řádků na první pozici požadované stránky, přečtení požadovaného počtu záznamů a uzavření result setu. Nicméně, zdá se, že MySQL JDBC ovladač se defaultně chová tak, že vždy načítá kompletní obsah result setů do paměti. Viz. výňatek z oficiální dokumentace:

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

Tím pádem jsem se už někde kolem 20 tisíc záznamech v tabulce dostával při 256MB paměti na Javu k OutOfMemoryError.

V dokumentaci je uvedeno i řešení tohoto problému na úrovni JDBC. Já jsem se dal jednodušší cestou - uvedením specifické MySQL klauzule LIMIT ve vlastním SQL příkazu.

Svět je plný překvapení.

Odkazy na webu

Zdrojové kódy ke stažení

Zdrojové soubory IntelliJ Idea projekt se zdrojovými soubory

Poznámka na závěr: Nepovažuji se za žádného MySQL databázového specialistu, takže vám budu vděčný, pokud moje závěry něčím doplníte, dovysvětlíte nebo nečím vyvrátíte.