MySQL, MariaDB

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

SungWookKang 2023. 9. 2. 06:11
반응형

[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 시스템 정보 수집

반응형