103

I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:

mysql -uroot dbname < dbname.sql

It is taking too long. There is a table with around 300 million rows, it's gotten to 1.5 million in around 3 hours. So, it seems that the whole thing would take 600 hours (that's 24 days) and is impractical. So my question is, is there a faster way to do this?

Further Info/Findings

  1. The tables are all InnoDB and there are no foreign keys defined. There are, however, many indexes.
  2. I do not have access to the original server and DB so I cannot make a new back up or do a "hot" copy etc.
  3. Setting innodb_flush_log_at_trx_commit = 2 as suggested here seems to make no (clearly visible/exponential) improvement.
  4. Server stats during the import (from MySQL Workbench): https://imgflip.com/gif/ed0c8.
  5. MySQL version is 5.6.20 community.
  6. innodb_buffer_pool_size = 16M and innodb_log_buffer_size = 8M. Do I need to increase these?
2
  • @Bert the server has 8 GB of RAM most of which is just unused. Can't add more storage either. How would that help? Is it really the write operations that are so slow? – SBhojani Nov 19 '14 at 20:53
  • @ChrisS no, the CPU usage is 3 to 4%. I'm not sure what the bottleneck is. I'm thinking it's the indexes. How would one find/confirm the bottleneck? – SBhojani Nov 19 '14 at 21:28
127

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB

InnoDB Architecture

You definitely need to change the following

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Why these settings ?

Restart mysql like this

service mysql restart --innodb-doublewrite=0

This disables the InnoDB Double Write Buffer

Import your data. When done, restart mysql normally

service mysql restart

This reenables the InnoDB Double Write Buffer

Give it a Try !!!

SIDE NOTE : You should upgrade to 5.6.21 for latest security patches.

4
  • 1
    I made a linux bash script for it, lowered some values to work inside vagrant with lower memory gist.github.com/OZZlE/57d550c3cc1c1ff17481e465e4f6d674 – OZZIE Dec 14 '18 at 9:12
  • @MonsterMMORPG innodb_write_io_threads came into existence starting in MySQL 5.1.38. If you are using MySQL 5.0 up to 5.1.37, that's why you don't see it. See dev.mysql.com/doc/refman/8.0/en/… – RolandoMySQLDBA Feb 14 '20 at 15:08
  • @MonsterMMORPG Just for completeness, Percona had that option in Percona Server 5.0 in its InnoDB/XtraDB before MySQL made it available in 5.1.38 by means of the InnoDB Plugin.. – RolandoMySQLDBA Jun 20 '20 at 14:27
  • It didn't work to add --innodb-doublewrite=0 to service start for me, I had to run the server manually with "/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid --skip-innodb-doublewrite" – Kevin Mar 4 at 17:05
15

Do you really need the entire database to be restored? If you don't, my 2c:

You can extract specific tables to do your restore on "chunks". Something like this:

zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql

I did it once and it took like 10 minutes to extract the table I needed - my full restore took 13~14 hours, with a 35GB (gziped) dump.

The /pattern/,/pattern/p with the -n parameter makes a slice "between the patterns" - including them.

Anyways, to restore the 35GB I used an AWS EC2 machine (c3.8xlarge), installed Percona via yum (Centos) and just added/changed the following lines on my.cnf:

max_allowed_packet=256M
wait_timeout=30000

I think the numbers are way too high, but worked for my setup.

1
  • no parameter name as wait_timeout – MonsterMMORPG Feb 14 '20 at 8:39
7

The fastest way to import your database is to copy the ( .frm, .MYD, .MYI ) files if MyISAM, directly to the /var/lib/mysql/"database name".

Otherwise you can try : mysql > use database_name; \. /path/to/file.sql

Thats another way to import your data.

1
  • This is not called "import" and it is not safe between MySQL versions. Don't do this until you go with all other options. – tanaydin Aug 4 '20 at 9:15
3

One way to help speed up the import is to lock the table while importing. Use the --add-locks option to mysqldump.

mysqldump --add-drop-table --add-locks --database db > db.sql

or you could turn on some useful parameters with --opt this turns on a bunch of useful things for the dump.

mysqldump --opt --database db > db.sql

If you have another storage device on the server, then use that - copying from one device to another is a way to speed up transfers.

You can also filter out tables that are not required with --ignore-table

0

This is how to set RolandoMySQLDBA configuration in linux:

  1. Open mysql conf file in your favourite text editor:

    sudo vi /etc/mysql/my.conf

  2. Type the following configuration at the end of the file:

    
    innodb_log_buffer_size = 256M
    innodb_log_file_size = 1G
    innodb_write_io_threads = 16
    innodb_flush_log_at_trx_commit = 0
    innodb_doublewrite = 0
    
  3. Save the file and run this command in mysql:

    set global innodb_fast_shutdown = 0

    you can see the new configuration with @@ followed by command in mysql:

    select @@innodb_buffer_pool_size;

    similarly with other commands

  4. Restart mysql:

    service mysql restart

  5. Restore your database state:

    mysql -u username -p database_name < /path/to/file.sql

I had to import 5.8GB of data which took me 40min with the pc configuration of inter corei5 2.5GHZ and 16gb ram

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