MySQL’de fragmented olmuş tabloları bulma ve optimize etme

Aşağıdaki query ile fragmented tabloları bulabilirsiniz.

mysql> select TABLE_NAME,Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql')
and Data_free > 0;

mysql> OPTIMIZE TABLE tablo_adi

şeklinde optimize edebilirsiniz. Bu işlem performansı artıracaktır.

Aşağıdaki script te otomatize edilmiş halde yapabilirsiniz. ( Google search )

#!/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 WHERE engine='MyISAM';" $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

Posted in Linux, MySQL on November 20th, 2012 by Kürşad DARA | | 0 Comments

Leave a reply