Obnova MySQL databáze je pomalá

[artom]

Dobrý den,

chtěl bych se poradit se zkušenými, mám cca 1GB dump z MySQL db, když udělám restore db na novém stroji tak trvá celé asi 2hod, což mi přijde extrémně hodně, jde problém v konfiguraci MySQL serveru? Zatím mám default konfiguraci z Debianu. Úložístě pracuje normálně, žadné potíže se zápisem/čtení nevykazuje. Artom.

[Reklama]

[James_Scott]

Obnovení dat z dumpu - převádějí se textové SQL do binárních dat, takže podle složitosti DB a rychlosti disků/CPU to může být dlouhé. Pro rychlejší obnovu bych volil replikaci DB / snapshot FS pokud to umožňuje.

[Ondrej Nemecek]

Pokud je ten dump složený ze samostatných insertů (1 řádek = 1 insert), je to normální chování. Použijte při vytváření extended insert:

Kód: [Vybrat]

mysqldump --extended-insert=true (...)
Dále pomůže, pokud vypnete auto commit a kontrolu referenční integrity. Také můžete použít mysqlimport (nebo analogicky LOAD DATA INFILE).

[Bel Shamharoth]

Pokud je to komprimovaný dump, tak 2 hodiny jsou bohužel asi odpovídající. Extended insert je u mysqldumpu default bůh ví jak dlouho, tady problém nebude. S disky taky problém nebývá, ty se většinou flákají. Problém je, pokud to mysql není nastavené totálně blbě, s indexy, ale s tím se toho moc nenadělá. Můžete si dumpnout zvlášť schema, to si upravit odřezáním indexů z create table, pak zvlášť data a nakonec ručně indexy, to by možná mohlo něco urychlit (postgres to tak dělá, pokud vím).

Nic méně pro rozumně rychlou obnovu je lepší raw kopie.

Replikaci u MySQL dostatečně nedůvěřuji a stejně potřebujete raw kopii.

[Ondrej Nemecek]

PS: Že je extended jako default ještě automaticky neznamená, že ta záloha je extended. Nemít zálohu extended má smysl, pokud s tím dumpem chci ručně pracovat (sql je pak čitelnější a editory ho lépe zvládají). Takže bych se nedivil, pokud by ten dump extended nebyl. Jinak souhlas. Možná ještě že import mohou zabít nejen indexy ale i trigery, pokud se spouští pro každý insert.

[Reklama]

[dustin]

Mysqldump má parametr --disable-keys, nalévání to výrazně urychlí. Rovněž je výrazné urychlení textový formát s oddělenými tabulkami (parametr --tab), ale ten se pak musí umět nalít zpět (stačí trochu skriptování) a je potřeba si ohlídat charset.

Mysql replikace funguje OK, používá se běžně.

Pokud mohou být všechny tabulky innodb, asi nejrychlejší klonování za běhu umí innobackupex, má velice jednoduché používání a zatím nám vždy fungoval na 100%.

[artom]

Díky za nápovědu.

Je to komplikovanější, dostavám zálohu od cizí firmy, na jejich SQL server nemám přístup a ani nejsou moc ochodtní něco řešit, raw data určitě nedostanu a replikaci podle mě také nepořešíme, těch db je více co budu potřebovat, proto se ptám, nechci čekat 2 dvěhodiny u každé.

Mohu říct, že chci ten mysqldump udělat takovými a parametry jake si řeknu.

Co mě zajímá je proč to tak dlouho trvá, měl jsem i větší db,  třeba 10-15GB a obnova trvala cca do 30minut, prostě to jelo, v topu jsem viděl zatež, zde to téměr nejede, je to tím dumpem, respektive návrhem té konkrétni db(je asi dost prasácká, ale nemohu úplně soudit) nebo je to konfigurací mysql serveru(želeto je 4jádro, 32GB RAM, disky na RAIDu nebo kombinací všeho?

Zkusím ješte toto.
---
SET FOREIGN_KEY_CHECKS=0;
SET unique_checks=0;
SET AUTOCOMMIT=0;

SQL DUMP...

SET FOREIGN_KEY_CHECKS=1;
SET unique_checks=1;
COMMIT;
SET AUTOCOMMIT=1;
---
A také jsem se dočel, že je dobré po dobu importu zakázat logování slow-queries.
A toto, ale o tom moc nevím.
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M

Artom.

[Ondrej Nemecek]

Vylaďte si parametry dumpu u sebe na počítači (možná stačí testovat jen s částí databáze) a pak si zažádejte dumpovat se stejnými parametry. V průběhu importu sledujte vytížení CPU a IO - tím se můžete dozvědět, kde je úzké hrdlo.

[artom]

Vylaďte si parametry dumpu u sebe na počítači (možná stačí testovat jen s částí databáze) a pak si zažádejte dumpovat se stejnými parametry. V průběhu importu sledujte vytížení CPU a IO - tím se můžete dozvědět, kde je úzké hrdlo.

Máte pravdu je tam vysoký IO wait při té obnově, prakticky tak, že jedno jádro je více než 90% iowait, pak to občas přeskočí na jiné jádro, co to znamená?

[Ondřej Vaniš]

Jak již bylo výše napsáno zkusit import bez indexů. Pravděpodobně dopočet indexů způsobuje nadměrné zatížení disků.

[artom]

Jak již bylo výše napsáno zkusit import bez indexů. Pravděpodobně dopočet indexů způsobuje nadměrné zatížení disků.

dobre, mohu to udelat takto, pozadal jsem o oddeleny dump schema a data, ten mam, mám dva soubory,  nahraji schema a pak udelam "alter table tabulka disable keys", nahraji data a opet je povolim, takto je to mysleno, jen pro jistotu. diky jdu to otestovat. artom

[dustin]

SQL příkazy pro vypnutí indexů/kontroly ref. integrity umí do dumpu dávat rovnou mysqldump - již jsem uváděl parametry.

Pokud nemáš baterií zálohovaný HW raid, který umí velice rychlý sync, je na ne-SSD obrovský rozdíl v rychlosti  mezi

innodb_flush_log_at_trx_commit=1

a

innodb_flush_log_at_trx_commit=2

Samozřejmě výměnou za nižší bezpečnost, ale to při nalévání asi nevadí. Pro testovací servery se SW raidem jedeme jedině innodb_flush_log_at_trx_commit=2. A nebo tam dej slušnější SSD (máme dobré zkušenosti s cenově rozumným Intelem DC S3500) a budeš rychlostí úplně někde jinde.

[Bel Shamharoth]

SQL příkazy pro vypnutí indexů/kontroly ref. integrity umí do dumpu dávat rovnou mysqldump - již jsem uváděl parametry.

Referenční integritu ano, ale vypnout klíče umí bohužel jen MyISAM, který snad nepoužívají. Resp. ono to tam je a je to ignorované.

[dustin]

Aha, to jsem nevěděl, díky.

http://dbahire.com/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/ , tj. LOAD INFILE a paralelizace z txt dumpů. Ve skriptu ještě voláme

Kód: [Vybrat]

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;