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!

Advertisements

Summer Series :: Your Input Needed

highres_10491631Last year the FVCP hosted an all day learning session on Drupal held at Fermilab, in Batavia. From 8am till around 4pm 20 people sat at laptops and did built, step by step, their own Drupal sites in a Redhat Openshift server. This was the first summer workshop we attempted and we believe it was a success. But as with the saying in sales, that was last year. What are you going to do for me this year?

Some of the sticking points is the requirement of charging participants. Last year we had the opportunity to offer it for free and Fermilab was kind enough to lend us a conference room. Moving forward we are unsure on maintaining a free workshop as several of the ideas require up-front costs, such as hardware and specialized presenters.

We have been tossing ideas about, but none seem to be sticking to the wall. Like spagetti, it just needs a bit more cooking. Here are some ideas and I would love to hear yours, or a vote for any of the following.

  • An all-day hack-a-thon. Pick a language/framework to build an app or website for a social cause that could use the help.
  • Social media / SEM / SEO workshop.
  • Step-by-step programming language school: Ruby, Python or PHP.
  • One day build from scratch your own site on Github.io, learning about Git, HTML and javascript.
  • Build your own Kano computer.