file_ summary_ by_ instanceテーブル
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. schema_ table_ statistics
最後に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_
のテーブルのデータは永続化されないので再起動することでリセットされます。