問題描述
最近有一臺(tái)MySQL5.6.21的服務(wù)器,在應(yīng)用發(fā)布后,并發(fā)線程Threads_running迅速升高,達(dá)到2000左右,大量線程處于等待Opening tables、closing tables狀態(tài),應(yīng)用端相關(guān)邏輯訪問超時(shí)。
【分析過程】
1、16:10應(yīng)用發(fā)布結(jié)束后,Opened_tables不斷增加,如下圖所示:

查看當(dāng)時(shí)故障期間抓取的pt-stalk日志文件,時(shí)間點(diǎn)2019-01-18 16:29:37,Open_tables 的值為3430,而table_open_cache的配置值為2000。
當(dāng)Open_tables值大于table_open_cache值時(shí),每次新的session打開表,有一些無法命中table cache,而不得不重新打開表。這樣反應(yīng)出來的現(xiàn)象就是有大量的線程處于opening tables狀態(tài)。
2、這個(gè)實(shí)例下的表,加上系統(tǒng)數(shù)據(jù)庫下總計(jì)851張,遠(yuǎn)小于table_open_cache的2000,為什么會(huì)導(dǎo)致Open_tables達(dá)到3430呢
從官方文檔中可以得到解釋,
https://dev.mysql.com/doc/refman/5.6/en/table-cache.html
1 | table_open_cache is related to max_connections. For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute. |
當(dāng)時(shí)并發(fā)線程數(shù)達(dá)到1980,假設(shè)這些并發(fā)連接中有30%是訪問2張表,其他都是單表,那么cache size就會(huì)達(dá)到(1980*30%*2+1980*70%*1)=2574
3、QPS在發(fā)布前后都比較平穩(wěn),從外部請(qǐng)求來看并沒有突增的連接請(qǐng)求,但在發(fā)布后threads_running上升到接近2000的高位,一直持續(xù)。猜測是由于某個(gè)發(fā)布的SQL語句觸發(fā)了問題。
4、查看當(dāng)時(shí)抓取的processlist信息,有一句SQL并發(fā)訪問很高,查詢了8張物理表,SQL樣本如下:
1 | < code >select id,name,email from table1 left join table2< br >union all< br >select id,name,email from table3 left join table4< br >union all< br >select id,name,email from table5 left join table6< br >union all< br >select id,name,email from table7 left join table8< br >where id in ('aaa');</ code > |
5、在測試環(huán)境中創(chuàng)建相同的8張表,清空表緩存,單個(gè)session執(zhí)行SQL前后對(duì)比,Open_tables的值會(huì)增加8,如果高并發(fā)的情況下,Open_tables的值就會(huì)大幅增加。
問題重現(xiàn)
在測試環(huán)境上模擬高并發(fā)訪問的場景,并發(fā)1000個(gè)線程同時(shí)執(zhí)行上面的SQL語句,復(fù)現(xiàn)了生產(chǎn)環(huán)境類似的現(xiàn)象,Open_tables迅速達(dá)到3800,大量進(jìn)程處于Opening tables、closing tables狀態(tài)。
優(yōu)化方案
1、 定位到問題原因后,我們與開發(fā)同事溝通,建議優(yōu)化該SQL,降低單句SQL查詢表的數(shù)量或大幅降低該SQL的并發(fā)訪問頻率。
不過開發(fā)同事還沒來的及優(yōu)化,生產(chǎn)環(huán)境上故障又出現(xiàn)了。當(dāng)時(shí)DBA排障時(shí)將table_open_cache從2000增加4000,CPU使用率上升,效果并不明顯,等待Opening tables的問題依然存在。
2、 分析故障期間抓取的pstack信息,用pt-pmp聚合后,看到大量線程在open_table時(shí)等待mutex資源:
01 | #0 0x0000003f0900e334 in __lll_lock_wait () from /lib64/libpthread.so.0 |
02 | #1 0x0000003f0900960e in _L_lock_995 () from /lib64/libpthread.so.0 |
03 | #2 0x0000003f09009576 in pthread_mutex_lock () from /lib64/libpthread.so.0 |
04 | #3 0x000000000069ce98 in open_table(THD*, TABLE_LIST*, Open_table_context*) () |
05 | #4 0x000000000069f2ba in open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) () |
06 | #5 0x000000000069f3df in open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int) () |
07 | #6 0x00000000006de821 in execute_sqlcom_select(THD*, TABLE_LIST*) () |
08 | #7 0x00000000006e13cf in mysql_execute_command(THD*) () |
09 | #8 0x00000000006e4d8f in mysql_parse(THD*, char*, unsigned int, Parser_state*) () |
10 | #9 0x00000000006e62cb in dispatch_command(enum_server_command, THD*, char*, unsigned int) () |
11 | #10 0x00000000006b304f in do_handle_one_connection(THD*) () |
12 | #11 0x00000000006b3177 in handle_one_connection () |
13 | #12 0x0000000000afe5ca in pfs_spawn_thread () |
14 | #13 0x0000003f09007aa1 in start_thread () from /lib64/libpthread.so.0 |
15 | #14 0x0000003f088e893d in clone () from /lib64/libc.so.6 |
這時(shí)table_cache_manager中的mutex沖突非常嚴(yán)重。
由于MySQL5.6.21下table_open_cache_instances參數(shù)的默認(rèn)值為1,想到增大table_open_cache_instances參數(shù),增加表緩存分區(qū),應(yīng)該可以緩解爭用。
3、 在測試環(huán)境上,我們調(diào)整兩個(gè)參數(shù)table_open_cache_instances=32,table_open_cache=6000,同樣并發(fā)1000個(gè)線程執(zhí)行問題SQL,這次等待Opening tables、closing tables的線程消失了,MySQL的QPS也從12000上升到55000。
對(duì)比相同情況下,只調(diào)整table_open_cache=6000,等待Opening tables的進(jìn)程數(shù)從861下降到203,問題有所緩解,有600多個(gè)進(jìn)程已經(jīng)從等待Opening tables變?yōu)檫\(yùn)行狀態(tài),QPS上升到40000左右,但不能根治。
源碼分析
查了下代碼有關(guān)table_open_cache的相關(guān)邏輯:
1、Table_cache::add_used_table函數(shù)如下,當(dāng)新的連接打開的表在table cache中不存在時(shí),打開表加入到used tables list:
01 | bool Table_cache::add_used_table(THD *thd, TABLE *table) |
03 | Table_cache_element *el; |
07 | DBUG_ASSERT(table->in_use == thd); |
10 | Try to get Table_cache_element representing this table in the cache |
11 | from array in the TABLE_SHARE. |
13 | el= table->s->cache_element[table_cache_manager.cache_index(this)]; |
18 | If TABLE_SHARE doesn't have pointer to the element representing table |
19 | in this cache, the element for the table must be absent from table the |
22 | Allocate new Table_cache_element object and add it to the cache |
23 | and array in TABLE_SHARE. |
25 | DBUG_ASSERT(! my_hash_search(&m_cache, |
26 | (uchar*)table->s->table_cache_key.str, |
27 | table->s->table_cache_key.length)); |
29 | if (!(el= new Table_cache_element(table->s))) |
32 | if (my_hash_insert(&m_cache, (uchar*)el)) |
38 | table->s->cache_element[table_cache_manager.cache_index(this)]= el; |
41 | /* Add table to the used tables list */ |
42 | el->used_tables.push_front(table); |
44 | m_table_count++; free_unused_tables_if_necessary(thd); |
2、每次add_used_table會(huì)調(diào)用Table_cache::free_unused_tables_if_necessary函數(shù),當(dāng)滿足m_table_count > table_cache_size_per_instance &&m_unused_tables時(shí),執(zhí)行remove_table,清除m_unused_tables列表中多余的cache。其中table_cache_size_per_instance= table_cache_size / table_cache_instances,MySQL5.6的默認(rèn)配置是2000/1=2000,當(dāng)m_table_count值大于2000并且m_unused_tables非空時(shí)就執(zhí)行remove_table,將m_unused_tables中的table cache清空。這樣m_table_count就是Open_tables的值正常會(huì)維持在2000上下。
01 | void Table_cache::free_unused_tables_if_necessary(THD *thd) |
04 | We have too many TABLE instances around let us try to get rid of them. |
06 | Note that we might need to free more than one TABLE object, and thus |
07 | need the below loop, in case when table_cache_size is changed dynamically, |
10 | if (m_table_count > table_cache_size_per_instance && m_unused_tables) |
12 | mysql_mutex_lock(&LOCK_open); |
13 | while (m_table_count > table_cache_size_per_instance && |
16 | TABLE *table_to_free= m_unused_tables; |
17 | remove_table(table_to_free); |
18 | intern_close_table(table_to_free); |
19 | thd->status_var.table_open_cache_overflows++; |
21 | mysql_mutex_unlock(&LOCK_open); |
3、增大table_cache_instances為32,當(dāng)Open_tables超過(2000/32=62)時(shí),就會(huì)滿足條件,加速上述邏輯中m_unused_tables的清理,使得table cache中數(shù)量進(jìn)一步減少,會(huì)導(dǎo)致Table_open_cache_overflows升高。
4、當(dāng)table_open_cache_instances從1增大到32時(shí),1個(gè)LOCK_open鎖分散到32個(gè)m_lock的mutex上,大大降低了鎖的爭用。
1 | /** Acquire lock on table cache instance. */ |
2 | void lock() { mysql_mutex_lock(&m_lock); } |
3 | /** Release lock on table cache instance. */ |
4 | void unlock() { mysql_mutex_unlock(&m_lock); } |
解決問題
我們生產(chǎn)環(huán)境同時(shí)采取下面優(yōu)化措施,問題得以解決:
1、 讀寫分離,增加read節(jié)點(diǎn),分散master庫的壓力;
2、 調(diào)整table_open_cache_instances=16;
3、 調(diào)整table_open_cache=6000;
總結(jié)
當(dāng)出現(xiàn)Opening tables等待問題時(shí),
1、建議找出打開表頻繁的SQL語句,優(yōu)化該SQL,降低單句SQL查詢表的數(shù)量或大幅降低該SQL的并發(fā)訪問頻率。
2、設(shè)置合適的table cache,同時(shí)增大table_open_cache_instances和 table_open_cache參數(shù)的值。