Mysql & Maria/admin

Performance Schema

dbavayne 2024. 12. 13. 23:29

 

MySQL의 Performance Schema는 데이터베이스 서버의 성능을 모니터링하고 분석하는 데 사용되는 도구입니다. Performance Schema는 서버의 동작에 대한 세부적인 정보를 수집하고,

이를 기반으로 성능 문제를 진단하거나 최적화를 수행할 수 있게 도와줍니다.

 

Instrument & Consumer

더보기

1. Instrument

  • 정의: "Instrument"는 MySQL 서버에서 발생하는 다양한 이벤트를 추적하기 위한 추적 도구입니다.
    각 이벤트 유형(예: 쿼리 실행, I/O 작업, 대기 이벤트 등)에 대해 세부 정보를 수집하기 위해 성능 스키마에서 사용됩니다.
  • 용도: Instrument는 MySQL의 다양한 작업을 모니터링하고, 이러한 작업에 대한 성능 데이터를 수집하는 데 사용됩니다.

주요 특징:

  • 각 instrument는 특정 이벤트에 대한 성능 데이터를 수집하는 데 사용됩니다.
  • 예를 들어, 쿼리 실행, 잠금 대기, 디스크 I/O 작업 등을 모니터링하기 위한 instrument가 존재합니다.
  • 각 instrument는 성능 데이터를 **"수집"**하는 역할을 합니다.
  • cpu에 영향을 줄 수 있다.

예시:

  • wait/io/file/innodb/innodb_data_file/read: InnoDB 데이터 파일을 읽는 I/O 작업에 대한 성능 데이터를 수집하는 instrument.
  • statement/sql/select: SELECT SQL 문에 대한 성능 데이터를 수집하는 instrument.

Instrument 활성화: MySQL에서는 instrument를 활성화하거나 비활성화할 수 있습니다.

-- 활성화된 instrument 확인
select * from performance_schema.setup_instruments;

-- 특정 instrument 활성화
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME = 'statement/sql/select';

-- 특정 instrument 비활성화
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'NO' 
WHERE NAME = 'statement/sql/select';

 

2. Consumer

  • 정의: "Consumer"는 Performance Schema에서 수집된 성능 데이터를 소비하는 구성 요소입니다.
    데이터를 소비한다는 것은 데이터를 분석하거나 저장하는 작업을 의미합니다.
  • 용도: Consumer는 수집된 데이터를 실제로 어떻게 처리할지 결정하는 역할을 합니다.
    예를 들어, 데이터를 파일에 기록하거나, 내부 테이블에 저장하거나, 외부 시스템에 전송하는 등의 작업을 할 수 있습니다.

주요 특징:

  • 각 consumer는 하나 이상의 instrument에서 수집된 데이터를 처리합니다.
  • 소비자는 데이터를 저장하거나 분석하는 데 필요한 처리를 합니다. 예를 들어, Performance Schema의 이벤트 데이터를 소비하는 여러 가지 방법이 있습니다.
  • 데이터를 필터링하고 저장하거나, 외부로 내보내는 작업을 수행합니다.

예시:

  • events_waits_history: 대기 이벤트 데이터를 기록하여 나중에 분석할 수 있도록 저장하는 소비자.
  • events_statements_history: 실행된 쿼리에 대한 성능 데이터를 기록하는 소비자.
  • global_status: 서버의 전반적인 상태 정보를 수집하고 소비하는 소비자.

Consumer 활성화: 각 consumer는 Performance Schema의 설정 테이블인 setup_consumers를 통해 관리됩니다. 필요에 따라 특정 consumer를 활성화하거나 비활성화할 수 있습니다.

-- 활성화된 consumer 확인
select * from performance_schema.setup_consumers ;

-- 특정 consumer 활성화
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME = 'events_statements_history';

-- 특정 consumer 비활성화
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'NO' 
WHERE NAME = 'events_statements_history';

 

 

주요 특징

더보기
  1. 데이터 수집: Performance Schema는 쿼리 실행, 인덱스 사용, 잠금, 세션 및 I/O 작업과 관련된 다양한 성능 데이터를 수집합니다. 이 정보는 주로 서버의 성능을 추적하고 분석하는 데 유용합니다.
  2. 내부 모니터링: MySQL 서버가 실행하는 여러 가지 작업을 추적하고 모니터링할 수 있습니다.
    예를 들어, 쿼리 처리 시간, 잠금 대기 시간, I/O 성능 등 다양한 지표를 추적합니다.
  3. 수집 가능한 정보:
    • 쿼리 실행 시간
    • 잠금 대기 시간
    • 버퍼 풀 사용량
    • I/O 작업 상태
    • 스레드 및 세션 관련 정보
  4. 쿼리 성능 분석: 특정 쿼리나 작업의 실행 계획과 성능을 분석하여 병목 현상이나 비효율적인 쿼리를 식별할 수 있습니다.

 

 

주요 테이블

더보기

Performance Schema는 여러 테이블로 구성되어 있으며, 각 테이블은 다양한 성능 데이터를 제공합니다.
몇 가지 주요 테이블은 다음과 같습니다:

  1. events_statements_summary_by_digest: SQL 문장의 성능 통계 요약 정보를 제공합니다.
  2. events_waits_summary_by_thread_by_event_name: 각 스레드별로 기다린 이벤트에 대한 정보를 제공합니다.
  3. threads: MySQL 스레드에 대한 정보, 각 스레드의 상태 등을 제공합니다.
  4. file_summary_by_event_name: 파일 I/O와 관련된 성능 데이터를 제공합니다.
  5. waits: 대기 이벤트에 대한 정보를 제공합니다.

아래 쿼리는

실행된 SQL 문장 중에서 가장 많이 실행된 10개 쿼리와 그 실행 횟수, 평균 실행 시간을 반환한다.

  • 쿼리 최적화: 자주 실행되지만 시간이 오래 걸리는 쿼리를 찾아 최적화할 수 있습니다.
  • 에러 및 경고 분석: 자주 에러나 경고가 발생하는 쿼리를 확인하고 원인을 분석할 수 있습니다.
  • 리소스 소비 추적: 특정 쿼리가 너무 많은 리소스를 소비하는 경우 이를 식별하고 개선할 수 있습니다.
SELECT 
	SCHEMA_NAME
	,DIGEST
	,DIGEST_TEXT
	,COUNT_STAR
    ,SUM_TIMER_WAIT / 1000000000 AS SUM_TIMER_WAIT_IN_SECONDS
    ,MIN_TIMER_WAIT / 1000000000 AS MIN_TIMER_WAIT_IN_SECONDS
    ,AVG_TIMER_WAIT / 1000000000 AS AVG_TIMER_WAIT_IN_SECONDS
    ,MAX_TIMER_WAIT / 1000000000 AS MAX_TIMER_WAIT_IN_SECONDS  
	,SUM_LOCK_TIME / 1000000000 AS SUM_LOCK_TIME_IN_SECONDS  
	,SUM_ERRORS
	,SUM_WARNINGS
	,SUM_ROWS_AFFECTED
	,SUM_ROWS_SENT
	,SUM_ROWS_EXAMINED
FROM 
    performance_schema.events_statements_summary_by_digest
where SCHEMA_NAME is not null    
ORDER BY 
    COUNT_STAR DESC
LIMIT 10;

desc events_statements_summary_by_digest;

 

컬럼명 설명
DIGEST SQL 쿼리의 Digest(해시 값), 동일한 SQL 문장을 그룹화하는 데 사용됨.
DIGEST_TEXT Digest에 해당하는 원본 SQL 쿼리.
COUNT_STAR 해당 SQL 문장이 실행된 총 횟수.
SUM_TIMER_WAIT 해당 SQL 문장이 실행되는 동안 소요된 총 시간 (나노초 단위).
MIN_TIMER_WAIT 해당 SQL 문장이 실행되는 동안 소요된 최소 시간.
AVG_TIMER_WAIT 해당 SQL 문장이 실행되는 동안 소요된 평균 시간.
MAX_TIMER_WAIT 해당 SQL 문장이 실행되는 동안 소요된 최대 시간.
SUM_LOCK_TIME 해당 SQL 문장이 실행되는 동안 발생한 잠금 대기 시간의 총합.
SUM_ERRORS 해당 SQL 문장이 실행되는 동안 발생한 에러 횟수.
SUM_WARNINGS 해당 SQL 문장이 실행되는 동안 발생한 경고 횟수.
SUM_ROWS_AFFECTED 해당 SQL 문장이 실행되는 동안 영향을 받은 행의 수.
SUM_ROWS_SENT 해당 SQL 문장이 실행되는 동안 전송된 행의 수.
SUM_ROWS_EXAMINED 해당 SQL 문장이 실행되는 동안 검토된 행의 수.
CREATED 해당 쿼리 Digest가 처음 생성된 시점.

 

 

 

Perfomance_schema 활용

더보기

SQL문 점검에 사용할 수 있다. timer 컬럼들은 다 '피코초' 단위이다.

 

1. events_statements_current

  • 용도: 현재 실행 중인 SQL 문장의 성능 데이터를 제공합니다.
  • 설명: 이 테이블은 현재 실행 중인 SQL 문장의 상태에 대한 실시간 정보를 담고 있습니다.
    즉, 아직 완료되지 않은 쿼리의 실행 정보를 제공합니다.
    쿼리 실행 도중에 발생하는 이벤트에 대한 정보를 즉시 추적할 수 있습니다.
  • 주요 컬럼:
    • THREAD_ID: 쿼리가 실행된 스레드의 ID.
    • EVENT_ID: 이벤트의 고유 ID.
    • SQL_TEXT: 실행 중인 SQL 쿼리.
    • TIMER_START: 쿼리 실행 시작 시각.
    • TIMER_END: 쿼리 실행 종료 시각 (아직 완료되지 않은 경우 null).
    • TIMER_WAIT: 쿼리가 실행되는 동안 발생한 대기 시간.
  • 특징: 실시간으로 현재 실행 중인 쿼리만 추적합니다.
    쿼리가 끝나면 해당 데이터는 events_statements_history나 events_statements_history_long로 이동할 수 있습니다.
SELECT 
    THREAD_ID, SQL_TEXT, TIMER_WAIT
FROM 
    performance_schema.events_statements_current
WHERE 
    SQL_TEXT LIKE 'SELECT%';

2. events_statements_history

  • 용도: 최근 실행된 SQL 문장에 대한 성능 데이터를 제공합니다.
  • 설명: 이 테이블은 events_statements_current에서 종료된 쿼리의 성능 데이터를 저장합니다. 즉, 쿼리가 실행을 완료한 후에 해당 쿼리에 대한 데이터가 이 테이블에 기록됩니다. 기본적으로 최근 10,000개의 SQL 실행 이벤트가 이 테이블에 저장됩니다. 이 데이터는 주로 쿼리 성능 분석 및 모니터링을 위해 사용됩니다.
  • 주요 컬럼:
    • THREAD_ID: 쿼리가 실행된 스레드 ID.
    • EVENT_ID: 이벤트의 고유 ID.
    • SQL_TEXT: 실행된 SQL 쿼리.
    • TIMER_START: 쿼리 시작 시간.
    • TIMER_END: 쿼리 종료 시간.
    • TIMER_WAIT: 쿼리가 실행되는 동안 발생한 대기 시간.
  • 특징: 최근 실행된 쿼리의 데이터를 추적하며, 주기적으로 데이터를 갱신합니다.
SELECT 
    THREAD_ID, SQL_TEXT, TIMER_WAIT, TIMER_START
FROM 
    performance_schema.events_statements_history
WHERE 
    SQL_TEXT LIKE 'SELECT%';

3. events_statements_history_long

  • 용도: 보다 많은 양의 SQL 문장에 대한 성능 데이터를 제공합니다. events_statements_history에 저장되지 못한 더 오래된 실행 데이터를 저장합니다.
  • 설명: 이 테이블은 events_statements_history보다 더 많은 양의 데이터를 보유합니다. 기본적으로 events_statements_history가 일정한 크기를 넘지 않도록 제한되기 때문에, 오래된 데이터는 events_statements_history_long로 이동합니다. 이 테이블은 주로 장기간의 쿼리 성능 데이터를 분석할 때 유용합니다.
  • 주요 컬럼:
    • THREAD_ID: 쿼리가 실행된 스레드 ID.
    • EVENT_ID: 이벤트의 고유 ID.
    • SQL_TEXT: 실행된 SQL 쿼리.
    • TIMER_START: 쿼리 시작 시간.
    • TIMER_END: 쿼리 종료 시간.
    • TIMER_WAIT: 쿼리가 실행되는 동안 발생한 대기 시간.
  • 특징: 오래된 쿼리 데이터를 저장하는 데 사용되며, events_statements_history가 가득 차면 데이터를 보존하기 위해 사용됩니다.
SELECT 
    THREAD_ID, SQL_TEXT, TIMER_WAIT, TIMER_START
FROM 
    performance_schema.events_statements_history_long
WHERE 
    SQL_TEXT LIKE 'SELECT%';

 

Read / Write 사용 패턴 및 성능 점검

select event_name,count(event_name)
from performance_schema.events_statements_history_long
group by event_name;

 

 

 

 

출처: gpt, https://hoing.io/archives/3811#i-9

저작자표시 비영리 변경금지