Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jan 27 '12 at 21:29

This question came from our site for professional and enthusiast programmers.

    
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. –  Cheeky Jan 28 '12 at 16:18

4 Answers 4

Have you checked Run MySQLDump without Locking Tables question?

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

share|improve this answer
    
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?? –  Cheeky Jan 27 '12 at 21:42
    
@Kane Black: I understand - so how do other big systems handle backups without disrupting / slowing their applications? –  Cheeky Jan 28 '12 at 8:52

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer

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.