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 

innodb_flush_log_at_trx_commit

• INSERT, UPDATE, DELETE 쿼리로 변동되는 MySQL의 데이터베이스 상태는 일시적으로 메모리(innodb_buffer_pool)에 보관된다. 메모리에 보관된 데이터는 정전이나 운영체제 장애가 발생할 경우 유실될 우려가 있다. flush 행위가 발생해야만 비로소 디스크에 영구적으로 저장된다. MySQL은 기본 설정 상태에서 매 트랜잭션 커밋 시점마다 flush를 발생시켜 데이터베이스의 원자성을 보장한다. 반면 잦은 flush는 결국 I/O 블로킹으로 이어져 응답 속도를 늦추는 결과를 초래한다. 극단적인 퍼포먼스가 필요할 경우 이 값을 수정하여 응답 속도를 4배 이상 향상시킬 수 있다. 목적에 따라 innodb_flush_log_at_trx_commit 값을 아래와 같이 수정할 수 있다. 관련 링크

$ nano /etc/my.cnf [mysqld]
// 기본값으로 가장 안전하고 권장되는 설정, 매 트랜잭션 커밋 시점마다 flush 발생
innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 

// 매 초마다 flush 발생, 응답 속도는 증가하나 운영체제 장애나 정전시 초 단위로 데이터 유실 발생
innodb_flush_log_at_trx_commit = 2 

• 단일 노드가 아닌 갈레라 클러스터 구성의 경우 동시에 모든 노드의 하드웨어가 장애로 중단되는 경우는 극히 드물기 때문에 innodb_flush_log_at_trx_commit = 2 옵션을 안전하게 사용할 수 있다. 관련 링크

performance_schema

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

 

• performance_schema 기능을 활성화하면 모니터링으로 인한 오버헤드로 전체 성능이 1~3% 하락한다. [관련 링크] 기능이 제공하는 이점을 생각하면 충분히 감수할만한 성능 차이라고 판단된다. MariaDB는 10.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
FROM   performance_schema.events_statements_summary_by_digest
ORDER  BY sum_timer_wait DESC;

SHOW GLOBAL STATUS

• SHOW GLOBAL STATUS 명령을 사용하면 현재 데이터베이스의 상태를 알 수 있는 다양한 정보를 조회할 수 있다. 그 중에는 현재 상태를 의미하는 것도 있고 누적된 수치를 제공하는 것도 있다. 이 정보를 적절히 활용하면 원격지에서 데이터베이스의 상황을 관찰할 수 있다.

// 현재 상태 조회 > SHOW GLOBAL STATUS;  Memory_used = 577656392 // 현재 사용 중인 메모리 bytes Max_used_connections = 306 // flush 이후 최대 동시 접속 수  // 상태를 초기화, RELOAD 권한 필요 > FLUSH STATUS; 

• 한편 SHOW GLOBAL STATUS LIKE 'wsrep_%'는 갈레라 클러스터의 현재 정보를 확인할 수 있는 정보만 추려서 확인할 수 있다. 관련 링크 제공되는 주요 정보는 아래와 같다.

# 갈레라 클러스터 정보를 조회
> SHOW GLOBAL STATUS LIKE 'wsrep_%';
wsrep_cluster_state_uuid = '2eebce42-dbf0-11e7-8263-b34f5d2d9960'
# 클러스터의 현재 상태
UUID wsrep_local_state_uuid = '2eebce42-dbf0-11e7-8263-b34f5d2d9960'
# 노드의 현재 상태 UUID, 모든 노드가 wsrep_cluster_state_uuid와 동일해야 정상
wsrep_cluster_size = 3
# 클러스터를 구성하는 노드 수
wsrep_ready = 'ON'
# 현재 노드의 클러스터 작동 여부 [ON, OFF]
wsrep_connected = 'ON'
# 현재 노드의 클러스터 동기화 여부
wsrep_local_state_comment = 'Synced'

현재 연결된 IP 주소 목록 확인

• INFORMATION_SCHEMA.PROCESSLIST 테이블의 쿼리를 가공하면 현재 연결되 IP 주소 목록을 확인할 수 있다.

SELECT 
  tmp.ipAddress, 
  COUNT(*) AS numConnections, 
  FLOOR(
    AVG(tmp.time)
  ) AS timeAVG, 
  MAX(tmp.time) AS timeMAX 
FROM 
  (
    SELECT 
      pl.id, 
      pl.user, 
      pl.host, 
      pl.db, 
      pl.command, 
      pl.time, 
      pl.state, 
      pl.info, 
      LEFT(
        pl.host, 
        (
          LOCATE(':', pl.host) -1
        )
      ) AS ipAddress 
    FROM 
      INFORMATION_SCHEMA.PROCESSLIST pl
  ) AS tmp 
GROUP BY 
  tmp.ipAddress 
ORDER BY 
  numConnections DESC;

참고 글

• How to Design Indexes, Really by Bill Karwin
• MySQL: Building the best INDEX for a given SELECT
• Rick’s RoTs — Rules of Thumb for MySQL
• MySQL Performance Schema Statement Digests
• Monitoring Processes with Performance Schema in MySQL 5.6
• What SQL is running in MySQL
• MySQL query digest with Performance Schema
• MySQL 5.7 Performance Tuning Immediately After Installation
• Innodb Performance Optimization Basics