[MySQL] 성능 모니터링을 위한 Performance_Schema 개념

[MySQL] 성능 모니터링을 위한 Performance_Schema 개념

 

l  Version : MySQL 5.X, 8.X

 

MySQL에서 다양한 성능 및 관리 지표를 얻기 위해 사용하는 정보는 performance_schema (이하 “성능 스키마”)와 information_schema이다. 이번 포스트에서는 performance_schema에 관한 전체적인 개념을 다루며 각 항목에 대한 자세한 세부 내용은 별도 포스팅 한다.

 

 MySQL performance_schema는 로우레벨에 실행되며, 런타임으로 서버 내부를 검사한다. 사용자가 자주 사용하는 것은 디폴트로 활성화되어 있기 때문에 따로 설정하지 않아도 정보를 볼 수 있지만, 그 외에는 필요에 따라 설정을 활성화해야 한다. performance_schema는 정보를 수집하는 instruments와 수집한 정보를 저장하는 consumers를 설정하여 이용하는 구조이다.

l  Performance_schema : 성능 데이터 검사에 사용.

l  Information_schema : 메타데이터 검사에 사용

 

	performance_schema	information_schema
주요 목적	성능 데이터의 수집	메타 데이터의 수집
애플리케이션	성능 개선	감시 도구이자 관리 도구
도입된 버전	5.5	5
SQL 표준	MySQL 전용 기능	O
설치방법	스토리지 엔진으로 설치	infomation schema API
데이터 수집 방법	mysqld 내부에서 런타임 수집	information schema 테이블 접근 시
통상적인 오버 헤드	있음	없음
출력에 의한 오버 헤드	적음	많음

 

Information_schema의 경우 DROP DATABASE 명령으로 삭제할 수 없다. 그 이유는 SYSTEM VIEW 이기 때문이다. 따라서 일반 사용자에게 all privileges on *.* 권한을 주더라도 삭제할 수 없다. 각 스키마의 table_type은 아래와 같다.

l   Information_schema : SYSTEM VIEW

l   mysql : BASE TABLE

l   performance_schema : BASE TABLE

l   sys : VIEW or BASE TABLE

 

performance_schema 이벤트는 서버의 바이너리 로그(데이터 수정을 설명)에 기록된 이벤트 및 이벤트 스케줄러 이벤트(저장 프로그램 유형)와 다르다. 성능 스키마 이벤트는 MySQL 서버의 특정 인스턴스에만 적용된다. 성능 스키마 테이블은 서버에 로컬로 간주되며 해당 테이블에 대한 변경 사항은 바이너리 로그에 복제되거나 기록되지 않는다.

 

[Performance_Schema 스토리지 엔진]

performance_schema 스토리지 엔진은 서버 소스 코드의 "계측 지점(Instrumentation Point)"을 사용하여 이벤트 데이터를 수집한다. 수집된 이벤트는 Performance_schema 데이터베이스의 테이블에 저장된다. 이러한 테이블은 다른 테이블과 마찬가지로 SELECT 문을 사용하여 쿼리할 수 있다. 그리고 성능 스키마의 테이블은 지속적인 온디스크 스토리지를 사용하지 않는 인메모리 테이블이기 때문에, 수집된 내용은 서버 시작 시 다시 채워지고 서버 종료 시 삭제된다.

 

아래 쿼리를 사용하여 performance_schema는 어떤 스토리지 엔진을 사용하는지 확인할 수 있다.

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE = 'PERFORMANCE_SCHEMA';

 

 

결과를 살펴보면 전용 PERFORMANCE_SCHEMA 스토리지 엔진을 사용하는 것을 확인할 수 있으며, Transactions 항목을 보면 NO 로 되어 있으므로 SQL을 사용할 수는 있지만 InnoDB 와는 다르게 Transaction 은 지원하지 않는다는 것을 알 수 있다.

 

성능 스키마 구성은 SQL 문을 통해performance_schema 데이터베이스의 테이블을 업데이트하여 동적으로 수정할 수 있다. 구성 변경은 데이터 수집에 즉시 영향을 미친다. 데이터 수집은 계측을 추가하기 위해 서버 소스 코드를 수정하는 방식으로 구현된다. 복제나 이벤트 스케줄러와 같은 다른 기능과 달리 성능 스키마와 관련된 별도의 스레드가 없다

 

 

[Performance_Schema 동작 방식]

Performance_schema가 동작은 두 가지로 나눌 수 있다.

l   Instrument(인스트루먼트) : 정보를 얻고자 하는 MySQL 코드를 나타냄

l   Consumer(컨슈머) : 어떤 코드를 수행하였는지에 대한 정보를 저장

 

Instrument(인스트루먼트)

performance_schema의 setup_instruments 테이블에는 지원되는 모든 인스트루먼트 목록이 포함되어 있으며, 모든 인스트루먼트 명은 슬래시로 구분해서 구성되어 있다. 몇 가지 예시를 살펴보자.

l   statement/sql/select

l   wait/synch/mutex/innodb/autoinc_mutex

인스트루먼트 이름의 가장 왼쪽이 인스트루먼트의 종류를 의미한다. 따라서 위의 예시에서 statement는 인스트루먼트가 sql 문장임을 나태내고 있으며, wait/ 은 대기 관련된 내용을 나타내고 있다. 인스트루먼트에서 수집할 수 있는 성능 정보 항목은 performance_schema.setup_instruments 에서 확인 할 수 있다. 그룹별로 몇 개의 인스투르먼트가 있는지 확인한다.

select substring_index(name, '/', 1) as category, count(*) from setup_instruments group by substring_index(name, '/', 1);

 

 

인스트루먼트의 상세 목록은 아래 쿼리를 사용한다. MySQL 5.7 버전에서는 1032개의 인스트루먼트가 있다.

select * from performance_schema.setup_instruments;

 

 

 

Consumer(컨슈머)

컨슈머는 인스트루먼트가 정보를 보내는 대상을 의미한다. Performance_schema는 인스트루먼트 결과를 다양한 테이블에 저장하게 된다. performance_schema의 종류는 아래 쿼리로 확인할 수 있으며, 각 테이블의 내용은 SELECT 구문으로 조회할 수 있다. 이번 포스트에서는 각 테이블의 기능 및 역할을 설명하지는 않는다. MySQL 5.7 기준으로 89개의 테이블이 있다. MySQL 8.0의 경우에는 110개 테이블이 존재한다. 버전별로 특성이 있기 때문에 잘 구분해서 사용할 수 있도록 한다.

use performance_schema; show tables;

 

 

 

[Performance_Schema 활성화]

인스트루먼트를 활성화하거나 비활성화 하는 방법은 performance_schema에서 수집하는 성능 지표 항목에 따라 활성 비활성을 할 수 있다. 아래 쿼리로 목록을 조회하고, 결과를 살펴보면 ENABLED 컬럼의 결과로 활성화 여부를 확인할 수 있다.

select * from performance_schema.setup_instruments;

 

 

 setup_instruments 테이블에서는 ENABLED와 TIMED 칼럼을 업데이트하여 기능을 활성화할 수 있습니다. UPDATE 문으로 이들 값을 'YES'로 변경하면 활성화되고, 'NO'로 변경하면 비활성화된다. setup_instruments의 TIMED 칼럼값을 'YES'로 변경하여 활성화하면 instruments의 타이밍 정보를 수집할 수 있다. memory 카테고리의 instruments는 계측하는 구조가 없으므로 TIMED 칼럼을 'YES'로 UPDATE해도 쿼리는 성공하지만 카테고리 레코드 값은 'NO'로 남아 있어 활성화할 수 없다.

update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES' where name='statement/sql/select';

 

인스트루먼트의 활성/비활성화를 쿼리로 업데이트한 경우, 영구적이지 않기 때문에 MySQL 서버 재시작시 다시 적용해주어야 한다. 그렇기 때문에 영구 적용을 위해서는 my.cnf에 시스템 변수로 설정한다.

mysql.cnf

performance-schema-instrument='wait/synch/mutex/sql/LOCK_status=ON' performance-schema-instrument='wait/synch/mutex/innodb/autoinc_mutex=ON'

 

컨슈머를 활성화하는 방법도 인스트루먼트와 유사하게 performance_schema.setup_consumers 테이블을 update하거나 my.cnf 시스템 변수에 등록한다.

 

 

 

[참고자료]

l   Chapter 1 MySQL Performance Schema : https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema.html

l   MySQL Performance Schema - 성능 스키마 : https://hoing.io/archives/3811

l   MySQL performance-schema-instruments 사용에 따른 성능 영향 실험 : https://engineering.linecorp.com/ko/blog/mysql-research-performance-schema-instruments#:~:text=performance_schema%EB%8A%94%20%EC%A0%95%EB%B3%B4%EB%A5%BC%20%EC%88%98%EC%A7%91,%EC%84%A4%EC%A0%95%ED%95%98%EC%97%AC%20%EC%9D%B4%EC%9A%A9%ED%95%98%EB%8A%94%20%EA%B5%AC%EC%A1%B0%EC%9E%85%EB%8B%88%EB%8B%A4.

 

 

 

 

2023-09-02 / Sungwook Kang / http://sungwookkang.com

 

MySQL, 성능 모니터, performance_schema, 성능스키마, MySQL모니터링, MySQL 시스템 정보 수집