5

I have a scheduled backup that runs daily in MySQL and I have noticed that during the backup a lot of queries that query a frequently used table seem to take forever to complete. This causes the website to lag like crazy during the backup and once the backup completes, specifically for the frequently used table, the site runs fast again.

Does MySQL lock table(s) during the backup and if so is there anyway to avoid this?

How do other big systems handle backups without disrupting / slowing their applications?

Thanks

  • How do you expect to do both a backup and high volume queries on the same object at the same time without causing performance issues? – JNK Jan 27 '12 at 21:41
  • @JNK: I am trying to find the best way to alow the backp with the least disruption in service. Other high availabilty sites must be doing this, so I am trying to find out how. – Paolo Broccardo Jan 28 '12 at 16:18
1

Have you checked Run MySQLDump without Locking Tables question?

Everything boils down to the way you're doing data backups.

  • Hi - I am doing the backup via MySql Administrator. The "Complete backup" option is being used. As for the link you posted, I do not see anywhere in MySQL Administrator where you can add extra options?? – Paolo Broccardo Jan 27 '12 at 21:42
  • @Kane Black: I understand - so how do other big systems handle backups without disrupting / slowing their applications? – Paolo Broccardo Jan 28 '12 at 8:52
1

In addition to the above changes to mysqldump, I would like to suggest that you try Percona Xtrabackup www.mysqlperformanceblog.com/2012/02/27/announcing-percona-xtrabackup-1-9-1/ which seems to offer more backup options, including parallel streaming which brings up to par with other mainstream databases.

0

I've always been under the impressions thatthe truely big systems use clusters and take nodes offline for backups.

It looks like LVM snapshots are also a good solution for getting good performance out of your MySQL DB during backups. Check out these articles:

Using LVM for MySQL Backup and Replication Setup

Living with backups

0

You better to use the command line backup utility for taking better control of your backup data.

With the command line backup utility, use the "--lock-tables=false" option to not to lock the MySQL databases.

Aravind

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.