Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You can also desactivate the binary logging during the load of your dump –  Cédric PEINTRE Feb 20 '12 at 14:54
add comment

6 Answers

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

  1. Use Extended inserts in dumps.
  2. Dump with --tab format so you can use mysqlimport, which is faster than mysql < dumpfile.
  3. Import with multiple threads, one for each table.
  4. 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.
  5. Turn off foreign key checks and turn on auto-commit.
  6. 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..

share|improve this answer
add comment

In addition to Abdul's answer, I'd like to stress the importance of the --disable-keys option, which turns off keys until all the data is loaded for a table. This option is enabled as part of the --opt toggle, which is enabled by default, but thought it important to point out.

If you do not skip keys during the inserts, then each row inserted will rebuild the index. An extremely slow process.

share|improve this answer
    
is --disable-keys part of the mysqldump? or the reload? –  Pat Farrell Feb 20 '12 at 18:53
    
it will be added in the dump file –  Derek Downey Feb 20 '12 at 19:40
    
thanks, I'll try --opt next time I do the dump. –  Pat Farrell Feb 20 '12 at 21:29
    
--opt is on by default –  jberryman Nov 2 '12 at 19:26
add comment

I have been dealing a lot with this lately. You can definitely improve import performance by doing the imports in parallel. Most of the slowdown is I/O based, but you can still get a 40% improvement by dumping into tables and then importing them say 4 at a time.

You can do this with xargs like this:

ls *.sql -1c | xargs -P4 -I tbl_name sh -c "mysql --user=username --password database < tbl_name"

having the files gzipped before pushing them to mysql doesn't slow anything down mostly because of the lowered I/O. My tables were compressed up to about 10:1, so it saves a lot of disk space.

I've found that on 4 core machines, using 4 processes is optimal, although only marginally better than using 3. If you have SSDs or a fast RAID, you will likely scale better.

Some other things to note. If you have 4k sector drives, make sure you have key_cache_block_size=4096 and myisam_block_size=4K.

If you are using MyISAM tables, set the myisam_repair_threads = 2 or higher. This will allow your extra cores to help rebuild indexes.

Make sure you aren't swapping at all. If you are, reduce the size of the innodb_buffer_pool_size.

I think I got some speedup with innnodb by these options too:

innodb_flush_method= O_DIRECT (LINUX ONLY)
innodb_flush_log_at_commit = 0
innodb_doublewrite=0
innodb_support_xa=0
innodb_checksums=0

(the last three I didn't test extensively - I think I found them as suggestions on the internets.) Note that the innodb_flush_log_at_commit=0 can result in corruption with mysql crashing or power going out.

share|improve this answer
    
Greg, welcome to the site and thank you for your answer. Could you provide some sources or reasoning for your suggestions on *_block_size and myisam_repair_threads? Also, not sure we should offer advice to tune variables based on 'suggestions from the internets' :) –  Derek Downey Feb 23 '12 at 15:28
add comment

Try SQLyog which has export as ZIP option for efficient storage as well as you can select "BULK INSERT" option while taking backup which can improve performance. Also it has an option to import a .sql file, Try it out.

share|improve this answer
add comment

If you mainly have MyISAM tables, you should increase the bulk insert buffer. Here is what the MySQL Documentation says on setting bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

There are two things you need to do

1) Add it to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

2) Set the global value for it

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

If you do not have the privilege to set bulk_insert_buffer_size globally, then do this

service mysql restart

Of course, this is not for InnoDB.

From another angle, whether the tables are InnoDB or MyISAM, if the indexes are larger that than the table, you may have too many indexes. I usually guestimate that a reload of a MyISAM mysqldump should take 3 times as long as the mysqldump took to make. I also guestimate that a reload of a InnoDB mysqldump should take 4 times as long as the mysqldump took to make.

If you are exceeding the 4:1 ratio for reloading a mysqldump, you definitely have one of two problems:

  • too many indexes
  • indexes just too large due to large columns

You can measure the size of your data by storage engine with this:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

See if the indexes are alsmost as big as the data or even bigger

You may also consider disabling binary logging like this:

echo "SET SQL_LOG_BIN=0;" > footable.sql
mysqldump --databases foo >> footable.sql

before reloading the script

share|improve this answer
add comment

If you bypass the filesystem altogether and just pipe the output of mysqldump directly into a MySQL process you should see noticeable performance improvements. How much ultimately depends on the type of disk drive you are using but I rarely use dump files anymore regardless of database size for this reason alone.

mysqldump -uxxx -pxxx -hxxx --single-transaction --routines --databases dbname | mysql -uyyy -pyyy -hyyy
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.