How to speed up MySQL Backups using MySQL Admin?

Question

I have a dedicated MySQL 5.1 database server with 2 Intel Xeon 2GHz quad core processors and 10 Gb of Ram.

I use the MySQL Admin tool to perform full backups of my database, with a size of 12 Gb, but the process takes almost 4 hours to complete.

While analyzing the processor usage during the backup, only one core is being used by the MySQL Admin, which is a total waste considering I have another 7 cores sitting idle.

Is there a way to speed the backup process using the MySQL Admin Tool, or do you recommend another procedure to perform faster backups?

Edited: The server runs Windows 2008 x64 Enterprise Edition

Answer 1

Unless your entire dataset fits into memory, your biggest bottle neck for doing your dump is getting the data off the disk and back on to the disk in the dump file. Using more cores probably won't make that go any quicker.

I'd say that 4 hours for 12GB of data is slow. We do several times that in only a couple of hours. If you're using a unix, I suggest you do a dump using:

# mysqldump --opt database | gzip > /tmp/database.sql.gz

First, "mysqldump --opt" gives you an SQL script which is the fastest method of importing data back into MySQL. Secondly, compressing the SQL script will reduce the IO required to save the dump, leaving more for doing the reading required for the dump. Thirdly, hopefully the compression will run on a second core, using more of your CPU.

Answer 2

Probably not. Backups generally abuse the disk I/O bus, so running more things in parallel is unlikely to improve total yield time.

You might try some parallel mysqldumps as Ian Selby suggests and compare the throughput.

Answer 3

Who is using one core - the mysql or the mysqladmin process? Is it a single database? I've got a script somewhere that does backups of each database in a separate file so it could be used to run more backups in parallel