MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 8 分

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

著者プロフィール

北川健太郎(きたがわけんたろう)

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで,好きなOracleDatabaseの機能はログオントリガー。

Twitter:@keny_lala

コメント

コメントの記入