MySQL/MariaDB, 슬로우 쿼리 원인 분석 및 성능 튜닝하기

슬로우 쿼리 원인 분석 및 해결

  • 데이터베이스의 슬로우 쿼리를 유발하는 요소는 많다. 형사가 단서를 잡아 하나씩 수사망을 좁혀가듯이 원인을 분석해가야 한다. 가장 먼저 선행되어야할, 그리고 가장 많이 사용해야 하는 것은 EXPLAIN(실행 계획) 또는 EXPLAIN FORMAT=JSON(전자보다 제공하는 정보가 더 많음) 문을 사용하여 해당 슬로우 쿼리의 인덱스 사용 정보를 확인하는 것이다. 아래와 같이 모든 쿼리에 사용이 가능하다.
EXPLAIN FORMAT=JSON
SELECT *
  FROM USER
  • 잘못된 인덱스 설계는 풀 테이블 스캔을 유발하여 슬로우 쿼리의 주범이 된다. 앞서 언급한 EXPLAIN 문으로 분석해보면 인덱스 사용 여부를 자세하게 확인할 수 있다. 쿼리에 명시된 WHERE, JOIN, ORDER BY 순서로 생성된 인덱스 만이 쿼리 실행에 사용된다. 싱글 컬럼일 경우 최소 해당 컬럼에 대한 싱글 컬럼 인덱스가 생성되어 있거나 멀티 컬럼 인덱스의 첫번째 컬럼 조건에 맞아야 정상적으로 인덱스를 사용하게 된다. ORDEY BY의 경우는 컬럼 순서 뿐만 아니라 쿼리에 명시된 ASC, DESC 또한 인덱스에도 동일하게 적용되어야 한다. (MySQL 권위자인 빌 카윈은 인덱스를 전화번호부에 비유했다. 사람의 성으로 대분류가 되어 있고 성마다 이름이 오름차순으로 정렬되어 있는 전화번호부에서는 쿼리 조건의 첫 컬럼에 성이 오지 않으면 인덱스를 사용할 수 없게 된다. [관련 링크])

  • MYSQL은 인덱스 생성시 컬럼의 정렬 순서로 ASC만을 지원한다. 인덱스 생성 사양에는 컬럼의 정렬 순서(ASC, DESC)를 명시할 수 있게 되어 있지만 실제로 DESC는 무시되고 ASC로만 인덱스를 생성한다. 즉, ORDER BY에 명시된 멀티 컬럼에 ASC, DESC가 혼재되어 있다면 인덱스는 무시된다.

innodb_buffer_pool_size

  • 인덱스 설계가 잘 되어 있는데도 슬로우 쿼리가 해결되지 않는다면? innodb_buffer_pool_size 파라메터를 의심해봐야 한다. (이름이 의미하듯이 InnoDB 스토리지 엔진에만 해당한다.) 해당 파라메터의 크기가 클수록 쿼리 실행시 디스크보다 메모리를 사용하게 되어 빠른 결과를 얻을 수 있다.
// 현재 설정된 innodb_buffer_pool_size 값 확인, 바이트 단위로 출력
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_%';
innodb_buffer_pool_size = 268435456

// innodb_buffer_pool_size 값 설정, MySQL 5.7.5 이상 및 SUPER 권한 필요
SET GLOBAL innodb_buffer_pool_size = 8589934592;

// innodb_buffer_pool_size 값 설정, MySQL 5.7.5 미만, 서비스 재시작 필요
// innodb_buffer_pool_instances는 설정된 innodb_buffer_pool_size를 쪼개어 병렬로 제어할 쓰레드의 개수, 각 인스턴스의 크기가 1GB 이상일 경우에만 작동
$ nano /etc/my.cnf
innodb_buffer_pool_size = 8589934592
innodb_buffer_pool_instances = 8
  • innodb_buffer_pool_size는 무조건 높게 잡는다고 능사가 아니다. 시스템 전체 메모리의 80% 수준으로 설정한다. (오직 데이터베이스만 설치되어 운용되는 시스템에 한해서다. 여러 프로세스가 실행 중인 복합적인 구성의 서버라면 값 설정에 있어 신중해야 한다.) [관련 링크]

  • 현재 설정된 innodb_buffer_pool_size이 충분한지는 아래와 같이 information_schema.TABLES 테이블에서 현재 사용량을 조회하여 확인할 수 있다.
SELECT engine,
       count(*) AS table_count,
       concat(round(sum(table_rows)/1000000,2),'M') AS table_rows,
       concat(round(sum(data_length)/(1024*1024*1024),2),'G') AS total_data,
       concat(round(sum(index_length)/(1024*1024*1024),2),'G') AS total_index,
       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') AS total_size,
       round(sum(index_length)/sum(data_length),2) AS index_frac
  FROM information_schema.TABLES
 GROUP BY engine
 ORDER BY sum(data_length+index_length) DESC

performance_schema

  • EXPLAIN은 쿼리 튜닝에 있어 훌륭한 도구이지만 데이터베이스의 전체적인 상황을 확인하고 원인을 분석하기에는 부족한다. MySQL5.5 이상부터 performance_schema라는 막강한 서버 모니터링 기능을 제공한다.

  • performance_schema 기능을 활성화하면 모니터링으로 인한 오버헤드로 전체 성능이 1~3% 하락한다. [관련 링크] 기능이 제공하는 이점을 생각하면 충분히 감수할만한 성능 차이라고 판단된다. MariaDB10.0.12 이상부터 기본값으로 비활성화되어 있다.

  • 비활성화된 기능을 활성화하려면 아래와 같이 my.cnf 파일을 수정하고 서비스를 재시작해야 한다.
// performance_schema 활성화 여부 확인
SHOW GLOBAL VARIABLES LIKE 'performance_schema'

// 기능 활성화 후 서비스 재시작
$ nano /etc/my.cnf
performance_schema = 1

// 특정 사용자에게 권한 부여
GRANT ALL ON performance_schema.* TO '{user_id}'@'{ip_address}' IDENTIFIED BY '{user_password}'
  • performance_schema 기능이 활성화되면 동일한 이름의 데이터베이스를 조회할 수 있다. 다양한 테이블을 제공하는데 대표적으로 현재 연결된 클라이언트 상태라던가, 슬로우 쿼리를 포함한 전체 쿼리 패턴 정보 등을 확인할 수 있다. 특히 performance_schema.threads 테이블은 운영 환경에 전혀 영향을 주지 않는다. (비슷한 정보를 제공하는 SHOW PROCESSLIST(SELECT * FROM information_schema.PROCESSLIST의 축약 명령)의 경우 실행시 일시적인 뮤텍스 락이 발생하는 단점이 있다.)
// performance_schema 데이터베이스 사용
USE performance_schema

// 현재 쓰레드(연결) 개수 확인
SELECT * FROM performance_schema.threads

// 현재까지 누적된 쿼리 패턴 통계 확인
SELECT * FROM performance_schema.events_statements_summary_by_digest

// 현재까지 누적된 쿼리 패턴 통계 초기화
TRUNCATE performance_schema.events_statements_summary_by_digest

// 현재 실행 중인 쿼리 확인
SELECT * FROM performance_schema.events_statements_current

// 최근 실행된 쿼리 이력 기능 활성화
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history'
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history_long'

// 최근 실행된 쿼리 이력 확인
SELECT * FROM performance_schema.events_statements_history

// 최근 실행된 10,000개 쿼리 이력 확인
SELECT * FROM performance_schema.events_statements_history_long
  • 제공되는 정보를 적절히 가공하여 아래와 같이 모든 실행 쿼리 패턴 단위의 시간 통계 및 풀 테이블 스캔 여부를 확인할 수도 있다. 소요 시간과 관계된 컬럼들은 SEC_TO_TIME({COLUMN}/1000000000000)를 사용하면 한 눈에 확인이 가능하게 출력해준다.
SELECT DIGEST_TEXT AS query,
       IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
       COUNT_STAR AS exec_count,
       SUM_ERRORS AS err_count,
       SUM_WARNINGS AS warn_count,
       SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) AS exec_time_total,
       SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000) AS exec_time_max,
       SEC_TO_TIME(AVG_TIMER_WAIT/1000000000000) AS exec_time_avg_ms,
       SUM_ROWS_SENT AS rows_sent,
       ROUND(SUM_ROWS_SENT / COUNT_STAR) AS rows_sent_avg,
       SUM_ROWS_EXAMINED AS rows_scanned,
       DIGEST AS digest
 WHERE performance_schema.events_statements_summary_by_digest
 ORDER BY SUM_TIMER_WAIT DESC

참고 글

저작자 표시 비영리 동일 조건 변경 허락
신고