[wp-hackers] Database Maintenance and Canonical Plug-ins

[Eric Mann]

I'm one of the many people who use a shared hosting solution to house a
WordPress blog.  In my case, I'm running three sites off the same
installation of WordPress (previously using WP-Hive, but I'm going to try
transitioning to the multisite feature of WP 3.0).  The issue I'm facing is
that my databases have an upper size limit of 100MB.  Normally this wouldn't
be an issue, but I checked today and found out that across my three sites I
had built a 30MB database already.

 

I found a system called WP Optimize that allowed me to quickly purge my old
post revisions from the database (realistically, I'm not going to review
articles from last month) and I was able to pare things back down to a
manageable 6MB total.  That's 6MB for three sites - a 3-times-per-week blog,
a once-in-a-while-when-I'm-bored writing blog, and a semi-static
professional portfolio, all of which have about 3 years of content.  Not too
bad when you think about it.

 

My concern is with the huge bloating effect that post revisions and
extraneous tables (left over from long-since-removed plug-ins) seem to have
on the size of the database.  I can't say whether or not this has had any
impact on performance, but I guarantee it would have caused issues had I let
it grow much closer to the 100MB limit.

 

So I'm proposing we implement a canonical plug-in for database management.
I'm suggesting the plug-in route because, really, this only affects a subset
of WP users . most people never look to see how large their database is, and
many of them will never have a problem with outgrowing their host.  But for
serious developers and long-term bloggers it could be a useful feature.

 

I'm thinking of a system that would do the following:

1)      Track what options and tables are created by plug-ins on
installation.  If the plug-ins are deleted, start a timer (maybe 1 month,
but configurable) and remove these deprecated values after they've aged out.
This would catch plug-ins with authors who fail to build in clean uninstall
functions.  I've seen far too many (and been guilty of it myself) lazy
systems that set up huge data structures and abandon them on uninstall.

2)      If a post hasn't been revised for a significant period of time
(again, configurable) its revision history will either be deleted or
archived (moved out of the database and into a static text file . maybe
XML?).  A lot of us have massive amounts of unused storage space on our
servers, so storing 100MB of post revisions in a mostly empty 5GB file
system would be a lot more efficient than keeping them around cluttering the
limited-size database.  Since the post revisions aren't used heavily, I
doubt not having them indexed in the DB would be too big of an issue .
still, they're there if you ever want to go back and look.

 

Now that 3.0 is out and we're all taking some time to step back, stretch,
and look at the whole landscape, I think spending some time revamping the
way we maintain the database would be nice.  As an added bonus, we could
also build some kind of auto-backup into a maintenance plug-in . something
that works outside the regular WordPress framework, automatically backs up
the system at regular intervals, and can be called upon to automatically
roll things back if necessary.  Then, if a future upgrade goes poorly, or if
a new plug-in borks things, you can just go to
http://myblog.url/wp-admin/database_restore.php, log in as usual, and roll
everything back to your last restore point.

 

In any case, I'm trolling for ideas, input, and feedback here.  Is this
worth some serious design time?  Would this be appropriate for consideration
as a canonical plug-in?  What else would you add to make it a serviceable
system?

 

~Eric Mann