[Mysql] 쿼리튜닝, Query Profiling

ㅁ 개요

 ㅇ mysql의 성능향상을 위한 구체적인 지표를 얻을 수 있는 방법을 찾고 있었다.

 ㅇ profiling을 통해 디비 프로세스의 다양한 성능지표를 확인하여 쿼리 성능을 확인할 수 있다.

 

 

ㅁ Mysql 버젼확인

 ㅇ 참고로 현재 테스트 중인 Mysql은 회사에서 사용 중인 AWS Aurora RDS와 동일한 5.7버젼에 맞추었다.

 

 

ㅁ SHOW PROFILE 이란?

 MySQL 에서 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인 할 수 있는 기능을 제공하며 쿼리 프로파일링(Query Profiling) 기능을 제공하고 있다. 쿼리 프로파일링(Query Profiling)는 MySQL 5.1 이상에서 부터 지원한다.

메모
SHOW PROFILE은 향후 MySQL 릴리스에서 제거될 것으로 예상되며, 대신 MySQL Performance Schema를 사용할 수 있다.

 

 

ㅁ profiling_history_size

 

# profiling_history_size 조회
show variables like '%profiling_history_size%';

# profiling_history_size 100으로 설정
set profiling_history_size=100;

 프로파일링 저장 사이즈는 profiling_history_size세션 변수에 의해 제어되며 기본값은 15이며, 최대값은 100이다. 값을 0으로 설정하면 프로파일링을 비활성화하는 실질적인 효과가 있다. Profiling은 세션별로 활성화 되고, 세션이 종료되면 프로파일링 정보가 손실된다. 

ㅁ SHOW PROFILE 사용법

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

 ㅇ SHOW PROFILE은 현재 세션이 진행되는 동안 실행된 SQL에 대한 리소스 사용량을 나태내는 프로파일링 정보를 표시한다.

 

 

ㅁ SHOW PROFILE 활성화

# Profile 활성화 확인
SELECT @@profiling;

  ㅇ 우선 설정 확인을 한다. 현재 0으로 설정이 되어 있지 않은 상태이다.

 

# profile 활성화
set profiling=1;

 ㅇ profile을 1로 설정하여 활성화 하였다.

 ㅇ 해당 기능은 deprecated 되어 이후 제거될 수 있음을 주의주고 있다.

 

 

ㅁ SHOW PROFILE 목록 보기 

# 테스트 조회
select * from TEST1;

# profile 목록조회
show profiles;

  IDEA에서 데이터베이스에 접근하고 있는 상황에서 테스트 조회를 하였지만, 툴에서 기본적으로 실행하는 쿼리 사항까지 확인 할 수 있었다. 테스트 조회 시 Query_ID 61 항목에서 확인 할 수 있으면 수행 시간이 0.00187025초 임을 알 수 있다.

 

 

SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 302 ORDER BY SEQ;

 ㅇ INFORMATION_SCHEMA.PROFILING을 이용하여 조회 조건을 추가하여 원하는 형태로 테이터를 가공할 수 있다.

 

 

ㅁ 프로파일링 실행

 ㅇ 테스트 조회 후 방금실행한 쿼리의 프로파일링을 확인 할 수 있다.

 

 

ㅁ 프로파일링 상세지표 확인

 ㅇ 현재 사용 중인 DataGrip에서 명령어 제안을 주고 있다.

 ㅇ TYPE에 따라 구체적인 지표를 상세하게 볼 수 있다.

 

 

ㅁ TYPE의 종류

TYPE	DESC
ALL	모든 정보
BLOCK IO	블록 입력 및 출력 작업에 대한 카운트
CONTEXT SWITCHES	자발적 및 비자발적 컨텍스트 전환 횟수
CPU	사용자 및 시스템 CPU 사용 시간
IPC	주고받은 메시지의 개수
MEMORY	사용되지 않고 있다.
PAGE FAULTS	메이저 및 마이너 페이지 폴트 수
SOURCE	함수가 발생한 파일의 이름 및 줄 번호와 함께 소스 코드의 함수 이름
SWAPS	스왑 카운트

 ㅇ ALL은 모든 지표를 한번에 볼 수 있다.

 ㅇ 개별 TYPE에 따라 어떤 지표를 볼 수 있는지 검색해 보았다.

 ㅇ MEMORY는 사용되지 않고 있어서 Status, Duration 외 추가적인 지표는 확인 할 수 없었다.

 

ㅁ ALL

 

 

ㅁ Block IO

 

 

ㅁ CPU

 

 

ㅁ Context Switches

 

 

ㅁ IPC

 

 

ㅁ MEMORY

 ㅇ MEMORY의 경우 사용하지 않기 때문에 다른 지표는 추가적으로 확인 할 수 없었다.

 

 

ㅁ PAGE FAULTS

 

 

ㅁ SOURCE

 

 

ㅁ SWAPS 

 

 

ㅁ 함께 보면 좋은 사이트 

 ㅇ SHOW PROFILE Statement

 

MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.30 SHOW PROFILE Statement

 ㅇ Query Profiling Using Performance Schema

MySQL :: MySQL 5.7 Reference Manual :: 25.19.1 Query Profiling Using Performance Schema