mysql database inladen traag

[Verwijderd]

Ik ben met mysql de laaste weken aan de gang.

Wij hebben een suse Linux server met Mysql 4.0.12 met daarop 13 databases. Deze databases wil ik overzetten naar een debian sarge server met daarop mysql 4.0.24.

Op de suse server gaf ik het commando:

code:

mysqldump --databases -u username -p database1 database2 database3 database4 database5 database6 database7 database8 database9 database10 database11 | bzip2 > /md0/mysql-backup/databasebackup.bz2

Vervolgende het database backup bestand naar de debian server verplaatst. Ik heb diverse mysqldump variaties geprobeert om de database hier in de mysql store te laden. Maar geen enkele werkte.

Na de mysql reference manual te hebben gelezen heb ik het volgende gedaan. database bestand met bzip2 uitgepakt tot een database.sql bestand en vervolgens ingelezen met het commando:

code:

mysql < xlfoodbackup.sql

Dit gaat eigenlijk allemaal prima. Het gaat alleen bagger traag. De backup met mysqldump nam een half uur in beslag. De restore op de tweede server nam bijna twee dagen in beslag!

Is de methode die ik gebruik nu eigenlijk niet de juiste? (Het staat wel in de mysql reference manual)
Het lijkt mij niet normaal dat er zo'n verschil zit tussen backuppen en restoren van een database.

Heeft iemand tips hoe dit beter te doen?

[DJ Buzzz]

Ik zou zeggen, kijk de man page van mysqldump eens door. Je kunt sowieso wel op een makkelijke manier alle databases in 1 keer doen met --all-databases

Verder zijn er nog de --quick optie die afhankelijk van de grootte van je tabellen het dumpen sneller kan laten verlopen en de --extended-insert die het importeren een stuk sneller maken. Verder zou je eens moeten kijken naar je table type (MyISAM / InnoDB), afhankelijk daarvan kunnen bepaalde opties ook een erg grote invloed hebben.

P.S. Debian Etch is ook net uit, als die sarge machine een net nieuwe installatie heeft is het misschien handig om te upgraden, zodat je langer security support e.d. hebt vanuit Debian.

[Verwijderd]

Ik zal de mysqldump manual eens doorlezen.

Ik heb debian Etch geprobeerd maar deze bevat alleen mysql 4.1 en 5.0. Onze client applicatie kan hiertegen niet authentificeren ook niet als old_passwords aanstaat.

Binnenkort komt er een nieuwe versie uit die dit wel moet kunnen.

De meeste tables zijn innoDB maar enkelen ook MyISAM (o.a. opgeslagen printopdrachten)

[ Voor 13% gewijzigd door Verwijderd op 22-04-2007 15:46 ]

[DJ Buzzz]

Bij InnoDB zou je ook even naar de innodb_flush_log_at_trx_commit optie kunnen kijken. Standaard staat deze zo ingesteld dat elke commit op je database wacht op een synchronious write naar je filesystem, iets dat voor betrouwbaar wenselijk kan zijn, maar zeker bij een grote import een flinke performance killer is.

[PowerSp00n]

Hoe groot zijn die databases en/of dump? En wat voor server hebben we het over? 2 Dagen is wel erg lang.

[Verwijderd]

De Server en de grote van de databases zijn beide niet spannend.

De server is een Dell poweredge 430sc met Pentium 4 2,4 gigahertz
36 Gigabyte scsi hardeschijf
1024 MB ram
Ingebouwde tapedrive.

De databases schomelen tussen de 1,5 en 2,2 Gigabyte.

Ik denk dat het te maken heeft met de innodb_flush_log_at_trx_commit die Dj Buzz net noemde. Je hoort tijdens een restore de hardeshijf ook niet flink te keer gaan ofzo. Je hoort hem heel traag blokje voor blokje wegschrijven.

[ Voor 31% gewijzigd door Verwijderd op 22-04-2007 16:52 ]

[JasperE]

Gebruik je fulltext indexes? Als ik me niet vergis heb ik ooit gelezen dat het tijd kan schelen als je -eerst- de tabel zonder fulltext index inlaad en dan vervolgens de fulltext index toevoegt.
Dan hoeft de index niet bij iedere rij insert geupdate te worden.

[Verwijderd]

JasperE schreef op zondag 22 april 2007 @ 16:55:
Gebruik je fulltext indexes?

Hoe kan ik zien of ik dat gebruik?

[decramy]

code:

mysql> describe domain;
+-------------+--------------+------+-----+---------------------+-------+
| Field       | Type         | Null | Key | Default             | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| domain      | varchar(255) | NO   | PRI |                     |       | 
| description | varchar(255) | NO   | MUL |                     |       | 
| aliases     | int(10)      | NO   |     | 0                   |       | 
| mailboxes   | int(10)      | NO   |     | 0                   |       | 
| maxquota    | int(10)      | NO   |     | 0                   |       | 
| transport   | varchar(255) | YES  |     | NULL                |       | 
| backupmx    | tinyint(1)   | NO   |     | 0                   |       | 
| created     | datetime     | NO   |     | 0000-00-00 00:00:00 |       | 
| modified    | datetime     | NO   |     | 0000-00-00 00:00:00 |       | 
| active      | tinyint(1)   | NO   |     | 1                   |       | 
+-------------+--------------+------+-----+---------------------+-------+
10 rows in set (0.00 sec)

mysql>

Descrition heeft een fulltext index, gezien 'MUL'

[DJ Buzzz]

Je kunt sowieso wel tijdens het importeren innodb_flush_log_at_trx_commit = 0 instellen, daarna kun je dat altijd weer aanpassen. Welke waarde je nodig hebt daarvoor hangt verder vooral af van de vereiste betrouwbaarheid van de data, betrouwbaardheid van je hardware (RAID / BBU e.d.) en algemene systeemstabiliteit. Naast 0 (geen single writes, geen flusing) en 1 (single writes, flush), heb je ook nog 2 als mogelijkheid waarbij wel alle commits atomair naar schijf worden geschreven, maar er geen sync() naar je hardware wordt gedaan.

Verder zou ik ook nog zeker even controleren of je extended-inserts gebruikt, die inserten alle data in 1 keer in een tabel. Bij een tabel met veel data gaat dit in combinatie met innodb_flush_log_at_trx_commit erg hard, 1000 records in 1 keer of los per records kan best wel eens voor die paar dagen import tijd zorgen...

[Verwijderd]

De optie innodb_flush_log_at_trx_commit was het inderdaad.

Het restoren gaat nu in een half uur. Voor operationeel gebruik lees ik in de handleiding inderdaad dat je beter een andere optie kan gebruiken maar voor restoren is dit inderdaad ideaal.

Het verklaart ook waarom de database xlfprint0041 wel snel inporteerde. Dit was namelijk een myisam database.