Suggestion for a safer way of backing up

I use BackWPUp on a number of sites and I found “mysterious” performance problems started creeping in over time.

I’m not saying it is BackWPup’s fault – not at all – however as a 30 year programming veteran I will offer the following observation as the reasons I’m sharing this with you.

It doesn’t matter if it’s your fault or not if your the one that ends up being blamed for it 🙂

After a great deal of research I finally spotted a significant pattern emerging – here is what happened:

In an effort to improve site security I also added another plugin that controls access to the wp-login function and restricts it to known IP addresses.

All fine and dandy so far – no problems yet.

That second plugin logged every single failure – and on some of the sites we have logged over 250,000 hack attempts – each and every one dutifully logged into a what became a massive database table.

A massive database table that of course when you do a SELECT * FROM (MASSIVE_DATABASE_TABLE) well that just eats up all available RAM and then some – causing environmental failures and a host of bogus errors.

Why would anyone want to do a SELECT * FROM such a large table you ask?

Because that is exactly what backups have to do.

Of course, depending on who your hosting provider is, and most hosting providers use a shared environment where RAM is at a premium, loading all that data into memory at once has negative effects on a sites performance. As that table grows in size it also causes many “mysterious” errors to occur in the backup logs or the site itself and ultimately many backups to simply fail. Followed by sites actually failing as well as the environment does or doesn’t deal with the RAM crisis smoothly.

So – shoot the idiots that decided to log all that dat – right? They’re there ones to blame after all aren’t they?

Easy fix – just don’t back up that table – right?

WRONG on both counts.

The logging is doing exactly what it should – what if you needed to check that log at some point? Why should that (or ANY) data be excluded from the backup?

So, to me, the real question is, why should a backup have to be adjusted to accommodate the SIZE of the data being backed up?

It should not and in fact it doesn’t have to.

A backup should in fact simply back stuff up no matter what that stuff is or how much of it exists.

I routinely backup databases with 100s of MILLIONS of records.

How?

Basically the process goes like this:

SELECT * FROM (MASSIVE_TABLE) WHERE id > 0 ORDER BY id LIMIT 100
SELECT * FROM (MASSIVE_TABLE) WHERE id > 100 ORDER BY id LIMIT 100
SELECT * FROM (MASSIVE_TABLE) WHERE id > 200 ORDER BY id LIMIT 100

and so on until you have an empty record set indicating the end of the table.

Technically you need to read the contents of the id field for the last record in each recordset and increment it accordingly so as to accommodate any “missing” (deleted) records but that is rather trivial to do.

You can also vary the “block size” (the “100” in the above example) in that you can choose a smaller block size for dealing with memory constrained environments and/or a larger block size for tables with light amounts of data per row. Since a simple “check” of the total record size could be performed as well this could be an auto-tuning variable if one wanted to attain maximum speed without losing reliability.

Could such a mechanism not be worked into BackWPUp?

I’ll bet if you did a deep root cause analysis of a lot of the problems being reported (and clogging up your tech support channels) you might find that more than a few of them are the “mysterious” errors that come from out of RAM conditions.

And I suspect a LOT of those errors are not being properly identified nor reported either.

Just my $0.02 worth

http://wordpress.org/plugins/backwpup/