MySQL 關於Table cache設定

原文連結：https://www.modb.pro/db/15158

摘要：mysql table相關引數理解

因為今天突然有個凌晨5點值班任務，隨意翻起同事的書來，看到表物件快取，這個跟自己平時理解，稍微有差異。所以整理了一下table_definition_cache，table_open_cache和table_open_cache_instances。

先看看官網怎麼說:

1.table_definition_cache

the number of table definitions (from .frm files) that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.

理解下來，就是控制總frm檔案的數量，還是個hash表，內部維護。如果開啟的表例項的數量超過了table_definition_cache設定，

LRU機制將開始標記表例項以進行清除，並最終將它們從資料字典快取中刪除。

簡單通俗點frm檔案有多少，就設定多少了。

2.table_open_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable

所有執行緒開啟的表的數量。增加這個值會增加mysqld需要的檔案描述符的數量。可以通過檢查Opened_tables狀態變數來檢查是否需要增加表快取。

是不是可以理解為ibd/MYI/MYD 檔案，開啟數量了。但mysql內部需要對錶進行操作的時候，第一需要找到最上層檔案的控制程式碼資訊，table_open_cache_instances是能提供的，之後對應的尋找ibd,MYI,MYD檔案。官網對於這部分沒有明確說明

3.table_open_cache_instances

The number of open tables cache instances. To improve scalability by reducing contention among sessions, the open tables cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances . A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables.

開啟的表快取例項的數量。為了通過減少會話間的爭用來提高可伸縮性，可以將開啟的表快取劃分為幾個大小為table_open_cache / table_open_cache_instances的較小快取例項。一個會話只需要鎖定一個例項就可以訪問DML語句。寫到這裡就已經大致瞭解到 如下關係:

table_definition_cache > table_open_cache_instances > table_open_cache

4.table相關的限制有哪些?

mysql是多執行緒，對於併發同一個檔案，不同資料的情況下，會開啟多個檔案，會存在哪些限制呢?下面是原始碼裡邏輯是怎樣

1)table_definition_cache

.frm檔案其實最大值只能到2000,跟官網給得最大值沒關係

Max值和說明有衝突，實際確認下來就是2000

2)open_files_limit

limit_1= 10 + max_connections + table_cache_size * 2;

limit_2= max_connections * 5;

limit_3= open_files_limit ? open_files_limit : 5000;

可以看出max_connections有關，需要藉助於table open file 的資訊

3)max_connections超出開啟檔案數量的伐值的時候，也跟table_open_cache有關

4)table_cache_size 計算方式

備註：TABLE_OPEN_CACHE_MIN=table_open_cache

5.定期檢視open table 情況，

通過 show global status like ‘%Open%_table%’; 確認是否調優這個引數

6.常見故障應對：

如：在執行資料庫通過 show processlist 可看到大量的 Opening tables、closing tables狀態，導致應用端訪問操作。

需要確認 table_open_cache=最大併發數表數量(join裡可能用到2張表)，時候滿足當前配置

如：但併發執行緒數達到1000，假設這些併發連線中有40%是訪問2張表，其他都是單表，那麼cache size就會達到(100040%2+100060%*1)=1400

建議定期監控值：

Open_tables / Opened_tables >= 0.85 表的重複使用率

Open_tables / table_open_cache <= 0.95 快取裡存在已開啟的表

1)5.7版本已經支援線上動態改配置資訊

set global table_definition_cache=2000;

set global table_open_cache=3000;

set global max_connection= 2000;

table_open_cache_instances引數修改需要重新啟動服務。

2)無法更改的時候，可通過flush操作，但存在問題

MySQL closes an unused table and removes it from the table cache under the following circumstances: When the cache is full and a thread tries to open a table that is not in the cache.When the cache contains more than table_open_cache entries and a table in the cache is no longer being used by any threads.When a table-flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.

這裡好奇FLUSH TABLE操作，有如下隱患：

關閉所有開啟的表，強制關閉所有正在使用的表，並重新整理查詢快取和準備好的語句快取。FLUSH TABLES還會從查詢快取中刪除所有查詢結果，比如RESET查詢快取語句。

備註：

另外 table_definition_cache為每個表的表空間中可以同時開啟的InnoDB檔案的數量定義了一個軟限制，這也是由innodb_open_files控制的。

如果設定了table_definition_cache和innodb_open_files，則使用最高設定。如果兩個變數都沒有設定，則使用預設值更高的table_definition_cache。

總結：

Table快取關於的引數table_definition_cache，table_definition_cache，table_open_cache_instances 按照實際環境和需求進行設定外，還有跟max_connections也要設定合理。有些環境裡發現max_connections過大，過小設定的問題，設定過大可能會存在等待的情況

這些引數控制不好，會給MySQL資料庫系統帶來效能上的瓶頸。如果把握不是很準，有個很保守的設定建議：把MySQL資料庫放在生產環境中試執行一段時間，然後把引數的值調整得比Opened_tables的數值大一些，並且保證在比較高負載的極端條件下依然比Opened_tables略大。

希望工作中，有幫助。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31556440/viewspace-2682015/，如需轉載，請註明出處，否則將追究法律責任。