Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
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.
TRUNCATE TABLE is permitted for table I/O summary tables. It resets the
counters 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_table
table has these grouping columns to indicate how events are aggregated: 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
xxx_READxxx_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
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_INSERTxxx_UPDATExxx_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.
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.
TRUNCATE TABLE is permitted for table I/O summary tables. It resets the
counters 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 structure of table_io_waits_summary_by_index_usage is 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.
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.
TRUNCATE TABLE is permitted for table lock summary tables. It resets the
counters to zero rather than removing rows.
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 will have one of these
values:
read normalread with shared locksread high priorityread no insertwrite allow writewrite concurrent insertwrite delayedwrite low prioritywrite 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 will
have one of these values:
read externalwrite external
The table_lock_waits_summary_by_table
table has these grouping columns to indicate how events are aggregated: 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 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 lock operations. They are the same as the sum of the corresponding
xxx_READxxx_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_NORMALxxx_READ_WITH_SHARED_LOCKSxxx_READ_HIGH_PRIORITYxxx_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_WRITExxx_WRITE_CONCURRENT_INSERTxxx_WRITE_DELAYEDxxx_WRITE_LOW_PRIORITYxxx_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.
As of MySQL 5.6.6, DELAYED inserts are deprecated, so these columns
will 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.