Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

3 Answers

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.

share|improve this answer
    
I'm running Windows 2008 x64, is there an alternative way to compress the dump? –  holiveira Jul 29 '09 at 21:30
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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