MySQL table cache 和 open table 的效能影響

最近同事跑程式時, 發現做大量 join update 時, 偶而會很慢, 但是 flush table 後就恢復正常。依官網所言, flush tables 做兩件事

• 清 query cache (等同於 reset query cache)
• 關掉所有打開的 table

經同事測試, 執行 reset query cache 無效, 剩下的原因只剩關 table。這讓我百思不得其解, 不就是開關檔, 怎麼會有效能差異? 後來經 Tib 提醒, 用 "show global status like '%open%'" 一看, 發覺相當地不得了。table_cache 預設值是 64, 開啟 MySQL server 至今 (應該不到一個月), 竟然開了一百多萬次 table, hit rate 是 0%  (可以用 mysqltuner.pl 摘要 show status 的結果)。

研究一下官網的 How MySQL Opens and Closes Tables 後 (下面留言也很有幫助), 得到以下的心得:

• 當 table_cache 沒東西或新增 temporary table 時, 會增加 Opened_tables, 這個變數存的是 MySQL server 開啟至今, 開啟 table 的次數。
• show tables 或操作 information_schema 會一口氣開一堆檔案, 若用到多個 database, 大家都來個幾下 show tables, 很快就會塞爆 table_cache, 所以不能設太小
• 但若 table_cache 大於等於 open_files_limit, MySQL 執行一會兒後會停住。查看 /var/log/syslog 後發現 errno 24, 結果是超出能開檔的數量限制, 讓 MySQL 無法做事, 因為 table_cache 占掉所有能開檔的數量, 結果要再存取別的 table 時, 卻無法再開檔了。將 open_files_limit 設得比 table_cache 大, 就沒事了
• table_cache 也不能設太大, 照這篇的說法, cache 滿的時候, 找 expired record 會很花時間

調大 table_cache 後, 程式有順利跑完, 但原本就是偶而會發生的事, 所以還要再多觀察一陣子, 才能確定是否有解掉問題。下次再發生時, 再來觀察 status 裡和開關 table 有關的記錄, 確定是否有關。

btw, Set ulimit parameters on ubuntu 說明如何改變 ulimit 的限制。照 MySQL 官網的說法, 改 my.cnf 應該能提高 open_files_limit, 若不行的話, 再來試看看改變 Ubuntu ulimit 開檔的預設值 (1024)。不過那篇文章提到要先設定 PAM 再重開機, server 不方便隨便重開測試, 先備忘吧。

2011-04-09 更新

試過改 my.cnf 無效, 改成修改 /etc/init.d/mysql , 將 /usr/bin/mysqld_safe 加上 --open-files-limit=N 後, 就 OK 了。