file_summary_*表

• 包含如下几张表，performance_schema在这些表中按照不同的分组列（不同纬度）进行聚合文件I/O事件相关的数据（例如：执行次数，总等待时间，最小、最大、平均等待时间），注意，文件I/O事件instruments默认开启，在consumers表中无具体的对应配置，默认文件I/O事件摘要表中就会聚合相关事件信息

admin@localhost : performance_schema 06:48:12> show tables like '%file_summary%';
+-----------------------------------------------+
| Tables_in_performance_schema (%file_summary%) |
+-----------------------------------------------+
| file_summary_by_event_name                    |
| file_summary_by_instance                      |
+-----------------------------------------------+
2 rows in set (0.00 sec)

• 其中，都包含file_summary_by_event_name 表的字段，除持之外，file_summary_by_instance表多了FILE_NAME和OBJECT_INSTANCE_BEGIN字段

CREATE TABLE `file_summary_by_event_name` (
  `EVENT_NAME` varchar(128) NOT NULL,
  `COUNT_STAR` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `COUNT_READ` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ` bigint(20) unsigned NOT NULL,
  `SUM_NUMBER_OF_BYTES_READ` bigint(20) NOT NULL,
  `COUNT_WRITE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `SUM_NUMBER_OF_BYTES_WRITE` bigint(20) NOT NULL,
  `COUNT_MISC` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_MISC` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_MISC` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_MISC` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_MISC` bigint(20) unsigned NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

• performance_schema维护用于聚合有关文件I/O操作相关的事件摘要信息表，每个文件I/O摘要表都有一个或多个分组列，以表明如何聚合这些事件信息。这些表中的事件名称来自setup_instruments表中的name字段：

  • file_summary_by_event_name表：按照EVENT_NAME列进行分组
  • file_summary_by_instance表：有额外的FILE_NAME、OBJECT_INSTANCE_BEGIN列，按照FILE_NAME、EVENT_NAME列进行分组

• 每个文件I/O事件摘要表有如下聚合字段：

  • COUNT_STAR，SUM_TIMER_WAIT，MIN_TIMER_WAIT，AVG_TIMER_WAIT，MAX_TIMER_WAIT：这些列聚合所有I/O操作数量和操作时间
  • COUNT_READ，SUM_TIMER_READ，MIN_TIMER_READ，AVG_TIMER_READ，MAX_TIMER_READ，SUM_NUMBER_OF_BYTES_READ：这些列聚合了所有文件读取操作，包括FGETS，FGETC，FREAD和READ系统调用，还包含了这些I/O操作的数据字节数
  • COUNT_WRITE，SUM_TIMER_WRITE，MIN_TIMER_WRITE，AVG_TIMER_WRITE，MAX_TIMER_WRITE，SUM_NUMBER_OF_BYTES_WRITE：这些列聚合了所有文件写操作，包括FPUTS，FPUTC，FPRINTF，VFPRINTF，FWRITE和PWRITE系统调用，还包含了这些I/O操作的数据字节数
  • COUNT_MISC，SUM_TIMER_MISC，MIN_TIMER_MISC，AVG_TIMER_MISC，MAX_TIMER_MISC：这些列聚合了所有其他文件I/O操作，包括CREATE，DELETE，OPEN，CLOSE，STREAM_OPEN，STREAM_CLOSE，SEEK，TELL，FLUSH，STAT，FSTAT，CHSIZE，RENAME和SYNC系统调用。注意：这些文件I/O操作没有字节计数信息

• 文件I/O事件摘要表允许使用TRUNCATE TABLE语句。但只将聚合列重置为零，而不是删除行

• MySQL server使用几种缓存技术通过缓存从文件中读取的信息来避免文件I/O操作，因此您可能不希望一个语句操作导致文件I/O操作。当然，如果内存不够时或者内存竞争比较大时可能导致查询效率低下，这个时候您可能需要通过刷新缓存或者重启server来让其数据通过文件I/O返回而不是通过缓存返回

• 表记录内容示例

# file_summary_by_event_name表
admin@localhost : performance_schema 11:00:44> select * from file_summary_by_event_name where SUM_TIMER_WAIT !=0 and EVENT_NAME like '%innodb%' limit 1\G;
*************************** 1. row ***************************
              EVENT_NAME: wait/io/file/innodb/innodb_data_file
              COUNT_STAR: 802
          SUM_TIMER_WAIT: 412754363625
          MIN_TIMER_WAIT: 0
          AVG_TIMER_WAIT: 514656000
          MAX_TIMER_WAIT: 9498247500
              COUNT_READ: 577
          SUM_TIMER_READ: 305970952875
          MIN_TIMER_READ: 15213375
          AVG_TIMER_READ: 530278875
          MAX_TIMER_READ: 9498247500
SUM_NUMBER_OF_BYTES_READ: 11567104
              COUNT_WRITE: 62
          SUM_TIMER_WRITE: 102102181875
          MIN_TIMER_WRITE: 5703375
          AVG_TIMER_WRITE: 1646809125
          MAX_TIMER_WRITE: 8521845000
SUM_NUMBER_OF_BYTES_WRITE: 13418496
              COUNT_MISC: 163
          SUM_TIMER_MISC: 4681228875
          MIN_TIMER_MISC: 0
          AVG_TIMER_MISC: 28719000
          MAX_TIMER_MISC: 1026122250
1 row in set (0.00 sec)

# file_summary_by_instance表
admin@localhost : performance_schema 11:01:23> select * from file_summary_by_instance where SUM_TIMER_WAIT!=0 and EVENT_NAME like '%innodb%' limit 1\G;
*************************** 1. row ***************************
                FILE_NAME: /data/mysqldata1/innodb_ts/ibdata1
              EVENT_NAME: wait/io/file/innodb/innodb_data_file
    OBJECT_INSTANCE_BEGIN: 139882156936704
              COUNT_STAR: 33
          SUM_TIMER_WAIT: 36976807875
          MIN_TIMER_WAIT: 4034250
          AVG_TIMER_WAIT: 1120509000
          MAX_TIMER_WAIT: 9498247500
              COUNT_READ: 23
          SUM_TIMER_READ: 33590587500
          MIN_TIMER_READ: 449268000
          AVG_TIMER_READ: 1460460000
          MAX_TIMER_READ: 9498247500
SUM_NUMBER_OF_BYTES_READ: 2490368
              COUNT_WRITE: 3
          SUM_TIMER_WRITE: 1386482250
          MIN_TIMER_WRITE: 22630125
          AVG_TIMER_WRITE: 462160500
          MAX_TIMER_WRITE: 1295497125
SUM_NUMBER_OF_BYTES_WRITE: 65536
              COUNT_MISC: 7
          SUM_TIMER_MISC: 1999738125
          MIN_TIMER_MISC: 4034250
          AVG_TIMER_MISC: 285676875
          MAX_TIMER_MISC: 1026122250
1 row in set (0.00 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/file-summary-tables.html

---

上一篇： objects_summary_global_by_type表 | 下一篇： table_*summary*表