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

Result will look something like this:

MySQL username: root
MySQL password:
...
database.cache_filter is 19% fragmented.
meinit.cache_filter optimize status OK
database.cache_page is 35% fragmented.
meinit.cache_page optimize status OK
...

You may comment out that line with OPTIMIZE TABLE in it, if you are just interested in seeing the fragmentation.

Comments

thanks for sharing this

thanks for sharing this piece..quite interesting and useful...made my job of one of essay experts and consultants on technology much easier

Nice. Thanks. An nive

Nice. Thanks.
An nive improvement would be to detect innodb tables and, for them, alter to engine myisam then back to innodb to fake an "optimize table".
Anyway, it's great.

[[email protected]]> alter table

[[email protected]]> alter table payment ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

I want to use this script but

I want to use this script but i don't know how to.

Check this thread to get a

Check this thread to get a "ratio of fragmentation":
http://serverfault.com/questions/202000/how-find-and-fix-fragmented-mysql-tables

for db_table in `mysql -Bse

for db_table in `mysql -Bse "SELECT CONCAT(TABLE_SCHEMA, ':', TABLE_NAME) AS \
db_table FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') \
AND Data_free > 0 AND NOT ENGINE='MEMORY' ORDER BY Data_free DESC;"`; do
  DB=`echo $db_table | cut -d: -f1`; TB=`echo $db_table | cut -d: -f2`; mysqlcheck -oa "$DB" "$TB"
done

great script, pimped a little

great script, pimped a little for us:
- no mysql-credentials, (using .my.cnf, for cron)
- no stats, just runs
- skips system-db
- skips views
- splitted lines

#!/bin/sh                                                                                                                                                          
mysql -NBe "SHOW DATABASES;" | grep -v 'lost+found' \
    | while read database ; do

    #skip system-db                                      
    if [ "$database" = "mysql" ] ; then
        continue
    fi
    mysql -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

        #skip views                               
        if [ "$datafree" = "NULL" ] ; then
            continue
        fi
        if [ "$datafree" -gt 0 ] ; then
            echo "$database.$name is fragmented"
            mysql -NBe "OPTIMIZE TABLE $name;" "$database"
        fi
    done
done

Hi, The above script gave

Hi,

The above script gave error,

[admin]# ./mysql_optimize
./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found
./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found
./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found
./mysql_optimize: line 10: read: ` ': not a valid identifier
./mysql_optimize: line 11: datalength: command not found

Any way to fix this?

Thank you.

I guess it's a copy-paste

I guess it's a copy-paste problem. Lines 10 and 11 end in a backslash (\), but I guess your copy-paste removed that or so.

no, it did nto removed

no, it did nto removed backlash, i have same issue, im sure i coppied properly..

About Consultancy Articles Contact




References Red Hat Certified Architect By Robert de Bock Robert de Bock
Curriculum Vitae By Fred Clausen +31 6 14 39 58 72
By Nelson Manning [email protected]