file_
file_テーブルは，
file_テーブル構成は以下のようになっています。
+---------------------------+---------------------+------+-----+---------+-------+ | 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_
たとえば，
例として，t0テーブルで試してみます。MySQLをリスタートして，sys.で確認していますが，
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_ テーブルを確認すると，
次に，
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_テーブルを確認すると，
このように，
sys.
最後にMySQL 5.schema_が使用できます。これは先ほど説明したtable_とfile_をジョインして，
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_テーブルの各種COUNT系カラムがrows_file_テーブルのCOUNT_
まとめ
今回はテーブルごとのリクエストやI/
これらを利用することで，performance_のテーブルのデータは永続化されないので再起動することでリセットされます。