gihyo.jpサイトのロゴ

MySQL道普請便り

第91回テーブルごとのリクエストやI/Oの統計を確認する

2019-02-26

本番環境の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で確認することができます。

mysql> SELECT NAME,ENABLED,TIMED FROM performance_schema.setup_instruments WHERE NAME='wait/io/table/sql/handler';
+---------------------------+---------+-------+
| NAME                      | ENABLED | TIMED |
+---------------------------+---------+-------+
| wait/io/table/sql/handler | YES     | YES   |
+---------------------------+---------+-------+

table_io_waits_summary_by_tableテーブル定義は以下のようになっています。

mysql> desc table_io_waits_summary_by_table;
+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| OBJECT_TYPE      | varchar(64)         | YES  | MUL | NULL    |       |
| OBJECT_SCHEMA    | varchar(64)         | YES  |     | NULL    |       |
| OBJECT_NAME      | varchar(64)         | YES  |     | NULL    |       |
| COUNT_STAR       | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WAIT   | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_READ       | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_READ   | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_WRITE      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WRITE  | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_FETCH      | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_FETCH  | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_INSERT     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_INSERT | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_UPDATE     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_UPDATE | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_DELETE     | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_DELETE | bigint(20) unsigned | NO   |     | NULL    |       |
+------------------+---------------------+------+-----+---------+-------+

この中から抜粋して、カラムの説明をします。

  • 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関連のカラムを確認してみます。

mysql> CREATE TABLE t0 (id serial, id2 int);
mysql> INSERT INTO t0 (id2) VALUES (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT OBJECT_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_table WHERE OBJECT_NAME='t0'\G
*************************** 1. row ***************************
 OBJECT_NAME: t0
 COUNT_FETCH: 0
COUNT_INSERT: 2
COUNT_UPDATE: 0
COUNT_DELETE: 0

INSERT文の発行は1回ですが、2行の挿入があったため、COUNT_INSERTが2となりました。

次にこれらデータをSELECTしてみます。

mysql> SELECT * FROM t0;
+----+------+
| id | id2  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

mysql> SELECT OBJECT_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_table WHERE OBJECT_NAME='t0'\G
*************************** 1. row ***************************
 OBJECT_NAME: t0
 COUNT_FETCH: 2
COUNT_INSERT: 2
COUNT_UPDATE: 0
COUNT_DELETE: 0

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テーブル構成は以下のようになっています。

+---------------------------+---------------------+------+-----+---------+-------+
| Field                     | Type                | Null | Key | Default | Extra |
+---------------------------+---------------------+------+-----+---------+-------+
| FILE_NAME                 | varchar(512)        | NO   | MUL | NULL    |       |
| EVENT_NAME                | varchar(128)        | NO   | MUL | NULL    |       |
| OBJECT_INSTANCE_BEGIN     | bigint(20) unsigned | NO   | PRI | NULL    |       |
| COUNT_STAR                | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WAIT            | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WAIT            | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WAIT            | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WAIT            | bigint(20) unsigned | NO   |     | NULL    |       |
| COUNT_READ                | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_READ            | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_READ            | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_READ            | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_READ            | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_NUMBER_OF_BYTES_READ  | bigint(20)          | NO   |     | NULL    |       |
| COUNT_WRITE               | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_WRITE           | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_WRITE           | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_WRITE           | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_WRITE           | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_NUMBER_OF_BYTES_WRITE | bigint(20)          | NO   |     | NULL    |       |
| COUNT_MISC                | bigint(20) unsigned | NO   |     | NULL    |       |
| SUM_TIMER_MISC            | bigint(20) unsigned | NO   |     | NULL    |       |
| MIN_TIMER_MISC            | bigint(20) unsigned | NO   |     | NULL    |       |
| AVG_TIMER_MISC            | bigint(20) unsigned | NO   |     | NULL    |       |
| MAX_TIMER_MISC            | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------------------+---------------------+------+-----+---------+-------+

この中から抜粋して、カラムの説明をします。

  • 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で確認していますが、負荷の高い操作のため本番環境では実施しないことをおすすめします。

mysql> SELECT * FROM sys.innodb_buffer_stats_by_table WHERE object_name='t0';
Empty set (0.03 sec)

mysql> SELECT FILE_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ FROM performance_schema.file_summary_by_instance WHERE FILE_NAME like '%t0%';
+-------------------------+------------+--------------------------+
| FILE_NAME               | COUNT_READ | SUM_NUMBER_OF_BYTES_READ |
+-------------------------+------------+--------------------------+
| /var/lib/mysql/t/t0.ibd |          0 |                        0 |
+-------------------------+------------+--------------------------+

mysql> select * from t.t0;
+----+------+
| id | id2  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.01 sec)

mysql> SELECT FILE_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ FROM performance_schema.file_summary_by_instance WHERE FILE_NAME like '%t0%';
+-------------------------+------------+--------------------------+
| FILE_NAME               | COUNT_READ | SUM_NUMBER_OF_BYTES_READ |
+-------------------------+------------+--------------------------+
| /var/lib/mysql/t/t0.ibd |          3 |                    49152 |
+-------------------------+------------+--------------------------+

バッファプールにロードされていない状態でSELECTしたあとに、file_summary_by_instance テーブルを確認すると、COUNT_READ,SUM_NUMBER_OF_BYTES_READが増えていることがわかります。

次に、バッファプールにロードされた状態で試してみます。

mysql> SELECT * FROM sys.innodb_buffer_stats_by_table WHERE object_name='t0';
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data     | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
| t             | t0          | 16.00 KiB | 62 bytes |     1 |            0 |         0 |           2 |
+---------------+-------------+-----------+----------+-------+--------------+-----------+-------------+
1 row in set (0.03 sec)

mysql> SELECT FILE_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ FROM performance_schema.file_summary_by_instance WHERE FILE_NAME like '%t0%';
+-------------------------+------------+--------------------------+
| FILE_NAME               | COUNT_READ | SUM_NUMBER_OF_BYTES_READ |
+-------------------------+------------+--------------------------+
| /var/lib/mysql/t/t0.ibd |          0 |                        0 |
+-------------------------+------------+--------------------------+
1 row in set (0.00 sec)

mysql> select * from t.t0;
+----+------+
| id | id2  |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

mysql> SELECT FILE_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ FROM performance_schema.file_summary_by_instance WHERE FILE_NAME like '%t0%';
+-------------------------+------------+--------------------------+
| FILE_NAME               | COUNT_READ | SUM_NUMBER_OF_BYTES_READ |
+-------------------------+------------+--------------------------+
| /var/lib/mysql/t/t0.ibd |          0 |                        0 |
+-------------------------+------------+--------------------------+

バッファプールにロードされた状態で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回数やレイテンシーを表示してくれる大変便利なビューです。

mysql> select * from schema_table_statistics where table_name='t0'\G
*************************** 1. row ***************************
     table_schema: t
       table_name: t0
    total_latency: 599.69 us
     rows_fetched: 2
    fetch_latency: 349.94 us
    rows_inserted: 2
   insert_latency: 249.75 us
     rows_updated: 0
   update_latency: 0 ps
     rows_deleted: 0
   delete_latency: 0 ps
 io_read_requests: 4
          io_read: 64.00 KiB
  io_read_latency: 136.42 us
io_write_requests: 1
         io_write: 16.00 KiB
 io_write_latency: 32.76 us
 io_misc_requests: 6
  io_misc_latency: 241.86 us
1 row in set (0.01 sec)

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のテーブルのデータは永続化されないので再起動することでリセットされます。

おすすめ記事

記事・ニュース一覧