テーブルを開く際のMySQLスレッドの問題の解決方法

問題の説明

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

2. このインスタンスとシステム データベースのテーブルの合計数は 851 で、table_open_cache の 2000 よりはるかに少ないです。なぜ Open_tables が 3430 に達するのでしょうか?

公式ドキュメントから説明を得ることができます。

https://dev.mysql.com/doc/refman/5.6/en/テーブルキャッシュ.html

table_open_cache は max_connections と関連しています。たとえば、同時実行接続数が 200 の場合、テーブル キャッシュ サイズを少なくとも 200 * N に指定します。ここで、N は実行するクエリの結合あたりのテーブルの最大数です。

当時、同時スレッド数は1980に達しました。これらの同時接続の30%が2つのテーブルにアクセスし、残りが1つのテーブルにアクセスしたと仮定すると、キャッシュサイズは(1980*30%*2+1980*70%*1)=2574になります。

3. リリース前後のQPSは比較的安定しており、外部リクエストの観点からは接続リクエストの急増はありませんでした。ただし、リリース後、threads_runningは2000近くまで上昇し、それが継続しました。この問題は、発行された特定の SQL ステートメントによって発生したと推測されます。

4. その時にキャプチャされたプロセスリスト情報を確認します。8 つの物理テーブルをクエリする、同時アクセス数の多い SQL 文があります。SQL サンプルは次のとおりです。

<code>テーブル 1 から id、名前、メールを選択し、テーブル 2 を左結合します<br>すべて結合します<br>テーブル 3 から id、名前、メールを選択し、テーブル 4 を左結合します<br>すべて結合します<br>テーブル 5 から id、名前、メールを選択し、テーブル 6 を左結合します<br>すべて結合します<br>テーブル 7 から id、名前、メールを選択し、テーブル 8 を左結合します<br>id が ('aaa') の場合;</code>

5. テスト環境に同じ 8 つのテーブルを作成し、テーブル キャッシュをクリアして、単一セッションで SQL を実行する前と後の結果を比較します。Open_tables の値は 8 増加します。同時実行性が高い場合、Open_tables の値が大幅に増加します。

問題の再現

テスト環境では、同時アクセスが多いシナリオをシミュレートし、1,000 スレッドを使用して上記の SQL 文を同時に実行しました。これにより、実稼働環境と同様の現象が再現されました。Open_tables の数はすぐに 3,800 に達し、多数のプロセスが Opening tables および Closing tables の状態になりました。

最適化計画

1. 問題の原因を突き止めた後、開発担当者と連絡を取り、SQL の最適化、単一ステートメントの SQL クエリ テーブルの数の削減、SQL の同時アクセス頻度の大幅な削減を提案しました。

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

2. 障害発生時にキャプチャされた pstack 情報を分析し、pt-pmp で集計します。opening_table の実行時に多数のスレッドがミューテックス リソースを待機していることがわかります。

#0 0x0000003f0900e334 __lll_lock_wait () 内、/lib64/libpthread.so.0 より
#1 0x0000003f0900960e が /lib64/libpthread.so.0 の _L_lock_995 () にあります
#2 /lib64/libpthread.so.0 の pthread_mutex_lock () の 0x0000003f09009576
#3 open_table(THD*, TABLE_LIST*, Open_table_context*) 内の 0x000000000069ce98 ()
#4 open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) 内の 0x000000000069f2ba()
#5 open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int) 内の 0x000000000069f3df()
#6 execute_sqlcom_select(THD*, TABLE_LIST*)() 内の 0x00000000006de821
#7 mysql_execute_command(THD*)() 内の 0x00000000006e13cf
#8 mysql_parse(THD*, char*, unsigned int, Parser_state*)() 内の 0x00000000006e4d8f
#9 0x00000000006e62cb が dispatch_command(enum_server_command, THD*, char*, unsigned int) にあります ()
#10 do_handle_one_connection(THD*)() 内の 0x00000000006b304f
#11 handle_one_connection() 内の 0x00000000006b3177
#12 pfs_spawn_thread() 内の 0x0000000000afe5ca
#13 /lib64/libpthread.so.0 の start_thread() で 0x0000003f09007aa1
#14 0x0000003f088e893d は /lib64/libc.so.6 からの clone() にあります

現時点では、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 スレッドで同時に実行しました。今度は、Opening tables と Closing tables を待機しているスレッドがなくなり、MySQL の QPS も 12000 から 55000 に増加しました。
同じ状況で、table_open_cache=6000 のみを調整したところ、Opening tables を待機しているプロセス数が 861 から 203 に減少し、問題は緩和されました。600 を超えるプロセスが Opening tables 待機状態から実行状態に変わり、QPS が約 40,000 に増加しました。ただし、問題は治癒していません。

ソースコード分析

table_open_cache のロジックのコードを確認します。

1. Table_cache::add_used_table 関数は以下のとおりです。新しい接続で開かれたテーブルがテーブル キャッシュに存在しない場合、開かれたテーブルは使用済みテーブル リストに追加されます。

bool Table_cache::add_used_table(THD *thd、TABLE *table)
{
 テーブルキャッシュ要素 *el;
 
 所有者をアサートする();
 
 DBUG_ASSERT(テーブル->in_use == thd);
 
 /*
 キャッシュ内のこのテーブルを表す Table_cache_element を取得してみてください
 TABLE_SHARE の配列から。
 */
 el = table->s->cache_element[table_cache_manager.cache_index(this)];
 
 もし (!el)
 {
 /*
  TABLE_SHAREにテーブルを表す要素へのポインタがない場合
  このキャッシュでは、テーブルの要素はテーブルに存在しない必要があります。
  キャッシュ。
 
  新しいTable_cache_elementオブジェクトを割り当ててキャッシュに追加します
  TABLE_SHARE の配列。
 */
 DBUG_ASSERT(! my_hash_search(&m_cache,
         (uchar*)テーブル->s->テーブルキャッシュキー.str、
         テーブル->s->table_cache_key.length));
 
 if (!(el= 新しい Table_cache_element(table->s)))
  true を返します。
 
 (my_hash_insert(&m_cache, (uchar*)el) の場合)
 {
  el を削除します。
  true を返します。
 }
 
 table->s->cache_element[table_cache_manager.cache_index(this)] = el;
 }
 
 /* 使用テーブルリストにテーブルを追加します */ 
 el->used_tables.push_front(テーブル);
 
 m_table_count++; 必要に応じて未使用のテーブルを解放します(thd);
 
 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 リスト内の冗長キャッシュがクリアされます。このうち、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 内のテーブル キャッシュがクリアされます。このように、Open_tables の値である m_table_count は通常 2000 前後に留まります。

void Table_cache::free_unused_tables_if_necessary(THD *thd)
{
 /*
 TABLE インスタンスが多すぎるので、それらを削除してみましょう。
 
 複数のTABLEオブジェクトを解放する必要がある場合があることに注意してください。
 table_cache_sizeが動的に変更される場合は、以下のループが必要です。
 サーバー実行時に。
 */
 (m_table_count > table_cache_size_per_instance && m_unused_tables) の場合
 {
 mysql_mutex_lock(&LOCK_open);
 (m_table_count > table_cache_size_per_instance && の場合
   m_未使用テーブル)
 {
  テーブル *table_to_free= m_unused_tables;  
  テーブルを削除します。
  テーブルを解放するために intern_close_table() を実行します。
  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_open_cache_overflows が増加します。

4. table_open_cache_instances が 1 から 32 に増加すると、1 つの LOCK_open ロックが 32 個の m_lock ミューテックスに分散され、ロックの競合が大幅に減少します。

/** テーブル キャッシュ インスタンスのロックを取得します。 */
 void lock() { mysql_mutex_lock(&m_lock); }
 /** テーブル キャッシュ インスタンスのロックを解除します。 */
 void ロック解除() { mysql_mutex_unlock(&m_lock); }

問題を解決する

この問題を解決するために、実稼働環境で次の最適化対策も実施しました。
1. 読み取りと書き込みを分離し、読み取りノードを増やして、マスターデータベースへの負荷を分散します。
2. table_open_cache_instances=16; を調整します。
3. table_open_cache=6000; を調整します。

要約する

オープニングテーブル待機問題が発生すると、
1. 頻繁にテーブルを開く SQL ステートメントを見つけ出し、SQL を最適化し、単一の SQL ステートメントで照会されるテーブルの数を減らすか、SQL の同時アクセス頻度を大幅に減らすことをお勧めします。

2. 適切なテーブルキャッシュを設定し、table_open_cache_instances および table_open_cache パラメータの値を増やします。