How to improve performance of MySQL dump restore

Question

Many of us who are working on their home or pet projects and who use databases for storing structured data may encounter performance issues when trying to dump/restore data. It can annoying just to sit and wait for another dump restore operation for dozens of minutes or even for hours.

I have quite typical machine specs - 4 core i5 7300, 8 Gb RAM, quite fast M2 drive and Windows 10/MySQL 5.7.

The problem was that trying to restore ~4.5Gb file it took more than 4 hours. That was ridiculous and I wondered if mysqld process isn't using even a half of system resources - CPU/Memory/Disk I/O

Generally speaking, this post relates to some kind of summary of related issues including credits to many other posts which I put below

Answer 1

Presuming you have MySQL 8.0.21 and above. Note the following is very risky, so only do the following if you are doing a data load to a clean database instance not just schema.

• disable redo logging (which also disables double write buffering) this is the important setting

• disable foreign key checks

• set innodb_file_per_table to OFF DBA question and why I chose OFF when I was doing my tests (emphasis mine)

  So as result of this bug – if you’re running innodb_file_per_table you will have a microstalls when tables are dropped and dropping a lot of tables in a batch can cause serve performance problems. The stalls can take more than a second for very large buffer pool.

In addition do the following from @Anton's answer

• innodb_flush_log_at_trx_commit to 0

From my actual tests to do a restore and anonymization post processing of data. The disabling of the redo logging reduced the time from nearly 2 hours to under 30 minutes.

To set it as part of the restore and anonymization sequence, here are the key parts of the restore script

cat dump.sql | mysql **username/password** \
  --init-command='ALTER INSTANCE DISABLE INNODB REDO_LOG; SET SESSION FOREIGN_KEY_CHECKS=0;'

cat anonymize.sql | mysql **username/password** \
  --init-command='ALTER INSTANCE DISABLE INNODB REDO_LOG; SET SESSION FOREIGN_KEY_CHECKS=0;'

Answer 2

I performed a number of experiments with MySQL parameters for better dump restore operations

+--------------------------------+---------+---------+-----------------------+---------------------+
|           Parameter            | Default | Changed | Performance (minutes) | Perfomance gain (%) |
+--------------------------------+---------+---------+-----------------------+---------------------+
| All default                    | -       | -       | 259 min               | -                   |
| innodb_buffer_pool_size        | 8M      | 4G      | 32 min                | +709%               |
| innodb_buffer_pool_size        | 4G      | 6G      | 32 min                | ~0%                 |
| innodb_log_file_size           | 48M     | 1G      | 11 min                | +190%               |
| innodb_log_file_size           | 1G      | 2G      | 10 min                | +10%                |
| max_allowed_packet             | 4M      | 128M    | 10 min                | ~0%                 |
| innodb_flush_log_at_trx_commit | 1       | 0       | 9 min 25 sec          | +5%                 |
| innodb_thread_concurrency      | 9       | 0       | 9 min 27 sec          | ~0%                 |
| innodb_double_write            | -       | off     | 8 min 5 sec           | +18%                |
+--------------------------------+---------+---------+-----------------------+---------------------+

Summary (for best dump restore performance):

1. Set innodb_buffer_pool_size to half of RAM
2. Set innodb_log_file_size to 1G
3. Set innodb_flush_log_at_trx_commit to 0
4. Disabling innodb_double_write recommended only for fastest performance, it should be enabled on production. I also found, that changing another related parameter innodb_flush_method didn't change performance. But this can be an issue of Windows platform.
5. If you have complex structure with a lot of foreign keys for example, you can try Bulk Data Loading for InnoDB Tables tricks, link is listed at bottom of page
6. As you can see, I tried to increase CPU utilization by setting innodb_thread_concurrency to 0 (and also setting innodb_read_io_threads to maximum of 64) but results didn't change - it seems that mysqld process is already quite efficient for multi-core environment.
7. Restoring only data (without table structure) also didn't affect performance

I also changed a number of other parameters, but those above are most relevant ones for dump restore operation so far.

It may seem obvious, but novice question can be - where I can find and set those settings?

In Windows, my.ini file is located at ProgramData/MySQL/MySQL Server <version>/my.ini. You won't find some settings there (like innodb_double_write) - it's ok, just add to the end of the file.

The best way to change settings is to use MySQL Workbench (Server > Options file > InnoDB).

I pay my credits to following posts (and a lot of similar ones), which I found very useful:

https://www.percona.com/blog/2018/02/22/restore-mysql-logical-backup-maximum-speed/ https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/ https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html https://dba.stackexchange.com/questions/86636/when-is-it-safe-to-disable-innodb-doublewrite-buffering https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html