MySQL中關(guān)于Table cache該如何設(shè)置

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

因?yàn)榻裉焱蝗挥袀€(gè)凌晨5點(diǎn)值班任務(wù)，隨意翻起同事的書(shū)來(lái)，看到表對(duì)象緩存，這個(gè)跟自己平時(shí)理解，稍微有差異。所以整理了一下table_definition_cache，table_open_cache和table_open_cache_instances。

先看看官網(wǎng)怎么說(shuō):

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.

理解下來(lái)，就是控制總frm文件的數(shù)量，還是個(gè)hash表，內(nèi)部維護(hù)。如果打開(kāi)的表實(shí)例的數(shù)量超過(guò)了table_definition_cache設(shè)置，

LRU機(jī)制將開(kāi)始標(biāo)記表實(shí)例以進(jìn)行清除，并最終將它們從數(shù)據(jù)字典緩存中刪除。

簡(jiǎn)單通俗點(diǎn)frm文件有多少，就設(shè)置多少了。

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

所有線程打開(kāi)的表的數(shù)量。增加這個(gè)值會(huì)增加mysqld需要的文件描述符的數(shù)量。可以通過(guò)檢查Opened_tables狀態(tài)變量來(lái)檢查是否需要增加表緩存。

是不是可以理解為ibd/MYI/MYD 文件，打開(kāi)數(shù)量了。但mysql內(nèi)部需要對(duì)表進(jìn)行操作的時(shí)候，第一需要找到最上層文件的句柄信息，table_open_cache_instances是能提供的，之后對(duì)應(yīng)的尋找ibd,MYI,MYD文件。官網(wǎng)對(duì)于這部分沒(méi)有明確說(shuō)明

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.

打開(kāi)的表緩存實(shí)例的數(shù)量。為了通過(guò)減少會(huì)話間的爭(zhēng)用來(lái)提高可伸縮性，可以將打開(kāi)的表緩存劃分為幾個(gè)大小為table_open_cache / table_open_cache_instances的較小緩存實(shí)例。一個(gè)會(huì)話只需要鎖定一個(gè)實(shí)例就可以訪問(wèn)DML語(yǔ)句。寫(xiě)到這里就已經(jīng)大致了解到 如下關(guān)系:

table_definition_cache > table_open_cache_instances > table_open_cache

4.table相關(guān)的限制有哪些?

mysql是多線程，對(duì)于并發(fā)同一個(gè)文件，不同數(shù)據(jù)的情況下，會(huì)打開(kāi)多個(gè)文件，會(huì)存在哪些限制呢?下面是源代碼里邏輯是怎樣

1)table_definition_cache

.frm文件其實(shí)最大值只能到2000,跟官網(wǎng)給得最大值沒(méi)關(guān)系

Max值和說(shuō)明有沖突，實(shí)際確認(rèn)下來(lái)就是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有關(guān)，需要借助于table open file 的信息

3)max_connections超出打開(kāi)文件數(shù)量的伐值的時(shí)候，也跟table_open_cache有關(guān)

4)table_cache_size 計(jì)算方式

備注：TABLE_OPEN_CACHE_MIN=table_open_cache

5.定期查看open table 情況，

通過(guò) show global status like ‘%Open%_table%’; 確認(rèn)是否調(diào)優(yōu)這個(gè)參數(shù)

6.常見(jiàn)故障應(yīng)對(duì)：

如：在運(yùn)行數(shù)據(jù)庫(kù)通過(guò) show processlist 可看到大量的 Opening tables、closing tables狀態(tài)，導(dǎo)致應(yīng)用端訪問(wèn)操作。

需要確認(rèn) table_open_cache=最大并發(fā)數(shù)表數(shù)量(join里可能用到2張表)，時(shí)候滿足當(dāng)前配置

如：但并發(fā)線程數(shù)達(dá)到1000，假設(shè)這些并發(fā)連接中有40%是訪問(wèn)2張表，其他都是單表，那么cache size就會(huì)達(dá)到(100040%2+100060%*1)=1400

建議定期監(jiān)控值：

Open_tables / Opened_tables >= 0.85 表的重復(fù)使用率

Open_tables / table_open_cache <= 0.95 緩存里存在已打開(kāi)的表

1)5.7版本已經(jīng)支持在線動(dòng)態(tài)改配置信息

set global table_definition_cache=2000;

set global table_open_cache=3000;

set global max_connection= 2000;

table_open_cache_instances參數(shù)修改需要重新啟動(dòng)服務(wù)。

2)無(wú)法更改的時(shí)候，可通過(guò)flush操作，但存在問(wèn)題

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操作，有如下隱患：

關(guān)閉所有打開(kāi)的表，強(qiáng)制關(guān)閉所有正在使用的表，并刷新查詢緩存和準(zhǔn)備好的語(yǔ)句緩存。FLUSH TABLES還會(huì)從查詢緩存中刪除所有查詢結(jié)果，比如RESET查詢緩存語(yǔ)句。

備注：

另外 table_definition_cache為每個(gè)表的表空間中可以同時(shí)打開(kāi)的InnoDB文件的數(shù)量定義了一個(gè)軟限制，這也是由innodb_open_files控制的。

如果設(shè)置了table_definition_cache和innodb_open_files，則使用最高設(shè)置。如果兩個(gè)變量都沒(méi)有設(shè)置，則使用默認(rèn)值更高的table_definition_cache。

總結(jié)：

Table緩存關(guān)于的參數(shù)table_definition_cache，table_definition_cache，table_open_cache_instances 按照實(shí)際環(huán)境和需求進(jìn)行設(shè)置外，還有跟max_connections也要設(shè)置合理。有些環(huán)境里發(fā)現(xiàn)max_connections過(guò)大，過(guò)小設(shè)置的問(wèn)題，設(shè)置過(guò)大可能會(huì)存在等待的情況

這些參數(shù)控制不好，會(huì)給MySQL數(shù)據(jù)庫(kù)系統(tǒng)帶來(lái)性能上的瓶頸。如果把握不是很準(zhǔn)，有個(gè)很保守的設(shè)置建議：把MySQL數(shù)據(jù)庫(kù)放在生產(chǎn)環(huán)境中試運(yùn)行一段時(shí)間，然后把參數(shù)的值調(diào)整得比Opened_tables的數(shù)值大一些，并且保證在比較高負(fù)載的極端條件下依然比Opened_tables略大。

關(guān)于MySQL中關(guān)于Table cache該如何設(shè)置就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。