調整 MySQL 1.3 G DB、8GB Ram、高 IO 等待

我在 8 核虛擬機、8 GB 記憶體上擁有大小為 1.3 GB 的 MySQL 數據庫，並且在優化配置時遇到了麻煩。有很多小行和很多連接正在進行。

我發現了這個工具：https ://tools.percona.com/wizard ，但它所暗示的差異只會使結果惡化。他們主要建議將 InnoDB 緩衝池增加到 6 GB。目前我有 1 GB 緩衝池，但是當我嘗試增加它時，性能實際上更差。為什麼會這樣？

有沒有辦法強制 MySQL 將整個 DB 記憶體在記憶體中，這樣就不會卡在如此高的 IO 等待上？

其餘的設置似乎沒有什麼區別。

my.cnf 的目前狀態：https ://gist.github.com/knyttl/ac6efe5c0730dd34a5cc

查看你的 my.cnf，你有很多東西的預設值。您迫切需要的是對 InnoDB 基礎設施的全面清理。點擊此處查看 InnoDB 基礎架構的圖示。

步驟 01) 執行此查詢

SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,2)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

這將告訴您設置 innodb_buffer_pool_size 有多大（以 MB 為單位）。

假設答案是 1024。

步驟 02) 執行此查詢

SELECT CEILING(Total_MYI_Bytes/POWER(1024,2)) RKBS FROM
(SELECT SUM(index_length) Total_MYI_Bytes FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

這將告訴您設置 key_buffer_size 有多大（以 MB 為單位）。

假設答案是 64。

STEP 03 : 執行這個查詢

SET GLOBAL innodb_fast_shutdown = 0;

這將在關閉期間對事務日誌中剩餘的任何事務執行完整的事務送出。事務日誌是

• /var/lib/mysql/ib_logfile0
• /var/lib/mysql/ib_logfile1

STEP 04 : 將所有數據轉儲到文本文件中

cd /root
mkdir datadump
cd datadump
MYSQLDUMP_CONNECT="-uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges --all-databases"
mysqldump ${MYSQLDUMP_CONNECT} ${MYSQLDUMP_OPTIONS} > MySQLData.sql

步驟 05：關閉 MySQL

service mysql stop

步驟 06：將以下內容添加到my.cnf

如果您使用的是 MySQL 5.5（或啟用了 InnoDB 外掛的 MySQL 5.1），請添加這些

[mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_read_io_threads=16
innodb_write_io_threads=16
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

如果您使用的是 MySQL 5.0，請添加這些

[mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_thread_concurrency=0
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

請注意，我選擇innodb_log_file_size為innodb_buffer_pool_size的 25%

步驟 07：為新的 InnoDB 文件騰出空間

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile`.bak
mv ibdata1 ib_data1.bak

步驟 08：啟動 MySQL

service mysql start

STEP 09 : 載入數據

mysql -uroot -p < /root/datadump/MySQLData.sql

第 10 步：如果一切執行良好，則執行以下命令：

rm -f ib_logfile0.bak
rm -f ib_logfile1.bak
rm -f ibdat1.bak

全部通過 InnoDB Cleanup 完成。

我以前曾多次建議過這樣的 InnoDB 改進

• Oct 29, 2010: Howto: 清理 mysql InnoDB 儲存引擎？
• Apr 13, 2011:如何縮放 my.cnf 設置和變數以從 16GB 伺服器移動到 128GB
• Feb 03, 2012: MySQL InnoDB 中表的計劃優化
• Mar 25, 2012:為什麼 InnoDB 將所有數據庫儲存在一個文件中？
• Apr 01, 2012: innodb_file_per_table 可取嗎？
• Jul 20, 2012:刪除 Magento MySQL 數據庫後回收磁碟空間

試試看 ！！！

引用自：https://serverfault.com/questions/497672