PERFORMANCE_SCHEMAの概要

セッションを追跡し、どのイベントがデータベースをロードしているかを正確に示す統計パックレポートを削除する機能がないため、MySQLには多くの石が投げられました。 バージョン5.5以降、MySQLはこの問題を解決し、将来的にMySQLで同様のツールの作成につながる可能性のあるプロトタイプを作成する必要性に困惑しました。 今日の私の話は、 PERFORMANCE_SCHEMAのような強力な（残念ながらMySQL開発者のみ）ツールについてです。 そのため、my.cnf構成ファイルでperformance_schema = ONを設定し、その制限されているが既に非常に興味深い機能の調査を開始します。

まず、少しの理論

このスキームで作業するためにあなたを待っているレーキを踏むことを避けるために、私は理論に少し遠足をします。 このスキームがどのように機能するかを既に知っているが、それがどのように適用されるかをまだ理解していない場合、実際の部分に直接行くことができます。

まず第一に。 PERFORMANCE_SCHEMAは静的です。 これは私たちにとって何を意味するのでしょうか？ そして、なぜそれが起こったのですか？ MySQLはミスから少し学習しており、 INFORMATION_SCHEMA 、特にINFORMATION_SCHEMA.PROCESSLISTビューで作業した後、システムビューでの作業方法を変更しました。 SHOW PROCESSLISTコマンド（など）を使用すると、サーバー＃56299 、 ＃61186がフリーズする場合があります。 この誤解の理由はバグ＃42930にあり、このコマンドを実行するとLOCK_thread_countロックが設定されることが明確に示されています。 これは、MySQLができないという事実につながります。新しいセッションの接続、期限切れの切断、新しいバイナリログの作成など。 このミューテックスをロックすることは、エンジン全体にとって重要です。 これは、スレッドによる動的メモリ割り当ての可能性のために行われました。 そのため、ある時点で、セッションパラメータを要求するときに、ビューは解放されたメモリ領域にアクセスせず、エラーを受け取り、サーバー全体をいっぱいにします。 ソースコードPERFORMANCE_SCHEMAは、mallocタイプのメモリの動的割り当てにコマンドを使用しません。 メモリの割り当ては、サーバーの起動時に一度だけ実行されます。 したがって、このスキームのすべてのパラメーターを実行時に変更することはできません。 一方で、これはロックなしでこの回路を動作させることを可能にし、他方では、その構成にいくつかの問題があり、そのいくつかはPERFORMANCE_SCHEMA ENGINEに含まれるツールによってバイパスされます 。 起動後、サーバーを再起動せずにこのエンジンを完全に無効にすることはできません。

第二に。 この機能はデータベース全体を監視するため、当然いくつかのリソースを消費します。 消費されるリソースの量は、要求を実行することで表示できる回線の構成によって異なります。

  'performance％'などの変数を表示します。
 + ------------------------------------------------- -+ --------- +
 | 変数名| 価値|
 + ------------------------------------------------- -+ --------- +
 |  performance_schema | オン| 
 |  performance_schema_max_cond_instances |  1000 | 
 |  performance_schema_max_file_instances |  10000 | 
 |  performance_schema_max_mutex_instances |  1,000,000 | 
 |  performance_schema_max_rwlock_instances |  1,000,000 | 
 |  performance_schema_max_table_instances |  50000 | 
 |  performance_schema_max_thread_instances |  1000 | 
 |  performance_schema_max_file_handles |  32768 | 
 |  performance_schema_max_table_handles |  100,000 | 
 |  performance_schema_events_waits_history_long_size |  10000 | 
 |  performance_schema_events_waits_history_size |  10 | 
 |  performance_schema_max_cond_classes |  80 | 
 |  performance_schema_max_file_classes |  50 | 
 |  performance_schema_max_mutex_classes |  200 | 
 |  performance_schema_max_rwlock_classes |  30 | 
 |  performance_schema_max_thread_classes |  50 | 
 + ------------------------------------------------- -+ --------- + 

最初のパラメーター- インスタンスが最も重要です。その十分な数により、データベースで発生するすべてを完全に監視できます。 ただし、ここでの主なことは無理しないことです。これは、 mutex + rwlockのペアがbuffer_poolの 16キロバイトブロックごとに作成されるためです 。 つまり、データベースに十分な量のRAMが割り当てられていると、リソースの消費量が消費され、これらのパラメーターを小さく設定しすぎると、すべてのシステムプロセスを監視できなくなります。 あなたがそれらを正しく設定したことを理解する方法私は少し低く言うでしょう。

ハンドルのパラメーターによると、名前からすべてが明らかだと思います。 これは、監視できる開いているテーブルとファイルの最大数です。

サイズパラメータのサイズは、データベースの負荷によって異なります。 あなたは自分でそれを拾う必要があります。 たとえば、クエリを開始するときに、その統計をevents_waits_history_longで確認する時間がない場合、このパラメータを増やすことを検討する必要があります。

クラスパラメータは、監視対象オブジェクトのタイプの最大数を示すため、理論上はあまり重要ではありません。 ここでは、プラグイン開発者に説明する必要があります。 監視に何らかの種類のミューテックスを含める必要があると考える場合、通常はその番号を示します。 たとえば、 InnoBDエンジンにはこのようなクラスが35個しかありません。

これらのパラメータを設定するときに貪欲である必要はないことに注意することが重要です。近くに設定すると、配列の空の要素を検索すると、 PERFORMANCE_SCHEMAが非常に遅くなります。 理論的には論理的です。90％の空の配列で空の要素を見つけることは、90％の空の配列でそれを見つけるよりも簡単だからです。

そのため、 PERFORMANCE_SCHEMAをオンにすると、リソースを消費し始めます（時には非常に困難になります）。 たとえば、産業データベースの1つの結果です。

 エンジンperformance_schemaステータスを表示します。
 + -------------------- + ---------------------------- ---------------------------- + ----------- +
 | タイプ| 名前| ステータス|
 + -------------------- + ---------------------------- ---------------------------- + ----------- +
 ...
 |  performance_schema |  events_waits_history.memory |  1,200,000 | 
 ...
 |  performance_schema |  events_waits_history_long.memory |  1,200,000 | 
 ...
 |  performance_schema |  mutex_instances.memory |  136000000 | 
 ...
 |  performance_schema |  rwlock_instances.memory |  200000000 | 
 ...
 |  performance_schema |  file_instances.memory |  6240000 | 
 ...
 |  performance_schema |  events_waits_summary_by_thread_by_event_name.memory |  17280000 | 
 ...
 |  performance_schema |  （pfs_table_share）.memory |  24,400,000 | 
 ...
 |  performance_schema |  performance_schema.memory |  394468704 | 
 + -------------------- + ---------------------------- ---------------------------- + ----------- + 

400Mbの空きRAMがある場合は、オンにします！

メモリ消費に加えて、このスキームはクエリ実行の速度も低下させます。 一部の推定によると、すべてのサブスクライバー（以下で説明します）では、パフォーマンスの低下は読み取りで約25％、書き込みで19％に達しますが、読み取りのアクティビティを8％監視せずに単にperformance_schema = ONに設定します 。 そして、あなたはそのようにそれをオフにすることはできないという事実を考慮して-注意してください。

3つ目の注意点は、監視の品質です。 上記で書いたように、大規模なデータベースを使用している場合、デフォルトで設定されているいくつかのパラメーターは適切ではない可能性があります。 コマンドを実行することで、この迷惑な誤解について学ぶことができます

  「performance％」などのステータスを表示します。
 + ------------------------------------------ + ------ -+
 | 変数名| 価値|
 + ------------------------------------------ + ------ -+
 |  Performance_schema_cond_classes_lost |  0 | 
 ...
 |  Performance_schema_thread_instances_lost |  0 | 
 + ------------------------------------------ + ------ -+ 

カウンターのいずれかがゼロより大きい値をとる場合、対応するパラメーターを増やす必要があります。

4番目。 監視対象を正確に理解し、どのパラメータがこれを担当しているのかを既に理解している場合、システムのリソースの一部を節約できます。 この表を更新してください。

  select * from setup_consumers;
 + ---------------------------------------------- +- ------- +
 |  NAME | 有効|
 + ---------------------------------------------- +- ------- +
 |  events_waits_current | はい| 
 |  events_waits_history | はい| 
 |  events_waits_history_long | はい| 
 ...
 |  file_summary_by_event_name | はい| 
 |  file_summary_by_instance | はい| 
 + ---------------------------------------------- +- ------- + 

またはこれ

  setup_instrumentsから*を選択します。
 + ------------------------------------------------- ----------- + --------- + ------- +
 |  NAME | 有効| 時限|
 + ------------------------------------------------- ----------- + --------- + ------- +
 |  wait / synch / mutex / sql / PAGE ::ロック| はい| はい| 
 |  wait / synch / mutex / sql / TC_LOG_MMAP :: LOCK_sync | はい| はい| 
 ...
 |  wait / io / file / innodb / innodb_temp_file | はい| はい| 
 + ------------------------------------------------- ----------- + --------- + ------- + 

5番目。 構成パラメーター、照会、または他の最適化を変更し、結果を評価したい場合、サーバーを再始動する必要はありません。 PERFORMANCE_SCHEMAのすべてのサマリーテーブルを単純にランク付けして、新しい結果を取得して1時間待機し、統計を蓄積して、現在のテーブルと履歴テーブルをすべてクリアできます。

最後に注目したいことです。 パフォーマンスを分析するとき、多くの場合、すべてのイベントに対する期待を、一部の抽象的な単位だけでなく、生き生きとした具体的な秒単位で確認したいことがあります。 簡単です。

  setup_timersから*を選択します。
 + ------ + ------------ +
 |  NAME |  TIMER_NAME |
 + ------ + ------------ +
 | 待って| サイクル| 
 + ------ + ------------ + 

1つの注意点があります。 プロセッササイクルで作業している場合、可能な限り正確な値が得られます。秒に切り替えると、かなりのエラーが発生します。このエラーの値は各マシンに固有であり、テーブルから検出できます。

  * from performance_timersから選択します。
 + ------------- + ----------------- + ----------------- -+ ---------------- +
 |  TIMER_NAME |  TIMER_FREQUENCY |  TIMER_RESOLUTION |  TIMER_OVERHEAD |
 + ------------- + ----------------- + ----------------- -+ ---------------- +
 | サイクル|  2395388059 |  1 |  72 | 
 | ナノセコンド|  1,000,000,000 |  1000 |  513 | 
 | ミクロコンド|  1,000,000 |  1 |  207 | 
 | ミリセコンド|  1038 |  1 |  225 | 
 | ティック|  101 |  1 |  531 | 
 + ------------- + ----------------- + ----------------- -+ ---------------- + 

練習から、サイクルをナノ秒に変換するためのそのような（〜500）オーバーヘッドは、テストの合計時間の約5％を浪費すると言うことができます。 しかし、これらのオーバーヘッドが12％以上を占める車を見ました。 そのため、リアルタイムで結果を解釈するときは注意してください。



それで、セットアップを整理しました。 それでは、負荷に関するデータを見ることができる場所から始めましょう。最も重要なのはどのようにですか。

 テーブルを表示します。
 + ---------------------------------------------- +
 |  Tables_in_performance_schema |
 + ---------------------------------------------- +
 ...
 |  events_waits_current | 
 |  events_waits_history | 
 |  events_waits_history_long | 
 |  events_waits_summary_by_instance | 
 |  events_waits_summary_by_thread_by_event_name | 
 |  events_waits_summary_global_by_event_name | 
 ...
 |  file_summary_by_event_name | 
 |  file_summary_by_instance | 
 ...
 | スレッド| 
 + ---------------------------------------------- + 

スレッド-INFORMATION_SCHEMA.PROCESLISTにまだ存在するプロセスへのリンクが含まれます。プロセスが停止した場合、スレッドはしばらくの間そのままになりますので、参加する場合は外部接続を使用してください。

events_waits_current （ history 、 hisotry_long ）-最新の待機イベントが含まれ、テーブルのサイズはパラメーターによって設定されます。 これは、ソース行へのリンクが含まれているため、理解できないロックに直面している人や、コードのどのセクションが遅くなっているのかを理解しようとしている人にとって最も必要なテーブルです。

events_waits_summary-病院の平均気温を取得できる情報が含まれています。 DBAにとって最も興味深いテーブル。

file_summary-入力出力の統計。最もホットなテーブルを取得できます。 ここで、データベースの作成時にinnodb_file_per_table = 1を設定しなかった場合、ここでInnoDBエンジンに適したものを表示できないことを理解する必要があります。

すべてのイベントテーブルには、入出力に関するすべての情報も含まれます。 file_summaryでより理解しやすい方法で複製される可能性が高いため、 「wait / io / file /％」などのevent_nameを要求から無視することをお勧めします。

私たちは理論を終えたようです。実践に移りましょう。 なぜこれがすべて必要なのか、このスキームからどのような有用性が得られるのか。

練習する

ごめんなさい 現時点では、このスキームから必要な情報を抽出することはできません。主なアプローチについて説明します。

最もホットなテーブル

  select substring_index（file_name、 '/'、-1）file_name、event_name、count_read、count_write from file_summary_by_instance where COUNT_READ + COUNT_WRITE> 0 order by COUNT_READ + COUNT_WRITE desc limit 30;
 + ------------------------------------- + ----------- --------------------------- + ------------ + --------- ---- +
 | ファイル名|  event_name |  count_read |  count_write |
 + ------------------------------------- + ----------- --------------------------- + ------------ + --------- ---- +
 |  proc.MYD |  wait / io / file / myisam / dfile |  102716781 |  0 | 
 |  ib_logfile1 |  wait / io / file / innodb / innodb_log_file |  0 |  1,008,958 | 
 |  innodb1 |  wait / io / file / innodb / innodb_data_file |  43106 |  958070 | 
 |  event.MYD |  wait / io / file / myisam / dfile |  633053 |  126470 | 
 |  ＃sql149f_301db7_b.frm |  wait / io / file / sql / FRM |  180012 |  0 | 
 ...
 |  event.MYI |  wait / io / file / myisam / kfile |  0 |  126466 | 
 |  ＃sql149f_337a12_3.frm |  wait / io / file / sql / FRM |  71372 |  0 | 
 ...
 |  job_events＃P＃job_events_201108.ibd |  wait / io / file / innodb / innodb_data_file |  8 |  34662 | 
 ...
 |  olap_transactions_hourly_amount.frm |  wait / io / file / sql / FRM |  3637 |  8264 | 
 + ------------------------------------- + ----------- --------------------------- + ------------ + --------- ---- +

次のことに注意してください。

proc.MYD-プロシージャコードを含むディレクトリで、何も書き込まず、たくさん読みます。 プロシージャがキャッシュされ、接続プールがオンになるようにドライバーを設定する価値があります。

ib_logfile1-さて 、ここで私たちは無力です、それはまだInnoDBです。

innodb1 - innodb_file_per_table = 1を設定する前にInnoDBテーブルを作成したか、MySQL 5.6で彼がそこに書いていることの詳細な説明を待っています。

event.MYD 、 event.MYI-まあ、 私はすでにこの製品の品質について話しました。

tmp /＃sql149f_301db7_b.frmおよびそれらに類似したもの-誰かが積極的に一時テーブルを使用している-イベントでより詳細な情報を探す必要があります。 イベントの場合は、 プロセスリストに参加できます。運がよければ、それらを赤字でキャッチできます。

job_events＃P＃job_events_201108.ibdは、innodbの最もホットなテーブルの1つです。 私たちはたくさん書いていますが、何も読まないで、建築について考えてみる価値はあります。

olap_transactions_hourly_amount.par-ここでは、すべてが正直に思えます。パーティションを変更するたびに、読み取りレベルは記録レベルにほぼ対応します。

これは単なる分析の一例です;もちろん、テーブルの数は少ないかもしれませんが、あなたと管理者は各テーブルで何が行われているかを知る必要があります。

最もホットなロック

  event_name、source、sum（timer_wait）timer_waitをevents_waits_history_longから選択します。event_nameは「wait / io / file％」グループとは異なり、event_nameでグループ化します。ソースの順序は3 desc limit 30です。
 + ------------------------------------------------- ----- + -------------------- + --------------- +
 |  event_name | ソース|  timer_wait |
 + ------------------------------------------------- ----- + -------------------- + --------------- +
 | 待機/同期/ cond / sql / COND_queue_state |  event_queue.cc:765 |  1998358450083 | 
 | 待機/同期/ rwlock / myisam / MYISAM_SHARE :: key_root_lock |  mi_rnext.c：43 |  82397115 | 
 ...
 |  wait / synch / mutex / sql / LOCK_plugin |  sql_plugin.cc:744 |  607986 | 
 + ------------------------------------------------- ----- + -------------------- + --------------- + 

（この表の情報はライブデータベースで非常に迅速に更新されるため、 events_waits_summary_by_thread_by_event_nameを使用して病院の平均気温を正確に計算することをお勧めします。特定のコード行はありませんが、情報はより客観的です。

1位と2位のギャップは怖いです。 ソースに行きます。

  if（！thd-> killed）
   {
     if（！abstime）
       mysql_cond_wait（＆COND_queue_state、＆LOCK_event_queue）;
    他に
       mysql_cond_timedwait（＆COND_queue_state、＆LOCK_event_queue、abstime）;  //ここが私たちの行です
   } 

まあ、それは大丈夫のようです、それはただのスリップです。 ただし、MySQLのソースコードを深く掘り下げることは決してcamp行ではありません。 これなしでデッドロックが発生する場合は、ミューテックスに起因する最も一般的な問題の概要を説明します。

MySQLの最近のバージョンでは、昇格された機能innobd_adaptive_hash_indexが登場しました 。 ご存知のように、Bツリー操作での検索は最適ではありません。 したがって、テーブルが小さく、インデックスがRAMに収まる場合、MySQLはこのバッファーにハッシュマップの形式でこのインデックスのコピーを作成します。 したがって、検索は可能な限り高速になります。 ただし、このソリューションには1つの落とし穴があります。 このキャッシュには、同期ミューテックスが1つしかありません。 このクリティカルセクションへのアクセスは、テーブルまたはインデックスの読み取りと書き込みの両方で実行されるため、競合するトランザクションが大量にあり、このミューテックスがホットになると、クエリの上部に表示されます。

| wait/synch/rwlock/innodb/btr_search_latch | btr0sea.c:XXX | 183897 |





その場合、このオプションを無効にすることを検討する必要があります。

InnoDBは、スレッドの代わりにアトミック操作を使用して、ミューテックスと読み取り/書き込みロックを実装します。 このような実装は、現在マルチプロセッサシステムに最適と見なされています。 私が理解している限り、ロックの代わりに、いくつかのカウンターがそこで増加します。 最上部でwait / synch / mutex / innodb / rw_lock_mutexまたはwait / synch / mutex / innodb / thr_local_mutexのような待機タイプに気付いた場合、ソースに移動してこれらの期待の行が#ifdef INNODB_RW_LOCKS_USE_ATOMICSセクションの下にあるかどうかを確認するのが面倒ではありません初期のコンパイラであり、 ＃else / * INNODB_RW_LOCKS_USE_ATOMICS * /に移動するため、このテクノロジーの魅力をすべて使用することはありません。

最新のInnoDBプラグインの1つで、特別なmutex wait / synch / mutex / innodb / flush_list_mutexが導入されました 。 ご存知のように、変更後のすべてのダーティブロックはしばらくの間RAMでハングし、しばらくしてからディスクにフラッシュされます。 以前は、バッファプールのミューテックスを使用してリセットを同期していましたが、これは失敗した決定であり、このプロセス用に別のクリティカルセクションが作成されました。 これにより、拡張性が向上し、ディスクへの書き込みプロセスを追跡できます。

多数のミューテックスの期待値wait / synch / rwlock / innodb / checkpoint_lockが表示される場合、ログファイルのサイズを増やすか、 innodb_max_dirty_pages_pctパラメーターの値が低すぎる必要があります。 チェックポイントプロセスの攻撃性を低くしてください。

おそらく最も人気があるのはwait / synch / mutex / innodb / buf_pool_mutexです。 このミューテックスは、データをバッファプールにロードする役割を果たします。 このミューテックスがトップでハングする場合、2つのオプションがあります。可能な限り少ないデータがRAMにロードされるようにデータベースアーキテクチャを変更するか、 XtraDBに切り替えます。 このプラグインがInnoBDよりも大きなデータベースではるかに効率的に動作するようなバージョンを聞いたことがありますが、私は自分でチェックしませんでした。

すべてが悪いと単純に言うロックがありますが、だれもこれであなたを助けることができません。 たとえば、 kernel_mutexのようなロックは、多くのトランザクションを持つ人にとって非常に苦痛です。各トランザクションを作成するとき、現在のトランザクションのリスト全体をメモリ領域にコピーする必要があるためです。 リストがかなり大きい場合、ロック（他のプロセスではバイタル）が長時間保持されます。

私の練習から、innodbプラグインのミューテックスは最もよく説明されていると言えます。

残りについては、ほとんどの場合、ソースを読む必要があります。

また、このスキームを使用すると、インスタンス全体のフリーズ中にロックを追跡できます。 しかし、これはまったく異なる話です。

おわりに

もちろん、このメカニズムは、Oracleで非常に愛されているトレースやstatsパックのパワーに置き換わるものではありませんが、今ではそこに何かがあります。 データベースがフリーズし、どこから始めればよいかわからない場合は、 PERFORMANCE_SCHEMAから始めます。 特にOracleはまだ存在せず、リリース5.6.Xでは、すべてのテーブルとインデックスの使用に関する統計を表示する2つのドロップデッドパフォーマンスが約束されていたため、特にそこには多くの有用な情報が常にあります（私はすでにそこに与えられているものをチェックアウトし、私にこれを個人的に情報は非常に有用であるように見えたため、欠落している、または逆に未使用のインデックスを検索するのは素晴らしいことです。 まあ、私たちは期待して待っています。



Z.Y. 特定のミューテックス問題の解決に直面している場合は、コメントで共有することを忘れないでください。現時点では、ミューテックスに関する完全な情報はないため、 PERFORMANCE_SCHEMAの使用に切り替えた多くの人に役立つと思います