optimize mysqldump for approx. 20GB databases for 8GB RAM

Question

MySQL server having following specifications:

1. 8GB RAM
2. Data & Index of size 49.64GB
3. Dump file size is 17.24GB(18512936320 Bytes)
4. CPU:
   1. Processor: Intel(R) Core(TM) i5-3330 CPU @ 3.00GHz
   2. Architecture: x86_64
   3. vendor: Intel Corp.
   4. size: 2400MHz
   5. capacity: 2400MHz
   6. width: 64 bits
5. OS:
   1. Distributor ID: Ubuntu
   2. Description: Ubuntu 14.04.5 LTS
   3. Release: 14.04
   4. Codename: trusty

Free memory(RAM): this may be variable on specific time

         total       used       free     shared    buffers     cached

Mem: 7.7G 5.2G 2.5G 15M 44M 4.6G -/+ buffers/cache: 597M 7.1G Swap: 7.9G 61M 7.8G

Database 98% tables are InnoDB and rest of it are MyISAM.

I have used following query:

mysqldump -u root -p --single-transaction --routines --events --triggers --all-databases | gzip > all_databases_backup.sql.gz

I have tried following Global variables :

key_buffer_size = 512M
Innodb_buffer_pool_size = 1024M
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_thread_concurrency = 0

To optimize the backup total time but not only able to reduce the time even 1 min.

• With default settings it is taking min : 27mins
• With the setting shown above it takes : 26mins

Is there any thing else I have to configure to optimize mysqldump?

Answer 1

I'm not sure about serious improvement of total time, but below some recommendations:

• I suggest You - convert all tables into InnoDB format, and adjust memory for InnoDB:

  Innodb_buffer_pool_size = 4096M

To main reasons - optimisation for single engine more easy to tune and MyISAM not affected by --single-transaction

• Split dump and GZIP

I do not know, what part of table You have in MyISAM, but it always better - finish all works with database as fast as possible, than continue other steps

GZIP is may be exactly slowest part of Your script and it really not optimised. It use single core not depending how many of them You have.

Answer 2

Simply put, disks are too slow to drastically shrink the time for a dump.

What OS? SSDs?

• Percona's XtraBackup may be faster than mysqldump.

• A dump, of any size, can be taken with less than a minute of downtime if you set up a LVM and take a snapshot. That is, mysqld is down only briefly, then you have all the time you need to copy the snapshot.

• A dump, of any size, can be done with zero downtime if you set up Master + Slave, then take the Slave offline and dump it.

Oops, I guess I am repeating myself: https://dba.stackexchange.com/a/2853/1876

Meanwhile...

If that server is dedicated to MySQL, change to these

key_buffer_size = 512M
innodb_buffer_pool_size = 3G

If it has spinning drives:

innodb_io_capacity = 200

Answer 3

You can make mysqldump export in tab-delimited format suitable for LOAD DATA INFILE. Each table will be saved into its own separate file.

https://dev.mysql.com/doc/refman/5.7/en/mysqldump-delimited-text.html

This is faster than exporting to SQL, and also restoring is faster since it uses LOAD DATA INFILE instead of INSERTs.

Also, if you want to transfer these tab-separated files off-site through a limited bandwidth link, you can use rsync in delta mode to only transfer the modifications.

Obviously, compression should be done once the backup is done, or it will be the limiting factor. If you want really fast compression, use lzop.

key_buffer setting will not speed up your backup since it is about myisam indexes only, and backups do not read indexes, only tables.

If you write the backups on the same physical drives as the database tables are, you might get IO throttling. Adding another drive is an option.

In backups, the most time consuming thing is actually converting all data into a text format (like converting ints and dates stored in binary format inside your tables into ASCII text). This is why a tool which handles table files at a binary level, like XtraBackup, has the potential to massively outperform a text/SQL backup tool like mysqldump.