MySQL大量線程處於Opening tables的問題分析

【作者】王棟：攜程技術保障中心資料庫專家，對資料庫疑難問題的排查和資料庫自動化智能化運維工具的開發有強烈的興趣。【問題描述】最近有一臺MySQL5.6.21的伺服器，在應用發佈後，併發線程Threads_running迅速升高，達到2000左右，大量線程處於等待Opening tables、c ...

【作者】

王棟：攜程技術保障中心資料庫專家，對資料庫疑難問題的排查和資料庫自動化智能化運維工具的開發有強烈的興趣。

【問題描述】

最近有一臺MySQL5.6.21的伺服器，在應用發佈後，併發線程Threads_running迅速升高，達到2000左右，大量線程處於等待Opening tables、closing tables狀態，應用端相關邏輯訪問超時。

【分析過程】

1、16:10應用發佈結束後，Opened_tables不斷增加，如下圖所示：

查看當時故障期間抓取的pt-stalk日誌文件，時間點2019-01-18 16:29:37，Open_tables 的值為3430，而table_open_cache的配置值為2000。
當Open_tables值大於table_open_cache值時，每次新的session打開表，有一些無法命中table cache，而不得不重新打開表。這樣反應出來的現象就是有大量的線程處於opening tables狀態。

2、這個實例下的表，加上系統資料庫下總計851張，遠小於table_open_cache的2000，為什麼會導致Open_tables達到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.

當時併發線程數達到1980，假設這些併發連接中有30%是訪問2張表，其他都是單表，那麼cache size就會達到（1980*30%*2+1980*70%*1）=2574

3、QPS在發佈前後都比較平穩，從外部請求來看並沒有突增的連接請求，但在發佈後threads_running上升到接近2000的高位，一直持續。猜測是由於某個發佈的SQL語句觸發了問題。

4、查看當時抓取的processlist信息，有一句SQL併發訪問很高，查詢了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、在測試環境中創建相同的8張表，清空表緩存，單個session執行SQL前後對比，Open_tables的值會增加8，如果高併發的情況下，Open_tables的值就會大幅增加。

【問題重現】

在測試環境上模擬高併發訪問的場景，併發1000個線程同時執行上面的SQL語句，復現了生產環境類似的現象，Open_tables迅速達到3800，大量進程處於Opening tables、closing tables狀態。

【優化方案】

1、定位到問題原因後，我們與開發同事溝通，建議優化該SQL，降低單句SQL查詢表的數量或大幅降低該SQL的併發訪問頻率。
不過開發同事還沒來的及優化，生產環境上故障又出現了。當時DBA排障時將table_open_cache從2000增加4000，CPU使用率上升，效果並不明顯，等待Opening tables的問題依然存在。

2、分析故障期間抓取的pstack信息，用pt-pmp聚合後，看到大量線程在open_table時等待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

這時table_cache_manager中的mutex衝突非常嚴重。
由於MySQL5.6.21下table_open_cache_instances參數的預設值為1，想到增大table_open_cache_instances參數，增加表緩存分區，應該可以緩解爭用。

3、在測試環境上，我們調整兩個參數table_open_cache_instances=32，table_open_cache=6000，同樣併發1000個線程執行問題SQL，這次等待Opening tables、closing tables的線程消失了，MySQL的QPS也從12000上升到55000。
對比相同情況下，只調整table_open_cache=6000，等待Opening tables的進程數從861下降到203，問題有所緩解，有600多個進程已經從等待Opening tables變為運行狀態，QPS上升到40000左右，但不能根治。

【源碼分析】

查了下代碼有關table_open_cache的相關邏輯：
1、Table_cache::add_used_table函數如下，當新的連接打開的表在table cache中不存在時，打開表加入到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會調用Table_cache::free_unused_tables_if_necessary函數，當滿足m_table_count > table_cache_size_per_instance &&m_unused_tables時，執行remove_table，清除m_unused_tables列表中多餘的cache。其中table_cache_size_per_instance= table_cache_size / table_cache_instances，MySQL5.6的預設配置是2000/1=2000，當m_table_count值大於2000並且m_unused_tables非空時就執行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，當Open_tables超過（2000/32=62）時，就會滿足條件，加速上述邏輯中m_unused_tables的清理，使得table cache中數量進一步減少，會導致Table_open_cache_overflows升高。

4、當table_open_cache_instances從1增大到32時，1個LOCK_open鎖分散到32個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); }

【解決問題】

我們生產環境同時採取下麵優化措施，問題得以解決：
1、讀寫分離，增加read節點，分散master庫的壓力；
2、調整table_open_cache_instances=16；
3、調整table_open_cache=6000；

【總結】

當出現Opening tables等待問題時，
1、建議找出打開表頻繁的SQL語句，優化該SQL，降低單句SQL查詢表的數量或大幅降低該SQL的併發訪問頻率。

2、設置合適的table cache，同時增大table_open_cache_instances和 table_open_cache參數的值。