Mysqldump performance degradation

Question

Inside my cronjobs I make a full mysqldump every night.
My database has total 1.5GB data inside 20 tables.
Nearly every table has indexes.

I make backup like this:

mysqldump --user=user  --password=pass  --default-character-set=utf8 database
            --single-transaction| gzip > "mybackupfile"

I make this for 2 months. This process takes nearly 1,5 minutes for 2 months.

Last week my hosting company changed my server. Just after the server change, this process started to long for 5 minutes. I told this to server company and they increased my CPU from 4GHz to 6 GHz so mysqldump process became 3,5 minutes. Then they increased to 12 GHz. But this didn't change the performance.

I checked my shared SSD disk performance with hdparm. It was 70 MB/sec. So I complain again. So they changed my hard disk to another one. Hard disk read speed became 170 MB/sec. So mysqldump process became 3 minutes.

But the duration is far from the previous value. What would be the cause for this performance degradation ? How can I isolate the problem ?

(Server is Centos 6.4, 12 GHz CPU, 8 GB RAM)

---

Edit: My company changed server again and I still have same problem. Old server has 3,5 minutes backup time now new server has 5 minutes time. Resultant file is 820 MB when zipped, 2.9 GB when unzipped.

I'm trying to find out what makes this dump slow.

Dump process started at 11:24:32 and stopped at 11:29:40. You can check it from screenshots' timestamps.

Screenshots:

• General
• Consumed memory
• Memory and CPU of gzip
• Memory and CPU of mysqldump
• Disk operations

hdparm results:

/dev/sda2:
 Timing cached reads:   3608 MB in  1.99 seconds = 1809.19 MB/sec
 Timing buffered disk reads: 284 MB in  3.00 seconds =  94.53 MB/sec

/dev/sda2:
 Timing cached reads:   2120 MB in  2.00 seconds = 1058.70 MB/sec
 Timing buffered disk reads: 330 MB in  3.01 seconds = 109.53 MB/sec

Answer 1

My question is: Do you really need a dump or just a copy?

There is a great way that is far away from mysql dump, it uses Linux LVM "LVM Snapshot":

http://www.lenzg.net/mylvmbackup/

The idea is to hold the database for a milli second, then LVM will make a hot copy (which takes another milli second) and then the database can continue to write data. The LVM copy is now ready for every action you want: copy the table files or open it as new mysql instance for a dump (which is not on the production database!).

This needs some modifications to your system. Maybe those mylvmbackup scripts are not completely finished jet. But if you have time yourself you can build on them and do your own work.

Btw: if you really go this way, I'm very interested in the scripts as I am also need a fast solution to clone a database from production environment to a test system for developers. We decided to use this LVM snapshot feature but - as always - had no time to start with it.

Answer 2

Obvious thing I'd look at is whether the amount of data increased in recent months. Most database-driven applications collect more data over time, so the database grows. If you still have copies of your nightly backups, I'd look at the file sizes to see if they have been increasing steadily.

Another possibility is that you have other processes doing read queries while you are backing up. Mysqldump by default creates a read lock on the database to ensure a consistent snapshot of data. But that doesn't block read queries. If there are still queries running, this could compete for CPU and disk resources, and naturally slow things down.

Or there could be other processes besides MySQL on the same server competing for resources.

And finally, as @andrew commented above, there could be other virtual machines on the same physical server, competing for the physical resources. This is beyond your control and not even something you can test for within the virtual machine. It's up to the hosting company to manage a balanced allocation of virtual machines.

The fact that the start of the problems coincides with your hosting company moving your server to another host makes a pretty good case that they moved you onto a busier host. Maybe they're trying to crowd more VM's onto a single host to save rackspace or something. This isn't something StackOverflow can answer for you -- you should talk to the hosting company.

The number or size of indexes doesn't matter during the backup, since mysqldump just does a SELECT * from each table, and that's a table-scan. No secondary indexes are used for those queries.

If you want a faster backup solution, here are a few solutions:

• If all your tables are InnoDB, you can use the --single-transaction option, which uses transaction isolation instead of locking to ensure a consistent backup. Then the difference between 3 and 6 minutes isn't so important, because other clients can continue to read and write to the database. (P.S.: You should be using InnoDB anyway.)

• Mysqldump with the --tab option. This dumps data into tab-delimited files, one file per table. It's a little bit faster to dump, but much faster to restore.

• Mydumper, an open source alternative to mysqldump. This has the option to run in a multi-threaded fashion, backing up tables in parallel. See http://2bits.com/backup/fast-parallel-mysql-backups-and-imports-mydumper.html for a good intro.

• Percona XtraBackup performs a physical backup, instead of a logical backup like mysqldump or mydumper. But it's often faster than doing a logical backup. It also avoids locking InnoDB tables, so you can run a backup while clients are reading and writing. Percona XtraBackup is free and open-source, and it works with plain MySQL Community Edition, as well as all variants like Percona Server, MariaDB, and even Drizzle. Percona XtraBackup is optimized for InnoDB, but it also works with MyISAM and any other storage engines (it has to do locking while backing up non-InnoDB tables though).