Optimize only fragmented tables in MySQL

Normally I use myqlcheck -A –optimize to optimize my MySQL database tables. But this will load your whole database and use more time than optimize only fragment tables.

I always use the MySQLTuner 1.1.1 – Major Hayden
to check and optimize my database, and I found a lot of fragmented tables in my database.

I did try to use myqlcheck -A –optimize to defrag those table. It shown OK in the message, but after I run the MySQLTuner, the fragmented tables still there.


-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 676M (Tables: 1783)
[--] Data in InnoDB tables: 68M (Tables: 137)
[--] Data in MEMORY tables: 0B (Tables: 5)
[!!] Total fragmented tables: 189

Today I found this useful shell script from here


#!/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

vi mysql_optimize
paste the code above
chmod 744 ./mysql_optimize
./mysql_optimize

The output will be like this

[[email protected] src]# ./mysql_optimize
MySQL username: admin
MySQL password:
. . .
database.table is 88% fragmented.
database.table optimize status OK
. . .

After that I use mysqltuner.pl check again.

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 676M (Tables: 1783)
[--] Data in InnoDB tables: 68M (Tables: 137)
[--] Data in MEMORY tables: 0B (Tables: 5)
[!!] Total fragmented tables: 14

The fragmented tables will decrease a lot.
Why I still have fragmented tables in the database ?

Because maybe you are using InnoDB. It may show the result


Table does not support optimize, doing recreate + analyze instead

You can optimize your InnoDB table in here.


Tags: , , , , , , , , , ,

Translate

Buy me a Caffè Latte

Thank you for the Caffè Latte

2013/02/15 saw art
2013/01/25 Parul Patel
2012/11/20 Barbara Nicholas $10
2012/10/08 Amy Stockman $5
2012/07/15 siewho chang $ 50
2012/06/08 Susan Hampton $10
2012/05/11 Chris Grima $10