Import velkých dat do MySQL
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:
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ů:
Výsledky testů
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
- Dokumentace MySQL hovořící o strategiích pro import rozsáhlých dat
- MySQL dokumentace – vypnutí kontroly cizích klíčů
- Tipy pro optimalizaci práce s InnoDB tabulkamy v MySQL
- Zdrojový článek, který mě navedl na řešení OOM v případě rozsáhlých ResultSetů
- Oficiální dokumentace MySQL JDBC ovladače
Zdrojové kódy ke stažení
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.
Související články:
- MySQL nebezpečí průtokových tabulek, zamyšlení nad insert into … select from
- Jak nainstalovat MySQL server na Windows Vista
- MySQL temporary tables inside Transaction
and the magic of implicit commit
(3 hlasů, průměrně: 5.00 z 5)
Loading ...
Imho nejrychlejsi moznosti pro mysql, je Load data in file – http://dev.mysql.com/doc/refman/5.0/en/load-data.html
Bylo by mozne pripravit soubory pro obe tabulky a pridat je do testu?
Asi by to neměl být problém. My jsme řešili úlohu importu dat z Excelu a šli jsme nejjednodušší možnou cestou. Navíc jsme při importu potřebovali aplikovat i nějakou logiku, která by při Load data in file nebylo možné.
Tak jsem dopsal poslední test – skutečně funkce Load data infile vychází ve výkonnostních statistikách nejlépe. Když jsem nad tím přemýšlel, asi by bylo možné tu naši logiku implementovat v předstihu a do souboru ukládat už vypočtená data. Provázání klíčů by se dalo řešit dodatečnými selecty, ve kterých by se dotáhly přidělené primární klíče pro importované záznamy (do tabulky totiž mohou v našem případě konkurentně přistupovat i další uživatelé, takže fixní hodnoty primárních klíčů nepřipadají v úvahu). Rozhodně by to asi za tu námahu stálo, kdybychom tahle čísla věděli už na začátku. Každopádně jsme teď chytřejší.
Lukáši, díky za komentář.
On je to vždy kompromis : )
Osobně jsem LOAD DATA IN FILE používal pouze při importech do jedné tabulky, nebo když těch dat bylo opravdu hodně, pak už se vynaložené úsilí při přípravě dat vyplatí.
Faktem je, že když jsem si cvičně implementoval metodu, která LOAD DATA INFILE využívá, tak si dokážu docela dobře představit i produkční využití. Ono to zase tak pracné není, jak jsem si původně myslel.
Naxem jsem nepochopil jesti tam cpes data z Javy nebo z SQL-souboru. Pokud ze souboru, taxe divim ze se ti ho chtelo vytvaret (mnohem vic prace nez pro LOAD DATA INFILE), pokud z Javy taxe divim ze nepises o pouziti PreparedStatement-s ci o executeBatch.
Super, zes udelal takovyhle prehled, ale s necim moc nesouhlasim:
“preferujte MyISAM před InnoDB” – MyISAM je stary netransakcni srot. Sice rychly, ale kdyz se rozsype, tak je vymalovano. Kdyz zjistis ze transakce ci jiny ficurky vznikly po roce nula prece jen potrebujes, pak muzes nejen pracne presypavat data, ale mas zadelano na hafo problemu. Rekl bych to naopak: Kdyz potrebujes rychlost, jen rychlost a to extemne moc, pak MyISAM.
Me navic Tvoje vysledky pro InnoDB prijdou zhruba stejny jako pro MyISAM: Kdyz se to dela dobre, pak oboje je pod 20s; mozna ale spatne vidim, uz je dost pozde.
Pravda je, že InnoDB v transakci dosahuje podobných výsledků jako MyISAM – taky jsem do těch výsledků čučel pozdě v noci. Tj. možná to s tou MyISAM není zase až o tolik výhodnější jak s InnoDB.
Data tam cpu z Javy přes Springovou podporu executeBatch – tj. asi nejrychlejší způsob, který jsem dokázal vymyslet. Pouze pro otestování LOAD DATA INFILE jsem si vytvořil externí TXT file, který jsem potom funkci předhodil (tam to snad ani nijak jinak nejde). Nejlíp když se koukneš rovnou do testovacího kódu.
Upravil jsem text článku podle toho, na co jsi Maaartine upozorňoval – skutečně ta MyISAM nevychází o moc líp než InnoDB. Já jsem původně porovnával časy na všechny testy ve třídě, jenže tam se hrozně projevila neefektivita InnoDB v autocommit režimu, z čehož jsem vyvodil špatný závěr. Dík za upozornění – kupodivu tedy MyISAM není výrazně rychlejší a to zmiňované doporučení jsem odstranil. Prostě víc očí víc vidí.
Zdá se, že tvrzení, že MyISAM je rychlejší než InnoDB je hodně zastaralá chiméra:
http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
Někde jsem zaspal dobu.
Par pripominek:
Import velkych dat neni o stotisicich ale milionech.
Clanek neni o rychlosti importu, ale rychlosti insertu.
Import dat pres inserty je sebevrazda.
Kdyz uz importuju pres inserty, tak si zavislosti behem operace vypinam. Pokud se chci dozvedet o nekonzistenci dat – tak je to dobre vyresit mimo import – pred nebo potom.
Ponechme slovíčkaření stranou. Typický usecase, který řešíme zas a znovu je ruční import dat z externí uživatelské DB, kterou má zákazník vyexportovanou ve formě CSV nebo Excelu. On demand import, který si zákazník dělá sám, typicky neobsahuje milióny záznamů (ještě jsem to ve své praxi neviděl), ale řádově desetitisíce nebo stotisíce záznamů (jen formát XLS množství výrazně omezuje s omezením 65 tis. záznamů na jeden list). Tím že je to on demand, musí bý v aplikaci rutina, která import provádí a tu píšeme pomocí insertů přes JDBC.
Máš pravdu, že pokud bych měl import o 10 mil. záznamech, tak bych to řešil specifickým importem ze strany dodavatele přímo nástroji, které se k DB dodávají. Tenhle problém tu ale neřeším.
Btw. kupodivu jsem nezaznamenal výraznou úsporu času vypnutím constraint.
Zdravím.
Technická pozn.: MS Excel 2007 už umí milion řádků.