Mysql – slow load speed of data from thesqldump

MySQLmysqldump

I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe 20 minutes. It generates about 15GB of data. The largest dumped files are in the 2GB range.

When I load the data into MySQL on another box, a six-core, 8GB machine, it takes forever. Easily 12 clock hours or more.

I'm just running the mysql client to load the file, i.e.

mysql database < footable.sql

directly with the file directly out of mysqldump

mysqldump database foo > footable.sql

Clearly I am doing something wrong. Where do I start so it can finish in a reasonable time?

I'm not using any switches on either the dump or the load.

Best Answer

Take these some points in your consideration they may help you in case of generating the dump and restoring it.

Use Extended inserts in dumps.
Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
Import with multiple threads, one for each table.
Use a different database engine if possible. importing into a heavily transactional engine like innodb is awfully slow. Inserting into a non-transactional engine like MyISAM is much much faster.
Turn off foreign key checks and turn on auto-commit.
If you are importing to innodb the single most effective thing you can do is to put innodb_flush_log_at_trx_commit = 2 in your my.cnf, temporarily while the import is running. you can put it back to 1 if you need ACID

Give it a try..

Related Solutions

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps

MySQL – Why DROP DATABASE Takes So Long

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password: 
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).

Best Answer

Related Solutions

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

MySQL – Why DROP DATABASE Takes So Long

Related Question