高性能MySQL 4th Nnwww 読書ログ 第3章 Performance Schema
流れとしては本を追うが、正確な翻訳は目的ではないので必要な部分だけ抜粋、補足も付ける
あとで構造化する
この章原文からしてわからづらい...
高負荷に晒されたデータベースのチューニングは反復作業です。チューニングのために変更を行うたび、あなたはその変更による影響を理解する必要があります。クエリは以前よりも速く実行されていますか?ロックによってアプリケーションの速度が低下したり、完全に失われたりしていませんか?メモリ使用量は変化しましたか?ディスクを待つ時間は変わりましたか?これらの質問に答える方法を理解すると、日々の状況をより迅速かつ自信を持って評価し、対応できるようになります。
パフォーマンススキーマは、これらの質問に答えるために必要なデータを格納するデータベースです。
この章ではMySQL内部で起きていることを表す情報について明らかにするため、パフォーマンススキーマとsysスキーマについて以下を説明します
それらはどのように動作するか
その制約は何か
どのように使用するのが最適であるか
Introduction to Performance Schema
パフォーマンススキーマは、MySQLサーバー内で実行される操作に関する低レベルのメトリックを提供します。パフォーマンススキーマのしくみを説明するために、先に紹介する必要がある2つの概念があります。
1つはインストゥルメントです。インストゥルメントとは、MySQLコードのうち私たちが情報を取得したい任意の部分を指します。たとえば、メタデータのロックに関する情報を集める場合は、 wait/lock/metadata/sql/mdl インストゥルメントを有効にする必要があります。
2番目の概念はコンシューマで、インストゥルメント化されたコードに関する情報を格納する単純なテーブルです。クエリをインストゥルメント化すると、コンシューマは実行された回数、インデックスが参照されなかった回数、実行時間などの情報を記録します。コンシューマは、ほとんどの人がパフォーマンススキーマと密接に関連付けています。
アプリケーションユーザーがMySQLに接続し、インストゥルメント化された命令を実行すると、performance_schemaは調査する各呼び出しの開始時と終了時にマクロを差し込むことで、対応するコンシューマテーブルに結果を記録します。ここで重要なのは、インストゥルメントを有効にすると追加のコードが呼び出されるため、CPU消費量が増加することです。
Instrument Elements
performance_schemaでは、サポートするすべてのインストゥルメントのリストがsetup_instrumentsテーブルに含まれています。すべてのインストゥルメントの名前は、スラッシュで区切られたパーツで構成されます。これらの命名規則について次の例で説明します
例
statement/sql/select
wait/synch/mutex/innodb/autoinc_mutex
インストゥルメント名の左端の部分は、インストゥルメントの種類を示します。したがって、statementはインストゥルメントがSQLステートメント関連であることを示し、waitは待機時間関連であることを示します。
残りの部分はサブカテゴリを示しており、左がより一般的で右がより特定の領域を指しています。
ほとんどのインストゥルメント名は名前の通り(self-descriptive)です。statement/sql/selectはSELECTクエリであり、wait/synch/mutex/innodb/autoinc_mutexはInnoDBがAUTO_INCREMENTカラムに設定するmutexです。setup_instrumentsテーブルには、より詳細な情報を含むDOCUMENTATIONカラムもあります。
setup_instrumentsを見てみる
code: mysql
mysql> SELECT * FROM performance_schema.setup_instruments
-> WHERE DOCUMENTATION IS NOT NULL LIMIT 5, 5\G
*************************** 1. row ***************************
NAME: statement/sql/error
ENABLED: YES
TIMED: YES
PROPERTIES:
VOLATILITY: 0
DOCUMENTATION: Invalid SQL queries (syntax error).
(以下略)
残念ながら、多くのインストゥルメントではDOCUMENTATIONカラムがNULLになる可能性があるため、インストゥルメント名、直感、およびMySQLソースコードの知識を使用して、特定のインストゥルメントが何を検査するかを理解する必要があります。
Consumer Organization
先に述べたように、コンシューマは、インストゥルメントの情報送信先です。パフォーマンススキーマは、インストゥルメントの結果を多数のテーブルに格納します。実際、MySQL Community 8.0.25 ではperformance_schemaに110個のテーブルが含まれています。それらが何を意図しているのかを理解するには、下記に続くグループを意識すると簡単です。
Current and historical data
まずイベントは、名前が次のように終わるテーブルに格納されます。
*_current
現在サーバーで発生しているイベント
*_history
スレッド毎の完了したイベント最新10件
件数は設定可能
*_history_long
最新10000件版、グローバル (Last 10,000 completed events per thread, globally)
(グローバルってなに?)
件数は設定可能
*_currentと*_historyは以下のデータで利用可能です。
events_waits
mutexの取得など、低レベルのサーバー待機時間
events_statements
SQLステートメント
events_stages
一時テーブルの作成やデータの送信などのプロファイル情報
events_transactions
トランザクション
avashe.icon infomation_schemaを使えば見たいテーブルを絞り込みやすい、以下はhistory_longが実際についているperformance_shemaのテーブルを列挙する例
code:sql
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA="performance_schema" and TABLE_NAME like "%history_long";
+----------------------------------+
| TABLE_NAME |
+----------------------------------+
| events_stages_history_long |
| events_statements_history_long |
| events_transactions_history_long |
| events_waits_history_long |
+----------------------------------+
Summary tables and digests
ダイジェストは、クエリの細かな差異を削除してクエリを集計する方法です。次の問合せの例を参照してください。
MySQLサーバーはSQLステートメントのダイジェスト情報を保持できます。ダイジェストを実行するプロセスは、各SQLステートメントを正規化された形式 (statement digestと呼ぶ) に変換し、正規化された結果からSHA-256ハッシュ値 (ダイジェストハッシュ値) を計算します。正規化では、類似したステートメントをグループ化して要約し、サーバーが実行しているステートメントの種類とその発生頻度に関する情報を公開できます。ダイジェストごとに、ダイジェストを生成する代表的なステートメントがサンプルとして格納されます。
code: sql
SELECT user,birthdate FROM users WHERE user_id=19;
SELECT user,birthdate FROM users WHERE user_id=13;
SELECT user,birthdate FROM users WHERE user_id=27;
ダイジェストではこうなります
code: sql
SELECT user,birthdate FROM users WHERE user_id=?
これにより、パフォーマンススキーマは、クエリの各バリエーションを個別に保持する必要なく、ダイジェストのレイテンシなどのメトリクスを追跡できます。
Instances
インスタンスは、MySQLが利用するオブジェクトを参照します。たとえば、file_instancesテーブルはファイルIOが生じているときにperformance_schemaが参照するファイル名と、これらのファイルにアクセスするスレッドの数が含まれます。
Setup
Setupテーブルはperformance_schemaの実行時セットアップのために使用される
Other tables
名前が厳密なパターンに従っていないOtherテーブルというテーブルがあります。たとえば、metadata_locksテーブルにはメタデータのロックに関するデータが保持されます。performance_schemaが解決に役立つ問題について議論する際に、この章の後半でいくつか紹介します。
Resource Consumption
パフォーマンススキーマによって収集されたデータは、メモリに保持されます。コンシューマーの最大サイズを設定することで、使用するメモリ量を制限できます。performance_schemaの一部のテーブルは自動スケールをサポートしています。つまり、起動時に最小限のメモリを割り当て、必要に応じてサイズを調整します。ただし、特定のインストゥルメンテーションを無効にしてテーブルを切り捨てた場合でも、このメモリは一度割り当てられると解放されません。
前述したように、インストルメント化された呼び出しごとに、performance_schemaにデータを格納するためのマクロ呼び出しがさらに2つ追加されます。つまり、多く計測するほど、CPU使用率が高くなります。CPU使用率への実際の影響は、インストゥルメントによって異なります。たとえば、SQLステートメント関連のインストゥルメントが呼ばれるのはクエリ中に1度だけかもしれませんが、待機時間(wait)のインストゥルメントはより頻繁に呼ばれるかもしれません。たとえば、100万行あるInnoDBテーブルをスキャンするには、100万行分の行ロックをかけ、解放する必要があります。このときインストゥルメントでロックを計測すると、CPU使用率が大幅に増加する可能性があります。その代わりにstatement/sql/selectを使えば1回の呼び出しですみます。したがって、SQLステートメント関連のインストゥルメントを有効にしても、CPU負荷が増加することはありません。メモリまたはメタデータロックのインストゥルメントについても同じです。
Limitations
performance_schemaを設定して使用する方法を説明する前に、その制限を理解しておくことが重要です。
MySQLコンポーネントでサポートされている必要がある
たとえば、メモリ関連のインストゥルメントを使用して、どのMySQLコンポーネントがメモリの大部分を消費しているか調査しているとしましょう。あなたは最もメモリを使っているコンポーネントが、メモリインストゥルメントをサポートしないストレージエンジンであることに気づきました。この場合、メモリがどこに使われたのか探すことはできません。
特定のインストゥルメントとコンシューマが有効になったときのみデータを収集できる
たとえば、すべてのインストゥルメントを無効にしてサーバーを起動し、その後メモリ使用量をインストゥルメントしようとしても、InnoDBバッファプールなどのグローバルバッファによって既に割り当てられた正確なメモリ量を知ることはできません。メモリインストゥルメントを有効にする前にすでに割り当てられているためです。
メモリを解放するのは困難である
コンシューマのサイズは、起動時に指定して制限することできますし、自動サイズ設定のままにすることもできます。後者の場合、起動時にメモリは割り当てられず、有効なデータが収集されたときにのみ割り当てられます。ただし、後で特定のインストゥルメントまたはコンシューマーを無効にしても、サーバーを再起動しない限り、メモリは解放されません。
この章の残りの部分では、これらの制限を認識していることを前提としているため、特にこれらの制限に焦点を当てることはしません。
sys Schema
バージョン5.7以降、標準のMySQLディストリビューションには、sysスキーマと呼ばれるperformance_schemaのコンパニオンスキーマが含まれています。このスキーマは、performance_schemaのビューとストアドルーチンのみで構成されています。performance_schemaをよりスムーズに使用できるように設計されていますが、それ自体にはデータは格納されません。
NOTE: sysスキーマは非常に便利ですが、performance_schemaテーブルに格納されているデータにのみアクセスすることを覚えておく必要があります。sysスキーマから利用できないデータが必要な場合は、performance_schemaの基になるテーブルにデータが存在するか確認してください。
Understanding Threads
MySQLサーバはマルチスレッドソフトウェアです。サーバの各コンポーネントはスレッドを使用します。たとえば、メインスレッドやストレージエンジンによって作成されたバックグラウンドスレッド、またはユーザー接続用に作成されたフォアグラウンドスレッドなどがあります。各スレッドには、少なくとも2つの一意な識別子があります。たとえば、Linuxの ps -eLf コマンドの出力に表示されるosのスレッドIDと、内部的なMySQLスレッドIDです。この内部的なMySQLスレッドIDは、ほとんどのperformance_schemaテーブルでTHREAD_IDと呼ばれます。さらに、各フォアグラウンドスレッドにはPROCESSLIST_IDが割り当てられています。これはコネクションの識別子で、MySQLのコマンドラインクライアントで接続するときに、SHOW PROCESSLISTコマンドの出力または"Your MySQL connection id is"という文字列に続いて表示されます。
WARNING: THREAD_ID と PROCESSLIST_ID は同じではありません
performance_schemaのthreadsテーブルには、サーバーに存在するすべてのスレッドが含まれます
code: sql
mysql> SELECT NAME, THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID
-> FROM performance_schema.threads;
+------------------------+-----------+----------------+--------------+
| NAME | THREAD_ID | PROCESSLIST_ID | THREAD_OS_ID |
+------------------------+-----------+----------------+--------------+
| thread/sql/main | 1 | NULL | 797580 |
| thread/innodb/io_ib... | 3 | NULL | 797583 |
| thread/innodb/io_lo... | 4 | NULL | 797584 |
...
| thread/sql/slave_io | 42 | 5 | 797618 |
| thread/sql/slave_sql | 43 | 6 | 797619 |
| thread/sql/event_sc... | 44 | 7 | 797620 |
| thread/sql/signal_h... | 45 | NULL | 797621 |
| thread/mysqlx/accep... | 46 | NULL | 797623 |
| thread/sql/one_conn... | 27823 | 27784 | 797695 |
| thread/sql/compress... | 48 | 9 | 797624 |
+------------------------+-----------+----------------+--------------+
44 rows in set (0.00 sec)
スレッドidなどの情報の他、threadsテーブルにはSHOW PROCESSLIST出力と同じデータと、RESOURCE_GROUPやPARENT_THREAD_IDなどのいくつかの追加カラムが含まれています。
WARNING: performance_schemaはすべての場所でTHREAD_IDを使用しますが、PROCESSLIST_IDはthreadsテーブルでのみ使用できます。PROCESSLIST_IDを取得する必要がある場合 (たとえば、ロックを保持している接続を強制終了する場合) 、threadsテーブルを照会してその値を取得する必要があります。
threadsテーブルは、実行中のクエリに関する追加情報を提供するために、他の多くのテーブルにjoinできます(e.g. クエリのデータ、ロック、mutex、開いているテーブルインスタンスなど)。
この章の残りの部分では、このテーブルとTHREAD_IDの意味を理解していただく必要があります。