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.
Nice, did you get that script from: http://meinit.nl/optimize-only-fragmented-tables-mysql ?
I think You could use just :
read -p “MySQL username: ” username
read -sp “MySQL password: ” password
Thaks for Your script.
—
Mareg
I think You could use just :
read -p “MySQL username: ” username
read -sp “MySQL password: ” password
Thaks for Your script.
—
Mareg
mysqlcheck -o –all-databases
this single line command replaces that script and it’s easier to use…
” It shown OK in the message, but after I run the MySQLTuner, the fragmented tables still there.”
Still same number of tables fragmented after running this. (141)