MySQL道普請便り

第186回information_schemaのInnoDB関連テーブルその1]

information_schemaデータベースはその配下のテーブルにおいて、そのMySQLが保有する定義情報やカラム定義などのさまざまな情報が格納されています。information_schemaデータベースのテーブルやビューはMySQLがデータを管理していて、ユーザからの書き込みは許可されず読み取りのみ可能です。今回はその中から、InnoDBに関するinformation_schemaのテーブルについて紹介したいと思います。

information_schemaのInnoDB関連テーブル一覧

information_schema内のINNODBというプレフィックスをもつテーブルがInnoDB関連テーブルです。2022/12現在、最新のMySQL 8.0.31では31個のテーブルが確認できます。公式ドキュメントでは26.51 INFORMATION_SCHEMA InnoDB テーブルになります。

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_BUFFER_PAGE                     |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_CACHED_INDEXES                  |
| INNODB_CMP                             |
| INNODB_CMP_PER_INDEX                   |
| INNODB_CMP_PER_INDEX_RESET             |
| INNODB_CMP_RESET                       |
| INNODB_CMPMEM                          |
| INNODB_CMPMEM_RESET                    |
| INNODB_COLUMNS                         |
| INNODB_DATAFILES                       |
| INNODB_FIELDS                          |
| INNODB_FOREIGN                         |
| INNODB_FOREIGN_COLS                    |
| INNODB_FT_BEING_DELETED                |
| INNODB_FT_CONFIG                       |
| INNODB_FT_DEFAULT_STOPWORD             |
| INNODB_FT_DELETED                      |
| INNODB_FT_INDEX_CACHE                  |
| INNODB_FT_INDEX_TABLE                  |
| INNODB_INDEXES                         |
| INNODB_METRICS                         |
| INNODB_SESSION_TEMP_TABLESPACES        |
| INNODB_TABLES                          |
| INNODB_TABLESPACES                     |
| INNODB_TABLESPACES_BRIEF               |
| INNODB_TABLESTATS                      |
| INNODB_TEMP_TABLE_INFO                 |
| INNODB_TRX                             |
| INNODB_VIRTUAL                         |
+----------------------------------------+
31 rows in set (0.00 sec)

この中からいくつかピックアップして紹介したいと思います。

INNODB_TABLESINNODB_INDEXESテーブル

INNODB_TABLESテーブルは、そのMySQLがもっているInnoDBのテーブル情報を格納しています。内部で管理する識別子TABLE_IDとテーブル名を保持していて、テーブル名はデータベース名/テーブル名(例:db1/table1という表記となります。

INNODB_INDEXESテーブルは、そのMySQLがもっているInnoDBのインデックス情報を格納しています。内部で管理する識別子INDEX_IDとインデックス名を保持していて、インデックスに関連付けられたテーブルを表す識別子TABLE_IDを持っています。どのテーブルのインデックスかはINNODB_TABLES.TABLE_IDを結合することで取得できます。

mysql> SELECT * FROM INNODB_TABLES WHERE NAME LIKE 'db1%';
+----------+--------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME   | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+--------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1068 | db1/t1 |   33 |      5 |     6 | Dynamic    |             0 | Single     |            0 |                  0 |
+----------+--------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM INNODB_INDEXES WHERE TABLE_ID=1068;
+----------+---------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+---------+----------+------+----------+---------+-------+-----------------+
|      158 | PRIMARY |     1068 |    3 |        4 |       4 |     6 |              50 |
+----------+---------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.00 sec)

InnoDBバッファプール関連テーブル

INNODB_CACHED_INDEXESテーブルは、現在のInnoDBバッファプールにキャッシュされたインデックスページの数を確認することができます。このテーブル単体ではテーブルスペースやインデックスの識別子のみ格納されているので、テーブル名やインデックス名を取得するには、前述のINNODB_INDEXESテーブルとINNODB_TABLESテーブルを結合して利用します。

SELECT
  tables.NAME AS table_name,
  indexes.NAME AS index_name,
  cached.N_CACHED_PAGES AS n_cached_pages
FROM
  INFORMATION_SCHEMA.INNODB_CACHED_INDEXES AS cached,
  INFORMATION_SCHEMA.INNODB_INDEXES AS indexes,
  INFORMATION_SCHEMA.INNODB_TABLES AS tables
WHERE
  cached.INDEX_ID = indexes.INDEX_ID
  AND indexes.TABLE_ID = tables.TABLE_ID;

+------------+------------+----------------+
| table_name | index_name | n_cached_pages |
+------------+------------+----------------+
| db1/t1     | PRIMARY    |            138 |
+------------+------------+----------------+

このクエリから次のようなカラムが出力されています。

  • table_name …テーブル名
  • index_name …インデックス名
  • n_cached_pages …現在バッファプールに載っているページ数

このテーブルを利用することで、そのMySQLにおいてどのようなデータがバッファプールを占めているか、ワークロードの特性を把握することができます。

また、INNODB_BUFFER_PAGE,INNODB_BUFFER_PAGE_LRUテーブルからも同じくバッファプールのページ数など情報を取得することが可能です。しかし、パフォーマンスに影響する可能性が大きいため、本番環境で実施することは推奨されていません。そのため、現在のバッファプールの状態を確認するときは、INNODB_CACHED_INDEXESテーブルを利用するのがよいでしょう。

その他にINNODB_BUFFER_POOL_STATSテーブルもあります。これはSHOW ENGINE INNODB STATUSの出力で提供されるものと同じバッファプール情報の多くを提供します。

INNODB_METRICS

INNODB_METRICSテーブルでは、InnoDBに関するパフォーマンスやリソース情報を提供します。パフォーマンスのボトルネック診断などに応用できる、カウンタに関する情報が格納されています。 各種カウンタはメモリ内に保存されていますので、再起動を実施するとリセットされます。

mysql> SELECT NAME,SUBSYSTEM,COUNT,STATUS,COMMENT FROM INNODB_METRICS LIMIT 5;
+--------------------------------+-----------+-------+----------+---------------------------------------------------------------------------------------------+
| NAME                           | SUBSYSTEM | COUNT | STATUS   | COMMENT                                                                                     |
+--------------------------------+-----------+-------+----------+---------------------------------------------------------------------------------------------+
| metadata_table_handles_opened  | metadata  |     0 | disabled | Number of table handles opened                                                              |
| metadata_table_handles_closed  | metadata  |     0 | disabled | Number of table handles closed                                                              |
| metadata_table_reference_count | metadata  |     0 | disabled | Table reference counter                                                                     |
| lock_deadlocks                 | lock      |     0 | enabled  | Number of deadlocks                                                                         |
| lock_deadlock_false_positives  | lock      |     0 | enabled  | Number of times a heuristic found a spurious candidate deadlock cycle in the wait-for graph |
+--------------------------------+-----------+-------+----------+---------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

一部割愛していますが、次のようなカラムがあります。

  • NAME …カウンタ名
  • SUBSYSTEM …カウンタ名に対するInnoDBのサブシステム名。
  • COUNT …カウンタ値
  • STATUS …カウンタが有効(enabled⁠⁠、または無効(disabled)かどうか
  • COMMENT …カウンタの説明

デフォルトでは少しのカウンタのみ有効化されています。システム変数innodb_monitor_enableを変更することで、カウンタを有効化することができます。特定のサブシステムのカウンタのみ有効にしたり、すべてのカウンタを有効にする、またはすべてのカウンタ無効にするなどオンラインでの設定が可能です。すべてのカウンタを有効化すると、オーバヘッドによりパフォーマンス影響があるので、必要なときに有効にするのが望ましいでしょう。

詳しくは15.15.6 InnoDB INFORMATION_SCHEMA メトリックテーブルをご参照ください。

SHOW GLOBAL STATUSperformance_schemaにおいてもパフォーマンス情報を提供していることはご存知だと思います。INNODB_METRICSテーブルから上記が提供している項目と同等のものもありますが、このテーブルからでしか得られない情報もあります。

たとえば、InnoDBデッドロックの合計回数があります。次のカウンタはMySQLを起動してからのInnoDBデッドロック発生回数の累積値になります。

mysql> SELECT NAME,SUBSYSTEM,COUNT,STATUS,COMMENT FROM INNODB_METRICS WHERE NAME='lock_deadlocks';
+----------------+-----------+-------+---------+---------------------+
| NAME           | SUBSYSTEM | COUNT | STATUS  | COMMENT             |
+----------------+-----------+-------+---------+---------------------+
| lock_deadlocks | lock      |    10 | enabled | Number of deadlocks |
+----------------+-----------+-------+---------+---------------------+

なにかInnoDBに関して収集したい情報があるときは、INNODB_METRICSテーブルから取得することも検討してみましょう。しかし、残念ながらINNODB_METRICSテーブルに関しての詳しい情報は公式ドキュメントでは提供されていません。ほしい情報がSHOW GLOBAL STATUSperformance_schemaで見当たらないときは、INNODB_METRICSテーブルのCOMMENTカラムからカウンタの情報を予測し、実際にほしいカウンタ情報なのか確認することが必要です。

まとめ

今回紹介したテーブルに関する公式ドキュメント次のとおりです。こちらも合わせてご参照いただければと思います。

また、今後のこの連載で、他のinformation_schemaのInnoDB関連テーブルについて「その2」として紹介したいと思います。

おすすめ記事

記事・ニュース一覧