130

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?
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
SBhojani
  • 1,403
  • 3
  • 10
  • 4
  • @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? –  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? –  Nov 19 '14 at 21:28
  • 1
    A 32GB database dump cannot really be considered to be "huge". I'm trying to find information about importing multi-terrabyte databases, and keep finding questions about databases I would consider to be quite small. – user1751825 Nov 15 '21 at 02:47

5 Answers5

171

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.

T.Todua
  • 184
  • 2
  • 14
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 1
    I made a linux bash script for it, lowered some values to work inside vagrant with lower memory https://gist.github.com/OZZlE/57d550c3cc1c1ff17481e465e4f6d674 – OZZIE Dec 14 '18 at 09: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 https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_write_io_threads – 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
  • 1
    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 04 '21 at 17:05
  • It simply works... – Rajat Jain Aug 30 '21 at 18:02
  • 2
    Without: Query OK, 40688878 rows affected, 0 warnings (2 hours 39 min 30.84 sec) With: Query OK, 40688878 rows affected, 0 warnings (8 min 3.49 sec) - local csv import on 4c 16gb SSD VM. – Marco Mar 17 '22 at 20:09
  • you can speed it up further by starting mysqld with eatmydata, eg eatmydata mysqld --innodb-doublewrite=0 --innodb-flush-method=fsync , benchmarks show a nearly 60% performance improvement on the MySQL benchmark testsuite: https://github.com/stewartsmith/libeatmydata#performance-improvements – hanshenrik Jul 12 '22 at 22:11
  • Well, this did exactly nothing on an AmazonLinux2 server with mysql 5.7.41 - I had to use less ram, but essentially the same otherwise. A 22min process finished in 22 minutes again. – Steve Horvath Nov 07 '23 at 07:31
17

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.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Bruno J. Araujo
  • 271
  • 2
  • 3
6

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

auspicious99
  • 105
  • 1
  • 8
pgee70
  • 171
  • 1
  • 5
6

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.

Alex
  • 85
  • 1
  • 5
    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 04 '20 at 09:15
2

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:

    [mysqld]
    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
    innodb_doublewrite = 0

  1. 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

  2. Restart mysql:

    service mysql restart

  3. 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

Ahtisham
  • 151
  • 4