Correctly optimize MySQL on a cronjob. Increase performance.

Discussion in 'Site & Server Administration' started by TiffanyJ.SSS, May 7, 2013.

0
  1. #1
    Figured the community could enjoy a little of this. :)

    http://blog.solidshellsecurity.com/2013/05/07/correctly-optimize-mysql-cronjob-increase-performance/

    cat optimize.sh
#!/bin/sh
#solidshellsecurity.com
 
mysql -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
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
if [ "$datafree" -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo "$database.$name is $fragmentation% fragmented."
mysql -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done
    Code (markup):
     
    TiffanyJ.SSS, May 7, 2013 IP