Performance Schema (1)

성능 스키마 소개

 

성능 스키마 ( Performance Schema ) 는 MySQL 서버 내에서 실행되는 작업에 대한 상세 메트릭을 제공한다. 성능 스키마는 크게 두 가지 개념을 바탕으로 작동된다. 

1. 인스트루먼트 ( instrument )

인스트루먼트는 정보를 얻고자 하는 MySQL의 코드 내 특정 부분을 나타낸다.

 

2. 컨슈머 ( consumer )

어떤 코드가 수행되었는지에 대한 정보를 저장하는 테이블을 나타낸다.대부분의 DBA가 성능 스키마를 통해 사용하는 부분이 이 컨슈머이다.

 

Performanc schema process

 

인스트루먼트 요소

 

SELECT * FROM performance_schema.setup_instruments WHERE DOCUMENTATION IS NOT NULL;

위와 같은 쿼리문을 통해 performance_schema의 setup_instruments 테이블을 조회할 수 있다. 이 테이블에는 지원되는 모든 인스트루먼트 목록이 포함되어 있다. 조회하면 아래와 같은 결과를 확인할 수 있다.

 

 

NAME 컬럼은 인스트루먼트의 이름을 나타내는데, 모두 슬래시로 구분되어 구성된다. 이름의 가장 왼쪽은 인스트루먼의 종류를 나타낸다. statement는 인스트루먼트가 SQL문임을 나타내고, wait은 대기임을 나타낸다.나머지 요소는 일반적인 것에 특화된 것으로 하위 시스템을 나타낸다.가령, select의 경우, statement 유형인 sql 하위 시스템의 일부이다.

 

컨슈머 체계

 

컨슈머는 인스트루먼트가 정보를 보내는 대상이다. 성능 스키마는 인스트루먼트 결과를 많은 테이블에 저장한다. MySQL 8.0.25버전을 기준으로 performance_schema에 110개의 테이블이 존재한다. 이 테이블들이 의도하는 바를 이해하기 위해서 그룹으로 묶어보자.

 

- 현재 및 과거 데이터

이벤트는 아래와 같이 끝나는 이벤트 모음이다.

*_current : 현재 서버에서 발생하고 있는 이벤트
*_history: 스레드당 최종 10개의 완료된 이벤트
*_history_long: 전역으로 스레드당 최종 10,000개의 완료된 이벤트

이 데이터들은 아래에 이용 가능하다.

events_waits: 뮤텍스 획득과 같은 로우 레벨 서버 대기
events_statements: SQL 문
events_stages: 임시테이블 생성이나 데이터 전송과 같은 프로파일 정보
events_transactions: 트랜잭션들

 

- 요약 테이블

요약 테이블은 테이블이 제안하는 모든 것이 집계된 정보이다. 예를 들어, memory_summary_by_thread_by_event_name 테이블은 사용자 커넥션 및 모든 백그라운드 스레드에 대한 MySQL 스레드당 집계된 메모리 사용량을 저장한다.

 

- 다이제스트 ( Digest )

다이제스트는 쿼리에서 변경되는 부분을 제거하고 쿼리를 집계하는 방법이다. 

SELECT * FROM user WHERE id = 1;
SELECT * FROM user WHERE id = 2;
SELECT * FROM user WHERE id = 3;

-- Digest
SELECT * FROM user WHERE id = ?

 

- 인스턴스

인스턴스는 MySQL 설치에 사용할 수 있는 객체 인스턴스다.

 

- 셋업

셋업 테이블은 성능 스키마의 런타임 설정에 사용된다.

 

이외에도 이름이 엄격한 패턴을 따르지 않는 테이블들도 더러 존재한다.

 

자원 소비

 

성능 스키마에 의해 수집된 데이터는 메모리에 보관된다. 컨슈머의 최대 사이즈를 설정해서 사용하는 메모리량을 제한할 수도 있다. performance_schema의 일부 테이블은 자동 크기 조정을 지원한다. 즉, 서버가 시작될 때 최소한의 메모리를 할당하고 필요에 따라 크기를 조정한다. 특정 인스트루먼트를 비활성화한다고 해도, 이 메모리는 일당 할당되면 해제되지 않는다는 점을 유의하자.

 

모든 인스트루먼트 호출은 performance_schema 테이블에 데이터를 저장하기 위해 두 개의 매크로를 호출한다. 더 많은 인스트루먼트를 호출하면 결국 CPU 사용량이 늘어나게 된다. 

 

제약 사항

 

performance_schema를 설정하고 사용하기 전, 제약 사항을 이해하는 것 역시 중요하다.

 

- MySQL 컴포넌트에서 지원해야 한다.

- 특정 인스트루먼트와 컨슈머가 활성화된 후에만 데이터를 수집한다.

- 메모리를 해제하기 어렵다.

 

스레드 이해

 

MySQL 서버는 멀티 스레드 방식의 소프트웨어이다. 가령, 스레드는 메인 스레드나 스토리지 엔진에 의해 생성된 백그라운드 스레드이거나 사용자 연결을 위해 생성된 포그라운드 스레드일 수 있다. 각 스레드는 '운영체제 스레드 ID'와 '내부적인 MySQL 스레드 ID' 최소 두 개의 고유 식별자가 있다. 그 중 내부적인 MySQL 스레드 ID의 경우, performance_schema 테이블에서 THREAD_ID라고 지칭된다. 또한, 각 포그라운드 스레드는 SHOW PROCESSLIST 명령어 결과로부터 확인 가능한 PROCESSLIST_ID를 가진다.

 

성능 스키마 설정

 

성능 스키마 자체와 메모리 사용 및 수집된 데이터에 대한 제한과 관련된 변수를 활성화/비활성화하는 것은 서버 시작시에만 변경이 가능하다. 반면, 성능 스키마의 인스트루먼트 및 컨슈머는 동적으로 활성화/비활성화 할 수 있다.

 

성능 스키마의 활성화/비활성화

 

성능 스키마는 performance_schema 변수를 ON / OFF 로 설정함으로써 활성화 / 비활성화할 수 있다. 이는 MySQL 설정 파일 또는 서버 시작 시 커맨드 라인을 통해서만 변경이 가능하다.

 

인스트루먼트 활성화/비활성화

 

setup_instruments 테이블을 통해 인스트루먼트를 활성화 / 비활성화 할 수 있다.

 

SELECT * FROM performance_schema.setup_instruments WHERE NAME = 'statement/sql/select';

인스트루먼트 조회 결과

위에서 조회한 setup_instruments 테이블에서 ENABLED 컬럼의 값을 YES 로 UPDATE 함으로써, 활성화를 진행할 수 있다. 이 때, 이렇게 활성화된 인스트루먼트는 영구적으로 적용되지 않고, 서버가 재시작될 때 다시 설정해줘야 한다. 이를 직접 업데이트하는 것 이외에도 ps_setup_enable_instrument 와 같은 프로시저를 이용할 수도 있다. 와일드카드를 사용할 수 있다는 장점이 있지만 이 역시도 비영구적이다. 

 

영구적으로 인스트루먼트를 활성화 / 비활성화하는 방법은 서버 시작 옵션을 조정하는 것이다. 설정 파라미터 performance-schema-instrument를 사용하면 활성화시킬 인스트루먼트를 정할 수 있다. 이 옵션도 프로시저와 마찬가지로 와일드카드를 지원한다.

 

컨슈머 활성화/비활성화

 

컨슈머 역시도 인스트루먼트와 유사하게 setup_consumers 테이블을 업데이트하거나, 업데이트하는 프로시저 ( ps_setup_enable_consumer )를 이용하거나, 서버 시작 시 옵션 performance-schema-consumer 파라미터를 설정함으로써 활성화 / 비활성화 할 수 있다.

 

특정 객체에 대한 모니터링 튜닝

 

성능 스키마에서 특정 객체( EVENT, FUNCTION, PROCEDURE, TABLE, TRIGGER )에 대해서도 모니터링을 활성화할 수 있다. 가령, user 데이터베이스의 트리거에 대해 performance_schema를 활성화하려면 다음 구문을 실행하면 된다.

INSERT INTO performance_schema.setup_objects (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, ENABLED) 
VALUES ('TRIGGER', 'user', '%', 'YES');

 

스레드 모니터링 튜닝

 

setup_threads 테이블에는 모니터링할 수 있는 백그라운드 스레드 목록이 포함되어 있다. ENABLED 열은 특정 스레드에 대한 인스트루먼트를 활성화할지 여부를 결정한다. HISTORY 열은 특정 스레드에 대해 계측된 이벤트를 _history 및 _history_long 테이블에도 저장해야되는지 여부를 결정한다. 가령, 이벤트 스케줄러에 대한 히스토리 로깅을 비활성화하려면 다음 구문을 실행하면 된다.

UPDATE performance_schema.setup_threads SET HISTORY = 'NO' WHERE NAME = 'thread/sql/event_scheduler';