Is there a way to improve the performance of MySQL imports that slow down over time?

Question

I have a huge mysqldump file (~700GB) and I want to restore it on another server. For the first GB, the import speed is pretty fast, around MB/s, but after a few GB of data, the speed drops to between 50kb/s and 200kb/s.

The parameters of mysqldump are --skip-comments --no-create-info --no-autocommit --quick --extended-insert --insert-ignore --compress, so it has been completed Made some speed improvements.

The import is done via pv /file.sql | mysql -u user DB

I also ran mysqltuner and improved some of its settings.

I want to know: Is it common for such a large mysqldump to slow down after a while? Or is there anything that can be improved further?

Answer 1

When importing large amounts of data, the following features should be disabled.

1. Disable trigger
2. Disable index
3. Disable constraints or checks
4. Disable foreign keys

Because every time you insert, the database will check the relationship of the inserted record in other tables. As the table gets larger, this process starts to slow down. During each insert, the database also performs additional insert operations or runs insert triggers into the table's indexes.

Also, since transactions in MySQL are weak, you need to work so that commit does not work after every insert. You can use the commit command, for example, once after every 10,000 records are inserted.