パフォーマンス負荷診断にMySQLシステムデータベースを使用する方法

ある達人がかつて、自分の妻のことを知るのと同じくらい、自分が管理するデータベースのことを知るべきだと言いました。個人的には、これには 2 つの理解の側面が含まれると考えています。

1. 安定性の面では、高可用性、読み書き分離、負荷分散、災害復旧管理などの高レベルの対策に重点が置かれています（生命の安定性を確保するのと同じように）

2. インスタンスレベルでは、メモリ、IO、ネットワーク、ホットテーブル、ホットインデックス、トップ SQL、デッドロック、ブロッキング、履歴的に異常な実行がある SQL (生活の質の詳細など) に注意する必要があります。MySQL の performance_data ライブラリと sys ライブラリは、非常に豊富なシステムログデータを提供するため、詳細をよりよく理解するのに役立ちます。以下は、よく使用されるデータの簡単なリストです。

sys ライブラリは、 performance_data内のいくつかのテーブルをより読みやすい方法でカプセル化するため、これらのデータのソースは引き続き performance_data ライブラリ内のデータになります。

以下は、一般的に使用されるシステムデータの大まかなリストです。これにより、インスタンスレベルで MySQL 操作中のリソース割り当てをより明確に理解できるようになります。

ステータス情報

MySQL ステータス変数は、一般的な情報のみを提供します。ステータス変数からは、IO またはメモリのホットスポットがどこにあるか、データベースとテーブルのホットスポットがどこにあるかなど、詳細なリソース消費量を知ることはできません。具体的な詳細情報を知りたい場合は、システムライブラリのデータが必要です。

sys ライブラリのビューはperformance_schemaライブラリに基づいているため、前提条件としてperformance_schema有効にする必要があります。

メモリ使用量:

メモリ/innodb_buffer_pool の使用

innodb_buffer_pool の使用状況の概要: 現在のインスタンスには 262144*16/1024 = 4096MB のバッファプールがあり、23260*16/1024 363MB が使用されていることがわかります。

innodb_buffer_pool が占有するメモリに関する詳細情報。これは library\table ディメンションごとにカウントできます。

セッショントランザクション分離レベルを READ UNCOMMITTED に設定します。
選択 
  データベース名、
  SUM(圧縮サイズ)/1024/1024 割り当てられたメモリとして、
  SUM(データサイズ)/1024/1024 ASデータメモリ、
  SUM(is_hashed)*16/1024 AS is_hashed_memory、
  SUM(is_old)*16/1024 AS is_old_memory
から 
（
 選択 
  INSTR(TABLE_NAME,'.')>0 の場合、replace(SUBSTRING(TABLE_NAME,1,INSTR(TABLE_NAME,'.')-1),'`','') となります。
  それ以外の場合は 'system_database' が database_name として終了します。
  INSTR(TABLE_NAME,'.')>0 の場合、replace(SUBSTRING(TABLE_NAME,INSTR(TABLE_NAME,'.')+1),'`','') となります。
  ELSE 'system_obj' END AS table_name,
  if(compressed_size = 0, 16384, compressed_size) AS compressed_size、
  データサイズ、
  if(is_hashed = 'YES',1,0) is_hashed、
  if(is_old = 'YES',1,0) is_old
 information_schema.innodb_buffer_page から
 TABLE_NAMEがNULLではない場合
）
GROUP BY データベース名
割り当てられたメモリの順序で並べ替え
制限 10;

データベースとテーブルの読み取りと書き込みの統計、論理レベルでのホットデータ統計

対象テーブルはperformance_schema.table_io_waits_summary_by_tableです。一部の記事では、これは論理 IO であると書かれています。実際は、論理 IO とは何の関係もありません。このテーブルのフィールドの意味は、テーブルに読み書きされた行数の統計に基づいています。実際の論理 IO レベルの統計に関しては、現在、どの利用可能なシステムテーブルをクエリすればよいかわかりません。このライブラリでは、この表の統計結果がどのように計算されるかを明確に示すことができます。

これはテーブルに読み書きされた行数に基づく累積値です。個人的には、この値自体を見るだけではあまり意味がないと思っています。意味のある参照値にするには、定期的に差分を収集して計算する必要があります。

次の表は、ライブラリレベルでの読み取りおよび書き込みのステータスを示しています。

ライブラリとテーブルの読み取りおよび書き込み統計、および物理 IO レベルでのホットデータ統計

ホットスポットデータは物理 IO ディメンションに基づいてカウントされ、どのライブラリとテーブルがどれだけの物理 IO を消費するかを判断します。ここでの元のシステムテーブルのデータは累積統計値です。最も極端なケースは、テーブルに行が 0 個あるが、物理的な読み取りおよび書き込み IO が多数ある場合です。

セッショントランザクション分離レベルを READ UNCOMMITTED に設定します。
選択 
 データベース名、
 IFNULL(cast(sum(total_read) as signed),0) AS total_read,
 IFNULL(cast(sum(total_written) as signed),0) AS total_written,
 IFNULL(cast(sum(total) AS SIGNED),0) AS total_read_written
から
（
 選択 
  部分文字列(REPLACE(ファイル, '@@datadir/', ''),1,instr(REPLACE(ファイル, '@@datadir/', ''),'/')-1) AS database_name,
  カウント_読み取り、
  場合 
   instr(total_read,'KiB')>0 の場合、replace(total_read,'KiB','')/1024
   instr(total_read,'MiB')>0 の場合、replace(total_read,'MiB','')/1024
   instr(total_read,'GiB')>0 の場合、replace(total_read,'GiB','')*1024
  END AS total_read、
  場合 
   instr(total_written,'KiB')>0 の場合、replace(total_written,'KiB','')/1024
   instr(total_written,'MiB')>0 の場合、replace(total_written,'MiB','')
   instr(total_written,'GiB')>0 の場合、replace(total_written,'GiB','')*1024
  END AS total_written、
  場合 
   instr(total,'KiB')>0 の場合、replace(total,'KiB','')/1024
   instr(total,'MiB')>0 の場合、replace(total,'MiB','')
   instr(total,'GiB')>0 の場合、replace(total,'GiB','')*1024
  END AS合計
 sys.io_global_by_file_by_bytes から 
 WHERE FILE LIKE '%@@datadir%' AND instr(REPLACE(file, '@@datadir/', ''),'/')>0 
)t
GROUP BY データベース名
ORDER BY total_read_written DESC;

追記: 私は個人的に、MySQL のカスタム format_*** 関数が好きではありません。この関数の本来の目的は優れており、一部のデータ (時間、ストレージスペースなど) をより読みやすいモードにフォーマットします。ただし、単位パラメータはサポートされていません。ほとんどの場合、固定単位で表示する必要があります。たとえば、時間をフォーマットする場合、単位のサイズに応じて、マイクロ秒、ミリ秒、秒、分、日単位で表示される場合があります。たとえば、時間を一律に秒単位でフォーマットしたい場合、残念ながらサポートされていません。一部のデータは、単に見るだけでは不十分で、アーカイブや分析のために読み出す必要さえあります。したがって、これは推奨されておらず、ここでは使用されません。

TOP SQL統計

実行時間、ブロック時間、返された行数などによって上位の SQL ステートメントをカウントできます。
さらに、last_seen の時間でフィルタリングして、最近特定の期間に出現した上位の SQL をカウントすることもできます。

セッショントランザクション分離レベルを READ UNCOMMITTED に設定します。

選択 
 スキーマ名、
 ダイジェストテキスト、
 カウントスター、
 avg_timer_wait/10000000000000 AS avg_timer_wait、
 max_timer_wait/10000000000000 AS max_timer_wait、
 sum_lock_time/count_star/10000000000000 AS avg_lock_time 、
 sum_rows_affected/count_star AS avg_rows_affected、
 sum_rows_sent/count_star AS avg_rows_sent 、
 sum_rows_examined/count_star AS avg_rows_examined、
 sum_created_tmp_disk_tables/count_star AS avg_create_tmp_disk_tables、
 sum_created_tmp_tables/count_star AS avg_create_tmp_tables、
 sum_select_full_join/count_star は avg_select_full_join として計算されます。
 sum_select_full_range_join/count_star は avg_select_full_range_join として計算されます。
 sum_select_range/count_star AS avg_select_range、
 sum_select_range_check/count_star AS avg_select_range、
 初めて見た、
 最後に見た
performance_schema.events_statements_summary_by_digest から
WHERE last_seen>date_add(NOW(), 間隔 -1 HOUR)
注文する 
最大タイマー待機
--平均タイマー待機
-- 影響を受ける行数の合計/星の数 
-- 合計ロック時間/カウントスター
--平均ロック時間
--平均送信行数
説明
制限 10;

この統計は、ステートメントではなく、トランザクションを実行するために MySQL が消費したリソースに基づいていることに注意してください。著者は最初しばらく混乱しました。簡単な例を挙げてみましょう。

参考までに、ループでデータを書き込むストアドプロシージャを次に示します。呼び出しメソッドは、N 個のテストデータを書き込みするためのcall create_test_data(N)，です。
たとえば、 call create_test_data(1000000) 100 万のテストデータが書き込まれます。この実行プロセスには数分かかります。著者のテストインスタンスによると、 avg_timer_waitのディメンションは間違いなく TOP SQL です。

しかし、クエリを実行しても、このストアドプロシージャの呼び出しが TOP SQL としてリストされていることは一度もありませんでした。その後、ストアドプロシージャ内にトランザクションを追加してみたところ、TOP SQL 全体を正常に収集できました。

したがって、 performance_schema.events_statements_summary_by_digestの統計は、バッチの実行時間ではなく、トランザクションに基づいています。

CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(
 `loopcnt` 内の INT
）
言語 SQL
決定論的ではない
SQLを含む
SQL セキュリティ定義者
コメント ''
始める
 -- トランザクションを開始します。 
  loopcnt>0の間、
   test_mrr(rand_id,create_date) に値 (RAND()*100000000,now(6)) を挿入します。
   loopcnt=loopcnt-1 を設定します。
  終了しながら;
  -  専念;
終わり

もう 1 つの興味深い点は、このシステムテーブルが切り捨てをサポートする数少ないテーブルの 1 つであることです。もちろん、内部的には継続的な収集プロセスも行われています。

失敗したSQL実行の統計

私は、実行に失敗した、または誤って解析された SQL ステートメントはシステムによって記録されないと常に思っていました。たとえば、タイムアウトのために実行に失敗したステートメントをカウントしたいと考えていました。後で、MySQL がこの情報を完全に記録することを知りました。

実行エラーのあるステートメントは、最終的な実行の失敗 (タイムアウトなど)、構文エラー、実行中に生成された警告など、ここに詳細に記録されます。 sum_errors>0 or sum_warnings>0去performance_schema.events_statements_summary_by_digest使用します。

セッショントランザクション分離レベルを READ UNCOMMITTED に設定します。

選択 
 スキーマ名、
 ダイジェストテキスト、
 カウントスター、
 初めて見た、
 最後に見た
performance_schema.events_statements_summary_by_digest から
sum_errors>0 または sum_warnings>0 の場合 
last_seen の降順で並べ替えます。

インデックス使用統計

performance_schema.table_io_waits_summary_by_index_usageシステムテーブルに基づくと、その統計ディメンションも「特定のインデックスクエリによって返される行数」になります。

どのインデックスが最も多く使用されているか、または最も少なく使用されているかに基づいて統計を収集できます。

しかし、この統計には誤解を招く可能性があります。
count_read,count_write,count_fetch,count_insert,count_update,count_deleteインデックスが使用されたときに影響を受ける行の数をカウントし、sum_timer_wait はインデックスでの累積待機時間です。

インデックスが使用されているが、データに影響がない場合（つまり、DML 文の条件によってデータがヒットしていない場合）、count_*** はカウントされませんが、sum_timer_wait はカウントされます。これは誤解を招くポイントです。インデックスは何度もヒットしていないのに、大量の timer_wait が生成されます。インデックスが同様の情報を見ると、インデックスを軽々しく削除することはできません。

待機イベント統計

MySQL データベースでのすべてのアクションには待機 (完了するまでの一定時間) が必要です。合計で 1,000 を超える待機イベントがあり、さまざまなカテゴリに属しています。バージョンごとに異なり、すべての待機イベントがデフォルトで有効になっているわけではありません。

個人的には、待機イベントはあくまでも参考用であり、問題の診断には役立たないと考えています。データベースが最適化されていたり、負荷が低かったりしても、特定のイベントでは一定期間にわたって大量の待機イベントが蓄積されます。

これらのイベントの待機イベントは、必ずしもすべて否定的ではありません。たとえば、同時実行プロセス中にロック待機が発生することがあります。この待機イベントの統計結果も累積的です。直接的な値だけを見ても、参照上の意味はありません。
定期的にデータを収集し、実際の状況に基づいて差異計算を行わない限り、参考値にしかなりません。

セッショントランザクション分離レベルを READ UNCOMMITTED に設定します。

SUBSTRING_INDEX(NAME, '/', 1) を wait_type,COUNT(1) として選択します。 
performance_schema.setup_instruments から
1 でグループ化 
ORDER BY 2 DESC;


選択
イベント名、
カウントスター、
合計タイマー待ち時間
performance_schema.events_waits_summary_global_by_event_name から
WHERE イベント名 != 'アイドル'
sum_timer_wait の降順で並べ替え
制限 100;

最後に、

1. MySQL が提供する多くのシステムテーブル (ビュー) のデータについては、特に avg_*** は累積値であるため、参照するには多くの総合的な要素と組み合わせる必要があるため、値自体を見るだけではあまり意味がありません。
2. システムテーブルに対するクエリは、システムパフォーマンス自体に一定の影響を及ぼす可能性があります。システムに重大な悪影響を与える可能性がある場合は、データ統計を収集しないでください。

要約する

上記は、MySQL システムデータベースを使用してパフォーマンス負荷診断を実行する方法について紹介しました。お役に立てば幸いです。ご質問がある場合は、メッセージを残してください。すぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。
この記事が役に立ったと思われた方は、ぜひ転載していただき、出典を明記してください。ありがとうございます!

以下もご興味があるかもしれません: