テーブルを開く際の MySQL スレッドの問題の解決 (例付き)

この記事の内容は、テーブルを開く際の MySQL スレッドの問題の解決に関するものです (例付き)。必要な方は参考にしていただければ幸いです。

問題の説明

最近 MySQL5.6.21 サーバーがあり、アプリケーションがリリースされた後、同時スレッド Threads_running が急速に増加し、約 2000 に達します。テーブルのオープンを待機しているスレッドの数、テーブルのクローズ状況、アプリケーション側関連の論理アクセス タイムアウト。

[分析プロセス]

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 値より大きい場合、新しいセッションがテーブルを開くたびに、一部のテーブルはテーブル キャッシュにヒットできず、テーブルを再度開く必要があります。これに反映される現象は、開始テーブル状態にあるスレッドが多数存在することです。

2. このインスタンスとシステム データベースの合計は 851 ですが、これは table_open_cache の 2000 よりはるかに少ないのはなぜですか?

公式ドキュメントから説明を得ることができます。
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 つのテーブルにアクセスし、その他はすべて単一テーブルである場合、キャッシュ サイズは (1980*30%*2 1980*70%*1) = 2574

3 に達します。リリース前もリリース後も比較的安定しています。 リクエストから判断すると、接続リクエストが急激に増加したわけではありませんが、リリース後、threads_running は 2,000 近くの高値まで上昇し、継続しています。推測では、特定のパブリッシュされた SQL ステートメントが問題を引き起こしたと考えられます。

4. そのときに取得された processlist 情報を確認します。SQL の同時アクセスが非常に多いという記述があります。SQL サンプルは次のとおりです。 ##5. テスト環境では、同じテーブルを 8 つ作成し、テーブル キャッシュをクリアし、1 つのセッションで SQL を実行する前と後で比較すると、Open_tables の値は 8 増加します。大幅に増加します。

問題の再現

テスト環境で大量の同時アクセスのシナリオをシミュレートし、上記の SQL ステートメントを 1000 スレッドで同時に実行し、実稼働環境でも同様の現象が発生し、Open_tables がすぐに 3800 に達し、多数のプロセスがテーブルを開く状態およびテーブルを閉じる状態になります。

最適化計画

1. 問題の原因を突き止めた後、開発同僚と連絡を取り、単一文の SQL の数を減らすために SQL を最適化することを提案しました。テーブルにクエリを実行するか、SQL の同時アクセス頻度を大幅に削減します。

しかし、開発同僚が最適化する前に、本番環境で障害が再び発生しました。当時、DBA がトラブルシューティングを行っていたときに、table_open_cache が 2000 から 4000 に増加しました。CPU 使用率は増加しましたが、その効果は明ら​​かではありませんでした。テーブルを開くのに待機するという問題は依然として存在していました。

2. 障害時に取得された pstack 情報を分析し、pt-pmp で集計したところ、open_table:

<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 (&#39;aaa&#39;);</code>

At this 時に多数のスレッドがミューテックス リソースを待機していることがわかりました。当時、table_cache_manager でのミューテックスの競合は非常に深刻でした。

MySQL 5.6.21 における table_open_cache_instances パラメータのデフォルト値は 1 であるため、table_open_cache_instances パラメータを増やし、テーブル キャッシュ パーティションを追加すると競合が軽減されると思います。

3. テスト環境では、table_open_cache_instances=32、table_open_cache=6000 の 2 つのパラメータを調整し、問題のある SQL を 1000 スレッドで同時に実行しましたが、今度はテーブルのオープンとテーブルのクローズを待機しているスレッドが消えました。 、MySQL QPS も 12,000 から 55,000 に増加しました。

同じ状況と比較すると、table_open_cache=6000 を調整するだけで、テーブルのオープンを待機しているプロセスの数が 861 から 203 に減少しました。600 を超えるプロセスがテーブルのオープンを待機している状態から実行状態に変わりました。 QPSは約40,000に増加しましたが、治療法ではありません。

ソースコード分析

table_open_cache の関連ロジックのコードを確認しました。
1. 新しい接続時の Table_cache::add_used_table 関数は次のとおりです。テーブルを開きます。テーブルがキャッシュに存在しない場合は、テーブルを開いて使用済みテーブル リストに追加します:

#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

2。 m_table_count > の場合、add_used_table は毎回 Table_cache::free_unused_tables_if_necessary 関数を呼び出します。 ; table_cache_size_per_instance &&m_unused_tables が満たされている場合、remove_table が実行され、m_unused_tables リストがクリアされます。このうち、table_cache_size_per_instance = table_cache_size / table_cache_instances です。m_table_count の値が 2000 より大きく、m_unused_tables が空でない場合、remove_table が実行され、m_unused_tables 内のテーブル キャッシュがクリアされます。このように、m_table_count は Open_tables の値であり、通常は 2000 程度のままになります。

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

3. table_cache_instances を 32 に増やします。Open_tables が (2000/32=62) を超えると条件が満たされ、上記のロジックで m_unused_tables のクリーンアップが加速され、テーブル キャッシュ内の数がさらに減ります。これにより、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参数的值。

以上がテーブルを開く際の MySQL スレッドの問題の解決 (例付き)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。