如何解決MySQL線程處于Openingtables的問題

這篇文章主要介紹了如何解決MySQL線程處于Opening tables的問題，具有一定借鑒價(jià)值，需要的朋友可以參考下。希望大家閱讀完這篇文章后大有收獲。下面讓小編帶著大家一起了解一下。

七里河網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)建站！從網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營維護(hù)。創(chuàng)新互聯(lián)建站成立于2013年到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)建站。

本篇文章給大家?guī)淼膬?nèi)容是關(guān)于MySQL線程處于Opening tables的問題解決（附示例），有一定的參考價(jià)值，有需要的朋友可以參考一下，希望對你有所幫助。

問題描述

最近有一臺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不斷增加，如下圖所示：
如何解決MySQL線程處于Opening 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，為什么會導(dǎo)致Open_tables達(dá)到3430呢
從官方文檔中可以得到解釋，
https://dev.mysql.com/doc/refman/5.6/en/table-cache.html

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就會達(dá)到（1980*30%*2+1980*70%*1）=2574

3、QPS在發(fā)布前后都比較平穩(wěn)，從外部請求來看并沒有突增的連接請求，但在發(fā)布后threads_running上升到接近2000的高位，一直持續(xù)。猜測是由于某個(gè)發(fā)布的SQL語句觸發(fā)了問題。

4、查看當(dāng)時(shí)抓取的processlist信息，有一句SQL并發(fā)訪問很高，查詢了8張物理表，SQL樣本如下：

select id,name,email from table1 left join table2
union all
select id,name,email from table3 left join table4
union all
select id,name,email from table5 left join table6
union all
select id,name,email from table7 left join table8
where id in ('aaa');

5、在測試環(huán)境中創(chuàng)建相同的8張表，清空表緩存，單個(gè)session執(zhí)行SQL前后對比，Open_tables的值會增加8，如果高并發(fā)的情況下，Open_tables的值就會大幅增加。

問題重現(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資源：

#0  0x0000003f0900e334 in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x0000003f0900960e in _L_lock_995 () from /lib64/libpthread.so.0
#2  0x0000003f09009576 in pthread_mutex_lock () from /lib64/libpthread.so.0
#3  0x000000000069ce98 in open_table(THD*, TABLE_LIST*, Open_table_context*) ()
#4  0x000000000069f2ba in open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) ()
#5  0x000000000069f3df in open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int) ()
#6  0x00000000006de821 in execute_sqlcom_select(THD*, TABLE_LIST*) ()
#7  0x00000000006e13cf in mysql_execute_command(THD*) ()
#8  0x00000000006e4d8f in mysql_parse(THD*, char*, unsigned int, Parser_state*) ()
#9  0x00000000006e62cb in dispatch_command(enum_server_command, THD*, char*, unsigned int) ()
#10 0x00000000006b304f in do_handle_one_connection(THD*) ()
#11 0x00000000006b3177 in handle_one_connection ()
#12 0x0000000000afe5ca in pfs_spawn_thread ()
#13 0x0000003f09007aa1 in start_thread () from /lib64/libpthread.so.0
#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。
對比相同情況下，只調(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：

bool Table_cache::add_used_table(THD *thd, TABLE *table)
{
  Table_cache_element *el;

  assert_owner();

  DBUG_ASSERT(table->in_use == thd);

  /*
    Try to get Table_cache_element representing this table in the cache
    from array in the TABLE_SHARE.
  */
  el= table->s->cache_element[table_cache_manager.cache_index(this)];

  if (!el)
  {
    /*
      If TABLE_SHARE doesn't have pointer to the element representing table
      in this cache, the element for the table must be absent from table the
      cache.

      Allocate new Table_cache_element object and add it to the cache
      and array in TABLE_SHARE.
    */
    DBUG_ASSERT(! my_hash_search(&m_cache,
                                 (uchar*)table->s->table_cache_key.str,
                                 table->s->table_cache_key.length));

    if (!(el= new Table_cache_element(table->s)))
      return true;

    if (my_hash_insert(&m_cache, (uchar*)el))
    {
      delete el;
      return true;
    }

    table->s->cache_element[table_cache_manager.cache_index(this)]= el;
  }

  /* Add table to the used tables list */  
  el->used_tables.push_front(table);

  m_table_count++;  free_unused_tables_if_necessary(thd);

  return false;
}

2、每次add_used_table會調(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的值正常會維持在2000上下。

void Table_cache::free_unused_tables_if_necessary(THD *thd)
{
  /*
    We have too many TABLE instances around let us try to get rid of them.

    Note that we might need to free more than one TABLE object, and thus
    need the below loop, in case when table_cache_size is changed dynamically,
    at server run time.
  */
  if (m_table_count > table_cache_size_per_instance && m_unused_tables)
  {
    mysql_mutex_lock(&LOCK_open);
    while (m_table_count > table_cache_size_per_instance &&
           m_unused_tables)
    {
      TABLE *table_to_free= m_unused_tables;      
      remove_table(table_to_free);
      intern_close_table(table_to_free);
      thd->status_var.table_open_cache_overflows++;
    }
    mysql_mutex_unlock(&LOCK_open);
  }
}

3、增大table_cache_instances為32，當(dāng)Open_tables超過（2000/32=62）時(shí)，就會滿足條件，加速上述邏輯中m_unused_tables的清理，使得table cache中數(shù)量進(jìn)一步減少，會導(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上，大大降低了鎖的爭用。

/** Acquire lock on table cache instance. */
  void lock() { mysql_mutex_lock(&m_lock); }
  /** Release lock on table cache instance. */
  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ù)的值。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享如何解決MySQL線程處于Opening tables的問題內(nèi)容對大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，遇到問題就找創(chuàng)新互聯(lián)，詳細(xì)的解決方法等著你來學(xué)習(xí)!

標(biāo)題名稱：如何解決MySQL線程處于Openingtables的問題
鏈接URL：http://muchs.cn/article34/jpesse.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、響應(yīng)式網(wǎng)站、自適應(yīng)網(wǎng)站、面包屑導(dǎo)航、電子商務(wù)、網(wǎng)站導(dǎo)航

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容