20.9.8.5. File I/O Summary Tables

The file I/O summary tables aggregate information about I/O operations:

file_summary_by_event_name: File events summarized per event name
file_summary_by_instance: File events summarized per file instance

For example:

mysql> SELECT * FROM
        file_summary_by_event_name\G...*************************** 2. row ***************************               EVENT_NAME: wait/io/file/sql/binlog               COUNT_STAR: 31           SUM_TIMER_WAIT: 8243784888           MIN_TIMER_WAIT: 0           AVG_TIMER_WAIT: 265928484           MAX_TIMER_WAIT: 6490658832...mysql> SELECT * FROM file_summary_by_instance\G...*************************** 2. row ***************************                FILE_NAME: /var/mysql/share/english/errmsg.sys               EVENT_NAME: wait/io/file/sql/ERRMSG               EVENT_NAME: wait/io/file/sql/ERRMSG    OBJECT_INSTANCE_BEGIN: 4686193384               COUNT_STAR: 5           SUM_TIMER_WAIT: 13990154448           MIN_TIMER_WAIT: 26349624           AVG_TIMER_WAIT: 2798030607           MAX_TIMER_WAIT: 8150662536...

TRUNCATE TABLE is permitted for file I/O summary tables. It resets the counters to zero rather than removing rows.

The file I/O summary tables have these grouping columns to indicate how events are aggregated:

file_summary_by_event_name has an EVENT_NAME column. Each row summarizes events for a given instrument.
file_summary_by_instance has FILE_NAME, EVENT_NAME, and OBJECT_INSTANCE_BEGIN columns. Each row summarizes events for a given file instrument instance.

The file I/O summary tables have the following summary columns containing aggregated values. The tables contain only the Only COUNT_READ COUNT_WRITE SUM_NUMBER_OF_BYTES_READ, and SUM_NUMBER_OF_BYTES_WRITE aggregation columns.) Some columns are more general and have values that are the same as the sum of the values of more fine-grained columns. In this way, aggregations at higher levels are available directly without the need for user-defined views that sum lower-level columns.

COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT

These columns aggregate all I/O operations.
COUNT_READ, SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ

These columns aggregate all read operations, including FGETS, FGETC, FREAD, and READ.
COUNT_WRITE, SUM_TIMER_WRITE, MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE

These columns aggregate all write operations, including FPUTS, FPUTC, FPRINTF, VFPRINTF, FWRITE, and PWRITE.
COUNT_MISC, SUM_TIMER_MISC, MIN_TIMER_MISC, AVG_TIMER_MISC, MAX_TIMER_MISC

These columns aggregate all other I/O operations, including CREATE, DELETE, OPEN, CLOSE, STREAM_OPEN, STREAM_CLOSE, SEEK, TELL, FLUSH, STAT, FSTAT, CHSIZE, RENAME, and SYNC. There are no byte counts for these operations.

The MySQL server uses several techniques to avoid I/O operations by caching information read from files, so it is possible that statements you might expect to result in I/O events will not. You may be able to ensure that I/O does occur by flushing caches or restarting the server to reset its state.