Drupal Database Maintenance Script
Friday, August 3rd, 2007I love Drupal, but its insistence on saving every session its ever created causes the sessions table to become massive. So I usually end up creating a shell script like the following:
drupal-db-maint.sh (I posted a copy to pastebin since WordPress makes the code impossible to read.)
echo `date` - Starting Drupal database maintenance
MYSQL_USERNAME=’insert your MySQL username here‘
MYSQL_PASSWORD=’insert your MySQL password here‘
MYSQL_DATABASE=’insert your database name here‘
# Note: 2592000 = sessions over 30 days old.
# Adjust the timespan as desired.
CLEAN_SESSIONS_SQL=’DELETE FROM `sessions` WHERE `timestamp` < (UNIX_TIMESTAMP() - 2592000)’
OPTIMIZE_CACHES_SQL=’OPTIMIZE TABLE `cache` , `cache_filter` , `cache_menu` , `cache_page` , `cache_views` , `sessions`’
# Note: I have tons of modules installed.
# I tried cleaning out their table names before posting, but you’ll want to double check this SQL.
OPTIMIZE_REST_SQL=’OPTIMIZE TABLE `access` , `accesslog` , `aggregator_category` , `aggregator_category_feed` , `aggregator_category_item` , `aggregator_feed` , `aggregator_item` , `authmap` , `blocks` , `blocks_roles` , `boxes` , `client` , `client_system` , `comments` , `contact` , `files` , `file_revisions` , `filters` , `filter_formats` , `flood` , `history` , `invite` , `menu` , `node` , `node_access` , `node_comment_statistics` , `node_counter` , `node_revisions` , `node_type` , `permission` , `profile_fields` , `profile_values` , `role` , `search_dataset` , `search_index` , `search_total` , `sequences` , `system` , `term_data` , `term_hierarchy` , `term_node` , `term_relation` , `term_synonym` , `url_alias` , `users` , `users_roles` , `variable` , `vocabulary` , `vocabulary_node_types` , `watchdog`’
# Note: I run this script on a Linode which has limited IO.
# Adding a short wait between commands lessens the load on the server.
SLEEP_TIME=5
echo $CLEAN_SESSIONS_SQL | mysql -u $MYSQL_USERNAME -p$MYSQL_PASSWORD $MYSQL_DATABASE
sleep $SLEEP_TIME
echo $OPTIMIZE_CACHES_SQL | mysql -u $MYSQL_USERNAME -p$MYSQL_PASSWORD $MYSQL_DATABASE > /dev/null
sleep $SLEEP_TIME
echo $OPTIMIZE_REST_SQL | mysql -u $MYSQL_USERNAME -p$MYSQL_PASSWORD $MYSQL_DATABASE > /dev/null
echo `date` - Done with Drupal database maintenance
Then just run crontab -e to edit your crontab and add a line similar to:
46 4 * * * /path/to/drupal-db-maint.sh
That will optimize your database nightly at 4:46 AM server time (usually UTC).
If you don’t like my solution, there are plenty of more elegant session cleaning solutions out there. I’m just more comfortable writing shell scripts and crontabs than Drupal modules that use hook_cron.
Posting code in WordPress so that its readable is pretty much impossible. Any suggestions?
