使用MySQLTuner檢查Mysql的設定
使用MySQLTunner(https://github.com/major/MySQLTuner-perl)檢查MySQL的設定
Step1)下載MySQLTunner
# git clone https://github.com/major/MySQLTuner-perl.git
Step2)執行「mysqltuner.pl」,需要登入mysql的帳號root與密碼
#cd MySQLTuner-perl
#./mysqltuner.pl
Step3)執行結果如下。
[!!]表示建議要修正
[!!]Name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
設定「skip-name-resolve=ON」可以修正上述問題,不要啟動Name resolution
[!!] Consider migrating 50 following tables to InnoDB:
有50個資料表使用MyISAM ,建議修改為InnoDB,需要一個一個執行「 ALTER TABLE 資料庫.資料表 ENGINE=InnoDB;」
[!!] Ratio InnoDB redo log capacity / InnoDB Buffer pool size (75%): 100.0M / 128.0M should be equal to 25%
設定「innodb_redo_log_capacity = 32M」
根據建議最後新增以下設定
#sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
skip-name-resolve=ON
key_buffer_size=0
innodb_redo_log_capacity = 32M
innodb_log_buffer_size= 16M
join_buffer_size = 4M
#紀錄花較多時間的查詢
slow_query_log=ON
long_query_time=2
slow_query_log_file='/var/log/mysql/slow_query.log'
Step4)重新啟動mysql
#service mysql restart
Step5)重新執行「mysqltuner.pl」