Optimize only fragmented tables in MySQL

Last updated on April 1st, 2012 at 12:55 am

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.
[adrotate banner=”2″]
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

[adrotate banner=”1″]
#!/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

[root@server 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:

6 thoughts on “Optimize only fragmented tables in MySQL”

  1. I think You could use just :

    read -p “MySQL username: ” username
    read -sp “MySQL password: ” password

    Thaks for Your script.

    Mareg

  2. I think You could use just :

    read -p “MySQL username: ” username
    read -sp “MySQL password: ” password

    Thaks for Your script.

    Mareg

  3. mysqlcheck -o –all-databases

    this single line command replaces that script and it’s easier to use…

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.