2

Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds instance, instance snapshot in AWS have 700GB. MySQL version 5.6, using innodb. At moment it's been 20 hours sience restoration is running, my estimations are that its going to finish in 4 - 6 hours, so the complete restore will be about 24 hours and that is way too much downtime for production environment. My question: How to tune mysql in order to speed up restoration? Thank you in Advance.

P.S Dump was done in 3 and a half hours.

2

Have you established what you are bottlenecked on in your import? If you are CPU bottlenecked, there is a very limited amount you can do other than put CPUs with faster single-thread performance into your private cloud instance.

A few things that will help:

innodb_doublewrite=0
innodb_flush_logs_on_trx_commit=0
innodb_io_capacity=<approximate amount of IOPS your storage can provide when using 16KB random writes>
innodb_log_file_size=1G
innodb_log_files_in_group=24 # you will want to change this later!
performance_schema=0
slow_query_log=0
general_log=0

Note: Make sure you remove all of these options after your import is complete, they are dangerous and not sensible for regular runtime operation! These are equivalent of making your race car go faster through weight saving that comes from removing the roll cage and seat belts.

Enabled disk write cache on your import machine's virtual disk, if the driver supports such a thing.

If you can get enough RAM during the import, once your ib_logfile* files are created, stop mysqld, put those files and ibdata1 on tmpfs, and symlink them in. When you are done, stop mysqld and move them back.

If ZFS is an option for you, put your /var/lib/mysql on ZFS on a volume with the following settings:

compression=lz4
logbias=throughput
recordsize=16k
sync=disabled
xattr=sa

This will approximately double your write throughput compared to other file systems, due to compression and sync=disabled functionality. Make sure you set sync=standard after the import is complete.

If you combine all of the above and you are disk I/O limited, you will see a performance improvement in the multiples. If you are CPU limited, the above will give you double-figure % points of improvement.

| improve this answer | |
  • Hi, thnx for your time. I have tried your options but unfortunately ending up with same amount of time for restoration. And in addition,got ~300 binary log files, 1.1G each one. Looks like the car is not a race one :). Or maybe we have missed something. Here is my /etc/my.cnf file: justpaste.it/6d6hn – Gheorghe Isac May 3 at 11:29
  • My bad, There should be no log_bin=0 (the way to disable it is to not specify it). Having a bin-log probably ate all the improvement from other options. Also, add innodb_flush_neighbours=0, that will give you a tiny bit extra on SSD. – Gordan Bobic May 3 at 11:35

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