MySQL Basic Tunning

環境說明:目前我用的是 64bit Intel 4 核心 2.4GHz CPU 的機器加上 8 GB 的 RAM ,硬碟選用一般的 SATA 介面,我的資料庫每小時會執行大概 30 萬個查詢 ( 295.73 k ),大約每秒 (80) 個查詢,每天 1000 萬次查詢,目前暫時沒有 Replication 機制 (想增加)。

最近常常 SHOW STATUS 看效能,而之前最常遭遇到的問題是:

  • Handler_read_rnd_next (82K) 過大,解法:當進行大量排序查詢的時候不理想,或許可以調高 read_buffer_size 。
  • Handler_read_rnd (32G) 過大,描述:表示每次 query 都必須要直接掃整個 table 而且 index 可能沒有發揮作用,但是我不曉得是不是 Active Record 造成的,因為我 WHERE 和 ORDER BY 選用的欄位都有檢查過了,應該都建了 index
  • Qcache_lowmem_prunes (40K) 描述:我應該要把 query cache 調小,因為每次查出來結果都不同(鮮少重複的查詢),所以不需要調太大。
  • Created_tmp_disk_tables (1,725 ) 過大,在磁碟上建立的臨時 table 太大,應該要調高 tmp_table_size 的數值(記憶體中建立臨時 table ),以改善效能。
  • Sort_merge_passes (24) 過大,應該要增加 sort_buffer 的值。
  • Opened_tables (2639) 過大,則要增加 table_cache 的值。
  • Table_locks_waited (112K) 過大,雖然我的 Table_locks_immediate 有 12M,而 Table_locks_waited 有 112K ,發生鎖定等待的情況大約是比例上為 1/100 (一百次鎖定中,可以直接鎖定 99 次,有一次要等待)

今天在 Matt Yonkovit 的 Big DBA Head 看到了他提出 MySQL 設定檔的範例 ( MySQL 本身也都會附上不同環境的設定例範,我的機器之前是以 my-huge.cnf 作為參考設定。):

64 bit system – 假設 64 bit CPU 以及作業系統
8GB+ of memory – 超過 8 GB 記憶體
Dedicated DB Box – 專職的資料庫伺服器
All MyISAM – 使用 MyISAM 儲存引擎
[mysqld]
thread_cache_size = 256
table_cache = 1024
key_buffer = 4000M
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
max_allowed_packet = 1M
tmp_table_size=64M
max_heap_table_size=64M
query_cache_size=128M
myisam_recover = backup
myisam_sort_buffer_size=512M
skip-innodb

參考網址:5 Minute DBA MyISAM Example Config Files

備註:Big DBA Head 的作者是 Matt Yonkovit ,之前是昇陽下面 MySQL 的資深顧問,有 11 年 Oracle 的經驗、9 年的 MySQL 的經驗、以及 8 年的 SQL Server 經驗。

Posted in DB
0 comments on “MySQL Basic Tunning
1 Pings/Trackbacks for "MySQL Basic Tunning"
  1. […] 資料參考:http://blog.roga.tw/2009/06/mysql-basic-tunning/ 將此篇文章分享給好友 […]

Leave a Reply

Your email address will not be published.