Pesky cache table

Sometimes that cache table just doesn’t clear. And when it doesn’t, look out. It’ll grow and grow until your website crashes into a glorious, user denied hellscape. Basically, it’ll just crash. Had this issue recently and while attempting to diagnose I came up with a little script to keep on top of things.

First, a spot of SQL

SELECT table_name AS “Tables”,
round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”
FROM information_schema.TABLES
WHERE table_schema = “DATABASE”
ORDER BY (data_length + index_length) DESC LIMIT 10;

Nothing too fancy here, just a list of the biggest tables

Next, we need a way to run that SQL on the command line. Well, it is a Drupal site, so drush is the answer

drush @prod sql-cli < table_size.sql

Again, nothing too fancy. Running an sql command line interface into the production alias and we will push in the above sql. This gives me:

Tables Size in MB
cache 6119.59
cache_menu 38.73
webform_submitted_data 31.09
search_index 16.03
cache_field 12.59
cache_metatag 5.58
field_revision_body 3.98
field_data_body 3.91
cache_form 3.53
cache_views 3.53

This is all fine and dandy, but I need something to tie it all together. Bash to the rescue!

#!/bin/sh
drush @prod sql-cli < table_size.sql 2>&1 | awk ‘{ print $1 ” ” $2 }’ | while read output;
do
echo $output >/dev/null
table=$(echo $output | awk ‘{ print $1}’ )
size=$(echo $output | awk ‘{ print $2 }’ )

#echo $table $size

if [ $size != “Size” ] && [ ${size%.*} -ge 6000 ]; then
#echo “Alert: $table table is $size mb”
mail -s “Alert: $table table is $size mb” MY_EMAIL_ADDRESS
fi
done

OK, let’s break this down.

  • Starting off we are reading that SQL output and pushing it into an output variable.
  • Using awk we get the table name and size.
  • There are some #echo’s in there I used for testing. Leave them in and uncomment for your own fun and profit.
  • From here we are testing that first the size is not the header “Size” and that it is in fact an integer larger than 6000. For production I increased this to 10000 but did not change it here. Set to your own liking.
  • If the size is indeed larger than your set number, fire off an email alert.

Could this be more robust? Sure. But the office is closing early for the holiday weekend and I wanted to get this off and running.

Have a great holiday everyone!