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

  • I've never really used the MySQL admin tool for backups so I don't know if this is the best answer, but you could always try doing mysqldumps instead. – Ian Selby Jul 29 '09 at 19:58

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.

  • I'm running Windows 2008 x64, is there an alternative way to compress the dump? – holiveira Jul 29 '09 at 21:30

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.


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

  • The core is used by the MySQL Admin process, and yes, there's only one database in this server. – holiveira Jul 29 '09 at 20:11

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.