本番環境のMySQLにおいて、どのテーブルへの読み込みが多いのか、または書き込みが多いかなど気になることがあると思います。そんな場合に、MySQLはテーブルごとのリクエストやI/Oの統計を確認できるテーブルを提供しています。
今回は、MySQL5.6とそれ以降で閲覧可能なperformance_schema
のtable_io_waits_summary_by_table
とfile_summary_by_instance
について紹介します。今回、使用しているMySQLのバージョンは8.0.14です。
table_io_waits_summary_by_tableテーブル
table_io_waits_summary_by_table
テーブルはユーザテーブル単位の読み込み、書き込みのリクエスト回数やレイテンシーなどを集計した情報を提供します。情報を収集するためには、wait/io/table/sql/handler
instrumentalをYES
にしておく必要があります。performance_schema
が有効な環境では、デフォルトでYES
になっていますので、気にする必要はないと思います。
下記のSQLで確認することができます。
table_io_waits_summary_by_table
テーブル定義は以下のようになっています。
この中から抜粋して、カラムの説明をします。
- OBJECT_TYPE…オブジェクトのタイプ(TABLEなど)を表示。
- OBJECT_SCHEMA…データベース名。
- OBJECT_NAME…テーブル名。
- COUNT_STAR…COUNT_READとCOUNT_WRITEの合計。
- COUNT_READ…COUNT_FETCHの集計と同じ。
- COUNT_WRITE…COUNT_INSERTとCOUNT_UPDATEとCOUNT_DELETEの合計。
- COUNT_FETCH…読み込まれた行の集計。
- COUNT_INSERT…挿入された行の集計。
- COUNT_UPDATE…更新された行の集計。
- COUNT_DELETE…削除された行の集計。
- TIMER関連カラム…操作にかかった合計(SUM)・最小(MIN)・平均(AVG)・最大(MAX)の時間
COUNT_FETCH、COUNT_INSERT、COUNT_UPDATEやCOUNT_DELETEを見ると、どのようなリクエストがテーブルに対して多く行われているかを確認することができます。
例として、t0
テーブルで試してみます。以下のようにテーブルを作成、INSERT文を発行して、COUNT関連のカラムを確認してみます。
INSERT文の発行は1回ですが、2行の挿入があったため、COUNT_INSERTが2となりました。
次にこれらデータをSELECTしてみます。
2行読み込みをしたため、COUNT_FETCHが2となりました。
このように、テーブルに対しての読み込みや書き込みのリクエスト回数を確認することができます。特定の時間におけるリクエスト回数を確認したい場合は、スナップショットを取得して、差分解析などする必要があります。
また、類似テーブルとしてtable_io_waits_summary_by_index_usage
テーブルがあります。これはテーブル単位ではなく、インデックス単位で集計されたテーブルになります。
file_summary_by_instanceテーブル
file_summary_by_instance
テーブルは、ファイルごとの読み込み・書き込みのIO回数やレイテンシーなどを集計した情報を提供します。ファイル単位のIO集計なので、ユーザテーブル(InnoDBテーブルであればibdファイル)だけではなく、エラーログ、バイナリーログやInnoDBログファイルなどのMySQLがIOを行うすべてのファイルの情報を提供します。
file_summary_by_instance
テーブル構成は以下のようになっています。
この中から抜粋して、カラムの説明をします。
- FILE_NAME…ファイル名。InnoDBテーブルであればibdファイル。
- EVENT_NAME…イベント名。
- COUNT_STAR…すべてのI/O操作を集計。
- COUNT_READ…FGETS、FGETC、FREAD、およびREADを含むすべての読み取り操作の集計
- SUM_NUMBER_OF_BYTES_READ…上記の読み取りバイト数の集計
- COUNT_WRITE…FPUTS、FPUTC、FPRINTF、VFPRINTF、FWRITE、およびPWRITEを含むすべての書き込み操作の集計
- SUM_NUMBER_OF_BYTES_WRITE…上記の書き込みバイト数の集計
- COUNT_MISC…CREATE、DELETE、OPEN、CLOSE、STREAM_OPEN、STREAM_CLOSE、SEEK、TELL、FLUSH、STAT、FSTAT、CHSIZE、RENAME、および SYNC を含むその他のすべての I/O 操作の集計
- TIMER関連カラム…それらの操作にかかった合計(SUM)・最小(MIN)・平均(AVG)・最大(MAX)の時間
あるテーブルのibdファイルのCOUNT_READとSUM_NUMBER_OF_BYTES_READを確認することで、そのテーブルに対しての読み込みIOが発生しているかどうかがわかります。
たとえば、バッファプールに必要なデータがロードされていない状態で、テーブルに対してSELECTが発行されると、MySQLはディスク(ibdファイル)へアクセスして、バッファプールへそのデータをロードする処理が発生します。その時、COUNT_READとSUM_NUMBER_OF_BYTES_READがカウントされます。バッファプールへロード済のデータへのSELECTではディスクへのアクセスは発生しないため、それらはカウントされません。
例として、先ほど使用したt0
テーブルで試してみます。MySQLをリスタートして、バッファプールが空の状態で試します。バッファプールの状態はsys.innodb_buffer_stats_by_table
で確認していますが、負荷の高い操作のため本番環境では実施しないことをおすすめします。
バッファプールにロードされていない状態でSELECTしたあとに、file_summary_by_instance
テーブルを確認すると、COUNT_READ,SUM_NUMBER_OF_BYTES_READが増えていることがわかります。
次に、バッファプールにロードされた状態で試してみます。
バッファプールにロードされた状態でSELECTしたあとに、file_summary_by_instance
テーブルを確認すると、COUNT_READ,SUM_NUMBER_OF_BYTES_READが増えていないことがわかります。
このように、テーブルごとのIO回数を確認することが可能です。
sys.schema_table_statistics
最後にMySQL 5.7とそれ以降であれば、sysスキーマのschema_table_statistics
が使用できます。これは先ほど説明したtable_io_waits_summary_by_table
とfile_summary_by_instance
をジョインして、ユーザテーブル単位の読み込みや書き込みのリクエスト回数、読み込みや書き込みのIO回数やレイテンシーを表示してくれる大変便利なビューです。
table_io_waits_summary_by_table
テーブルの各種COUNT系カラムがrows_xxxカラムと対応し、file_summary_by_instance
テーブルのCOUNT_READがio_read_requests、SUM_NUMBER_OF_BYTES_READがio_readというようになっています。
まとめ
今回はテーブルごとのリクエストやI/Oの統計を確認する方法について紹介しました。
これらを利用することで、ワークロードにおいてディスクへのアクセスが多いテーブルであったり、更新行の多いテーブルを確認して、問題への事前対策が実施できるようになると思います。注意として、performance_schema
のテーブルのデータは永続化されないので再起動することでリセットされます。