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. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
  2. *************************** 1. row ***************************
  3.            NAME: dml_inserts
  4.       SUBSYSTEM: dml
  5.           COUNT: 46273
  6.       MAX_COUNT: 46273
  7.       MIN_COUNT: NULL
  8.       AVG_COUNT: 492.2659574468085
  9.     COUNT_RESET: 46273
  10. MAX_COUNT_RESET: 46273
  11. MIN_COUNT_RESET: NULL
  12. AVG_COUNT_RESET: NULL
  13.    TIME_ENABLED: 2014-11-28 16:07:53
  14.   TIME_DISABLED: NULL
  15.    TIME_ELAPSED: 94
  16.      TIME_RESET: NULL
  17.          STATUS: enabled
  18.            TYPE: status_counter
  19.         COMMENT: Number of rows inserted

Enabling, Disabling, and Resetting Counters

You can enable, disable, and reset counters using the following variables:

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.

Note

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.

  1. mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS ORDER BY NAME;
  2. +------------------------------------------+---------------------+----------+
  3. | name                                     | subsystem           | status   |
  4. +------------------------------------------+---------------------+----------+
  5. | adaptive_hash_pages_added                | adaptive_hash_index | disabled |
  6. | adaptive_hash_pages_removed              | adaptive_hash_index | disabled |
  7. | adaptive_hash_rows_added                 | adaptive_hash_index | disabled |
  8. | adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index | disabled |
  9. | adaptive_hash_rows_removed               | adaptive_hash_index | disabled |
  10. | adaptive_hash_rows_updated               | adaptive_hash_index | disabled |
  11. | adaptive_hash_searches                   | adaptive_hash_index | enabled  |
  12. | adaptive_hash_searches_btree             | adaptive_hash_index | enabled  |
  13. | buffer_data_reads                        | buffer              | enabled  |
  14. | buffer_data_written                      | buffer              | enabled  |
  15. | buffer_flush_adaptive                    | buffer              | disabled |
  16. | buffer_flush_adaptive_avg_pass           | buffer              | disabled |
  17. | buffer_flush_adaptive_avg_time_est       | buffer              | disabled |
  18. | buffer_flush_adaptive_avg_time_slot      | buffer              | disabled |
  19. | buffer_flush_adaptive_avg_time_thread    | buffer              | disabled |
  20. | buffer_flush_adaptive_pages              | buffer              | disabled |
  21. | buffer_flush_adaptive_total_pages        | buffer              | disabled |
  22. | buffer_flush_avg_page_rate               | buffer              | disabled |
  23. | buffer_flush_avg_pass                    | buffer              | disabled |
  24. | buffer_flush_avg_time                    | buffer              | disabled |
  25. | buffer_flush_background                  | buffer              | disabled |
  26. | buffer_flush_background_pages            | buffer              | disabled |
  27. | buffer_flush_background_total_pages      | buffer              | disabled |
  28. | buffer_flush_batches                     | buffer              | disabled |
  29. | buffer_flush_batch_num_scan              | buffer              | disabled |
  30. | buffer_flush_batch_pages                 | buffer              | disabled |
  31. | buffer_flush_batch_scanned               | buffer              | disabled |
  32. | buffer_flush_batch_scanned_per_call      | buffer              | disabled |
  33. | buffer_flush_batch_total_pages           | buffer              | disabled |
  34. | buffer_flush_lsn_avg_rate                | buffer              | disabled |
  35. | buffer_flush_neighbor                    | buffer              | disabled |
  36. | buffer_flush_neighbor_pages              | buffer              | disabled |
  37. | buffer_flush_neighbor_total_pages        | buffer              | disabled |
  38. | buffer_flush_n_to_flush_by_age           | buffer              | disabled |
  39. | buffer_flush_n_to_flush_requested        | buffer              | disabled |
  40. | buffer_flush_pct_for_dirty               | buffer              | disabled |
  41. | buffer_flush_pct_for_lsn                 | buffer              | disabled |
  42. | buffer_flush_sync                        | buffer              | disabled |
  43. | buffer_flush_sync_pages                  | buffer              | disabled |
  44. | buffer_flush_sync_total_pages            | buffer              | disabled |
  45. | buffer_flush_sync_waits                  | buffer              | disabled |
  46. | buffer_LRU_batches_evict                 | buffer              | disabled |
  47. | buffer_LRU_batches_flush                 | buffer              | disabled |
  48. | buffer_LRU_batch_evict_pages             | buffer              | disabled |
  49. | buffer_LRU_batch_evict_total_pages       | buffer              | disabled |
  50. | buffer_LRU_batch_flush_avg_pass          | buffer              | disabled |
  51. | buffer_LRU_batch_flush_avg_time_est      | buffer              | disabled |
  52. | buffer_LRU_batch_flush_avg_time_slot     | buffer              | disabled |
  53. | buffer_LRU_batch_flush_avg_time_thread   | buffer              | disabled |
  54. | buffer_LRU_batch_flush_pages             | buffer              | disabled |
  55. | buffer_LRU_batch_flush_total_pages       | buffer              | disabled |
  56. | buffer_LRU_batch_num_scan                | buffer              | disabled |
  57. | buffer_LRU_batch_scanned                 | buffer              | disabled |
  58. | buffer_LRU_batch_scanned_per_call        | buffer              | disabled |
  59. | buffer_LRU_get_free_loops                | buffer              | disabled |
  60. | buffer_LRU_get_free_search               | Buffer              | disabled |
  61. | buffer_LRU_get_free_waits                | buffer              | disabled |
  62. | buffer_LRU_search_num_scan               | buffer              | disabled |
  63. | buffer_LRU_search_scanned                | buffer              | disabled |
  64. | buffer_LRU_search_scanned_per_call       | buffer              | disabled |
  65. | buffer_LRU_single_flush_failure_count    | Buffer              | disabled |
  66. | buffer_LRU_single_flush_num_scan         | buffer              | disabled |
  67. | buffer_LRU_single_flush_scanned          | buffer              | disabled |
  68. | buffer_LRU_single_flush_scanned_per_call | buffer              | disabled |
  69. | buffer_LRU_unzip_search_num_scan         | buffer              | disabled |
  70. | buffer_LRU_unzip_search_scanned          | buffer              | disabled |
  71. | buffer_LRU_unzip_search_scanned_per_call | buffer              | disabled |
  72. | buffer_pages_created                     | buffer              | enabled  |
  73. | buffer_pages_read                        | buffer              | enabled  |
  74. | buffer_pages_written                     | buffer              | enabled  |
  75. | buffer_page_read_blob                    | buffer_page_io      | disabled |
  76. | buffer_page_read_fsp_hdr                 | buffer_page_io      | disabled |
  77. | buffer_page_read_ibuf_bitmap             | buffer_page_io      | disabled |
  78. | buffer_page_read_ibuf_free_list          | buffer_page_io      | disabled |
  79. | buffer_page_read_index_ibuf_leaf         | buffer_page_io      | disabled |
  80. | buffer_page_read_index_ibuf_non_leaf     | buffer_page_io      | disabled |
  81. | buffer_page_read_index_inode             | buffer_page_io      | disabled |
  82. | buffer_page_read_index_leaf              | buffer_page_io      | disabled |
  83. | buffer_page_read_index_non_leaf          | buffer_page_io      | disabled |
  84. | buffer_page_read_other                   | buffer_page_io      | disabled |
  85. | buffer_page_read_system_page             | buffer_page_io      | disabled |
  86. | buffer_page_read_trx_system              | buffer_page_io      | disabled |
  87. | buffer_page_read_undo_log                | buffer_page_io      | disabled |
  88. | buffer_page_read_xdes                    | buffer_page_io      | disabled |
  89. | buffer_page_read_zblob                   | buffer_page_io      | disabled |
  90. | buffer_page_read_zblob2                  | buffer_page_io      | disabled |
  91. | buffer_page_written_blob                 | buffer_page_io      | disabled |
  92. | buffer_page_written_fsp_hdr              | buffer_page_io      | disabled |
  93. | buffer_page_written_ibuf_bitmap          | buffer_page_io      | disabled |
  94. | buffer_page_written_ibuf_free_list       | buffer_page_io      | disabled |
  95. | buffer_page_written_index_ibuf_leaf      | buffer_page_io      | disabled |
  96. | buffer_page_written_index_ibuf_non_leaf  | buffer_page_io      | disabled |
  97. | buffer_page_written_index_inode          | buffer_page_io      | disabled |
  98. | buffer_page_written_index_leaf           | buffer_page_io      | disabled |
  99. | buffer_page_written_index_non_leaf       | buffer_page_io      | disabled |
  100. | buffer_page_written_other                | buffer_page_io      | disabled |
  101. | buffer_page_written_system_page          | buffer_page_io      | disabled |
  102. | buffer_page_written_trx_system           | buffer_page_io      | disabled |
  103. | buffer_page_written_undo_log             | buffer_page_io      | disabled |
  104. | buffer_page_written_xdes                 | buffer_page_io      | disabled |
  105. | buffer_page_written_zblob                | buffer_page_io      | disabled |
  106. | buffer_page_written_zblob2               | buffer_page_io      | disabled |
  107. | buffer_pool_bytes_data                   | buffer              | enabled  |
  108. | buffer_pool_bytes_dirty                  | buffer              | enabled  |
  109. | buffer_pool_pages_data                   | buffer              | enabled  |
  110. | buffer_pool_pages_dirty                  | buffer              | enabled  |
  111. | buffer_pool_pages_free                   | buffer              | enabled  |
  112. | buffer_pool_pages_misc                   | buffer              | enabled  |
  113. | buffer_pool_pages_total                  | buffer              | enabled  |
  114. | buffer_pool_reads                        | buffer              | enabled  |
  115. | buffer_pool_read_ahead                   | buffer              | enabled  |
  116. | buffer_pool_read_ahead_evicted           | buffer              | enabled  |
  117. | buffer_pool_read_requests                | buffer              | enabled  |
  118. | buffer_pool_size                         | server              | enabled  |
  119. | buffer_pool_wait_free                    | buffer              | enabled  |
  120. | buffer_pool_write_requests               | buffer              | enabled  |
  121. | compression_pad_decrements               | compression         | disabled |
  122. | compression_pad_increments               | compression         | disabled |
  123. | compress_pages_compressed                | compression         | disabled |
  124. | compress_pages_decompressed              | compression         | disabled |
  125. | ddl_background_drop_indexes              | ddl                 | disabled |
  126. | ddl_background_drop_tables               | ddl                 | disabled |
  127. | ddl_log_file_alter_table                 | ddl                 | disabled |
  128. | ddl_online_create_index                  | ddl                 | disabled |
  129. | ddl_pending_alter_table                  | ddl                 | disabled |
  130. | ddl_sort_file_alter_table                | ddl                 | disabled |
  131. | dml_deletes                              | dml                 | enabled  |
  132. | dml_inserts                              | dml                 | enabled  |
  133. | dml_reads                                | dml                 | disabled |
  134. | dml_updates                              | dml                 | enabled  |
  135. | file_num_open_files                      | file_system         | enabled  |
  136. | ibuf_merges                              | change_buffer       | enabled  |
  137. | ibuf_merges_delete                       | change_buffer       | enabled  |
  138. | ibuf_merges_delete_mark                  | change_buffer       | enabled  |
  139. | ibuf_merges_discard_delete               | change_buffer       | enabled  |
  140. | ibuf_merges_discard_delete_mark          | change_buffer       | enabled  |
  141. | ibuf_merges_discard_insert               | change_buffer       | enabled  |
  142. | ibuf_merges_insert                       | change_buffer       | enabled  |
  143. | ibuf_size                                | change_buffer       | enabled  |
  144. | icp_attempts                             | icp                 | disabled |
  145. | icp_match                                | icp                 | disabled |
  146. | icp_no_match                             | icp                 | disabled |
  147. | icp_out_of_range                         | icp                 | disabled |
  148. | index_page_discards                      | index               | disabled |
  149. | index_page_merge_attempts                | index               | disabled |
  150. | index_page_merge_successful              | index               | disabled |
  151. | index_page_reorg_attempts                | index               | disabled |
  152. | index_page_reorg_successful              | index               | disabled |
  153. | index_page_splits                        | index               | disabled |
  154. | innodb_activity_count                    | server              | enabled  |
  155. | innodb_background_drop_table_usec        | server              | disabled |
  156. | innodb_checkpoint_usec                   | server              | disabled |
  157. | innodb_dblwr_pages_written               | server              | enabled  |
  158. | innodb_dblwr_writes                      | server              | enabled  |
  159. | innodb_dict_lru_count                    | server              | disabled |
  160. | innodb_dict_lru_usec                     | server              | disabled |
  161. | innodb_ibuf_merge_usec                   | server              | disabled |
  162. | innodb_log_flush_usec                    | server              | disabled |
  163. | innodb_master_active_loops               | server              | disabled |
  164. | innodb_master_idle_loops                 | server              | disabled |
  165. | innodb_master_purge_usec                 | server              | disabled |
  166. | innodb_master_thread_sleeps              | server              | disabled |
  167. | innodb_mem_validate_usec                 | server              | disabled |
  168. | innodb_page_size                         | server              | enabled  |
  169. | innodb_rwlock_sx_os_waits                | server              | enabled  |
  170. | innodb_rwlock_sx_spin_rounds             | server              | enabled  |
  171. | innodb_rwlock_sx_spin_waits              | server              | enabled  |
  172. | innodb_rwlock_s_os_waits                 | server              | enabled  |
  173. | innodb_rwlock_s_spin_rounds              | server              | enabled  |
  174. | innodb_rwlock_s_spin_waits               | server              | enabled  |
  175. | innodb_rwlock_x_os_waits                 | server              | enabled  |
  176. | innodb_rwlock_x_spin_rounds              | server              | enabled  |
  177. | innodb_rwlock_x_spin_waits               | server              | enabled  |
  178. | lock_deadlocks                           | lock                | enabled  |
  179. | lock_rec_locks                           | lock                | disabled |
  180. | lock_rec_lock_created                    | lock                | disabled |
  181. | lock_rec_lock_removed                    | lock                | disabled |
  182. | lock_rec_lock_requests                   | lock                | disabled |
  183. | lock_rec_lock_waits                      | lock                | disabled |
  184. | lock_row_lock_current_waits              | lock                | enabled  |
  185. | lock_row_lock_time                       | lock                | enabled  |
  186. | lock_row_lock_time_avg                   | lock                | enabled  |
  187. | lock_row_lock_time_max                   | lock                | enabled  |
  188. | lock_row_lock_waits                      | lock                | enabled  |
  189. | lock_table_locks                         | lock                | disabled |
  190. | lock_table_lock_created                  | lock                | disabled |
  191. | lock_table_lock_removed                  | lock                | disabled |
  192. | lock_table_lock_waits                    | lock                | disabled |
  193. | lock_timeouts                            | lock                | enabled  |
  194. | log_checkpoints                          | recovery            | disabled |
  195. | log_lsn_buf_pool_oldest                  | recovery            | disabled |
  196. | log_lsn_checkpoint_age                   | recovery            | disabled |
  197. | log_lsn_current                          | recovery            | disabled |
  198. | log_lsn_last_checkpoint                  | recovery            | disabled |
  199. | log_lsn_last_flush                       | recovery            | disabled |
  200. | log_max_modified_age_async               | recovery            | disabled |
  201. | log_max_modified_age_sync                | recovery            | disabled |
  202. | log_num_log_io                           | recovery            | disabled |
  203. | log_padded                               | recovery            | enabled  |
  204. | log_pending_checkpoint_writes            | recovery            | disabled |
  205. | log_pending_log_flushes                  | recovery            | disabled |
  206. | log_waits                                | recovery            | enabled  |
  207. | log_writes                               | recovery            | enabled  |
  208. | log_write_requests                       | recovery            | enabled  |
  209. | metadata_table_handles_closed            | metadata            | disabled |
  210. | metadata_table_handles_opened            | metadata            | disabled |
  211. | metadata_table_reference_count           | metadata            | disabled |
  212. | os_data_fsyncs                           | os                  | enabled  |
  213. | os_data_reads                            | os                  | enabled  |
  214. | os_data_writes                           | os                  | enabled  |
  215. | os_log_bytes_written                     | os                  | enabled  |
  216. | os_log_fsyncs                            | os                  | enabled  |
  217. | os_log_pending_fsyncs                    | os                  | enabled  |
  218. | os_log_pending_writes                    | os                  | enabled  |
  219. | os_pending_reads                         | os                  | disabled |
  220. | os_pending_writes                        | os                  | disabled |
  221. | purge_del_mark_records                   | purge               | disabled |
  222. | purge_dml_delay_usec                     | purge               | disabled |
  223. | purge_invoked                            | purge               | disabled |
  224. | purge_resume_count                       | purge               | disabled |
  225. | purge_stop_count                         | purge               | disabled |
  226. | purge_undo_log_pages                     | purge               | disabled |
  227. | purge_upd_exist_or_extern_records        | purge               | disabled |
  228. | trx_active_transactions                  | transaction         | disabled |
  229. | trx_commits_insert_update                | transaction         | disabled |
  230. | trx_nl_ro_commits                        | transaction         | disabled |
  231. | trx_rollbacks                            | transaction         | disabled |
  232. | trx_rollbacks_savepoint                  | transaction         | disabled |
  233. | trx_rollback_active                      | transaction         | disabled |
  234. | trx_ro_commits                           | transaction         | disabled |
  235. | trx_rseg_current_size                    | transaction         | disabled |
  236. | trx_rseg_history_len                     | transaction         | enabled  |
  237. | trx_rw_commits                           | transaction         | disabled |
  238. | trx_undo_slots_cached                    | transaction         | disabled |
  239. | trx_undo_slots_used                      | transaction         | disabled |
  240. +------------------------------------------+---------------------+----------+
  241. 235 rows in set (0.01 sec)

 

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.

  1. mysql> SET GLOBAL innodb_monitor_enable = module_dml;
  2.  
  3. mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS
  4.        WHERE subsystem ='dml';
  5. +-------------+-----------+---------+
  6. | name        | subsystem | status  |
  7. +-------------+-----------+---------+
  8. | dml_reads   | dml       | enabled |
  9. | dml_inserts | dml       | enabled |
  10. | dml_deletes | dml       | enabled |
  11. | dml_updates | dml       | enabled |
  12. +-------------+-----------+---------+

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.

  1. Create a simple InnoDB table:

    1. mysql> USE test;
    2. Database changed
    3.  
    4. mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
    5. Query OK, 0 rows affected (0.02 sec)
  2. Enable the dml_inserts counter.

    1. mysql> SET GLOBAL innodb_monitor_enable = dml_inserts;
    2. Query OK, 0 rows affected (0.01 sec)

    A description of the dml_inserts counter can be found in the COMMENT column of the INNODB_METRICS table:

    1. mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts";
    2. +-------------+-------------------------+
    3. | NAME        | COMMENT                 |
    4. +-------------+-------------------------+
    5. | dml_inserts | Number of rows inserted |
    6. +-------------+-------------------------+
  3. Query the INNODB_METRICS table for the dml_inserts counter data. Because no DML operations have been performed, the counter values are zero or NULL. The TIME_ENABLED and TIME_ELAPSED values indicate when the counter was last enabled and how many seconds have elapsed since that time.

    1. mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
    2. *************************** 1. row ***************************
    3.            NAME: dml_inserts
    4.       SUBSYSTEM: dml
    5.           COUNT: 0
    6.       MAX_COUNT: 0
    7.       MIN_COUNT: NULL
    8.       AVG_COUNT: 0
    9.     COUNT_RESET: 0
    10. MAX_COUNT_RESET: 0
    11. MIN_COUNT_RESET: NULL
    12. AVG_COUNT_RESET: NULL
    13.    TIME_ENABLED: 2014-12-04 14:18:28
    14.   TIME_DISABLED: NULL
    15.    TIME_ELAPSED: 28
    16.      TIME_RESET: NULL
    17.          STATUS: enabled
    18.            TYPE: status_counter
    19.         COMMENT: Number of rows inserted
  4. Insert three rows of data into the table.

    1. mysql> INSERT INTO t1 values(1);
    2. Query OK, 1 row affected (0.00 sec)
    3.  
    4. mysql> INSERT INTO t1 values(2);
    5. Query OK, 1 row affected (0.00 sec)
    6.  
    7. mysql> INSERT INTO t1 values(3);
    8. Query OK, 1 row affected (0.00 sec)
  5. Query the INNODB_METRICS table again for the dml_inserts counter data. A number of counter values have now incremented including COUNT, MAX_COUNT, AVG_COUNT, and COUNT_RESET. Refer to the INNODB_METRICS table definition for descriptions of these values.

    1. mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
    2. *************************** 1. row ***************************
    3.            NAME: dml_inserts
    4.       SUBSYSTEM: dml
    5.           COUNT: 3
    6.       MAX_COUNT: 3
    7.       MIN_COUNT: NULL
    8.       AVG_COUNT: 0.046153846153846156
    9.     COUNT_RESET: 3
    10. MAX_COUNT_RESET: 3
    11. MIN_COUNT_RESET: NULL
    12. AVG_COUNT_RESET: NULL
    13.    TIME_ENABLED: 2014-12-04 14:18:28
    14.   TIME_DISABLED: NULL
    15.    TIME_ELAPSED: 65
    16.      TIME_RESET: NULL
    17.          STATUS: enabled
    18.            TYPE: status_counter
    19.         COMMENT: Number of rows inserted
  6. Reset the dml_inserts counter and query the INNODB_METRICS table again for the dml_inserts counter data. The %_RESET values that were reported previously, such as COUNT_RESET and MAX_RESET, are set back to zero. Values such as COUNT, MAX_COUNT, and AVG_COUNT, which cumulatively collect data from the time the counter is enabled, are unaffected by the reset.

    1. mysql> SET GLOBAL innodb_monitor_reset = dml_inserts;
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
    5. *************************** 1. row ***************************
    6.            NAME: dml_inserts
    7.       SUBSYSTEM: dml
    8.           COUNT: 3
    9.       MAX_COUNT: 3
    10.       MIN_COUNT: NULL
    11.       AVG_COUNT: 0.03529411764705882
    12.     COUNT_RESET: 0
    13. MAX_COUNT_RESET: 0
    14. MIN_COUNT_RESET: NULL
    15. AVG_COUNT_RESET: 0
    16.    TIME_ENABLED: 2014-12-04 14:18:28
    17.   TIME_DISABLED: NULL
    18.    TIME_ELAPSED: 85
    19.      TIME_RESET: 2014-12-04 14:19:44
    20.          STATUS: enabled
    21.            TYPE: status_counter
    22.         COMMENT: Number of rows inserted
  7. To reset all counter values, you must first disable the counter. Disabling the counter sets the STATUS value to disabled.

    1. mysql> SET GLOBAL innodb_monitor_disable = dml_inserts;
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
    5. *************************** 1. row ***************************
    6.            NAME: dml_inserts
    7.       SUBSYSTEM: dml
    8.           COUNT: 3
    9.       MAX_COUNT: 3
    10.       MIN_COUNT: NULL
    11.       AVG_COUNT: 0.030612244897959183
    12.     COUNT_RESET: 0
    13. MAX_COUNT_RESET: 0
    14. MIN_COUNT_RESET: NULL
    15. AVG_COUNT_RESET: 0
    16.    TIME_ENABLED: 2014-12-04 14:18:28
    17.   TIME_DISABLED: 2014-12-04 14:20:06
    18.    TIME_ELAPSED: 98
    19.      TIME_RESET: NULL
    20.          STATUS: disabled
    21.            TYPE: status_counter
    22.         COMMENT: Number of rows inserted
    Note

    Wildcard match is supported for counter and module names. For example, instead of specifying the full dml_inserts counter name, you can specify dml_i%. You can also enable, disable, or reset multiple counters or modules at once using a wildcard match. For example, specify dml_% to enable, disable, or reset all counters that begin with dml_.

  8. 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.

    1. mysql> SET GLOBAL innodb_monitor_reset_all = dml_inserts;
    2. Query OK, 0 rows affected (0.00 sec)
    3.  
    4. mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
    5. *************************** 1. row ***************************
    6.            NAME: dml_inserts
    7.       SUBSYSTEM: dml
    8.           COUNT: 0
    9.       MAX_COUNT: NULL
    10.       MIN_COUNT: NULL
    11.       AVG_COUNT: NULL
    12.     COUNT_RESET: 0
    13. MAX_COUNT_RESET: NULL
    14. MIN_COUNT_RESET: NULL
    15. AVG_COUNT_RESET: NULL
    16.    TIME_ENABLED: NULL
    17.   TIME_DISABLED: NULL
    18.    TIME_ELAPSED: NULL
    19.      TIME_RESET: NULL
    20.          STATUS: disabled
    21.            TYPE: status_counter
    22.         COMMENT: Number of rows inserted


Rechercher dans le manuel MySQL

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

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

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.

Table des matières Haut