I have a 83GB compressed .sql.gz file that I'm importing. The DB size on the source server (the size of the folder) was 240GB.

I'm importing it on a Amazon cloud server, with 2CPUs, 4GM RAM and SSD disk, using innodb buffer of 2.2GB and max packet of 128MB. The allowed IOPS on the server are 1200. I'm running version 5.5.63 for "legacy reasons".

The import command is like this:

zcat dump.sql.gz | mysql -u root -ppasswd DB_NAME

Most of the volume is due to 4 compressed tables that are also partitioned by timestamp range.

The import is going very slowly. The .ibd files for the compressed tables (or rather, the one that is at that time receiving data) increase at a super-slow rate of 40MB per 5 minute period, as I find using "ls -l" 5 minutes apart. The intriguing thing is that the iostat utility reports that every 5sec between 50MB-140MB are written on disk, here is an example:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
      33.00    0.00    0.99    2.37   11.86   51.78

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
xvda            657.60         0.00     13877.70          0      69388

So, bottom line, the operating system reports that the disk is seeing a lot of traffic, but the innodb table files are increasing at a snail's pace.

Thanks for any clues as to what is going on.

  • Is it swapping? Is that innodb_buffer_pool_size = 2.2G? I worry that the I/O is swapping; lowering that setting would help. – Rick James Jul 16 '20 at 20:22
  • "compressed tables" -- Do you mean ROW_FORMAT=COMPRESSED? (Or something else?) – Rick James Jul 16 '20 at 20:25
  • Was the dump file generated by mysqldump? With what options? – Rick James Jul 16 '20 at 20:26
  • It's barely swapping, just 150MB, with 1G available memory. Yes that's what the 2.2GB setting is referring to. The compression is as Rick writes, with 4K block size. The backup was taken with mysqldump --single-transaction --routines --triggers, piped through gzip. – GID Jul 16 '20 at 20:44
  • "block size" -- are you referring to innodb_page_size? If so, would you explain why you are using 4KB? And what does your data look like? I see the zip got about 3:1, which is very typical for "text". InnoDB's COMPRESS rarely does better than 2:1. So, I suspect 4KB blocks is inefficient. – Rick James Jul 17 '20 at 1:56

The only thing you can do to speed up the import without restarting the instance or restarting the import is to do the following:

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2;

This will increase the log flushing throughout.

If you have the query cache on, disable it immediately !!!

mysql> SET GLOBAL query_cache_size = 0;
mysql> SET GLOBAL query_cache_tpye = 0;

These are the only things you can do dynamically to speed things up a little.


UPDATE 2020-07-17 13:52 EDT

Since you can do restarts, add this to my.cnf

innodb_doublewrite = 0
innodb_log_buffer_size = 32M

and restart mysqld and then restart the load.

If you have binary logging enabled in the DB, disable the binary logs for the import

zcat dump.sql.gz | mysql -u root -ppasswd DB_NAME --init-command="SET sql_log_bin=0"

When the reload is done, remove innodb_doublewrite = 0 and restart mysqld.

I have suggested disabling the double write buffer before

  • I made all these changes, and there was no improvement. I have now restarted the import with these params already in place. It's still crawling. I calculated the rate over a few hours, it writes at an average of 140KB/sec, utterly ridiculous. If you can suggest other params that would require a restart, by all means do it, I can restart it. – GID Jul 17 '20 at 17:01

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.