Loading...
MySQL 9.5 Reference Manual 9.5의 17.15.6 InnoDB INFORMATION_SCHEMA Metrics Table의 한국어 번역본입니다.
아래의 경우에 피드백에서 신고해주신다면 반영하겠습니다.
감사합니다 :)
INNODB_METRICS 테이블은 InnoDB 성능 및 리소스 관련 카운터에 대한 정보를 제공합니다.
INNODB_METRICS 테이블 컬럼은 아래에 나와 있습니다. 컬럼 설명은
Section 28.4.21, “The INFORMATION_SCHEMA INNODB_METRICS Table” 을 참조하십시오.
1mysql> 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 10MAX_COUNT_RESET: 46273 11MIN_COUNT_RESET: NULL 12AVG_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
다음 변수들을 사용하여 카운터를 활성화, 비활성화, 초기화할 수 있습니다:
innodb_monitor_enable:
카운터를 활성화합니다.1SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];
innodb_monitor_disable:
카운터를 비활성화합니다.1SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];
innodb_monitor_reset: 카운터 값을 0으로 초기화합니다.1SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];
innodb_monitor_reset_all:
모든 카운터 값을 초기화합니다. 이 옵션을 사용하기 전에 카운터는 비활성 상태여야 합니다:
innodb_monitor_reset_all.1SET GLOBAL innodb_monitor_reset_all = [counter-name|module_name|pattern|all];
카운터와 카운터 모듈은 MySQL 서버 설정 파일을 사용하여 시작 시점에 활성화할 수도 있습니다. 예를 들어, log 모듈과
metadata_table_handles_opened,
metadata_table_handles_closed 카운터를 활성화하려면 MySQL 서버 설정 파일의 [mysqld] 섹션에 다음 라인을 추가합니다.
1[mysqld] 2innodb_monitor_enable = log,metadata_table_handles_opened,metadata_table_handles_closed
설정 파일에서 여러 개의 카운터나 모듈을 활성화할 때는, 위의 예처럼
innodb_monitor_enable 변수를 지정한 다음 콤마로 구분된 카운터 및 모듈 이름을 나열하십시오. 설정 파일에서는
innodb_monitor_enable 변수만 사용할 수 있습니다.
innodb_monitor_disable 및
innodb_monitor_reset 변수는 명령줄에서만 지원됩니다.
참고
각 카운터는 일정 수준의 런타임 오버헤드를 추가하므로, 프로덕션 서버에서는 특정 문제를 진단하거나 특정 기능을 모니터링하는 용도로 카운터를 보수적으로 사용해야 합니다. 카운터를 보다 광범위하게 사용하려면 테스트 또는 개발 서버 사용을 권장합니다.
사용 가능한 카운터 목록은 변경될 수 있습니다. MySQL 서버 버전에서 사용 가능한 카운터를 확인하려면 Information Schema
INNODB_METRICS 테이블을 조회하십시오.
기본적으로 활성화되어 있는 카운터는
SHOW ENGINE INNODB STATUS 출력에 표시되는 항목과 대응됩니다.
SHOW ENGINE INNODB STATUS 출력에 표시되는 카운터는 시스템 레벨에서는 항상 활성화되어 있지만,
INNODB_METRICS 테이블에 대해서는 비활성화할 수 있습니다. 카운터 상태는 지속되지 않습니다. 별도 설정이 없으면, 서버가 재시작될 때 카운터는 기본 활성 또는 비활성 상태로 돌아갑니다.
카운터 추가 또는 제거로 인해 영향을 받는 프로그램을 실행하는 경우, 업그레이드 과정의 일부로서 릴리스 노트를 검토하고
INNODB_METRICS 테이블을 조회하여 이러한 변경 사항을 확인하는 것이 좋습니다.
1mysql> 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_by_dirty_page | buffer | disabled | 40| buffer_flush_n_to_flush_requested | buffer | disabled | 41| buffer_flush_pct_for_dirty | buffer | disabled | 42| buffer_flush_pct_for_lsn | buffer | disabled | 43| buffer_flush_sync | buffer | disabled | 44| buffer_flush_sync_pages | buffer | disabled | 45| buffer_flush_sync_total_pages | buffer | disabled | 46| buffer_flush_sync_waits | buffer | disabled | 47| buffer_LRU_batches_evict | buffer | disabled | 48| buffer_LRU_batches_flush | buffer | disabled | 49| buffer_LRU_batch_evict_pages | buffer | disabled | 50| buffer_LRU_batch_evict_total_pages | buffer | disabled | 51| buffer_LRU_batch_flush_avg_pass | buffer | disabled | 52| buffer_LRU_batch_flush_avg_time_est | buffer | disabled | 53| buffer_LRU_batch_flush_avg_time_slot | buffer | disabled | 54| buffer_LRU_batch_flush_avg_time_thread | buffer | disabled | 55| buffer_LRU_batch_flush_pages | buffer | disabled | 56| buffer_LRU_batch_flush_total_pages | buffer | disabled | 57| buffer_LRU_batch_num_scan | buffer | disabled | 58| buffer_LRU_batch_scanned | buffer | disabled | 59| buffer_LRU_batch_scanned_per_call | buffer | disabled | 60| buffer_LRU_get_free_loops | buffer | disabled | 61| buffer_LRU_get_free_search | Buffer | disabled | 62| buffer_LRU_get_free_waits | buffer | disabled | 63| buffer_LRU_search_num_scan | buffer | disabled | 64| buffer_LRU_search_scanned | buffer | disabled | 65| buffer_LRU_search_scanned_per_call | buffer | disabled | 66| buffer_LRU_single_flush_failure_count | Buffer | disabled | 67| buffer_LRU_single_flush_num_scan | buffer | disabled | 68| buffer_LRU_single_flush_scanned | buffer | disabled | 69| buffer_LRU_single_flush_scanned_per_call | buffer | disabled | 70| buffer_LRU_unzip_search_num_scan | buffer | disabled | 71| buffer_LRU_unzip_search_scanned | buffer | disabled | 72| buffer_LRU_unzip_search_scanned_per_call | buffer | disabled | 73| buffer_pages_created | buffer | enabled | 74| buffer_pages_read | buffer | enabled | 75| buffer_pages_written | buffer | enabled | 76| buffer_page_read_blob | buffer_page_io | disabled | 77| buffer_page_read_fsp_hdr | buffer_page_io | disabled | 78| buffer_page_read_ibuf_bitmap | buffer_page_io | disabled | 79| buffer_page_read_ibuf_free_list | buffer_page_io | disabled | 80| buffer_page_read_index_ibuf_leaf | buffer_page_io | disabled | 81| buffer_page_read_index_ibuf_non_leaf | buffer_page_io | disabled | 82| buffer_page_read_index_inode | buffer_page_io | disabled | 83| buffer_page_read_index_leaf | buffer_page_io | disabled | 84| buffer_page_read_index_non_leaf | buffer_page_io | disabled | 85| buffer_page_read_other | buffer_page_io | disabled | 86| buffer_page_read_rseg_array | buffer_page_io | disabled | 87| buffer_page_read_system_page | buffer_page_io | disabled | 88| buffer_page_read_trx_system | buffer_page_io | disabled | 89| buffer_page_read_undo_log | buffer_page_io | disabled | 90| buffer_page_read_xdes | buffer_page_io | disabled | 91| buffer_page_read_zblob | buffer_page_io | disabled | 92| buffer_page_read_zblob2 | buffer_page_io | disabled | 93| buffer_page_written_blob | buffer_page_io | disabled | 94| buffer_page_written_fsp_hdr | buffer_page_io | disabled | 95| buffer_page_written_ibuf_bitmap | buffer_page_io | disabled | 96| buffer_page_written_ibuf_free_list | buffer_page_io | disabled | 97| buffer_page_written_index_ibuf_leaf | buffer_page_io | disabled | 98| buffer_page_written_index_ibuf_non_leaf | buffer_page_io | disabled | 99| buffer_page_written_index_inode | buffer_page_io | disabled | 100| buffer_page_written_index_leaf | buffer_page_io | disabled | 101| buffer_page_written_index_non_leaf | buffer_page_io | disabled | 102| buffer_page_written_on_log_no_waits | buffer_page_io | disabled | 103| buffer_page_written_on_log_waits | buffer_page_io | disabled | 104| buffer_page_written_on_log_wait_loops | buffer_page_io | disabled | 105| buffer_page_written_other | buffer_page_io | disabled | 106| buffer_page_written_rseg_array | buffer_page_io | disabled | 107| buffer_page_written_system_page | buffer_page_io | disabled | 108| buffer_page_written_trx_system | buffer_page_io | disabled | 109| buffer_page_written_undo_log | buffer_page_io | disabled | 110| buffer_page_written_xdes | buffer_page_io | disabled | 111| buffer_page_written_zblob | buffer_page_io | disabled | 112| buffer_page_written_zblob2 | buffer_page_io | disabled | 113| buffer_pool_bytes_data | buffer | enabled | 114| buffer_pool_bytes_dirty | buffer | enabled | 115| buffer_pool_pages_data | buffer | enabled | 116| buffer_pool_pages_dirty | buffer | enabled | 117| buffer_pool_pages_free | buffer | enabled | 118| buffer_pool_pages_misc | buffer | enabled | 119| buffer_pool_pages_total | buffer | enabled | 120| buffer_pool_reads | buffer | enabled | 121| buffer_pool_read_ahead | buffer | enabled | 122| buffer_pool_read_ahead_evicted | buffer | enabled | 123| buffer_pool_read_requests | buffer | enabled | 124| buffer_pool_size | server | enabled | 125| buffer_pool_wait_free | buffer | enabled | 126| buffer_pool_write_requests | buffer | enabled | 127| compression_pad_decrements | compression | disabled | 128| compression_pad_increments | compression | disabled | 129| compress_pages_compressed | compression | disabled | 130| compress_pages_decompressed | compression | disabled | 131| cpu_n | cpu | disabled | 132| cpu_stime_abs | cpu | disabled | 133| cpu_stime_pct | cpu | disabled | 134| cpu_utime_abs | cpu | disabled | 135| cpu_utime_pct | cpu | disabled | 136| dblwr_async_requests | dblwr | disabled | 137| dblwr_flush_requests | dblwr | disabled | 138| dblwr_flush_wait_events | dblwr | disabled | 139| dblwr_sync_requests | dblwr | disabled | 140| ddl_background_drop_tables | ddl | disabled | 141| ddl_log_file_alter_table | ddl | disabled | 142| ddl_online_create_index | ddl | disabled | 143| ddl_pending_alter_table | ddl | disabled | 144| ddl_sort_file_alter_table | ddl | disabled | 145| dml_deletes | dml | enabled | 146| dml_inserts | dml | enabled | 147| dml_reads | dml | disabled | 148| dml_system_deletes | dml | enabled | 149| dml_system_inserts | dml | enabled | 150| dml_system_reads | dml | enabled | 151| dml_system_updates | dml | enabled | 152| dml_updates | dml | enabled | 153| file_num_open_files | file_system | enabled | 154| ibuf_merges | change_buffer | enabled | 155| ibuf_merges_delete | change_buffer | enabled | 156| ibuf_merges_delete_mark | change_buffer | enabled | 157| ibuf_merges_discard_delete | change_buffer | enabled | 158| ibuf_merges_discard_delete_mark | change_buffer | enabled | 159| ibuf_merges_discard_insert | change_buffer | enabled | 160| ibuf_merges_insert | change_buffer | enabled | 161| ibuf_size | change_buffer | enabled | 162| icp_attempts | icp | disabled | 163| icp_match | icp | disabled | 164| icp_no_match | icp | disabled | 165| icp_out_of_range | icp | disabled | 166| index_page_discards | index | disabled | 167| index_page_merge_attempts | index | disabled | 168| index_page_merge_successful | index | disabled | 169| index_page_reorg_attempts | index | disabled | 170| index_page_reorg_successful | index | disabled | 171| index_page_splits | index | disabled | 172| innodb_activity_count | server | enabled | 173| innodb_background_drop_table_usec | server | disabled | 174| innodb_dblwr_pages_written | server | enabled | 175| innodb_dblwr_writes | server | enabled | 176| innodb_dict_lru_count | server | disabled | 177| innodb_dict_lru_usec | server | disabled | 178| innodb_ibuf_merge_usec | server | disabled | 179| innodb_master_active_loops | server | disabled | 180| innodb_master_idle_loops | server | disabled | 181| innodb_master_purge_usec | server | disabled | 182| innodb_master_thread_sleeps | server | disabled | 183| innodb_mem_validate_usec | server | disabled | 184| innodb_page_size | server | enabled | 185| innodb_rwlock_sx_os_waits | server | enabled | 186| innodb_rwlock_sx_spin_rounds | server | enabled | 187| innodb_rwlock_sx_spin_waits | server | enabled | 188| innodb_rwlock_s_os_waits | server | enabled | 189| innodb_rwlock_s_spin_rounds | server | enabled | 190| innodb_rwlock_s_spin_waits | server | enabled | 191| innodb_rwlock_x_os_waits | server | enabled | 192| innodb_rwlock_x_spin_rounds | server | enabled | 193| innodb_rwlock_x_spin_waits | server | enabled | 194| lock_deadlocks | lock | enabled | 195| lock_deadlock_false_positives | lock | enabled | 196| lock_deadlock_rounds | lock | enabled | 197| lock_rec_grant_attempts | lock | enabled | 198| lock_rec_locks | lock | disabled | 199| lock_rec_lock_created | lock | disabled | 200| lock_rec_lock_removed | lock | disabled | 201| lock_rec_lock_requests | lock | disabled | 202| lock_rec_lock_waits | lock | disabled | 203| lock_rec_release_attempts | lock | enabled | 204| lock_row_lock_current_waits | lock | enabled | 205| lock_row_lock_time | lock | enabled | 206| lock_row_lock_time_avg | lock | enabled | 207| lock_row_lock_time_max | lock | enabled | 208| lock_row_lock_waits | lock | enabled | 209| lock_schedule_refreshes | lock | enabled | 210| lock_table_locks | lock | disabled | 211| lock_table_lock_created | lock | disabled | 212| lock_table_lock_removed | lock | disabled | 213| lock_table_lock_waits | lock | disabled | 214| lock_threads_waiting | lock | enabled | 215| lock_timeouts | lock | enabled | 216| log_checkpoints | log | disabled | 217| log_concurrency_margin | log | disabled | 218| log_flusher_no_waits | log | disabled | 219| log_flusher_waits | log | disabled | 220| log_flusher_wait_loops | log | disabled | 221| log_flush_avg_time | log | disabled | 222| log_flush_lsn_avg_rate | log | disabled | 223| log_flush_max_time | log | disabled | 224| log_flush_notifier_no_waits | log | disabled | 225| log_flush_notifier_waits | log | disabled | 226| log_flush_notifier_wait_loops | log | disabled | 227| log_flush_total_time | log | disabled | 228| log_free_space | log | disabled | 229| log_full_block_writes | log | disabled | 230| log_lsn_archived | log | disabled | 231| log_lsn_buf_dirty_pages_added | log | disabled | 232| log_lsn_buf_pool_oldest_approx | log | disabled | 233| log_lsn_buf_pool_oldest_lwm | log | disabled | 234| log_lsn_checkpoint_age | log | disabled | 235| log_lsn_current | log | disabled | 236| log_lsn_last_checkpoint | log | disabled | 237| log_lsn_last_flush | log | disabled | 238| log_max_modified_age_async | log | disabled | 239| log_max_modified_age_sync | log | disabled | 240| log_next_file | log | disabled | 241| log_on_buffer_space_no_waits | log | disabled | 242| log_on_buffer_space_waits | log | disabled | 243| log_on_buffer_space_wait_loops | log | disabled | 244| log_on_file_space_no_waits | log | disabled | 245| log_on_file_space_waits | log | disabled | 246| log_on_file_space_wait_loops | log | disabled | 247| log_on_flush_no_waits | log | disabled | 248| log_on_flush_waits | log | disabled | 249| log_on_flush_wait_loops | log | disabled | 250| log_on_recent_closed_wait_loops | log | disabled | 251| log_on_recent_written_wait_loops | log | disabled | 252| log_on_write_no_waits | log | disabled | 253| log_on_write_waits | log | disabled | 254| log_on_write_wait_loops | log | disabled | 255| log_padded | log | disabled | 256| log_partial_block_writes | log | disabled | 257| log_waits | log | enabled | 258| log_writer_no_waits | log | disabled | 259| log_writer_on_archiver_waits | log | disabled | 260| log_writer_on_file_space_waits | log | disabled | 261| log_writer_waits | log | disabled | 262| log_writer_wait_loops | log | disabled | 263| log_writes | log | enabled | 264| log_write_notifier_no_waits | log | disabled | 265| log_write_notifier_waits | log | disabled | 266| log_write_notifier_wait_loops | log | disabled | 267| log_write_requests | log | enabled | 268| log_write_to_file_requests_interval | log | disabled | 269| metadata_table_handles_closed | metadata | disabled | 270| metadata_table_handles_opened | metadata | disabled | 271| metadata_table_reference_count | metadata | disabled | 272| module_cpu | cpu | disabled | 273| module_dblwr | dblwr | disabled | 274| module_page_track | page_track | disabled | 275| os_data_fsyncs | os | enabled | 276| os_data_reads | os | enabled | 277| os_data_writes | os | enabled | 278| os_log_bytes_written | os | enabled | 279| os_log_fsyncs | os | enabled | 280| os_log_pending_fsyncs | os | enabled | 281| os_log_pending_writes | os | enabled | 282| os_pending_reads | os | disabled | 283| os_pending_writes | os | disabled | 284| page_track_checkpoint_partial_flush_request | page_track | disabled | 285| page_track_full_block_writes | page_track | disabled | 286| page_track_partial_block_writes | page_track | disabled | 287| page_track_resets | page_track | disabled | 288| purge_del_mark_records | purge | disabled | 289| purge_dml_delay_usec | purge | disabled | 290| purge_invoked | purge | disabled | 291| purge_resume_count | purge | disabled | 292| purge_stop_count | purge | disabled | 293| purge_truncate_history_count | purge | disabled | 294| purge_truncate_history_usec | purge | disabled | 295| purge_undo_log_pages | purge | disabled | 296| purge_upd_exist_or_extern_records | purge | disabled | 297| sampled_pages_read | sampling | disabled | 298| sampled_pages_skipped | sampling | disabled | 299| trx_active_transactions | transaction | disabled | 300| trx_allocations | transaction | disabled | 301| trx_commits_insert_update | transaction | disabled | 302| trx_nl_ro_commits | transaction | disabled | 303| trx_on_log_no_waits | transaction | disabled | 304| trx_on_log_waits | transaction | disabled | 305| trx_on_log_wait_loops | transaction | disabled | 306| trx_rollbacks | transaction | disabled | 307| trx_rollbacks_savepoint | transaction | disabled | 308| trx_rollback_active | transaction | disabled | 309| trx_ro_commits | transaction | disabled | 310| trx_rseg_current_size | transaction | disabled | 311| trx_rseg_history_len | transaction | enabled | 312| trx_rw_commits | transaction | disabled | 313| trx_undo_slots_cached | transaction | disabled | 314| trx_undo_slots_used | transaction | disabled | 315| undo_truncate_count | undo | disabled | 316| undo_truncate_done_logging_count | undo | disabled | 317| undo_truncate_start_logging_count | undo | disabled | 318| undo_truncate_usec | undo | disabled | 319+---------------------------------------------+---------------------+----------+ 320314 rows in set (0.00 sec)
각 카운터는 특정 모듈과 연관되어 있습니다. 모듈 이름을 사용하여 특정 서브시스템에 대한 모든 카운터를 활성화, 비활성화 또는 초기화할 수 있습니다. 예를 들어,
module_dml 을 사용하면 dml 서브시스템과 연관된 모든 카운터를 활성화할 수 있습니다.
1mysql> SET GLOBAL innodb_monitor_enable = module_dml; 2 3mysql> 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+-------------+-----------+---------+
모듈 이름은
innodb_monitor_enable 및 관련 변수와 함께 사용할 수 있습니다.
모듈 이름과 해당하는 SUBSYSTEM 이름은 아래와 같습니다.
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 = log)module_metadata (subsystem = metadata)module_os (subsystem = os)module_purge (subsystem = purge)module_trx (subsystem = transaction)module_undo (subsystem = undo)Example 17.11 Working with INNODB_METRICS Table Counters
이 예제에서는 카운터를 활성화, 비활성화, 초기화하는 방법과
INNODB_METRICS 테이블에서 카운터 데이터를 조회하는 방법을 보여줍니다.
InnoDB 테이블을 생성합니다:1mysql> USE test; 2Database changed 3 4mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB; 5Query OK, 0 rows affected (0.02 sec)
dml_inserts 카운터를 활성화합니다.1mysql> SET GLOBAL innodb_monitor_enable = dml_inserts; 2Query OK, 0 rows affected (0.01 sec)
dml_inserts 카운터에 대한 설명은
INNODB_METRICS 테이블의 COMMENT 컬럼에서 확인할 수 있습니다:
1mysql> 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+-------------+-------------------------+
dml_inserts 카운터 데이터에 대해
INNODB_METRICS 테이블을 조회합니다. 아직 DML 작업이 수행되지 않았으므로 카운터 값은 0 또는 NULL 입니다.
TIME_ENABLED 및
TIME_ELAPSED 값은 카운터가 마지막으로 활성화된 시점과 그 이후 경과된 초를 나타냅니다.1mysql> 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 10MAX_COUNT_RESET: 0 11MIN_COUNT_RESET: NULL 12AVG_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
1mysql> INSERT INTO t1 values(1); 2Query OK, 1 row affected (0.00 sec) 3 4mysql> INSERT INTO t1 values(2); 5Query OK, 1 row affected (0.00 sec) 6 7mysql> INSERT INTO t1 values(3); 8Query OK, 1 row affected (0.00 sec)
dml_inserts 카운터 데이터에 대해
INNODB_METRICS 테이블을 다시 조회합니다. 이제
COUNT, MAX_COUNT,
AVG_COUNT,
COUNT_RESET 등을 포함한 여러 카운터 값이 증가했습니다. 이러한 값에 대한 설명은
INNODB_METRICS 테이블 정의를 참조하십시오.1mysql> 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 10MAX_COUNT_RESET: 3 11MIN_COUNT_RESET: NULL 12AVG_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
dml_inserts 카운터를 초기화한 후
dml_inserts 카운터 데이터에 대해
INNODB_METRICS 테이블을 다시 조회합니다. 이전에 보고되었던
COUNT_RESET,
MAX_RESET 등의 %_RESET 값은 0 으로 되돌아갑니다. 카운터가 활성화된 시점부터 데이터를 누적하는
COUNT,
MAX_COUNT,
AVG_COUNT 등의 값은 초기화의 영향을 받지 않습니다.1mysql> SET GLOBAL innodb_monitor_reset = dml_inserts; 2Query OK, 0 rows affected (0.00 sec) 3 4mysql> 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 13MAX_COUNT_RESET: 0 14MIN_COUNT_RESET: NULL 15AVG_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
STATUS 값이
disabled 로 설정됩니다.1mysql> SET GLOBAL innodb_monitor_disable = dml_inserts; 2Query OK, 0 rows affected (0.00 sec) 3 4mysql> 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 13MAX_COUNT_RESET: 0 14MIN_COUNT_RESET: NULL 15AVG_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
참고
카운터 및 모듈 이름에 대해 와일드카드 매치가 지원됩니다. 예를 들어 전체
dml_inserts 카운터 이름을 지정하는 대신 dml_i% 를 지정할 수 있습니다. 와일드카드 매치를 사용하여 여러 카운터나 모듈을 한 번에 활성화, 비활성화 또는 초기화할 수도 있습니다. 예를 들어
dml_% 를 지정하면 dml_ 로 시작하는 모든 카운터를 활성화, 비활성화 또는 초기화할 수 있습니다.
innodb_monitor_reset_all
옵션을 사용하여 모든 카운터 값을 초기화할 수 있습니다. 모든 값은 0 또는 NULL 로 설정됩니다.1mysql> SET GLOBAL innodb_monitor_reset_all = dml_inserts; 2Query OK, 0 rows affected (0.00 sec) 3 4mysql> 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 13MAX_COUNT_RESET: NULL 14MIN_COUNT_RESET: NULL 15AVG_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
17.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables
17.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table