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.
Best Answer
The only thing you can do to speed up the import without restarting the instance or restarting the import is to do the following:
This will increase the log flushing throughout.
If you have the query cache on, disable it immediately !!!
These are the only things you can do dynamically to speed things up a little.
GIVE IT A TRY !!!
UPDATE 2020-07-17 13:52 EDT
Since you can do restarts, add this to my.cnf
and restart mysqld and then restart the load.
If you have binary logging enabled in the DB, disable the binary logs for the import
When the reload is done, remove
innodb_doublewrite = 0
and restart mysqld.I have suggested disabling the double write buffer before
Dec 19, 2014
: When is it safe to disable InnoDB doublewrite buffering?Sep 29, 2016
: MySQL settings useful to speed up a mysqldump import