Drupal Database Maintenance Script

For a while now I’ve found it odd that Drupal’s cron.php doesn’t optimize the more ephemeral tables within the database. (Maybe it does, but I’ve never seen it work). The cache, watchdog, and sessions tables in particular create a significant amount of overhead on a relatively busy site. So I wrote this daily maintenance script to keep the database size compact and clean out the overhead on the offending tables. Note that this is my first shell script ever, so it’s certainly ripe for criticism.
First, I created a .sql file to specify which tables I wanted optimized. I wanted to keep these commands separate from the shell script itself; no particular reason, it just felt cleaner to me. This file is called “dbmaint.sql”.
OPTIMIZE TABLE `bloginfo` , `cache` , `cache_filter` , `history` , `scheduler` , `sessions` , `url_alias` , `users` , `variable` , `watchdog`
Next, I created a shell script, which I called “dbmaint.cron”. The .cron extension isn’t necessary, but it’s a great way to understand at a glance that this script is run using a cron job. In that script, I wrote:
mysql --user=dbusernamehere --password=passwordhere databasenamehere < /var/www/pathgoeshere/scripts/dbmaint.sql
I used the full path to dbmaint.sql because the sh command didn’t like it otherwise. My final step was creating the cronjob, which used this command to execute the script:
sh /var/www/vhosts/pathgoeshere/scripts/dbmaint.cron
I’m not capturing the output because I want this happening on a regular basis and don’t need logging to confirm it. I can look at the database tables and know if they’ve been optimized in the past 24 hours or not just by experience in how much overhead should be created.
Anyway, hope that helps someone! I couldn’t find anything this simple out there, so I figured it was worth publishing my own.

One thought on “Drupal Database Maintenance Script

  1. Hi – this sounds like a good plan to me. There is certainly room within Drupal for optimisation!
    I have no experience in terms of DB optimisation though; is there perhaps a check-list to go by to determine which tables are viable to put into the SQL?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: