슬로우 쿼리 원인 분석 및 해결
- 데이터베이스의 슬로우 쿼리를 유발하는 요소는 많다. 형사가 단서를 잡아 하나씩 수사망을 좁혀가듯이 원인을 분석해가야 한다. 가장 먼저 선행되어야할, 그리고 가장 많이 사용해야 하는 것은
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 스토리지 엔진에만 해당한다.) 해당 파라메터의 크기가 클수록 쿼리 실행시 디스크보다 메모리를 사용하게 되어 빠른 결과를 얻을 수 있다.
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_%';
innodb_buffer_pool_size = 268435456
SET GLOBAL innodb_buffer_pool_size = 8589934592;
$ 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 파일을 수정하고 서비스를 재시작해야 한다.
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의 축약 명령)의 경우 실행시 일시적인 뮤텍스 락이 발생하는 단점이 있다.)
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
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'
wsrep_cluster_size = 3
wsrep_ready = 'ON'
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;
