曹嘯，民生銀行信息科技部MySQL DBA，目前主要致力於MySQL源碼及相關特性研究工作。在銀行科技行業工作多年，兼具開發及運維工作經驗，同時對銀行多個業務領域亦有涉獵。
一、引言
MySQL數據庫自問世以來，就因它的體積小、速度快、低成本等優勢受到眾多企業的追捧。同時由於它的完全開源特性，更增進了廣大數據庫愛好者對其深入研究的興趣，通過源碼的研究與探索，MySQL越來越多的優秀特性被廣泛挖掘出來。
本文將圍繞MySQL table-cache相關參數進行相應的源碼解讀及性能分析，旨在為使用MySQL的眾多數據庫工程師提供一些實際開發或運維工作的助益。
二、參數源碼解讀
table-cache相關參數具體包括：
open_files_limit；
max_connections；
table_open_cache；
table_definition_cache。
MySQL實例進程在啟動時會根據配置文件my.cnf中對這四個參數的設置進行自適應的調整生效，由於MySQL在設置這四個參數時存在嚴格的順序和依賴關係，故將它們放在一起分析討論。
首先看一下源碼中關於這四個參數的自適應關係函數（源碼位於MySQLd.cc），該函數在main函數中被調用，內部分別調用了各自的設置函數。
void adjust_related_options(ulong *requested_open_files)
{
…
adjust_open_files_limit(requested_open_files);
adjust_max_connections(*requested_open_files);
adjust_table_cache_size(*requested_open_files);
adjust_table_def_size;
}
由於這四個設置函數存在嚴格的順序調用關係，下文將依次對各個函數進行拆分說明。
1、open_files_limit
該參數作用為限定了MySQL實例進程能打開的文件描述符最大值。關於該參數設置函數的聲明：
void adjust_open_files_limit(ulong *requested_open_files)
函數中的參數requested_open_files為指針變量，這是一個貫穿前後的指針，它指向的內存中所存儲的內容將會被後續函數多次用到。
limit_1= 10 + max_connections + table_cache_size * 2;
limit_2= max_connections * 5;
limit_3= open_files_limit ? open_files_limit : 5000;
request_open_files= max<ulong>(max<ulong>(limit_1, limit_2), limit_3);/<ulong>/<ulong>
由於該參數首先被設置，所以這部分計算邏輯所取用的變量均來自配置文件中的設置值（源碼中的table_cache_size對應配置文件中的table_open_cache），根據計算後得出的limit_1,2,3將取最大值存放在變量request_open_files中。
PS：此處需注意的是，request_open_files和requested_open_files是不同的。
effective_open_files= my_set_max_open_files(request_open_files);
open_files_limit= effective_open_files;
隨後，函數my_set_max_open_files會以request_open_files作為參數，來計算effective_open_files的值。此函數中的計算邏輯會根據不同的系統環境選擇不同分支，但因本文的分析和測試均針對服務器，故以Linux分支為主。
不同的分支計算邏輯略有不同，在Linux環境下，函數中會調用系統函數getrlimit和setrlimit來獲取系統資源的最大使用量，在系統允許範圍內，計算所得到的effective_open_files的值與request_open_files的值相等。筆者所用測試物理機測得的系統允許上限值為1024*1024。
此外，另一個可能影響effective_open_files數值的是MySQL實例的啟動方式:
若MySQL實例在非root下啟動，則effective_open_files的值會受到系統對於單進程句柄的限制，即命令ulimit –n得到的值；
若在root下啟動實例，則不會受到系統對單進程的限制。
計算後所得的effective_open_files的值會賦給open_files_limit，作為實例中最終生效的實際參數。
概括來講，在未達到最大值時，計算所得的effective_open_files 與request_open_files 值相等，並將effective_open_files的最終值賦給open_files_limit；若超過最大值則effective_open_files會以配置文件中的open_files_limit設置值生效，若配置文件中設置也超限，則取系統對單進程文件描述符的限制做生效值。為了更清晰的說明此處邏輯，筆者進行了以下對比測試。
為了便於更直觀的看清測試結果，我們更改系統單進程文件描述符限制為56789，更改系統對全局文件描述符限制為655350。然後分別使用下列三種配置文件啟動實例：
配置一： 配置文件中設置open_files_limit值為1040000。
使用非root用戶啟動MySQL實例，進入數據庫查看變量生效值：
此時是以系統對單進程文件描述符限制生效的。
關閉實例進程，使用root用戶啟動MySQL實例，再次進入數據庫查看變量生效值：
此時則以配置文件中的設置值生效。
配置二：配置文件中設置table_open_cache=520000，open_files_limit=655350。
在root用戶下啟動實例，可以看到此條件下可以按照配置文件生效：
隨後，更改配置文件中設置table_open_cache=530000，open_files_limit不變，在root用戶下重啟實例，觀察變量生效情況：
產生變化的原因是根據修改的table_open_cache值計算所得的effective_open_files會超出系統允許的上限，故MySQL會使用配置文件中的設置生效，並重新計算table_open_cache的值。
配置三：配置文件設置table_open_cache=530000，open_files_limit=1049000。
在root用戶下重啟實例，觀察變量生效情況：
此時由於計算所得的effective_open_files和配置文件中的open_files_limit設置值均超限，故使用系統對單進程文件描述符限制作為生效值，並進一步計算得到實際的table_open_cache。
讓我們回到源碼中繼續解讀。在設置了open_files_limit生效值後，MySQL源碼中通過封裝C語言的標準輸出函數實現了自己的輸出函數，並在一定條件下向error.log中輸入相應信息。
if (effective_open_files < request_open_files)
{
if (open_files_limit == 0)
{
sql_print_warning("Changed limits: max_open_files: %lu (requested %lu)", effective_open_files, request_open_files);
}
else
{
sql_print_warning("Could not increase number of max_open_files to "
"more than %lu (request: %lu)", effective_open_files, request_open_files);
根據源碼邏輯，當effective_open_files小於request_open_files的值時，error.log中就會記錄相應信息。同時又根據配置文件中是否設置了open_files_limit的值來輸出不同內容的錯誤信息。以前文配置一為例，非root用戶啟動MySQL計算所得的effective_open_files小於request_open_files，查看error.log中的信息有如下內容：
對於root用戶啟動，由於比較條件不滿足，則無相應信息輸出。
if (requested_open_files)
*requested_open_files= min<ulong>(effective_open_files, request_open_files);/<ulong>
函數體的最後在effective_open_files和request_open_files中取小值放在了指針requested_open_files所指的內存中，以便於後續函數對該變量的調用。
2、max_connections
該參數限制了MySQL實例允許的最大連接數，在數據庫的維護工作中相較於其他參數也更容易直觀的接觸到，下面讓我們看一下在源碼中是如何對這個參數進行設置及生效的。
void adjust_max_connections(ulong requested_open_files)
此函數中的參數requested_open_files變量值即為前文函數中requested_open_files指針變量所指內容。關於max_connections的計算邏輯則相對簡單。
limit= requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;
if (limit < max_connections)
{
sql_print_warning("Changed limits: max_connections: %lu (requested %lu)",limit, max_connections);
max_connections= limit;
}
首先根據算式計算limit變量的值，此處用到的TABLE_OPEN_CACHE_MIN是在頭文件sql_const.h中定義的宏定義變量，值為400。
隨後將limit的值與配置文件中設定的max_connections進行比較，若limit較小，則向error.log中輸出一串提示信息，並以limit作為最終生效值；若limit較大，則直接以配置文件的設置值生效，同時不打印提示信息。以下為操作實例：
設置配置文件中max_connections值為5000，其他配置沿用前文。以非root用戶啟動MySQL實例，在數據庫中查看max_connections的值：
此時max_connections按照配置文件中的設置進行生效，原因在於非root用戶啟動實例，requested_open_files取系統限定的effective_open_files值為56789，計算limit=56789-10-400*2=55979，該值遠大於5000，故取較小值即配置文件中的設置值生效。
保持同樣的條件不變，僅更改配置文件中的max_connections設置值為60000大於55979，使用非root用戶重啟MySQL實例，再次查看max_connections生效值，已經更新為預期的55979了。
3、table_open_cache
此參數規定了內存中允許打開表的數量，從它的作用可以看出，當MySQL在處理查詢請求時，table_open_cache將會起到較大作用，有效設置並使用此參數可以降低熱點表的頻繁開關動作，從而改善性能。關於該參數設置的源碼如下：
void adjust_table_cache_size(ulong requested_open_files)
limit= max<ulong>((requested_open_files - 10 - max_connections) / 2,/<ulong>
TABLE_OPEN_CACHE_MIN);
if (limit < table_cache_size)
{
sql_print_warning("Changed limits: table_open_cache: %lu (requested %lu)",limit, table_cache_size);
table_cache_size= limit;
}
對比max_connections的實現函數可以看出，兩個函數的內部結構相似，都是以requested_open_files為參數，根據各自的邏輯計算出limit變量的值，並將其與配置文件中的設置值比較，取更小的值作為最終生效值。需要注意的是，table_open_cache在源碼文件sys_vars.cc中被限定了取值範圍(0,512*1024)，這個範圍會對table_open_cache的實際生效值產生影響。下面看一些配置實例：
設置配置文件中table_open_cache=30000，max_connections=5000，在非root用戶下啟動實例。
此模式下，requested_open_files=56789,max_connections生效值5000，算式(requested_open_files - 10 - max_connections) / 2值為25889>400，故limit=25889，同時小於配置文件中的設置值，故最終生效值應為25889。在數據庫中查看變量的實際生效值：
結果符合預期。
保持其他條件不變，更改配置文件中table_open_cache值為20000，再用非root用戶重啟實例，此時limit值仍為25889，但已大於配置文件中的設置值，故生效值應為20000。到數據庫中查看實際生效值驗證。
4、table_definition_cache
此參數乍一看很容易與table_open_cache相混淆，畢竟這對“孿生兄弟”從生效到功能都有相近之處。table_definition_cache定義了內存中可打開的表結構數量。此參數的設置函數與前列很大的一個不同點是沒有使用requested_open_files作為參數，僅僅聲明瞭一個無參函數：
void adjust_table_def_size
此參數的設置算式僅使用了已生效的table_open_cache作為計算變量。
default_value= min<ulong> (400 + table_cache_size / 2, 2000);/<ulong>
table_open_cache函數中有一個很值得關注的點，在函數體內涉及了MySQL動態哈希鏈表的訪問。這個鏈表是MySQL在啟動主函數中定義並且用來存放部分系統變量的動態鏈表，下面我們來詳細瞭解一下函數內部的訪問流程。
首先函數體內定義了一個系統變量類型的指針var，隨後調用函數訪問哈希表，並將定位到的內存塊賦值給類對象var。
var= intern_find_sys_var(STRING_WITH_LEN("table_definition_cache"));
此處STRING_WITH_LEN是一個宏定義，返回內容為對應字符串本身及長度。我們到intern函數內部看一下：
var=(sys_var*)my_hash_search(&system_variable_hash,(uchar*) str, length ? length : strlen(str));
return var;
intern函數內部調用my_hash_search函數查找table_definition_cache字符串在哈希鏈表中對應的結點，並將找到的位置返回類對象指針var。在找到var指針的目標結點後，table_definition_cache設置主函數就把前面計算所得到的值寫入var所指向的內存。
var->update_default(default_value);
值得注意的是，即便函數已將計算結果賦給目標結點，但函數依然會首先判斷配置文件中是否設置了table_definition_cache的值。若配置文件中未設置，則以計算所得的結果作為生效值；若配置文件中有對應設置，則優先以配置文件中的設置生效。
if (! table_definition_cache_specified)
table_def_size= default_value;
另一個需要注意的是雖然MySQL默認配置文件中設置的table_definition_cache優先生效，但在頭文件sql_const.h中宏定義了table_definition_cache的下限值為400，故即便在配置文件中設置了一個很小的值，MySQL也會自動將生效值上調為下限值。下面看一些對應的配置實例：
我們選擇前例中requested_open_files=56789，max_connections=5000，table_open_cache=25889的條件進行測試。
首先，我們在配置文件中設置table_definition_cache=35486並將其註釋，保存文件。
重啟MySQL實例，令修改的配置文件生效。根據前面對源碼的分析，計算公式default_value= min<ulong> (400 + table_cache_size / 2, 2000)=2000，此時配置文件中的設置無效，應以計算結果生效。進入數據庫中查看生效值：/<ulong>
然後修改配置文件，取消table_definition_cache的相關注釋，保存配置文件並重啟實例，到數據庫中查看生效值：
此時已按照配置文件設置生效。
最後為驗證MySQL對table_definition_cache下限的自適應調整，我們修改配置文件中的對應值為table_definition_cache=15並保存，重啟，再次進入數據庫查看生效值：
可以看到，生效值已被MySQL自適應調整為源碼中宏定義的下限值。
至此，上文已完成對table_cache相關的源碼分析內容，當我們在實際工作中需要調整相關參數，可以參考前文並配置。現在讓我們進一步思考，在知曉了這些參數間的關聯和配置方法後，如何設置相應的值才算合理？這些參數對MySQL的實際使用性能又會有多大的影響？下文將會對這部分內容進行測試分析。
三、 參數性能影響及測試分析
上文介紹了table-cache相關參數在MySQL數據庫正常運轉過程中存在至關重要的作用，但並不是每個參數的微調都會對性能產生顯著的影響，以下將對它們逐一進行說明。
嚴格來講，open_files_limit和max_connections對MySQL的重要性並非體現在性能方面。
對於open_files_limit來說，不合理的設置將會直接導致MySQL實例down掉或在啟動時根本無法正常拉起進程。對於這些場景，MySQL會有簡單直接的錯誤信息來提示DBA需要進行相應的調整。
對於max_connections來說，設置過大可能會對其它參數的生效產生影響，設置過小又無法滿足業務高峰時的連接需求，從而造成大量的連接等待和超時。通常根據實際情況設置在能夠滿足業務峰值的大小即可。
基於上述原因，這兩個參數的性能影響在此不做深入探討，而把重點放在對另外兩個參數的測試及分析。
1、table_definition_cache(TDC)
本節對TDC可能產生的性能影響進行測試分析。使用的MySQL測試版本為5.7.18，測試服務器為單實例單庫，庫中共建立40000張表，每張表內5000行數據。測試條件為保證其他參數一致的前提下分別設置TDC=10000和TDC=50000進行對比。
首先比較兩種場景下全局變量的區別，分別修改配置文件中TDC設置值為10000和50000，再分別重啟實例，查看相關全局變量如下：
TDC=10000
TDC=50000
通過對比可以看出，重啟後兩種場景的TDC生效值分別已達到預期的10000和50000。同時，我們在此處對比另外一個變量open_table_definitions，這個變量表示在當前狀態下打開的表結構數量，兩種場景下在MySQL實例剛剛重啟後由於TDC的不同，打開的表結構數量是不同的：
TDC=10000的場景下打開的表結構數量被限制在10000；
TDC=50000的場景下實例中的表結構全部被打開（其中有40000張測試表+219張系統表）。
對比結果說明當TDC生效值大於庫中全表數量時，實例啟動時會將所有表結構加載到內存；當TDC生效值小於庫中全表數量時，MySQL會按照TDC的生效值加載相應數量的表結構到內存。
隨後我們來比較TDC的差別是否會對性能產生影響。測試使用sysbench工具模擬客戶端向服務器發送請求，申請訪問的表數量為35000，連接併發數為50，連接發送請求持續時間為5min，使用以上測試模式分別測試TDC=10000和TDC=50000兩條件下四種基本SQL語句的qps，對比結果如下：
從測試結果對比可以看出，不同的TDC對MySQL基準性能的影響並不大。那麼導致這樣的因素是什麼？
我們查看兩個測試後全局狀態變量open_table_definitions的值進行對比：
TDC=10000
TDC=50000
通過比較可以發現，雖然TDC設置值為10000的模式無法在啟動實例時將所有表結構都加載入內存，但在實際請求到達服務器時，MySQL允許在內存中“超載”TDC定義的表結構數量，這就使得實例當前已打開的表結構數量可超過TDC的限定值。同時，對於單一表來說，重複訪問並不會增加表結構的打開次數。因此，TDC對於性能的影響便不是很大了。
2、table_open_cache（TOC）
本節測試TOC可能產生的性能影響。使用的MySQL版本及測試庫環境與TDC測試相同。首先比較兩種不同TOC對性能帶來的影響。根據前文，使用root和非root用戶啟動實例會導致open_files_limit的生效值有區別，進而影響TOC的生效值。本例在配置文件中設置TOC值為350000，再分別使用root及非root用戶啟動實例，觀察實際生效值如下：
root用戶啟動：
非root用戶啟動：
由於TOC決定了內存中打開表的數量，功能上對查詢SQL的影響更為明顯，故使用sysbench僅發送select語句並統計qps，申請訪問的表數量是35000，訪問持續時間為5min。更改不同的連接併發數分別測試qps並繪製成折線圖，結果如下：
從曲線圖可以看出，TOC較大（=350000）的條件下，查詢語句的qps隨併發數增加變化比較明顯，整體呈現先迅速上升後平穩回落的趨勢，在64-128併發範圍內達到高峰。反觀TOC較小（=30262）的模式下qps隨併發數的增加無明顯變化，而且持續處於較低水平。
為了更加清晰的分析TOC對性能的影響，我們統計了每個併發下獨立測試的緩存命中率並繪製成曲線進行比較：
比較二者命中率曲線可以看出，TOC較大的模式下，緩存命中率隨併發數的變化趨勢與qps曲線基本一致，雖然存在一定的波動，但整體命中率均在99%以上，這時我們可以判定緩存的效率是較高的；但TOC較小的模式下，緩存命中率在不同併發數下普遍較差（低於50%），同時隨併發數的增加還有急速下降的趨勢。
我們再統計一下各個併發測試下的table_open_cache_overflows的值繪製成曲線並觀察：
從曲線圖可以看出，TOC較大的模式下overflow值非常小（基本為0），這與該模式下緩存命中率較高的結果相吻合；但TOC較小的模式下則存在較大的overflow，這說明該模式下較小的緩存無法滿足大量的併發訪問請求，緩存不得不持續將新表刷入內存。
四、總結
通過本文對table-cache相關源碼的分析及對比測試，我們可以得出一些相關結論：
open_files_limit，max_connections， table_open_cache， table_definition_cache四個參數在MySQL實例啟動時依次生效，且相互存在制約關係，若需要單獨更改某一變量，要注意可能產生的影響；
open_files_limit參數的生效會受到不同啟動方式的影響進而影響其它參數的生效值，設置時要按需選擇；
open_files_limit和max_connections參數對性能影響較小，設置時可滿足業務量需求即可，二者更多影響的是MySQL實例的正常運作；
table_definition_cache參數對性能影響較小；
table_open_cache參數對實際性能影響較大，但生效值需要在上下限間合理設置，為儘可能發揮cache性能，生效值應設置為大併發下可維持較高命中率的同時不發生緩存overflow。
以上是本文全部內容，希望讀到此文的廣大前輩和同行能夠積極提出文中的不足並不吝指正。
近期熱文
近期活動
閱讀更多 dbaplus社群 的文章
