20.30.19. INFORMATION_SCHEMA INNODB_METRICS Таблица

Вы должны иметь PROCESS полномочие запросить эту таблицу.

По умолчанию относительно небольшие данные собираются. Чтобы запустить, остановитесь, и сбросьте счетчики, Вы устанавливаете один из параметры конфигурации innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, или innodb_monitor_reset_all, используя имя счетчика, имя модуля, подстановочного соответствия для такого имени, используя символ "%", или специальное ключевое слово all. Например:

-- Turn on a counter:set global innodb_monitor_enable = counter_name;-- Turn off a counter:set global innodb_monitor_disable = counter_name;-- Reset a counter:set global innodb_monitor_reset = counter_name;-- Reset all the counter values:set global innodb_monitor_reset_all = counter_name;-- Turn on a group of counters:set global innodb_monitor_enable = module_name;-- Turn off a group of counters:set global innodb_monitor_enable = module_name;-- Turn on monitor "dml_num_inserts":set global innodb_monitor_enable = dml_num_inserts;-- Reset all values for "dml_num_inserts":set global innodb_monitor_reset_all = dml_num_inserts;-- Reset the counters for all dml monitorsset global innodb_monitor_reset = "dml_%";-- Turn on all monitors for the transaction subsystemset global innodb_monitor_enable = module_trx;-- Turn off all monitorsset global innodb_monitor_disable = all;

Имена модуля соответствуют, но не идентичны, значения от SUBSYSTEM столбец. Вот значения, для которых можно использовать module_name с innodb_monitor_enable и связанные параметры конфигурации, наряду с соответствием SUBSYSTEM names:

Поскольку каждый счетчик налагает определенную степень издержек времени выполнения на сервере, обычно Вы включаете большему количеству счетчиков на тесте и серверах разработки во время экспериментирования и сравнительного тестирования, и только позволяете счетчикам на производственных серверах диагностировать известные проблемы или аспекты монитора, которые, вероятно, будут узкими местами для определенного сервера и рабочей нагрузки.

Элементы, представленные в innodb_metrics таблица подвержена изменениям, таким образом, для самого актуального списка, запросите рабочий сервер MySQL. С MySQL 5.6.10 элементы включают:

mysql: select name, subsystem from information_schema.innodb_metrics order by name;+------------------------------------------+---------------------+| name                                     | subsystem           |+------------------------------------------+---------------------+| adaptive_hash_pages_added                | adaptive_hash_index || adaptive_hash_pages_removed              | adaptive_hash_index || adaptive_hash_rows_added                 | adaptive_hash_index || adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index || adaptive_hash_rows_removed               | adaptive_hash_index || adaptive_hash_rows_updated               | adaptive_hash_index || adaptive_hash_searches                   | adaptive_hash_index || adaptive_hash_searches_btree             | adaptive_hash_index || buffer_data_reads                        | buffer              || buffer_data_written                      | buffer              || buffer_flush_adaptive                    | buffer              || buffer_flush_adaptive_pages              | buffer              || buffer_flush_adaptive_total_pages        | buffer              || buffer_flush_avg_page_rate               | buffer              || buffer_flush_background                  | buffer              || buffer_flush_background_pages            | buffer              || buffer_flush_background_total_pages      | buffer              || buffer_flush_batches                     | buffer              || buffer_flush_batch_num_scan              | buffer              || buffer_flush_batch_pages                 | buffer              || buffer_flush_batch_scanned               | buffer              || buffer_flush_batch_scanned_per_call      | buffer              || buffer_flush_batch_total_pages           | buffer              || buffer_flush_lsn_avg_rate                | buffer              || buffer_flush_neighbor                    | buffer              || buffer_flush_neighbor_pages              | buffer              || buffer_flush_neighbor_total_pages        | buffer              || buffer_flush_n_to_flush_requested        | buffer              || buffer_flush_pct_for_dirty               | buffer              || buffer_flush_pct_for_lsn                 | buffer              || buffer_flush_sync                        | buffer              || buffer_flush_sync_pages                  | buffer              || buffer_flush_sync_total_pages            | buffer              || buffer_flush_sync_waits                  | buffer              || buffer_LRU_batches                       | buffer              || buffer_LRU_batch_num_scan                | buffer              || buffer_LRU_batch_pages                   | buffer              || buffer_LRU_batch_scanned                 | buffer              || buffer_LRU_batch_scanned_per_call        | buffer              || buffer_LRU_batch_total_pages             | buffer              || buffer_LRU_get_free_search               | Buffer              || buffer_LRU_search_num_scan               | buffer              || buffer_LRU_search_scanned                | buffer              || buffer_LRU_search_scanned_per_call       | buffer              || buffer_LRU_single_flush_failure_count    | Buffer              || buffer_LRU_single_flush_num_scan         | buffer              || buffer_LRU_single_flush_scanned          | buffer              || buffer_LRU_single_flush_scanned_per_call | buffer              || buffer_LRU_unzip_search_num_scan         | buffer              || buffer_LRU_unzip_search_scanned          | buffer              || buffer_LRU_unzip_search_scanned_per_call | buffer              || buffer_pages_created                     | buffer              || buffer_pages_read                        | buffer              || buffer_pages_written                     | buffer              || buffer_page_read_blob                    | buffer_page_io      || buffer_page_read_fsp_hdr                 | buffer_page_io      || buffer_page_read_ibuf_bitmap             | buffer_page_io      || buffer_page_read_ibuf_free_list          | buffer_page_io      || buffer_page_read_index_ibuf_leaf         | buffer_page_io      || buffer_page_read_index_ibuf_non_leaf     | buffer_page_io      || buffer_page_read_index_inode             | buffer_page_io      || buffer_page_read_index_leaf              | buffer_page_io      || buffer_page_read_index_non_leaf          | buffer_page_io      || buffer_page_read_other                   | buffer_page_io      || buffer_page_read_system_page             | buffer_page_io      || buffer_page_read_trx_system              | buffer_page_io      || buffer_page_read_undo_log                | buffer_page_io      || buffer_page_read_xdes                    | buffer_page_io      || buffer_page_read_zblob                   | buffer_page_io      || buffer_page_read_zblob2                  | buffer_page_io      || buffer_page_written_blob                 | buffer_page_io      || buffer_page_written_fsp_hdr              | buffer_page_io      || buffer_page_written_ibuf_bitmap          | buffer_page_io      || buffer_page_written_ibuf_free_list       | buffer_page_io      || buffer_page_written_index_ibuf_leaf      | buffer_page_io      || buffer_page_written_index_ibuf_non_leaf  | buffer_page_io      || buffer_page_written_index_inode          | buffer_page_io      || buffer_page_written_index_leaf           | buffer_page_io      || buffer_page_written_index_non_leaf       | buffer_page_io      || buffer_page_written_other                | buffer_page_io      || buffer_page_written_system_page          | buffer_page_io      || buffer_page_written_trx_system           | buffer_page_io      || buffer_page_written_undo_log             | buffer_page_io      || buffer_page_written_xdes                 | buffer_page_io      || buffer_page_written_zblob                | buffer_page_io      || buffer_page_written_zblob2               | buffer_page_io      || buffer_pool_bytes_data                   | buffer              || buffer_pool_bytes_dirty                  | buffer              || buffer_pool_pages_data                   | buffer              || buffer_pool_pages_dirty                  | buffer              || buffer_pool_pages_free                   | buffer              || buffer_pool_pages_misc                   | buffer              || buffer_pool_pages_total                  | buffer              || buffer_pool_reads                        | buffer              || buffer_pool_read_ahead                   | buffer              || buffer_pool_read_ahead_evicted           | buffer              || buffer_pool_read_requests                | buffer              || buffer_pool_size                         | server              || buffer_pool_wait_free                    | buffer              || buffer_pool_write_requests               | buffer              || compression_pad_decrements               | compression         || compression_pad_increments               | compression         || compress_pages_compressed                | compression         || compress_pages_decompressed              | compression         || ddl_background_drop_indexes              | ddl                 || ddl_background_drop_tables               | ddl                 || ddl_online_create_index                  | ddl                 || ddl_pending_alter_table                  | ddl                 || dml_deletes                              | dml                 || dml_inserts                              | dml                 || dml_reads                                | dml                 || dml_updates                              | dml                 || file_num_open_files                      | file_system         || ibuf_merges                              | change_buffer       || ibuf_merges_delete                       | change_buffer       || ibuf_merges_delete_mark                  | change_buffer       || ibuf_merges_discard_delete               | change_buffer       || ibuf_merges_discard_delete_mark          | change_buffer       || ibuf_merges_discard_insert               | change_buffer       || ibuf_merges_insert                       | change_buffer       || ibuf_size                                | change_buffer       || icp_attempts                             | icp                 || icp_match                                | icp                 || icp_no_match                             | icp                 || icp_out_of_range                         | icp                 || index_merges                             | index               || index_splits                             | index               || innodb_activity_count                    | server              || innodb_background_drop_table_usec        | server              || innodb_checkpoint_usec                   | server              || innodb_dblwr_pages_written               | server              || innodb_dblwr_writes                      | server              || innodb_dict_lru_usec                     | server              || innodb_ibuf_merge_usec                   | server              || innodb_log_flush_usec                    | server              || innodb_master_active_loops               | server              || innodb_master_idle_loops                 | server              || innodb_master_purge_usec                 | server              || innodb_master_thread_sleeps              | server              || innodb_mem_validate_usec                 | server              || innodb_page_size                         | server              || innodb_rwlock_s_os_waits                 | server              || innodb_rwlock_s_spin_rounds              | server              || innodb_rwlock_s_spin_waits               | server              || innodb_rwlock_x_os_waits                 | server              || innodb_rwlock_x_spin_rounds              | server              || innodb_rwlock_x_spin_waits               | server              || lock_deadlocks                           | lock                || lock_rec_locks                           | lock                || lock_rec_lock_created                    | lock                || lock_rec_lock_removed                    | lock                || lock_rec_lock_requests                   | lock                || lock_rec_lock_waits                      | lock                || lock_row_lock_current_waits              | lock                || lock_row_lock_time                       | lock                || lock_row_lock_time_avg                   | lock                || lock_row_lock_time_max                   | lock                || lock_row_lock_waits                      | lock                || lock_table_locks                         | lock                || lock_table_lock_created                  | lock                || lock_table_lock_removed                  | lock                || lock_table_lock_waits                    | lock                || lock_timeouts                            | lock                || log_checkpoints                          | recovery            || log_lsn_buf_pool_oldest                  | recovery            || log_lsn_checkpoint_age                   | recovery            || log_lsn_current                          | recovery            || log_lsn_last_checkpoint                  | recovery            || log_lsn_last_flush                       | recovery            || log_max_modified_age_async               | recovery            || log_max_modified_age_sync                | recovery            || log_num_log_io                           | recovery            || log_pending_checkpoint_writes            | recovery            || log_pending_log_writes                   | recovery            || log_waits                                | recovery            || log_writes                               | recovery            || log_write_requests                       | recovery            || metadata_mem_pool_size                   | metadata            || metadata_table_handles_closed            | metadata            || metadata_table_handles_opened            | metadata            || metadata_table_reference_count           | metadata            || os_data_fsyncs                           | os                  || os_data_reads                            | os                  || os_data_writes                           | os                  || os_log_bytes_written                     | os                  || os_log_fsyncs                            | os                  || os_log_pending_fsyncs                    | os                  || os_log_pending_writes                    | os                  || os_pending_reads                         | os                  || os_pending_writes                        | os                  || purge_del_mark_records                   | purge               || purge_dml_delay_usec                     | purge               || purge_invoked                            | purge               || purge_resume_count                       | purge               || purge_stop_count                         | purge               || purge_undo_log_pages                     | purge               || purge_upd_exist_or_extern_records        | purge               || trx_active_transactions                  | transaction         || trx_commits_insert_update                | transaction         || trx_nl_ro_commits                        | transaction         || trx_rollbacks                            | transaction         || trx_rollbacks_savepoint                  | transaction         || trx_rollback_active                      | transaction         || trx_ro_commits                           | transaction         || trx_rseg_current_size                    | transaction         || trx_rseg_history_len                     | transaction         || trx_rw_commits                           | transaction         || trx_undo_slots_cached                    | transaction         || trx_undo_slots_used                      | transaction         |+------------------------------------------+---------------------+209 rows in set (0.00 sec)