MySQL performance-schema-instruments 사용에 따른 성능 영향 실험

안녕하세요. 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 1\G
*************************** 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 namesubsystem namecount
module_metadatametadata3
module_locklock16
module_bufferbuffer75
module_buf_pagebuffer_page_io32
module_osos9
module_trxtransaction12
module_purgepurge7
module_compresscompression4
module_filefile_system1
module_indexindex6
module_adaptive_hashadaptive_hash_index8
module_ibuf_systemchange_buffer8
module_srvserver25
module_ddlddl6
module_dmldml4
module_logrecovery15
module_icpicp4

개요 문서는 이곳에서, 각 칼럼 정보는 이곳에서 확인할 수 있습니다.

이용 방법

매트릭스를 활성화, 비활성화, 초기화하려면 아래 명령을 실행합니다. 카운터별, 모듈(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_wite 시나리오의 벤치마크를 실행했습니다. 

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
NODISABLED3506.251.0312.28performance_schema와 innodb_metrics의 설정을 모두 비활성화한 상태
NO
NODEFAULT3496.401.0292.29performance_schema의 instruments를 모두 비활성화한 상태
DEFAULT
DEFAULTDISABLED3391.850.9982.36innodb_metrics의 설정을 모두 비활성화한 상태
DEFAULT
DEFAULTDEFAULT3399.321.002.35디폴트 설정
DEFAULT
DEFAULTENABLED3389.690.9972.36innodb_metrics의 설정을 모두 활성화한 상태
YES
DEFAULTDEFAULT2906.150.8552.75performance_schema의 instruments를 모두 활성화한 상태
YES
YESDEFAULT2837.440.8352.82performance_schema의 instruments, TIMED를 모두 활성화한 상태
YES
DEFAULTENABLED2908.340.8562.75performance_schema와 innodb_metrics의 설정을 모두 활성화한 상태
YES
YESENABLED2825.310.8312.83performance_schema(TIMED도 활성)와 innodb_metrics의 설정을 모두 활성화한 상태

 

‘TPS’와 ‘모두 DEFAULT일 때 대비 TPS 비율’ 결과로 각 설정에 따른 효과를 확인해보겠습니다. 실제 운영 환경에서는 디폴트 설정에서 instruments를 비활성화하는 일은 없을 테지만, 비활성화하면 속도가 빨라진다는 것을 알 수 있습니다. innodb_metrics 설정은 일괄적으로 ON/OFF해도 TPS에서 큰 차이가 발생하지 않았습니다. 반면 performance_schema의 instruments는, 전체를 일괄적으로 ON/OFF한 극단적인 경우지만 TPS에서 약 15% 정도의 성능 저하가 발생했습니다. 비록 sysbench의 oltp_read_wite라는 굉장히 단순한 시나리오에 buffer_pool이 DB 데이터로 넘치는 등 임시 환경에서 실험한 결과지만, 그럼에도 performance_schema의 instruments는 전체를 일괄적으로 ON하는 것은 피하고 필요한 항목별로 설정하는 것이 좋을 것 같습니다. 반면 innodb_metrics의 설정은 일괄 활성화해도 큰 영향은 없을 듯합니다. 모두 online에서 변경 가능하므로 실제 환경에서는 상황을 살펴 가며 실행하면 좋을 것 같습니다. 

 

정리

perfromance_schema와 information_schema.innodb_metrics 테이블을 살펴보며 성능 정보를 수집하는 구조와 설정 방법을 설명하고, 이 설정을 일괄적으로 활성화 혹은 비활성화했을 때의 성능을 sysbench의 oltp_read_wite 시나리오에 따른 간단한 벤치마크로 검증했습니다. 모든 설정을 일괄적으로 활성화한 경우 15% 정도의 성능 저하가 발생했는데요. 이 실험은 이미 말씀드렸듯 단순한 시나리오(쿼리) 바탕이고 부하도 그다지 크지 않았으므로 실제 운영 환경에서는 적당히 상황을 살피면서 설정해야 할 것입니다. 다만 그런 상황에서 이 실험 결과가 하나의 기준이 될 수 있기를 바랍니다.

 


[1] 오쿠노 미키야(奥野幹也) 저, “자세히 풀어 쓴 MySQL5.7 멈추지 않는 진화에 뒤떨어지지 않기 위한 테크니컬 가이드(詳解MySQL5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド)”, 쇼에이샤(翔泳社), 2016년(이 글과 관련된 자세한 내용을 보시려면 5장을 읽어보시기 바랍니다)

[2] objects는 엄밀히 따지면 전체가 아니라 mysql, performance_schema, information_schema를 제외한 모든 데이터베이스가 디폴트로 활성화되어 있습니다. 이것은 setup_objects를 보면 알 수 있습니다. 

Related Post