Rechercher dans le manuel MySQL
26.12.17.8 Table I/O and Lock Wait Summary Tables
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 indextable_io_waits_summary_by_table: Table I/O waits per tabletable_lock_waits_summary_by_table: Table lock waits per table
26.12.17.8.1 The table_io_waits_summary_by_table 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_WAITThese columns aggregate all I/O operations. They are the same as the sum of the corresponding
xxx_READxxx_WRITECOUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READThese columns aggregate all read operations. They are the same as the sum of the corresponding
xxx_FETCHCOUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITEThese columns aggregate all write operations. They are the same as the sum of the corresponding
xxx_INSERTxxx_UPDATExxx_DELETECOUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH,MAX_TIMER_FETCHThese columns aggregate all fetch operations.
COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERTThese columns aggregate all insert operations.
COUNT_UPDATE,SUM_TIMER_UPDATE,MIN_TIMER_UPDATE,AVG_TIMER_UPDATE,MAX_TIMER_UPDATEThese columns aggregate all update operations.
COUNT_DELETE,SUM_TIMER_DELETE,MIN_TIMER_DELETE,AVG_TIMER_DELETE,MAX_TIMER_DELETEThese columns aggregate all delete operations.
The
table_io_waits_summary_by_table
table has these indexes:
Unique index on (
OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME)
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
PRIMARYindicates that table I/O used the primary index.A value of
NULLmeans that table I/O used no index.Inserts are counted against
INDEX_NAME = NULL.
The
table_io_waits_summary_by_index_usage
table has these indexes:
Unique index on (
OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME)
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 theOPERATIONcolumn, 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 normalAn 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 theOPERATIONcolumn, 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_WAITThese columns aggregate all lock operations. They are the same as the sum of the corresponding
xxx_READxxx_WRITECOUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READThese 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_INSERTCOUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITEThese columns aggregate all write-lock operations. They are the same as the sum of the corresponding
xxx_WRITE_ALLOW_WRITExxx_WRITE_CONCURRENT_INSERTxxx_WRITE_LOW_PRIORITYxxx_WRITE_NORMALCOUNT_READ_NORMAL,SUM_TIMER_READ_NORMAL,MIN_TIMER_READ_NORMAL,AVG_TIMER_READ_NORMAL,MAX_TIMER_READ_NORMALThese 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_LOCKSThese 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_PRIORITYThese 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_INSERTThese columns aggregate internal read locks.
COUNT_READ_EXTERNAL,SUM_TIMER_READ_EXTERNAL,MIN_TIMER_READ_EXTERNAL,AVG_TIMER_READ_EXTERNAL,MAX_TIMER_READ_EXTERNALThese 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_WRITEThese 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_INSERTThese columns aggregate internal write locks.
COUNT_WRITE_LOW_PRIORITY,SUM_TIMER_WRITE_LOW_PRIORITY,MIN_TIMER_WRITE_LOW_PRIORITY,AVG_TIMER_WRITE_LOW_PRIORITY,MAX_TIMER_WRITE_LOW_PRIORITYThese columns aggregate internal write locks.
COUNT_WRITE_NORMAL,SUM_TIMER_WRITE_NORMAL,MIN_TIMER_WRITE_NORMAL,AVG_TIMER_WRITE_NORMAL,MAX_TIMER_WRITE_NORMALThese columns aggregate internal write locks.
COUNT_WRITE_EXTERNAL,SUM_TIMER_WRITE_EXTERNAL,MIN_TIMER_WRITE_EXTERNAL,AVG_TIMER_WRITE_EXTERNAL,MAX_TIMER_WRITE_EXTERNALThese columns aggregate external write locks.
The
table_lock_waits_summary_by_table
table has these indexes:
Unique index on (
OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME)
TRUNCATE TABLE is permitted
for table lock summary tables. It resets the summary columns
to zero rather than removing rows.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-table-waits-summary-tables.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.
