Optimize 正常都會出現 status ok (MyISAM), 但是 InnoDB 的都會出現此訊息: Table does not support optimize, doing recreate + analyze instead.

mysqlcheck -o --all-databases -uroot -p -hlocalhost

訊息如下

wordpress.wp_commentmeta                           Table is already up to date
wordpress.wp_comments                              Table is already up to date
wordpress.wp_croer_meta                            Table is already up to date
wordpress.wp_croer_posts                           Table is already up to date
wordpress.wp_filemeta                              Table is already up to date
wordpress.wp_links                                 Table is already up to date
wordpress.wp_options                               Table is already up to date
wordpress.wp_postmeta                              Table is already up to date
wordpress.wp_posts                                 Table is already up to date
wordpress.wp_statpress                             OK
wordpress.wp_term_relationships                    Table is already up to date
wordpress.wp_term_taxonomy                         Table is already up to date
wordpress.wp_terms                                 Table is already up to date
wordpress.wp_usermeta                              Table is already up to date
wordpress.wp_users                                 Table is already up to date
wordpress.wp_wpb2d_excluded_files
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
wordpress.wp_wpb2d_options
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
wordpress.wp_wpb2d_processed_dbtables
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
wordpress.wp_wpb2d_processed_files
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

優化InnoDB table,請先備份你的資料庫再執行

ALTER TABLE table.name ENGINE='InnoDB';

網路有找到OPTIMIZE TABLE shell script的程式來優化mysql

原文在:http://www.justin.my/2010/09/optimize-only-fragmented-tables-in-mysql/

vim mysql_optimize.sh

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

chmod +x ./mysql_optimize.sh

./mysql_optimize.sh  #打mysql帳密就可以執行

參考資料:

http://blog.longwin.com.tw/2012/03/mysql-myisam-innodb-optimize-2012/

http://www.justin.my/2010/09/optimize-only-fragmented-tables-in-mysql/

點閱: 140

By tony

自由軟體愛好者~喜歡不斷的思考各種問題,有新的事物都會想去學習嘗試 做實驗並熱衷研究 沒有所謂頂天的技術 只有謙虛及不斷的學習 精進專業,本站主要以分享系統及網路相關知識、資源而建立。 Github http://stnet253.github.io

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料