Rechercher dans le manuel MySQL
15.14.6 InnoDB INFORMATION_SCHEMA Metrics Table
The INNODB_METRICS
table provides
information about InnoDB
performance and
resource-related counters.
INNODB_METRICS
table columns are
shown below. For column descriptions, see
Section 25.38.22, “The INFORMATION_SCHEMA INNODB_METRICS Table”.
- *************************** 1. row ***************************
- NAME: dml_inserts
- SUBSYSTEM: dml
- COUNT: 46273
- MAX_COUNT: 46273
- MIN_COUNT: NULL
- AVG_COUNT: 492.2659574468085
- COUNT_RESET: 46273
- MAX_COUNT_RESET: 46273
- MIN_COUNT_RESET: NULL
- AVG_COUNT_RESET: NULL
- TIME_ENABLED: 2014-11-28 16:07:53
- TIME_DISABLED: NULL
- TIME_ELAPSED: 94
- TIME_RESET: NULL
- STATUS: enabled
- TYPE: status_counter
- COMMENT: Number of rows inserted
Enabling, Disabling, and Resetting Counters
You can enable, disable, and reset counters using the following variables:
innodb_monitor_enable
: Enables counters.innodb_monitor_disable
: Disables counters.innodb_monitor_reset
: Resets counter values to zero.innodb_monitor_reset_all
: Resets all counter values. A counter must be disabled before usinginnodb_monitor_reset_all
.
Counters and counter modules can also be enabled at startup using
the MySQL server configuration file. For example, to enable the
log
module,
metadata_table_handles_opened
and
metadata_table_handles_closed
counters, enter
the following line in the [mysqld]
section of
the MySQL server configuration file.
[mysqld]
innodb_monitor_enable = module_recovery,metadata_table_handles_opened,metadata_table_handles_closed
When enabling multiple counters or modules in a configuration
file, specify the
innodb_monitor_enable
variable
followed by counter and module names separated by a comma, as
shown above. Only the
innodb_monitor_enable
variable
can be used in a configuration file. The
innodb_monitor_disable
and
innodb_monitor_reset
variables
are supported on the command line only.
Because each counter adds a degree of runtime overhead, use counters conservatively on production servers to diagnose specific issues or monitor specific functionality. A test or development server is recommended for more extensive use of counters.
Counters
The list of available counters is subject to change. Query the
INFORMATION_SCHEMA.INNODB_METRICS
table for counters available in your MySQL server version.
The counters enabled by default correspond to those shown in
SHOW ENGINE INNODB
STATUS
output. Counters shown in
SHOW ENGINE INNODB
STATUS
output are always enabled at a system level but
can be disable for the INNODB_METRICS
table. Counter status is not persistent. Unless configured
otherwise, counters revert to their default enabled or disabled
status when the server is restarted.
If you run programs that would be affected by the addition or
removal of counters, it is recommended that you review the
releases notes and query the
INNODB_METRICS
table to identify
those changes as part of your upgrade process.
- +------------------------------------------+---------------------+----------+
- +------------------------------------------+---------------------+----------+
- | adaptive_hash_pages_added | adaptive_hash_index | disabled |
- | adaptive_hash_pages_removed | adaptive_hash_index | disabled |
- | adaptive_hash_rows_added | adaptive_hash_index | disabled |
- | adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index | disabled |
- | adaptive_hash_rows_removed | adaptive_hash_index | disabled |
- | adaptive_hash_rows_updated | adaptive_hash_index | disabled |
- | adaptive_hash_searches | adaptive_hash_index | enabled |
- | adaptive_hash_searches_btree | adaptive_hash_index | enabled |
- | buffer_page_read_blob | buffer_page_io | disabled |
- | buffer_page_read_fsp_hdr | buffer_page_io | disabled |
- | buffer_page_read_ibuf_bitmap | buffer_page_io | disabled |
- | buffer_page_read_ibuf_free_list | buffer_page_io | disabled |
- | buffer_page_read_index_ibuf_leaf | buffer_page_io | disabled |
- | buffer_page_read_index_ibuf_non_leaf | buffer_page_io | disabled |
- | buffer_page_read_index_inode | buffer_page_io | disabled |
- | buffer_page_read_index_leaf | buffer_page_io | disabled |
- | buffer_page_read_index_non_leaf | buffer_page_io | disabled |
- | buffer_page_read_other | buffer_page_io | disabled |
- | buffer_page_read_system_page | buffer_page_io | disabled |
- | buffer_page_read_trx_system | buffer_page_io | disabled |
- | buffer_page_read_undo_log | buffer_page_io | disabled |
- | buffer_page_read_xdes | buffer_page_io | disabled |
- | buffer_page_read_zblob | buffer_page_io | disabled |
- | buffer_page_read_zblob2 | buffer_page_io | disabled |
- | buffer_page_written_blob | buffer_page_io | disabled |
- | buffer_page_written_fsp_hdr | buffer_page_io | disabled |
- | buffer_page_written_ibuf_bitmap | buffer_page_io | disabled |
- | buffer_page_written_ibuf_free_list | buffer_page_io | disabled |
- | buffer_page_written_index_ibuf_leaf | buffer_page_io | disabled |
- | buffer_page_written_index_ibuf_non_leaf | buffer_page_io | disabled |
- | buffer_page_written_index_inode | buffer_page_io | disabled |
- | buffer_page_written_index_leaf | buffer_page_io | disabled |
- | buffer_page_written_index_non_leaf | buffer_page_io | disabled |
- | buffer_page_written_other | buffer_page_io | disabled |
- | buffer_page_written_system_page | buffer_page_io | disabled |
- | buffer_page_written_trx_system | buffer_page_io | disabled |
- | buffer_page_written_undo_log | buffer_page_io | disabled |
- | buffer_page_written_xdes | buffer_page_io | disabled |
- | buffer_page_written_zblob | buffer_page_io | disabled |
- | buffer_page_written_zblob2 | buffer_page_io | disabled |
- | buffer_pool_size | server | enabled |
- | compression_pad_decrements | compression | disabled |
- | compression_pad_increments | compression | disabled |
- | compress_pages_compressed | compression | disabled |
- | compress_pages_decompressed | compression | disabled |
- | ddl_background_drop_indexes | ddl | disabled |
- | ddl_background_drop_tables | ddl | disabled |
- | ddl_log_file_alter_table | ddl | disabled |
- | ddl_online_create_index | ddl | disabled |
- | ddl_pending_alter_table | ddl | disabled |
- | ddl_sort_file_alter_table | ddl | disabled |
- | dml_deletes | dml | enabled |
- | dml_inserts | dml | enabled |
- | dml_reads | dml | disabled |
- | dml_updates | dml | enabled |
- | file_num_open_files | file_system | enabled |
- | ibuf_merges | change_buffer | enabled |
- | ibuf_merges_delete | change_buffer | enabled |
- | ibuf_merges_delete_mark | change_buffer | enabled |
- | ibuf_merges_discard_delete | change_buffer | enabled |
- | ibuf_merges_discard_delete_mark | change_buffer | enabled |
- | ibuf_merges_discard_insert | change_buffer | enabled |
- | ibuf_merges_insert | change_buffer | enabled |
- | ibuf_size | change_buffer | enabled |
- | icp_attempts | icp | disabled |
- | icp_match | icp | disabled |
- | icp_no_match | icp | disabled |
- | icp_out_of_range | icp | disabled |
- | innodb_activity_count | server | enabled |
- | innodb_background_drop_table_usec | server | disabled |
- | innodb_checkpoint_usec | server | disabled |
- | innodb_dblwr_pages_written | server | enabled |
- | innodb_dblwr_writes | server | enabled |
- | innodb_dict_lru_count | server | disabled |
- | innodb_dict_lru_usec | server | disabled |
- | innodb_ibuf_merge_usec | server | disabled |
- | innodb_log_flush_usec | server | disabled |
- | innodb_master_active_loops | server | disabled |
- | innodb_master_idle_loops | server | disabled |
- | innodb_master_purge_usec | server | disabled |
- | innodb_master_thread_sleeps | server | disabled |
- | innodb_mem_validate_usec | server | disabled |
- | innodb_page_size | server | enabled |
- | innodb_rwlock_sx_os_waits | server | enabled |
- | innodb_rwlock_sx_spin_rounds | server | enabled |
- | innodb_rwlock_sx_spin_waits | server | enabled |
- | innodb_rwlock_s_os_waits | server | enabled |
- | innodb_rwlock_s_spin_rounds | server | enabled |
- | innodb_rwlock_s_spin_waits | server | enabled |
- | innodb_rwlock_x_os_waits | server | enabled |
- | innodb_rwlock_x_spin_rounds | server | enabled |
- | innodb_rwlock_x_spin_waits | server | enabled |
- | log_checkpoints | recovery | disabled |
- | log_lsn_buf_pool_oldest | recovery | disabled |
- | log_lsn_checkpoint_age | recovery | disabled |
- | log_lsn_current | recovery | disabled |
- | log_lsn_last_checkpoint | recovery | disabled |
- | log_lsn_last_flush | recovery | disabled |
- | log_max_modified_age_async | recovery | disabled |
- | log_max_modified_age_sync | recovery | disabled |
- | log_num_log_io | recovery | disabled |
- | log_padded | recovery | enabled |
- | log_pending_checkpoint_writes | recovery | disabled |
- | log_pending_log_flushes | recovery | disabled |
- | log_waits | recovery | enabled |
- | log_writes | recovery | enabled |
- | log_write_requests | recovery | enabled |
- | metadata_table_handles_closed | metadata | disabled |
- | metadata_table_handles_opened | metadata | disabled |
- | metadata_table_reference_count | metadata | disabled |
- | os_data_fsyncs | os | enabled |
- | os_data_reads | os | enabled |
- | os_data_writes | os | enabled |
- | os_log_bytes_written | os | enabled |
- | os_log_fsyncs | os | enabled |
- | os_log_pending_fsyncs | os | enabled |
- | os_log_pending_writes | os | enabled |
- | os_pending_reads | os | disabled |
- | os_pending_writes | os | disabled |
- +------------------------------------------+---------------------+----------+
Counter Modules
Each counter is associated with a particular module. Module names
can be used to enable, disable, or reset all counters for a
particular subsystem. For example, use
module_dml
to enable all counters associated
with the dml
subsystem.
- +-------------+-----------+---------+
- +-------------+-----------+---------+
- | dml_reads | dml | enabled |
- | dml_inserts | dml | enabled |
- | dml_deletes | dml | enabled |
- | dml_updates | dml | enabled |
- +-------------+-----------+---------+
Module names can be used with
innodb_monitor_enable
and related
variables.
Module names and corresponding SUBSYSTEM
names
are listed below.
module_adaptive_hash
(subsystem =adaptive_hash_index
)module_buffer
(subsystem =buffer
)module_buffer_page
(subsystem =buffer_page_io
)module_compress
(subsystem =compression
)module_ddl
(subsystem =ddl
)module_dml
(subsystem =dml
)module_file
(subsystem =file_system
)module_ibuf_system
(subsystem =change_buffer
)module_icp
(subsystem =icp
)module_index
(subsystem =index
)module_innodb
(subsystem =innodb
)module_lock
(subsystem =lock
)module_log
(subsystem =recovery
)module_metadata
(subsystem =metadata
)module_os
(subsystem =os
)module_purge
(subsystem =purge
)module_trx
(subsystem =transaction
)module_undo
(subsystem =undo
)
Example 15.11 Working with INNODB_METRICS Table Counters
This example demonstrates enabling, disabling, and resetting a
counter, and querying counter data in the
INNODB_METRICS
table.
Create a simple
InnoDB
table:Enable the
dml_inserts
counter.A description of the
dml_inserts
counter can be found in theCOMMENT
column of theINNODB_METRICS
table:Query the
INNODB_METRICS
table for thedml_inserts
counter data. Because no DML operations have been performed, the counter values are zero or NULL. TheTIME_ENABLED
andTIME_ELAPSED
values indicate when the counter was last enabled and how many seconds have elapsed since that time.- *************************** 1. row ***************************
- NAME: dml_inserts
- SUBSYSTEM: dml
- COUNT: 0
- MAX_COUNT: 0
- MIN_COUNT: NULL
- AVG_COUNT: 0
- COUNT_RESET: 0
- MAX_COUNT_RESET: 0
- MIN_COUNT_RESET: NULL
- AVG_COUNT_RESET: NULL
- TIME_ENABLED: 2014-12-04 14:18:28
- TIME_DISABLED: NULL
- TIME_ELAPSED: 28
- TIME_RESET: NULL
- STATUS: enabled
- TYPE: status_counter
- COMMENT: Number of rows inserted
Insert three rows of data into the table.
Query the
INNODB_METRICS
table again for thedml_inserts
counter data. A number of counter values have now incremented includingCOUNT
,MAX_COUNT
,AVG_COUNT
, andCOUNT_RESET
. Refer to theINNODB_METRICS
table definition for descriptions of these values.- *************************** 1. row ***************************
- NAME: dml_inserts
- SUBSYSTEM: dml
- COUNT: 3
- MAX_COUNT: 3
- MIN_COUNT: NULL
- AVG_COUNT: 0.046153846153846156
- COUNT_RESET: 3
- MAX_COUNT_RESET: 3
- MIN_COUNT_RESET: NULL
- AVG_COUNT_RESET: NULL
- TIME_ENABLED: 2014-12-04 14:18:28
- TIME_DISABLED: NULL
- TIME_ELAPSED: 65
- TIME_RESET: NULL
- STATUS: enabled
- TYPE: status_counter
- COMMENT: Number of rows inserted
Reset the
dml_inserts
counter and query theINNODB_METRICS
table again for thedml_inserts
counter data. The%_RESET
values that were reported previously, such asCOUNT_RESET
andMAX_RESET
, are set back to zero. Values such asCOUNT
,MAX_COUNT
, andAVG_COUNT
, which cumulatively collect data from the time the counter is enabled, are unaffected by the reset.- Query OK, 0 rows affected (0.00 sec)
- *************************** 1. row ***************************
- NAME: dml_inserts
- SUBSYSTEM: dml
- COUNT: 3
- MAX_COUNT: 3
- MIN_COUNT: NULL
- AVG_COUNT: 0.03529411764705882
- COUNT_RESET: 0
- MAX_COUNT_RESET: 0
- MIN_COUNT_RESET: NULL
- AVG_COUNT_RESET: 0
- TIME_ENABLED: 2014-12-04 14:18:28
- TIME_DISABLED: NULL
- TIME_ELAPSED: 85
- TIME_RESET: 2014-12-04 14:19:44
- STATUS: enabled
- TYPE: status_counter
- COMMENT: Number of rows inserted
To reset all counter values, you must first disable the counter. Disabling the counter sets the
STATUS
value todisabled
.- Query OK, 0 rows affected (0.00 sec)
- *************************** 1. row ***************************
- NAME: dml_inserts
- SUBSYSTEM: dml
- COUNT: 3
- MAX_COUNT: 3
- MIN_COUNT: NULL
- AVG_COUNT: 0.030612244897959183
- COUNT_RESET: 0
- MAX_COUNT_RESET: 0
- MIN_COUNT_RESET: NULL
- AVG_COUNT_RESET: 0
- TIME_ENABLED: 2014-12-04 14:18:28
- TIME_DISABLED: 2014-12-04 14:20:06
- TIME_ELAPSED: 98
- TIME_RESET: NULL
- STATUS: disabled
- TYPE: status_counter
- COMMENT: Number of rows inserted
NoteWildcard match is supported for counter and module names. For example, instead of specifying the full
dml_inserts
counter name, you can specifydml_i%
. You can also enable, disable, or reset multiple counters or modules at once using a wildcard match. For example, specifydml_%
to enable, disable, or reset all counters that begin withdml_
.After the counter is disabled, you can reset all counter values using the
innodb_monitor_reset_all
option. All values are set to zero or NULL.- Query OK, 0 rows affected (0.00 sec)
- *************************** 1. row ***************************
- NAME: dml_inserts
- SUBSYSTEM: dml
- COUNT: 0
- MAX_COUNT: NULL
- MIN_COUNT: NULL
- AVG_COUNT: NULL
- COUNT_RESET: 0
- MAX_COUNT_RESET: NULL
- MIN_COUNT_RESET: NULL
- AVG_COUNT_RESET: NULL
- TIME_ENABLED: NULL
- TIME_DISABLED: NULL
- TIME_ELAPSED: NULL
- TIME_RESET: NULL
- STATUS: disabled
- TYPE: status_counter
- COMMENT: Number of rows inserted
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-information-schema-metrics-table.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.