MySQL中關于Table cache該如何設置

這篇文章將為大家詳細講解有關MySQL中關于Table cache該如何設置，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

因為今天突然有個凌晨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略大。

關于MySQL中關于Table cache該如何設置就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。