Performance Schema를 사용하기 위해서는 몇 가지 설정을 해 주어야 한다.
1. 시스템 설정 변수 값 확인
다음 명령을 이용하여 성능 정보를 수집하기 위하여 사용하는 시스템 변수 설정 값을 확인해 볼 수 있다.
mysql> SHOW VARIABLES LIKE 'perf%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| performance_schema | ON |
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | 10000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_transactions_history_size | 10 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_hosts_size | -1 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | -1 |
| performance_schema_max_digest_length | 1024 |
| performance_schema_max_file_classes | 80 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | -1 |
| performance_schema_max_index_stat | -1 |
| performance_schema_max_memory_classes | 320 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | -1 |
| performance_schema_max_prepared_statements_instances | -1 |
| performance_schema_max_program_instances | -1 |
| performance_schema_max_rwlock_classes | 40 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | -1 |
| performance_schema_max_sql_text_length | 1024 |
| performance_schema_max_stage_classes | 150 |
| performance_schema_max_statement_classes | 193 |
| performance_schema_max_statement_stack | 10 |
| performance_schema_max_table_handles | -1 |
| performance_schema_max_table_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | -1 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | -1 |
| performance_schema_setup_objects_size | -1 |
| performance_schema_users_size | -1 |
+----------------------------------------------------------+-------+
값을 수정하기 위해서는 MySQL 기동 시 Command Line으로 추가 시켜서 기동 시키거나 my.cnf 파일에 각각의 환경 변수 값을 설정해 주고 기동을 해주면 된다.
|
my.cnf 파일에 적용 방법
[mysqld] performance_schema_events_waits_history_size=20 performance_schema_events_waits_history_long_size=15000
Command Line으로 추가 시켜서 기동 시키는 방법
/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock --performance_schema_events_waits_history_size=20 --performance_schema_events_waits_history_long_size=15000 |
2. 모니터링 할 성능 지표 선택 및 모니터링 테이블 활성 화
|
1. 설정 가능한 테이블 목록 리스트 업
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME LIKE 'setup%'; +-------------------+ | TABLE_NAME | +-------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +-------------------+
mysql> show tables from performance_schema where Tables_in_performance_schema like 'setup%' ; +------------------------------+ | Tables_in_performance_schema | +------------------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +------------------------------+ 5 rows in set (0.00 sec)
2. 모니터링 항목 별 시간 단위 설정 값 확인 및 변경
mysql> SELECT * FROM setup_timers; +-------------+-------------+ | NAME | TIMER_NAME | +-------------+-------------+ | idle | MICROSECOND | | wait | CYCLE | | stage | NANOSECOND | | statement | NANOSECOND | | transaction | NANOSECOND | +-------------+-------------+
mysql> UPDATE setup_timers SET TIMER_NAME = 'NANOSECOND' -> WHERE NAME = 'wait';
mysql> SELECT * FROM setup_timers; +-------------+-------------+ | NAME | TIMER_NAME | +-------------+-------------+ | idle | MICROSECOND | | wait | NANOSECOND | | stage | NANOSECOND | | statement | NANOSECOND | | transaction | NANOSECOND | +-------------+-------------+
3. 모니터링 테이블들 사용 설정 상태 확인 및 설정
mysql> select * from performance_schema.setup_consumers ; +----------------------------------+---------+ | NAME | ENABLED | +----------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_current | YES | | events_statements_history | YES | | events_statements_history_long | NO | | events_transactions_current | YES | | events_transactions_history | NO | | events_transactions_history_long | NO | | events_waits_current | NO | | events_waits_history | NO | | events_waits_history_long | NO | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)
mysql> update performance_schema.setup_consumers set ENABLED = 'YES' WHERE name = 'events_waits_current' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from performance_schema.setup_consumers ; +----------------------------------+---------+ | NAME | ENABLED | +----------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_current | YES | | events_statements_history | YES | | events_statements_history_long | NO | | events_transactions_current | YES | | events_transactions_history | NO | | events_transactions_history_long | NO | | events_waits_current | YES | | events_waits_history | NO | | events_waits_history_long | NO | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)
4. 수집 지표 별 사용 설정
mysql> select * from performance_schema.setup_instruments ; +------------------------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +------------------------------------------------------------------------+---------+-------+ | wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO | | wait/synch/mutex/sql/LOCK_des_key_file | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done | NO | NO | | wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue | NO | NO | | wait/io/file/sql/binlog | YES | YES | | wait/io/file/sql/binlog_cache | YES | YES | | wait/io/file/sql/binlog_index | YES | YES | | wait/io/file/sql/binlog_index_cache | YES | YES | | wait/io/file/sql/relaylog | YES | YES | | wait/io/file/sql/relaylog_cache | YES | YES | | wait/io/file/sql/relaylog_index | YES | YES | | wait/io/file/sql/relaylog_index_cache | YES | YES | | stage/sql/After create | NO | NO | | stage/sql/allocating local table | NO | NO | | stage/sql/preparing for alter table | NO | NO | | stage/sql/altering table | NO | NO | | stage/sql/committing alter table to storage engine | NO | NO | | stage/sql/Changing master | NO | NO | | stage/sql/Checking master version | NO | NO | | stage/sql/checking permissions | NO | NO | | statement/sql/select | YES | YES | | statement/sql/create_table | YES | YES | | statement/sql/create_index | YES | YES | | statement/sql/alter_table | YES | YES | | statement/sql/update | YES | YES | | statement/sql/insert | YES | YES | | statement/sql/insert_select | YES | YES | | statement/sql/delete | YES | YES | | statement/sql/truncate | YES | YES | | statement/sql/drop_table | YES | YES | | statement/abstract/relay_log | YES | YES | | transaction | NO | NO | | wait/io/socket/sql/server_tcpip_socket | NO | NO | | wait/io/socket/sql/server_unix_socket | NO | NO | | wait/io/socket/sql/client_connection | NO | NO | | idle | YES | YES | | memory/performance_schema/mutex_instances | YES | NO | | memory/performance_schema/rwlock_instances | YES | NO | +------------------------------------------------------------------------+---------+-------+
UPDATE setup_instruments SET ENABLED = 'NO'; 전체 비 활성화 UPDATE setup_instruments SET TIMED = 'NO'; 전체 timing off UPDATE setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'wait/io/file/%'; 특정 지표만 비활성화 UPDATE setup_instruments SET ENABLED = IF(NAME LIKE 'wait/io/file/%', 'NO', 'YES'); 특정 지표만 비활성화 시키고 나머진 활성화
5. 사용 가능한 테이블 리스트 업
mysql> show tables from performance_schema ; +------------------------------------------------------+ | Tables_in_performance_schema | +------------------------------------------------------+ | accounts | | cond_instances | | events_stages_current | | events_stages_history | | events_stages_history_long | | events_stages_summary_by_account_by_event_name | | events_stages_summary_by_host_by_event_name | | events_stages_summary_by_thread_by_event_name | | events_stages_summary_by_user_by_event_name | | events_stages_summary_global_by_event_name | | events_statements_current | | events_statements_history | | events_statements_history_long | | events_statements_summary_by_account_by_event_name | | events_statements_summary_by_digest | | events_statements_summary_by_host_by_event_name | | events_statements_summary_by_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | events_transactions_current | | events_transactions_history | | events_transactions_history_long | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | | events_waits_current | | events_waits_history | | events_waits_history_long | | events_waits_summary_by_account_by_event_name | | events_waits_summary_by_host_by_event_name | | events_waits_summary_by_instance | | events_waits_summary_by_thread_by_event_name | | events_waits_summary_by_user_by_event_name | | events_waits_summary_global_by_event_name | | file_instances | | file_summary_by_event_name | | file_summary_by_instance | | global_status | | global_variables | | host_cache | | hosts | | memory_summary_by_account_by_event_name | | memory_summary_by_host_by_event_name | | memory_summary_by_thread_by_event_name | | memory_summary_by_user_by_event_name | | memory_summary_global_by_event_name | | metadata_locks | | mutex_instances | | objects_summary_global_by_type | | performance_timers | | prepared_statements_instances | | replication_applier_configuration | | replication_applier_status | | replication_applier_status_by_coordinator | | replication_applier_status_by_worker | | replication_connection_configuration | | replication_connection_status | | replication_group_member_stats | | replication_group_members | | rwlock_instances | | session_account_connect_attrs | | session_connect_attrs | | session_status | | session_variables | | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | | socket_instances | | socket_summary_by_event_name | | socket_summary_by_instance | | status_by_account | | status_by_host | | status_by_thread | | status_by_user | | table_handles | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | | threads | | user_variables_by_thread | | users | | variables_by_thread | +------------------------------------------------------+ 87 rows in set (0.00 sec)
SELECT * FROM performance_schema.events_waits_current ; |