I have a huge mysqldump file (~700GB) that I want to restore on a different server. For the first GBs, the import is quite fast with some MB/s, but after some GB of data, the speed drops to something between 50kb/s to 200kb/s.

Params for the mysqldump were --skip-comments --no-create-info --no-autocommit --quick --extended-insert --insert-ignore --compress, so some speed improvements were done already.

Import is done by pv /file.sql | mysql -u USER DB

I also run mysqltuner and improved some of the settings there.

I’m wondering: Is it common for a mysqldump this large to have slow speeds after some time? Or is there anything that can be improved further?

CodePudding user response：

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

1. disable triggers
2. disable indexes
3. disable constraints or checks
4. disable foreign-keys

Because every time during an insert, the DB checks the relationships of the inserted record in other tables. And as tables get bigger and bigger, this process starts to slow down. Each time during an insert, the DB also performs an additional insert into the index of that table or runs insert triggers.

Also, because the transaction in MySQL is very weak, it is necessary to work so that commit does not work every time after insert, the commit command can be used, for example, once after every 10000 record inserts.

CodePudding user response：

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

innodb_log_file_size=1G  # from 50M to support almost 1 Hr before rotation
innodb_log_buffer_size=500M  # from 16M to support about 30 min before write log data
innodb_buffer_pool_size=4G  # from 128M to better support your data loading process
innodb_change_buffer_max_size=50  # to improve innodb_date_writes RPS of 369

Please view profile for contact info. Many more Global Variables could be significantly improved.

After a couple days of similar processing, let us know if better/worse for performance, please.