안녕하세요. LINE에서 주로 MySQL 기반 DB 오퍼레이션, 각종 DB 관리 툴, 검증 툴 등을 개발하고 있는 Otsuka(tom__bo)입니다. 이번 글에서는 MySQL의 성능 정보를 얻을 때 이용하는 performance_schema 데이터베이스와 information_schema 데이터베이스의 innodb_metrics 테이블을 모두 활성화하면, MySQL 전체 성능이 어느 정도 나빠지는지 실험한 내용을 소개하겠습니다.
performance_schema, information_schema.innodb_metrics에 관해서는 '詳解MySQL5.7(일본어)'[1]이라는 책을 참고하여 공부했습니다. 더 자세히 알고 싶으신 분은 한번 읽어보시기 바랍니다.
성능 조사에 사용되는 데이터베이스와 테이블
보통 MySQL의 성능과 관련된 정보를 얻고 싶을 땐 performance_schema의 instruments를 이용하거나 information_schema의 innodb_metrics 테이블(또는 이를 포함하는 성능 관련 테이블을 정리한 sys.metrics 뷰)을 이용합니다. 성능 정보를 확인하면 MySQL 내부, 특히 InnoDB 스토리지 엔진에서 어떤 처리가 몇 번 호출되고 이런 처리에 시간이 얼마나 걸리는지 파악할 수 있습니다. 성능 정보를 얻는 과정에선 당연히 오버헤드가 발생하는데요. 이번에 그 오버헤드가 어느 정도인지 간단히 실험한 결과를 소개하고자 합니다. 먼저 performance_schema와 innodb_metrics 이용법을 간단히 설명하고 그 다음 실험 결과를 소개하겠습니다. 실험에 사용한 MySQL 버전은 5.7.22-community입니다.
performance_schema
개요
MySQL의 성능 정보를 얻고자 할 때 가장 먼저 보는 것이 performance_schema 데이터베이스일 것입니다. performance_schema에는 어떤 처리가 호출된 횟수나 그 처리에 걸린 시간 등의 정보가 축적되어 있는데요. 이 정보는 MySQL의 소스 코드 곳곳에 심어져 있는 instruments를 통해 수집된 것입니다. 사용자가 자주 사용하는 것은 디폴트로 활성화되어 있기 때문에 따로 설정하지 않아도 정보를 볼 수 있지만, 그 외에는 필요에 따라 설정을 활성화해야 합니다. performance_schema는 정보를 수집하는 instruments와 수집한 정보를 저장하는 consumers를 설정하여 이용하는 구조입니다. 구체적으로 말씀드리면 performance_schema 데이터베이스에 있는 setup_instruments, setup_objects, setup_consumers, threads, setup_actors, setup_timers 테이블에서 대응하는 레코드를 설정(활성화와 수집 방법 지정)합니다.
디폴트 상태에서 objects(데이터베이스), threads, actors(클라이언트 유저)는 계측이 활성화되어 있습니다. setup_instruments, setup_consumers에서 수집하는 대상의 instruments, consumer를 활성화하여 디폴트 설정 외의 instruments로 계측을 시작할 수 있습니다. [2]
수집할 수 있는 정보
instruments에서 수집할 수 있는 성능 정보 항목을 살펴보겠습니다. 이 항목들은 performance_schema.setup_instruments 테이블에서 확인할 수 있습니다. MySQL 5.7.22 버전에서는 아래와 같이 총 1,032개의 instruments가 있고, '/'로 끊어서 분류되어 있습니다. 첫 번째 '/'까지의 대분류를 보니 결과는 아래와 같았습니다.
select substring_index(name, '/', 1) as category, count(*) from setup_instruments group by substring_index(name, '/', 1);
+-------------+----------+
| category | count(*) |
+-------------+----------+
| idle | 1 |
| memory | 379 |
| stage | 132 |
| statement | 193 |
| transaction | 1 |
| wait | 326 |
+-------------+----------+
6 rows in set (0.00 sec)'詳解MySQL5.7'[1]의 188페이지, 리스트 5.2와 비교하면 idle, transaction을 제외한 카테고리에서 instruments 수가 늘어났습니다. 그리고 MySQL 8.0.13 버전에서는 추가로 error라는 카테고리가 더해져 모두 합치면 1217개로 늘어났습니다.
이용 방법
setup_instruments 테이블에서는 ENABLED와 TIMED 칼럼, setup_consumers 테이블에서는 ENABLED 칼럼으로 기능을 활성화할 수 있습니다. UPDATE 문으로 이들 값을 'YES'로 변경하면 활성화되고, 'NO'로 변경하면 비활성화됩니다. setup_instruments의 TIMED 칼럼값을 'YES'로 변경하여 활성화하면 instruments의 타이밍 정보를 수집할 수 있습니다. memory 카테고리의 instruments는 계측하는 구조가 없으므로 TIMED 칼럼을 'YES'로 UPDATE해도 쿼리는 성공하지만 카테고리 레코드 값은 'NO'로 남아 있어 활성화할 수 없습니다.
아래는 각 테이블의 샘플입니다.
select * from setup_instruments limit 1;
+-------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO |
+-------------------------------------------+---------+-------+
select * from setup_consumers limit 1;
+-----------------------+---------+
| NAME | ENABLED |
+-----------------------+---------+
| events_stages_current | NO |
+-----------------------+---------+INFORMATION_SCHEMA.INNODB_METRICS
개요
INNODB_METRICS 테이블에는 innodb_monitor에서 수집한 InnoDB 스토리지 엔진 정보가 축적됩니다. 여기에서 performance_schema와 'SHOW ENGINE INNODB STATUS'에는 표시되지 않는 정보도 수집할 수 있습니다. INNODB_METRICS 테이블에는 카운터(counter)의 이름인 'NAME', 카운터의 카테고리를 나타내는 'SUBSYSTEM', 그 밖에 카운터 상태, 계측치의 통계 정보 등이 들어 있습니다. 각 항목의 설명은 'COMMENT' 칼럼에서 확인할 수 있습니다.
아래는 innodb_metrics 테이블의 샘플입니다.
select * from innodb_metrics limit 1G
*************************** 1. row ***************************
NAME: metadata_table_handles_opened
SUBSYSTEM: metadata
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: counter
COMMENT: Number of table handles opened수집할 수 있는 정보
MySQL 5.7.22에서는 총 235개 항목이 크게 17개 subsystem으로 나누어져 있습니다. 각 subsystem에서 위에서 말씀드렸던 카운터별 상태, 계측치, 계측치의 통계 정보를 수집할 수 있습니다.
subsystem의 각 카운터 수는 아래 표와 같습니다. 표에는 'module name'이라는 칼럼이 있는데요. 이것은 subsystem에 포함되는 카운터를 활성화할 때 지정하는 subsystem과 같은 분류입니다. 자세한 사정이나 이유는 모르겠지만 subsystem 단위로 카운터를 활성화할 때는 subsystem에 대응하는 module을 지정해서 활성화해야 합니다.
| module name | subsystem name | count |
|---|---|---|
| module_metadata | metadata | 3 |
| module_lock | lock | 16 |
| module_buffer | buffer | 75 |
| module_buf_page | buffer_page_io | 32 |
| module_os | os | 9 |
| module_trx | transaction | 12 |
| module_purge | purge | 7 |
| module_compress | compression | 4 |
| module_file | file_system | 1 |
| module_index | index | 6 |
| module_adaptive_hash | adaptive_hash_index | 8 |
| module_ibuf_system | change_buffer | 8 |
| module_srv | server | 25 |
| module_ddl | ddl | 6 |
| module_dml | dml | 4 |
| module_log | recovery | 15 |
| module_icp | icp | 4 |
개요 문서는 이곳에서, 각 칼럼 정보는 이곳에서 확인할 수 있습니다.
이용 방법
매트릭스를 활성화, 비활성화, 초기화하려면 아래 명령을 실행합니다. 카운터별, 모듈(subsystem)별, 문자열 패턴 또는 전체선택 중 하나의 방법으로 카운터를 지정하여 설정할 수 있습니다.
SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];
SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];
SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];예를 들어 index 서브시스템을 활성화하려면 아래 쿼리를 실행합니다.
SET GLOBAL innodb_monitor_enable = module_index;MySQL 5.7 버전부터 sys 스키마가 추가되었는데요. 이 스키마에 innodb_metrics 포함, 아래 3곳에서 얻은 정보를 한꺼번에 수집할 수 있는 뷰(view)가 있습니다.
- performance_schema.global_status 테이블 정보
- INFORMATION_SCHEMA.INNODB_METRICS 테이블 정보
- performance_schema에서 얻은 메모리 관련 정보
이 테이블을 카테고리(type 칼럼)별로 살펴보면 아래와 같은 정보를 얻을 수 있습니다.
select type, count(*) from sys.metrics group by type;
+--------------------------------------+----------+
| type | count(*) |
+--------------------------------------+----------+
| Global Status | 221 |
| InnoDB Metrics - adaptive_hash_index | 8 |
| InnoDB Metrics - buffer | 57 |
| InnoDB Metrics - buffer_page_io | 32 |
| InnoDB Metrics - change_buffer | 8 |
| InnoDB Metrics - compression | 4 |
| InnoDB Metrics - ddl | 6 |
| InnoDB Metrics - dml | 4 |
| InnoDB Metrics - icp | 4 |
| InnoDB Metrics - index | 6 |
| InnoDB Metrics - lock | 12 |
| InnoDB Metrics - metadata | 3 |
| InnoDB Metrics - os | 5 |
| InnoDB Metrics - purge | 7 |
| InnoDB Metrics - recovery | 12 |
| InnoDB Metrics - server | 22 |
| InnoDB Metrics - transaction | 12 |
| Performance Schema | 2 |
| System Time | 2 |
+--------------------------------------+----------+
19 rows in set (0.01 sec)type 칼럼의 'InnoDB Metrics - ...' 각 분류 개수를 INNODB_METRICS와 비교해보면 그 수가 같거나 작습니다. 대략적인 정보를 보고 싶을 때는 편리하지만, 수집할 수 있는 정보가 Variable_name과 Variable_value로 한정되어 있습니다. 애써 얻은 정보를 전체적으로 파악하지 못하는 것은 바람직하지 않으므로, 여기서는 이 뷰의 출처가 되는 INFORMATION_SCHEMA.INNODB_METRICS 테이블을 살펴보겠습니다.
실험
다음으로 제가 실험한 환경과 내용, 결과를 말씀드리겠습니다.
환경
실험 환경은 다음과 같습니다. 각 설정의 성능 비교 작업에 큰 영향이 없는 상세 설정이나 하드웨어 정보는 생략했습니다.
- MySQL: 5.7.22 (community)
- buffer_pool_size: 10GB
내용
sysbench의 oltp_read_write 시나리오를 아래와 같은 옵션으로 설정, 벤치마크를 실행하여 처리된 트랜잭션 수로 각 설정의 성능을 간단히 비교하겠습니다. sysbench의 prepare로 데이터를 준비한 단계에서 데이터베이스의 크기는 약 2.4GB였습니다. 아래 명령으로 oltp_read_write 시나리오의 벤치마크를 실행했습니다.
sysbench /usr/share/sysbench/oltp_read_write.lua
--db-driver=mysql
--table-size=1000000
--tables=10
--mysql-host=xxx
--mysql-port=3306
--mysql-user=sysbench
--mysql-password=sysbench
--mysql-db=sysbench
--time=600
--threads=10
run그리고 실험마다 mysqld를 재실행하여 buffer_pool을 읽지 않도록 아래 설정을 my.cnf에 추가했습니다.
innodb_buffer_pool_dump_at_shutdown = OFF
innodb_buffer_pool_load_at_startup = OFF실험 결과
위 설정으로 performance_schema의 instruments/consumers 모든 항목, 마찬가지로 instruments/consumers의 TIMED를 포함한 모든 항목, innodb_metrics의 모든 항목을 활성 혹은 비활성화하는 조합으로 실험을 진행하여 TPS(1초당 트랜잭션 처리 수)를 디폴트 설정과 비교한 결과는 아래와 같습니다.
| SETUP_INSTRUMENTS ALL 'ENABLED' COLUMN |
SETUP_INSTRUMENTS ALL 'TIMED' COLUMN |
ALL INNODB_METRICS |
TPS | 모두 DEFAULT일 때 대비 TPS 비율 |
AVERAGE LATENCY |
COMMENT |
| NO | NO | DISABLED | 3506.25 | 1.031 | 2.28 | performance_schema와 innodb_metrics의 설정을 모두 비활성화한 상태 |
| NO | NO | DEFAULT | 3496.40 | 1.029 | 2.29 | performance_schema의 instruments를 모두 비활성화한 상태 |
| DEFAULT | DEFAULT | DISABLED | 3391.85 | 0.998 | 2.36 | innodb_metrics의 설정을 모두 비활성화한 상태 |
| DEFAULT | DEFAULT | DEFAULT | 3399.32 | 1.00 | 2.35 | 디폴트 설정 |
| DEFAULT | DEFAULT | ENABLED | 3389.69 | 0.997 | 2.36 | innodb_metrics의 설정을 모두 활성화한 상태 |
| YES | DEFAULT | DEFAULT | 2906.15 | 0.855 | 2.75 | performance_schema의 instruments를 모두 활성화한 상태 |
| YES | YES | DEFAULT | 2837.44 | 0.835 | 2.82 | performance_schema의 instruments, TIMED를 모두 활성화한 상태 |
| YES | DEFAULT | ENABLED | 2908.34 | 0.856 | 2.75 | performance_schema와 innodb_metrics의 설정을 모두 활성화한 상태 |
| YES | YES | ENABLED | 2825.31 | 0.831 | 2.83 | performance_schema(TIMED도 활성)와 innodb_metrics의 설정을 모두 활성화한 상태 |
'TPS'와 '모두 DEFAULT일 때 대비 TPS 비율' 결과로 각 설정에 따른 효과를 확인해보겠습니다. 실제 운영 환경에서는 디폴트 설정에서 instruments를 비활성화하는 일은 없을 테지만, 비활성화하면 속도가 빨라진다는 것을 알 수 있습니다. innodb_metrics 설정은 일괄적으로 ON/OFF해도 TPS에서 큰 차이가 발생하지 않았습니다. 반면 performance_schema의 instruments는, 전체를 일괄적으로 ON/OFF한 극단적인 경우지만 TPS에서 약 15% 정도의 성능 저하가 발생했습니다. 비록 sysbench의 oltp_read_write라는 굉장히 단순한 시나리오에 buffer_pool이 DB 데이터로 넘치는 등 임시 환경에서 실험한 결과지만, 그럼에도 performance_schema의 instruments는 전체를 일괄적으로 ON하는 것은 피하고 필요한 항목별로 설정하는 것이 좋을 것 같습니다. 반면 innodb_metrics의 설정은 일괄 활성화해도 큰 영향은 없을 듯합니다. 모두 online에서 변경 가능하므로 실제 환경에서는 상황을 살펴 가며 실행하면 좋을 것 같습니다.
정리
perfromance_schema와 information_schema.innodb_metrics 테이블을 살펴보며 성능 정보를 수집하는 구조와 설정 방법을 설명하고, 이 설정을 일괄적으로 활성화 혹은 비활성화했을 때의 성능을 sysbench의 oltp_read_write 시나리오에 따른 간단한 벤치마크로 검증했습니다. 모든 설정을 일괄적으로 활성화한 경우 15% 정도의 성능 저하가 발생했는데요. 이 실험은 이미 말씀드렸듯 단순한 시나리오(쿼리) 바탕이고 부하도 그다지 크지 않았으므로 실제 운영 환경에서는 적당히 상황을 살피면서 설정해야 할 것입니다. 다만 그런 상황에서 이 실험 결과가 하나의 기준이 될 수 있기를 바랍니다.
[1] 오쿠노 미키야(奥野幹也) 저, "자세히 풀어 쓴 MySQL5.7 멈추지 않는 진화에 뒤떨어지지 않기 위한 테크니컬 가이드(詳解MySQL5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド)", 쇼에이샤(翔泳社), 2016년(이 글과 관련된 자세한 내용을 보시려면 5장을 읽어보시기 바랍니다)
[2] objects는 엄밀히 따지면 전체가 아니라 mysql, performance_schema, information_schema를 제외한 모든 데이터베이스가 디폴트로 활성화되어 있습니다. 이것은 setup_objects를 보면 알 수 있습니다.