0

I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM).

The import takes more than six hours:

  • More than 500 tables
  • I have 2 large tables: the first one 57GB, the second 21GB
  • InnoDB for all tables
  • The commands used are the following:

    mysql -u root -p{db_pwd} -h {db_host} -f -e "\
    
    SET foreign_key_checks=0; SET unique_checks=0; SET autocommit=0; \
    
    source /tmp/dump.sql; \
    
    SET foreign_key_checks=1; SET unique_checks=1; SET autocommit=1;"
    

How can I optimize my import with removing indexes, or by means of other strategy, on these large tables?

I tried with:

   innodb_flush_log_at_trx_commit = 0
    innodb_autoinc_lock_mode=2
    innodb_write_io_threads = 16 
    innodb_log_file_size = 1073741824 (1 Go)
    innodb_log_buffer_size = ‭8388608‬  (8 Mo)
    innodb_buffer_pool_size = 3/4 RAM

but apparently no impact.

0

You may try to edit your mysqldump file to remove indexes. It's just a text file with SQL statements, so you need to somehow find CREATE TABLE statements in it and remove KEY lines from them (except from PRIMARY KEY). But that can hardly help with the speed because InnoDB index creation is quite efficient and doesn't take overhead time compared to separate index creation after the import.

If you have limited time access to the RDS instance, I would recommend to deploy the dump on your local server/PC (it may take even longer), then make a dump of only smaller tables to apply on RDS separately, then think how to move the larger ones. LOAD DATA INFILE may be not much faster than mysqldump so it's worth considering copying tablespaces (e.g. "cold backup method" here: https://dev.mysql.com/doc/refman/8.0/en/innodb-migration.html ).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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