MySQL道普請便り

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

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

本番環境のMySQLにおいて,どのテーブルへの読み込みが多いのか,または書き込みが多いかなど気になることがあると思います。そんな場合に,MySQLはテーブルごとのリクエストやI/Oの統計を確認できるテーブルを提供しています。

今回は,MySQL5.6とそれ以降で閲覧可能なperformance_schematable_io_waits_summary_by_tablefile_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テーブルがあります。これはテーブル単位ではなく,インデックス単位で集計されたテーブルになります。

著者プロフィール

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

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

Twitter:@keny_lala