Topic: Affa Backup stalls other process's

[jameswilson]

Using the brilliant SME and the excellent Affa backup contrib.
I use this to backup the most important machine every 4 hours.

This all owrks flawlessly, but when affa calls the mysqldump, anything reliant on mysql (a web site, php/mysql etc) just stops until the dump is complete.
Ive played with the bandwidth etc, but it wont affect the dump speed.

maybe i should be backing up mysql different and this may be by design to stop inconsistencies etc

So basically can i lower the priority of the dump so that it can run more backgrounded, or should i be backing up the db differenty. Or should i only backup during quiet periods.
Id rather not backup only nightly, id prefer to do it hourly, but cant due to the site stopping during backups.

Any suggestions?

James

[mmccarn]

A little googling tells me that there isn't an easy answer to your question.

If your mysql tables all use "InnoDB", then you can run backups without locking your users out -- but my SME server does not seem to have InnoDB support enabled (you can check on your system using mysqladmin variables |grep  have_inno)

You may get some improvement by adding the "--quick" option to the "mysqldump" command.

You may be able to completely re-work the SME sql backups to use "mysqlhotcopy" (warning: this would have major implications!)

The most common suggestion I found online for backing up large, non-InnoDB databases without locking out users involves setting up mysql database replication to another server, then backing up the backup server -- which sounds like a pretty big deal to me.

Affa calls the SME "pre-backup" event to create the sql backups, which then uses the action script /etc/e-smith/action/mysql-dump-tables to create the sql backups.  If you want to make changes, make a copy of this script and see if you can modify it to run faster.

If you find a solution (perhaps simply adding "--quick" will do it), you should then open a new feature request in bugzilla requesting implemetation of your changes -- this will usually get the devs to comment on your changes, and add them to SME if they improve the base product.

One more option that occurs to me: You may want to implement a custom backup for your website databases, but still use the SME mysql-dump-tables for the SME tables.  This would involve creating a script for affa to run before the backup, then customizing the SME pre-backup event to ignore the tables you're backing up manually (and, if you get it working, a new feature request for your changes to mysql-dump-tables, if any).

Here's the google search I started with: http://www.google.com/search?q=mysqldump+without+locking+databases

[CharlieBrady]

So basically can i lower the priority of the dump so that it can run more backgrounded,...

That would only make things worse - your problem is mysql locking, not I/O or CPU bandwidth.

... or should i be backing up the db differenty. Or should i only backup during quiet periods.
Id rather not backup only nightly, id prefer to do it hourly, but cant due to the site stopping during backups.

If your mysql data is so voluminous and important, you should look at replication, so your backup is continuous. In the meantime, backup during quiet periods might be your best bet.

[Stefano]

If your mysql data is so voluminous and important, you should look at replication, so your backup is continuous. In the meantime, backup during quiet periods might be your best bet.

I agree..

I would add that maybe SME is not the right tool.. I guess OP is trying to achieve a kind of HA.. if so, I would take a look to a clustering solution for virtualization and a virtual SME (hint: take a look at Proxmox)

my 2c