Optimize only fragmented tables in MySQL

When you are using MySQL, you will (likely) have tables that can be fragmented. In MySQL terms this is called “OPTIMIZE”.

You could simply OPTIMIZE every table in every database, but during an OPTIMIZE, the tables are locked, so writing is not possible.

To minimize the time that MySQL will be locked (and results cannot be written), here is a script that checks fragmentation of every table of every database. Only if a table is fragmented, the table is OPTIMIZED.

#!/bin/sh

echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
  if [ "$datafree" -gt 0 ] ; then
   fragmentation=$(($datafree * 100 / $datalength))
   echo "$database.$name is $fragmentation% fragmented."
   mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
  fi
done
done

Another one:

#!/bin/sh

# --- Variabls
MYSQLROOTUSR='root'
MYSQLROOTPW='password'
PERCENT='55'
ROWCOUNT='300'

mysql -u $MYSQLROOTUSR -p"$MYSQLROOTPW" -NBe "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, (DATA_FREE*100/DATA_LENGTH) as PRC FROM INFORMATION_SCHEMA.TABLES WHERE (DATA_FREE*100/DATA_LENGTH) >= $PERCENT AND TABLE_ROWS >= $ROWCOUNT ORDER BY TABLE_SCHEMA, PRC DESC;" | while read TABLE_SCHEMA TABLE_NAME TABLE_ROWS DATA_LENGTH INDEX_LENGTH DATA_FREE PRC; do

        echo "$TABLE_SCHEMA.$TABLE_NAME is $PRC% fragmented and has $TABLE_ROWS rows."
        mysql -u "$MYSQLROOTUSR" -p"$MYSQLROOTPW" -NBe "OPTIMIZE TABLE $TABLE_NAME;" "$TABLE_SCHEMA"
done