1

I have a 500G mysqldump to load and it seems taking forever. My server is with Intel i9-10920X @ 3.5GHz and 128GB RAM. The database is set up in a HDD harddisk. I have the mysql (Ver 8.0.29-0ubuntu0.20.04.3) setup as follows:

innodb_buffer_pool_size = 32G
innodb_log_buffer_size = 512M
innodb_log_file_size = 2G
innodb_write_io_threads = 32
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

I further set the following before source .sql file:

SET global max_allowed_packet=1000000000;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

Right now it read 20k rows per second. How can I optimize further? Thanks!

Improve this question
1
  • Your last 3 SET's above were only for the SESSION that was active. Since you have 128G, wondering why you did not config innnodb_buffer_pool_size=64G and innodb_change_buffer_max_size=50 for 50% to improve load rate per second?
    – Wilson Hauck
    Jun 28, 2022 at 13:51

2 Answers 2

Reset to default
2

How long do you mean when you say forever? Hours? Days? I wouldn't be surprised if it takes days.

Importing an .sql dump file is notoriously time-consuming. It is bound to be single-threaded, so you can only use one CPU core, no matter what type of CPU you have.

The I/O system is important. As you fill the 2G InnoDB log file, the dirty pages from the buffer pool must flush to disk. Using a fast directly-attached disk system like NVMe can help. Using a striping RAID-0 or RAID-10 can help. Using remote storage (for example AWS EBS) is bad for latency.

Using minimal indexes in your tables can help. Think about every row write being multiplied by the number of indexes in the table. The table itself is stored as a clustered index, and that's one write. Then each secondary index is an additional write. Unique indexes must be synchronous writes (though if you set unique_checks=off, this is relaxed). Non-unique indexes can be deferred by change buffering, but they do need to get merged into the tablespace eventually.

Instead of loading a .sql file, it could be much faster to use LOAD DATA [LOCAL] INFILE. See my presentation Load Data Fast!. But you can't use that with .sql dump files. It's for CSV files and similar.

Loading multiple tables in parallel using LOAD DATA [LOCAL] INFILE is probably the best you can get for bulk loading large data sets. This is the idea behind the MySQL Shell Parallel Table Import Utility. See an evaluation of parallel table import here:

To get any faster, you'd have to resort to physical backups instead of dump files (i.e. Percona XtraBackup), or filesystem snapshots.

Improve this answer
2
  • Thanks so much! I wonder how to find out the bottleneck? I have another .sql to import that inserts one row per update. And it gets very slow...
    – cccfran
    Jul 1, 2022 at 15:37
  • Yes, importing large SQL scripts is always quite slow. It has to parse and optimize each SQL query, then write to the table. Multiple writes by the number of indexes it has to update. This seems quite slow when you're waiting for millions of them to complete. The bottleneck is that you're writing to an ACID database.
    – Bill Karwin
    Jul 1, 2022 at 15:59
0

  1. This file is still relatively large for mysql. It is recommended to split the file.

  2. Turn on automatic submission or submit multiple times in batches.

3.innodb_buffer_pool is set larger, and the log cache is also set larger.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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