MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The following sections describe the table I/O and lock wait summary tables:
table_io_waits_summary_by_index_usage
:
Table I/O waits per index
table_io_waits_summary_by_table
:
Table I/O waits per table
table_lock_waits_summary_by_table
:
Table lock waits per table
The
table_io_waits_summary_by_table
table aggregates all table I/O wait events, as generated by
the wait/io/table/sql/handler
instrument.
The grouping is by table.
The
table_io_waits_summary_by_table
table has these grouping columns to indicate how the table
aggregates events: OBJECT_TYPE
,
OBJECT_SCHEMA
, and
OBJECT_NAME
. These columns have the same
meaning as in the
events_waits_current
table.
They identify the table to which the row applies.
table_io_waits_summary_by_table
has the following summary columns containing aggregated
values. As indicated in the column descriptions, some
columns are more general and have values that are the same
as the sum of the values of more fine-grained columns. For
example, columns that aggregate all writes hold the sum of
the corresponding columns that aggregate inserts, updates,
and deletes. 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. They are the
same as the sum of the corresponding
and
xxx
_READ
columns.
xxx
_WRITE
COUNT_READ
,
SUM_TIMER_READ
,
MIN_TIMER_READ
,
AVG_TIMER_READ
,
MAX_TIMER_READ
These columns aggregate all read operations. They are
the same as the sum of the corresponding
columns.
xxx
_FETCH
COUNT_WRITE
,
SUM_TIMER_WRITE
,
MIN_TIMER_WRITE
,
AVG_TIMER_WRITE
,
MAX_TIMER_WRITE
These columns aggregate all write operations. They are
the same as the sum of the corresponding
,
xxx
_INSERT
,
and
xxx
_UPDATE
columns.
xxx
_DELETE
COUNT_FETCH
,
SUM_TIMER_FETCH
,
MIN_TIMER_FETCH
,
AVG_TIMER_FETCH
,
MAX_TIMER_FETCH
These columns aggregate all fetch operations.
COUNT_INSERT
,
SUM_TIMER_INSERT
,
MIN_TIMER_INSERT
,
AVG_TIMER_INSERT
,
MAX_TIMER_INSERT
These columns aggregate all insert operations.
COUNT_UPDATE
,
SUM_TIMER_UPDATE
,
MIN_TIMER_UPDATE
,
AVG_TIMER_UPDATE
,
MAX_TIMER_UPDATE
These columns aggregate all update operations.
COUNT_DELETE
,
SUM_TIMER_DELETE
,
MIN_TIMER_DELETE
,
AVG_TIMER_DELETE
,
MAX_TIMER_DELETE
These columns aggregate all delete operations.
TRUNCATE TABLE
is permitted
for table I/O summary tables. It resets the summary columns
to zero rather than removing rows. Truncating this table
also truncates the
table_io_waits_summary_by_index_usage
table.
The
table_io_waits_summary_by_index_usage
table aggregates all table index I/O wait events, as
generated by the
wait/io/table/sql/handler
instrument. The
grouping is by table index.
The columns of
table_io_waits_summary_by_index_usage
are nearly identical to
table_io_waits_summary_by_table
.
The only difference is the additional group column,
INDEX_NAME
, which corresponds to the name
of the index that was used when the table I/O wait event was
recorded:
A value of PRIMARY
indicates that
table I/O used the primary index.
A value of NULL
means that table I/O
used no index.
Inserts are counted against INDEX_NAME =
NULL
.
TRUNCATE TABLE
is permitted
for table I/O summary tables. It resets the summary columns
to zero rather than removing rows. This table is also
truncated by truncation of the
table_io_waits_summary_by_table
table. A DDL operation that changes the index structure of a
table may cause the per-index statistics to be reset.
The
table_lock_waits_summary_by_table
table aggregates all table lock wait events, as generated by
the wait/lock/table/sql/handler
instrument. The grouping is by table.
This table contains information about internal and external locks:
An internal lock corresponds to a lock in the SQL layer.
This is currently implemented by a call to
thr_lock()
. In event rows, these
locks are distinguished by the
OPERATION
column, which has one of
these values:
read normal read with shared locks read high priority read no insert write allow write write concurrent insert write delayed write low priority write normal
An external lock corresponds to a lock in the storage
engine layer. This is currently implemented by a call to
handler::external_lock()
. In event
rows, these locks are distinguished by the
OPERATION
column, which has one of
these values:
read external write external
The
table_lock_waits_summary_by_table
table has these grouping columns to indicate how the table
aggregates events: OBJECT_TYPE
,
OBJECT_SCHEMA
, and
OBJECT_NAME
. These columns have the same
meaning as in the
events_waits_current
table.
They identify the table to which the row applies.
table_lock_waits_summary_by_table
has the following summary columns containing aggregated
values. As indicated in the column descriptions, some
columns are more general and have values that are the same
as the sum of the values of more fine-grained columns. For
example, columns that aggregate all locks hold the sum of
the corresponding columns that aggregate read and write
locks. 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 lock operations. They are
the same as the sum of the corresponding
and
xxx
_READ
columns.
xxx
_WRITE
COUNT_READ
,
SUM_TIMER_READ
,
MIN_TIMER_READ
,
AVG_TIMER_READ
,
MAX_TIMER_READ
These columns aggregate all read-lock operations. They
are the same as the sum of the corresponding
,
xxx
_READ_NORMAL
,
xxx
_READ_WITH_SHARED_LOCKS
,
and
xxx
_READ_HIGH_PRIORITY
columns.
xxx
_READ_NO_INSERT
COUNT_WRITE
,
SUM_TIMER_WRITE
,
MIN_TIMER_WRITE
,
AVG_TIMER_WRITE
,
MAX_TIMER_WRITE
These columns aggregate all write-lock operations. They
are the same as the sum of the corresponding
,
xxx
_WRITE_ALLOW_WRITE
,
xxx
_WRITE_CONCURRENT_INSERT
,
xxx
_WRITE_DELAYED
,
and
xxx
_WRITE_LOW_PRIORITY
columns.
xxx
_WRITE_NORMAL
COUNT_READ_NORMAL
,
SUM_TIMER_READ_NORMAL
,
MIN_TIMER_READ_NORMAL
,
AVG_TIMER_READ_NORMAL
,
MAX_TIMER_READ_NORMAL
These columns aggregate internal read locks.
COUNT_READ_WITH_SHARED_LOCKS
,
SUM_TIMER_READ_WITH_SHARED_LOCKS
,
MIN_TIMER_READ_WITH_SHARED_LOCKS
,
AVG_TIMER_READ_WITH_SHARED_LOCKS
,
MAX_TIMER_READ_WITH_SHARED_LOCKS
These columns aggregate internal read locks.
COUNT_READ_HIGH_PRIORITY
,
SUM_TIMER_READ_HIGH_PRIORITY
,
MIN_TIMER_READ_HIGH_PRIORITY
,
AVG_TIMER_READ_HIGH_PRIORITY
,
MAX_TIMER_READ_HIGH_PRIORITY
These columns aggregate internal read locks.
COUNT_READ_NO_INSERT
,
SUM_TIMER_READ_NO_INSERT
,
MIN_TIMER_READ_NO_INSERT
,
AVG_TIMER_READ_NO_INSERT
,
MAX_TIMER_READ_NO_INSERT
These columns aggregate internal read locks.
COUNT_READ_EXTERNAL
,
SUM_TIMER_READ_EXTERNAL
,
MIN_TIMER_READ_EXTERNAL
,
AVG_TIMER_READ_EXTERNAL
,
MAX_TIMER_READ_EXTERNAL
These columns aggregate external read locks.
COUNT_WRITE_ALLOW_WRITE
,
SUM_TIMER_WRITE_ALLOW_WRITE
,
MIN_TIMER_WRITE_ALLOW_WRITE
,
AVG_TIMER_WRITE_ALLOW_WRITE
,
MAX_TIMER_WRITE_ALLOW_WRITE
These columns aggregate internal write locks.
COUNT_WRITE_CONCURRENT_INSERT
,
SUM_TIMER_WRITE_CONCURRENT_INSERT
,
MIN_TIMER_WRITE_CONCURRENT_INSERT
,
AVG_TIMER_WRITE_CONCURRENT_INSERT
,
MAX_TIMER_WRITE_CONCURRENT_INSERT
These columns aggregate internal write locks.
COUNT_WRITE_DELAYED
,
SUM_TIMER_WRITE_DELAYED
,
MIN_TIMER_WRITE_DELAYED
,
AVG_TIMER_WRITE_DELAYED
,
MAX_TIMER_WRITE_DELAYED
These columns aggregate internal write locks.
DELAYED
inserts are deprecated;
expect these columns to be removed in a future release.
COUNT_WRITE_LOW_PRIORITY
,
SUM_TIMER_WRITE_LOW_PRIORITY
,
MIN_TIMER_WRITE_LOW_PRIORITY
,
AVG_TIMER_WRITE_LOW_PRIORITY
,
MAX_TIMER_WRITE_LOW_PRIORITY
These columns aggregate internal write locks.
COUNT_WRITE_NORMAL
,
SUM_TIMER_WRITE_NORMAL
,
MIN_TIMER_WRITE_NORMAL
,
AVG_TIMER_WRITE_NORMAL
,
MAX_TIMER_WRITE_NORMAL
These columns aggregate internal write locks.
COUNT_WRITE_EXTERNAL
,
SUM_TIMER_WRITE_EXTERNAL
,
MIN_TIMER_WRITE_EXTERNAL
,
AVG_TIMER_WRITE_EXTERNAL
,
MAX_TIMER_WRITE_EXTERNAL
These columns aggregate external write locks.
TRUNCATE TABLE
is permitted
for table lock summary tables. It resets the summary columns
to zero rather than removing rows.