I just had another look at what I wrote the week before last (you know, being home-sick/on vacation has it’s advantages) and additionally read up on " OPTIMIZE TABLE" again. The comments in the manual mention " SHOW TABLE STATUS", which gives you a complete list, but it doesn’t allow you to filter certain kinds of things out (like I only wanted to see MyISAM tables in the list, I only wanted database and table).

So I went ahead and looked around in MySQL’s own databases and if you look closely at information_schema, it’s got a list of all databases/tables with an additional pointer whether or not databases are fragmented, the row Data_free. I only found this, because I looked at how the mysqltuner figured whether or not you have fragmented tables.

So, without further ado, here’s the final script I’m gonna torture for the next week:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

#!/bin/bash

# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e 'use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND
Data_free > 0' | grep -v "^+" | sed "s,t,.," )"

for fragment in $FRAGMENTED_TABLES; do
  database="$( echo $fragment | cut -d. -f1 )"
  table="$( echo $fragment | cut -d. -f2 )"
  [ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;
  OPTIMIZE TABLE $table;" > /dev/null 2>&1
done

# vim: set tw=80 ts=2 st=2 et :

I know it ain’t completely bullet proof and it sure as hell isn’t neat, but I think it does the job. Also, if you don’t want to paste it, here’s the file download.