슬로우 쿼리 원인 분석 및 해결
- 데이터베이스의 슬로우 쿼리를 유발하는 요소는 많다. 형사가 단서를 잡아 하나씩 수사망을 좁혀가듯이 원인을 분석해가야 한다. 가장 먼저 선행되어야할, 그리고 가장 많이 사용해야 하는 것은 EXPLAIN(실행 계획) 또는 EXPLAIN FORMAT=JSON(전자보다 제공하는 정보가 더 많음) 문을 사용하여 해당 슬로우 쿼리의 인덱스 사용 정보를 확인하는 것이다. 아래와 같이 모든 쿼리에 사용이 가능하다.
EXPLAIN FORMAT=JSON
SELECT *
FROM USER
- 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이 충분한지는 아래와 같이 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 옵션을 안전하게 사용할 수 있다. 관련 링크
- 비활성화된 기능을 활성화하려면 아래와 같이 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;
참고 글