[[233746]]

一、引言

MySQL數(shù)據(jù)庫自問世以來，就因它的體積小、速度快、低成本等優(yōu)勢受到眾多企業(yè)的追捧。同時由于它的完全開源特性，更增進了廣大數(shù)據(jù)庫愛好者對其深入研究的興趣，通過源碼的研究與探索，MySQL越來越多的優(yōu)秀特性被廣泛挖掘出來。

本文將圍繞MySQL table-cache相關(guān)參數(shù)進行相應的源碼解讀及性能分析，旨在為使用MySQL的眾多數(shù)據(jù)庫工程師提供一些實際開發(fā)或運維工作的助益。

二、參數(shù)源碼解讀

table-cache相關(guān)參數(shù)具體包括：

• open_files_limit；
• max_connections；
• table_open_cache；
• table_definition_cache。

MySQL實例進程在啟動時會根據(jù)配置文件my.cnf中對這四個參數(shù)的設(shè)置進行自適應的調(diào)整生效，由于MySQL在設(shè)置這四個參數(shù)時存在嚴格的順序和依賴關(guān)系，故將它們放在一起分析討論。

首先看一下源碼中關(guān)于這四個參數(shù)的自適應關(guān)系函數(shù)（源碼位于MySQLd.cc），該函數(shù)在main函數(shù)中被調(diào)用，內(nèi)部分別調(diào)用了各自的設(shè)置函數(shù)。 

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();  
}    

由于這四個設(shè)置函數(shù)存在嚴格的順序調(diào)用關(guān)系，下文將依次對各個函數(shù)進行拆分說明。

1、open_files_limit

該參數(shù)作用為限定了MySQL實例進程能打開的文件描述符最大值。關(guān)于該參數(shù)設(shè)置函數(shù)的聲明： 

void adjust_open_files_limit(ulong *requested_open_files) 

函數(shù)中的參數(shù)requested_open_files為指針變量，這是一個貫穿前后的指針，它指向的內(nèi)存中所存儲的內(nèi)容將會被后續(xù)函數(shù)多次用到。 

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);  

由于該參數(shù)首先被設(shè)置，所以這部分計算邏輯所取用的變量均來自配置文件中的設(shè)置值（源碼中的table_cache_size對應配置文件中的table_open_cache），根據(jù)計算后得出的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;  

隨后，函數(shù)my_set_max_open_files會以request_open_files作為參數(shù)，來計算effective_open_files的值。此函數(shù)中的計算邏輯會根據(jù)不同的系統(tǒng)環(huán)境選擇不同分支，但因本文的分析和測試均針對服務器，故以Linux分支為主。

不同的分支計算邏輯略有不同，在Linux環(huán)境下，函數(shù)中會調(diào)用系統(tǒng)函數(shù)getrlimit和setrlimit來獲取系統(tǒng)資源的最大使用量，在系統(tǒng)允許范圍內(nèi)，計算所得到的effective_open_files的值與request_open_files的值相等。筆者所用測試物理機測得的系統(tǒng)允許上限值為1024*1024。

此外，另一個可能影響effective_open_files數(shù)值的是MySQL實例的啟動方式:

• 若MySQL實例在非root下啟動，則effective_open_files的值會受到系統(tǒng)對于單進程句柄的限制，即命令ulimit –n得到的值；
• 若在root下啟動實例，則不會受到系統(tǒng)對單進程的限制。

計算后所得的effective_open_files的值會賦給open_files_limit，作為實例中最終生效的實際參數(shù)。

概括來講，在未達到最大值時，計算所得的effective_open_files 與request_open_files 值相等，并將effective_open_files的最終值賦給open_files_limit；若超過最大值則effective_open_files會以配置文件中的open_files_limit設(shè)置值生效，若配置文件中設(shè)置也超限，則取系統(tǒng)對單進程文件描述符的限制做生效值。為了更清晰的說明此處邏輯，筆者進行了以下對比測試。

為了便于更直觀的看清測試結(jié)果，我們更改系統(tǒng)單進程文件描述符限制為56789，更改系統(tǒng)對全局文件描述符限制為655350。然后分別使用下列三種配置文件啟動實例：

配置一：配置文件中設(shè)置open_files_limit值為1040000。

使用非root用戶啟動MySQL實例，進入數(shù)據(jù)庫查看變量生效值：

此時是以系統(tǒng)對單進程文件描述符限制生效的。

關(guān)閉實例進程，使用root用戶啟動MySQL實例，再次進入數(shù)據(jù)庫查看變量生效值：

此時則以配置文件中的設(shè)置值生效。

配置二：配置文件中設(shè)置table_open_cache=520000，open_files_limit=655350。

在root用戶下啟動實例，可以看到此條件下可以按照配置文件生效：

隨后，更改配置文件中設(shè)置table_open_cache=530000，open_files_limit不變，在root用戶下重啟實例，觀察變量生效情況：

  

產(chǎn)生變化的原因是根據(jù)修改的table_open_cache值計算所得的effective_open_files會超出系統(tǒng)允許的上限，故MySQL會使用配置文件中的設(shè)置生效，并重新計算table_open_cache的值。 

配置三：配置文件設(shè)置table_open_cache=530000，open_files_limit=1049000。 

在root用戶下重啟實例，觀察變量生效情況：

 

此時由于計算所得的effective_open_files和配置文件中的open_files_limit設(shè)置值均超限，故使用系統(tǒng)對單進程文件描述符限制作為生效值，并進一步計算得到實際的table_open_cache。 

讓我們回到源碼中繼續(xù)解讀。在設(shè)置了open_files_limit生效值后，MySQL源碼中通過封裝C語言的標準輸出函數(shù)實現(xiàn)了自己的輸出函數(shù)，并在一定條件下向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);  
  }  
} 

 

根據(jù)源碼邏輯，當effective_open_files小于request_open_files的值時，error.log中就會記錄相應信息。同時又根據(jù)配置文件中是否設(shè)置了open_files_limit的值來輸出不同內(nèi)容的錯誤信息。以前文配置一為例，非root用戶啟動MySQL計算所得的effective_open_files小于request_open_files，查看error.log中的信息有如下內(nèi)容：

 

對于root用戶啟動，由于比較條件不滿足，則無相應信息輸出。    

if (requested_open_files) 
 
equested_open_files= min<ulong>(effective_open_files, request_open_files); 

 

函數(shù)體的最后在effective_open_files和request_open_files中取小值放在了指針requested_open_files所指的內(nèi)存中，以便于后續(xù)函數(shù)對該變量的調(diào)用。 

2、max_connections 

該參數(shù)限制了MySQL實例允許的最大連接數(shù)，在數(shù)據(jù)庫的維護工作中相較于其他參數(shù)也更容易直觀的接觸到，下面讓我們看一下在源碼中是如何對這個參數(shù)進行設(shè)置及生效的。    

 

void adjust_max_connections(ulong requested_open_files) 

此函數(shù)中的參數(shù)requested_open_files變量值即為前文函數(shù)中requested_open_files指針變量所指內(nèi)容。關(guān)于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; 
  } 

 

首先根據(jù)算式計算limit變量的值，此處用到的TABLE_OPEN_CACHE_MIN是在頭文件sql_const.h中定義的宏定義變量，值為400。 

隨后將limit的值與配置文件中設(shè)定的max_connections進行比較，若limit較小，則向error.log中輸出一串提示信息，并以limit作為最終生效值；若limit較大，則直接以配置文件的設(shè)置值生效，同時不打印提示信息。以下為操作實例： 

設(shè)置配置文件中max_connections值為5000，其他配置沿用前文。以非root用戶啟動MySQL實例，在數(shù)據(jù)庫中查看max_connections的值：

 

此時max_connections按照配置文件中的設(shè)置進行生效，原因在于非root用戶啟動實例，requested_open_files取系統(tǒng)限定的effective_open_files值為56789，計算limit=56789-10-400*2=55979，該值遠大于5000，故取較小值即配置文件中的設(shè)置值生效。 

保持同樣的條件不變，僅更改配置文件中的max_connections設(shè)置值為60000大于55979，使用非root用戶重啟MySQL實例，再次查看max_connections生效值，已經(jīng)更新為預期的55979了。

 

3、table_open_cache 

此參數(shù)規(guī)定了內(nèi)存中允許打開表的數(shù)量，從它的作用可以看出，當MySQL在處理查詢請求時，table_open_cache將會起到較大作用，有效設(shè)置并使用此參數(shù)可以降低熱點表的頻繁開關(guān)動作，從而改善性能。關(guān)于該參數(shù)設(shè)置的源碼如下：  

void adjust_table_cache_size(ulong requested_open_files)  
     limit= max<ulong>((requested_open_files - 10 - max_connections) / 2,  
                    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的實現(xiàn)函數(shù)可以看出，兩個函數(shù)的內(nèi)部結(jié)構(gòu)相似，都是以requested_open_files為參數(shù)，根據(jù)各自的邏輯計算出limit變量的值，并將其與配置文件中的設(shè)置值比較，取更小的值作為最終生效值。需要注意的是，table_open_cache在源碼文件sys_vars.cc中被限定了取值范圍(0,512*1024)，這個范圍會對table_open_cache的實際生效值產(chǎn)生影響。下面看一些配置實例： 

設(shè)置配置文件中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，同時小于配置文件中的設(shè)置值，故最終生效值應為25889。在數(shù)據(jù)庫中查看變量的實際生效值：

 

結(jié)果符合預期。 

保持其他條件不變，更改配置文件中table_open_cache值為20000，再用非root用戶重啟實例，此時limit值仍為25889，但已大于配置文件中的設(shè)置值，故生效值應為20000。到數(shù)據(jù)庫中查看實際生效值驗證。

 

4、table_definition_cache 

此參數(shù)乍一看很容易與table_open_cache相混淆，畢竟這對“孿生兄弟”從生效到功能都有相近之處。table_definition_cache定義了內(nèi)存中可打開的表結(jié)構(gòu)數(shù)量。此參數(shù)的設(shè)置函數(shù)與前列很大的一個不同點是沒有使用requested_open_files作為參數(shù)，僅僅聲明了一個無參函數(shù)： 

 

void adjust_table_def_size() 

此參數(shù)的設(shè)置算式僅使用了已生效的table_open_cache作為計算變量。 

 

default_value= min<ulong> (400 + table_cache_size / 2, 2000); 

table_open_cache函數(shù)中有一個很值得關(guān)注的點，在函數(shù)體內(nèi)涉及了MySQL動態(tài)哈希鏈表的訪問。這個鏈表是MySQL在啟動主函數(shù)中定義并且用來存放部分系統(tǒng)變量的動態(tài)鏈表，下面我們來詳細了解一下函數(shù)內(nèi)部的訪問流程。 

首先函數(shù)體內(nèi)定義了一個系統(tǒng)變量類型的指針var，隨后調(diào)用函數(shù)訪問哈希表，并將定位到的內(nèi)存塊賦值給類對象var。 

 

var= intern_find_sys_var(STRING_WITH_LEN("table_definition_cache")); 

此處STRING_WITH_LEN()是一個宏定義，返回內(nèi)容為對應字符串本身及長度。我們到intern函數(shù)內(nèi)部看一下： 

 

var=(sys_var*)my_hash_search(&system_variable_hash,(uchar*) str, length ? length : strlen(str));  
return var; 

 

intern函數(shù)內(nèi)部調(diào)用my_hash_search函數(shù)查找table_definition_cache字符串在哈希鏈表中對應的結(jié)點，并將找到的位置返回類對象指針var。在找到var指針的目標結(jié)點后，table_definition_cache設(shè)置主函數(shù)就把前面計算所得到的值寫入var所指向的內(nèi)存。 

 

var->update_default(default_value); 

值得注意的是，即便函數(shù)已將計算結(jié)果賦給目標結(jié)點，但函數(shù)依然會首先判斷配置文件中是否設(shè)置了table_definition_cache的值。若配置文件中未設(shè)置，則以計算所得的結(jié)果作為生效值；若配置文件中有對應設(shè)置，則優(yōu)先以配置文件中的設(shè)置生效。 

 

if (! table_definition_cache_specified)  
_def_size= default_value; 

 

另一個需要注意的是雖然MySQL默認配置文件中設(shè)置的table_definition_cache優(yōu)先生效，但在頭文件sql_const.h中宏定義了table_definition_cache的下限值為400，故即便在配置文件中設(shè)置了一個很小的值，MySQL也會自動將生效值上調(diào)為下限值。下面看一些對應的配置實例： 

我們選擇前例中requested_open_files=56789，max_connections=5000，table_open_cache=25889的條件進行測試。 

首先，我們在配置文件中設(shè)置table_definition_cache=35486并將其注釋，保存文件。 

重啟MySQL實例，令修改的配置文件生效。根據(jù)前面對源碼的分析，計算公式default_value= min<ulong> (400 + table_cache_size / 2, 2000)=2000，此時配置文件中的設(shè)置無效，應以計算結(jié)果生效。進入數(shù)據(jù)庫中查看生效值：

 

然后修改配置文件，取消table_definition_cache的相關(guān)注釋，保存配置文件并重啟實例，到數(shù)據(jù)庫中查看生效值：

 

此時已按照配置文件設(shè)置生效。 

最后為驗證MySQL對table_definition_cache下限的自適應調(diào)整，我們修改配置文件中的對應值為table_definition_cache=15并保存，重啟，再次進入數(shù)據(jù)庫查看生效值：

 

可以看到，生效值已被MySQL自適應調(diào)整為源碼中宏定義的下限值。 

至此，上文已完成對table_cache相關(guān)的源碼分析內(nèi)容，當我們在實際工作中需要調(diào)整相關(guān)參數(shù)，可以參考前文并配置。現(xiàn)在讓我們進一步思考，在知曉了這些參數(shù)間的關(guān)聯(lián)和配置方法后，如何設(shè)置相應的值才算合理？這些參數(shù)對MySQL的實際使用性能又會有多大的影響？下文將會對這部分內(nèi)容進行測試分析。 

三、 參數(shù)性能影響及測試分析 

上文介紹了table-cache相關(guān)參數(shù)在MySQL數(shù)據(jù)庫正常運轉(zhuǎn)過程中存在至關(guān)重要的作用，但并不是每個參數(shù)的微調(diào)都會對性能產(chǎn)生顯著的影響，以下將對它們逐一進行說明。 

嚴格來講，open_files_limit和max_connections對MySQL的重要性并非體現(xiàn)在性能方面。 

對于open_files_limit來說，不合理的設(shè)置將會直接導致MySQL實例down掉或在啟動時根本無法正常拉起進程。對于這些場景，MySQL會有簡單直接的錯誤信息來提示DBA需要進行相應的調(diào)整。 

對于max_connections來說，設(shè)置過大可能會對其它參數(shù)的生效產(chǎn)生影響，設(shè)置過小又無法滿足業(yè)務高峰時的連接需求，從而造成大量的連接等待和超時。通常根據(jù)實際情況設(shè)置在能夠滿足業(yè)務峰值的大小即可。 

基于上述原因，這兩個參數(shù)的性能影響在此不做深入探討，而把重點放在對另外兩個參數(shù)的測試及分析。 

1、table_definition_cache(TDC) 

本節(jié)對TDC可能產(chǎn)生的性能影響進行測試分析。使用的MySQL測試版本為5.7.18，測試服務器為單實例單庫，庫中共建立40000張表，每張表內(nèi)5000行數(shù)據(jù)。測試條件為保證其他參數(shù)一致的前提下分別設(shè)置TDC=10000和TDC=50000進行對比。 

首先比較兩種場景下全局變量的區(qū)別，分別修改配置文件中TDC設(shè)置值為10000和50000，再分別重啟實例，查看相關(guān)全局變量如下： 

• TDC=10000

 

• TDC=50000

 

 

通過對比可以看出，重啟后兩種場景的TDC生效值分別已達到預期的10000和50000。同時，我們在此處對比另外一個變量open_table_definitions，這個變量表示在當前狀態(tài)下打開的表結(jié)構(gòu)數(shù)量，兩種場景下在MySQL實例剛剛重啟后由于TDC的不同，打開的表結(jié)構(gòu)數(shù)量是不同的： 

• TDC=10000的場景下打開的表結(jié)構(gòu)數(shù)量被限制在10000；
• TDC=50000的場景下實例中的表結(jié)構(gòu)全部被打開（其中有40000張測試表+219張系統(tǒng)表）。 

對比結(jié)果說明當TDC生效值大于庫中全表數(shù)量時，實例啟動時會將所有表結(jié)構(gòu)加載到內(nèi)存；當TDC生效值小于庫中全表數(shù)量時，MySQL會按照TDC的生效值加載相應數(shù)量的表結(jié)構(gòu)到內(nèi)存。 

隨后我們來比較TDC的差別是否會對性能產(chǎn)生影響。測試使用sysbench工具模擬客戶端向服務器發(fā)送請求，申請訪問的表數(shù)量為35000，連接并發(fā)數(shù)為50，連接發(fā)送請求持續(xù)時間為5min，使用以上測試模式分別測試TDC=10000和TDC=50000兩條件下四種基本SQL語句的qps，對比結(jié)果如下：

 

從測試結(jié)果對比可以看出，不同的TDC對MySQL基準性能的影響并不大。那么導致這樣的因素是什么？ 

我們查看兩個測試后全局狀態(tài)變量open_table_definitions的值進行對比： 

• TDC=10000

 

• TDC=50000

  

通過比較可以發(fā)現(xiàn)，雖然TDC設(shè)置值為10000的模式無法在啟動實例時將所有表結(jié)構(gòu)都加載入內(nèi)存，但在實際請求到達服務器時，MySQL允許在內(nèi)存中“超載”TDC定義的表結(jié)構(gòu)數(shù)量，這就使得實例當前已打開的表結(jié)構(gòu)數(shù)量可超過TDC的限定值。同時，對于單一表來說，重復訪問并不會增加表結(jié)構(gòu)的打開次數(shù)。因此，TDC對于性能的影響便不是很大了。 

2、table_open_cache（TOC） 

本節(jié)測試TOC可能產(chǎn)生的性能影響。使用的MySQL版本及測試庫環(huán)境與TDC測試相同。首先比較兩種不同TOC對性能帶來的影響。根據(jù)前文，使用root和非root用戶啟動實例會導致open_files_limit的生效值有區(qū)別，進而影響TOC的生效值。本例在配置文件中設(shè)置TOC值為350000，再分別使用root及非root用戶啟動實例，觀察實際生效值如下： 

• root用戶啟動：

 

 

  

• 非root用戶啟動：

 

 

  

由于TOC決定了內(nèi)存中打開表的數(shù)量，功能上對查詢SQL的影響更為明顯，故使用sysbench僅發(fā)送select語句并統(tǒng)計qps，申請訪問的表數(shù)量是35000，訪問持續(xù)時間為5min。更改不同的連接并發(fā)數(shù)分別測試qps并繪制成折線圖，結(jié)果如下：

 

從曲線圖可以看出，TOC較大（=350000）的條件下，查詢語句的qps隨并發(fā)數(shù)增加變化比較明顯，整體呈現(xiàn)先迅速上升后平穩(wěn)回落的趨勢，在64-128并發(fā)范圍內(nèi)達到高峰。反觀TOC較小（=30262）的模式下qps隨并發(fā)數(shù)的增加無明顯變化，而且持續(xù)處于較低水平。 

為了更加清晰的分析TOC對性能的影響，我們統(tǒng)計了每個并發(fā)下獨立測試的緩存命中率并繪制成曲線進行比較：

 

比較二者命中率曲線可以看出，TOC較大的模式下，緩存命中率隨并發(fā)數(shù)的變化趨勢與qps曲線基本一致，雖然存在一定的波動，但整體命中率均在99%以上，這時我們可以判定緩存的效率是較高的；但TOC較小的模式下，緩存命中率在不同并發(fā)數(shù)下普遍較差（低于50%），同時隨并發(fā)數(shù)的增加還有急速下降的趨勢。    

我們再統(tǒng)計一下各個并發(fā)測試下的table_open_cache_overflows的值繪制成曲線并觀察：

 

從曲線圖可以看出，TOC較大的模式下overflow值非常小（基本為0），這與該模式下緩存命中率較高的結(jié)果相吻合；但TOC較小的模式下則存在較大的overflow，這說明該模式下較小的緩存無法滿足大量的并發(fā)訪問請求，緩存不得不持續(xù)將新表刷入內(nèi)存。 

四、總結(jié) 

通過本文對table-cache相關(guān)源碼的分析及對比測試，我們可以得出一些相關(guān)結(jié)論： 

• open_files_limit，max_connections， table_open_cache， table_definition_cache四個參數(shù)在MySQL實例啟動時依次生效，且相互存在制約關(guān)系，若需要單獨更改某一變量，要注意可能產(chǎn)生的影響；
• open_files_limit參數(shù)的生效會受到不同啟動方式的影響進而影響其它參數(shù)的生效值，設(shè)置時要按需選擇；
• open_files_limit和max_connections參數(shù)對性能影響較小，設(shè)置時可滿足業(yè)務量需求即可，二者更多影響的是MySQL實例的正常運作；
• table_definition_cache參數(shù)對性能影響較小；
• table_open_cache參數(shù)對實際性能影響較大，但生效值需要在上下限間合理設(shè)置，為盡可能發(fā)揮cache性能，生效值應設(shè)置為大并發(fā)下可維持較高命中率的同時不發(fā)生緩存overflow。 

以上是本文全部內(nèi)容，希望讀到此文的廣大前輩和同行能夠積極提出文中的不足并不吝指正。