Question 113280 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL import gets slower over time – improvements possible?

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search