Aurora MySQL-Compatible DB 클러스터에서 SELECT 문이 느리게 실행되는 문제를 해결하려면 어떻게 해야 합니까?

간략한 설명

다음과 같은 이유로 Aurora MySQL-Compatible DB 클러스터에서 SELECT 문이 느리게 실행될 수 있습니다.

• 데이터베이스 시스템 리소스가 과도하게 사용되고 있습니다.
• 데이터베이스가 잠기고 있습니다.
• SELECT 문이 대규모 테이블의 전체 테이블 스캔을 수행합니다. 또는 쿼리에 필요한 인덱스가 없습니다.
• 장기 실행 트랜잭션으로 인해 InnoDB 기록 목록 길이(HLL)가 늘어나고 있습니다.

해결 방법

참고: AWS Command Line Interface(AWS CLI) 명령을 실행할 때 오류가 발생하면 AWS CLI의 오류 해결을 참조하십시오. 또한 최신 AWS CLI 버전을 사용하고 있는지 확인하십시오.

CloudWatch Database Insights를 사용하여 SELECT 문이 느린 이유를 확인하십시오.

성능 개선 도우미를 활성화하고 Database Insights를 사용하여 높은 DB 로드를 유발하는 쿼리를 탐지합니다. 데이터베이스 로드 차트에서 SQL, 사용자 및 대기와 같은 차원을 모니터링합니다. 데이터베이스 원격 분석 탭의 느린 SQL 쿼리 섹션도 모니터링합니다. 또한 성능 개선 도우미의 SQL 통계에서 쿼리 실행을 분석할 수 있습니다. 예를 들어 호출당 검사한 행 수가 비정상적으로 많으면 실행 계획이 비효율적입니다.

지표를 사용하여 DB 인스턴스 시스템 리소스 모니터링

높은 CPU, 낮은 메모리 또는 DB 인스턴스 클래스의 용량을 초과하는 대용량 워크로드로 인해 SELECT 문이 느리게 실행될 수 있습니다. DB 인스턴스 리소스를 모니터링하려면 다음 도구를 사용하십시오.

• CPU 사용률을 모니터링하려면 Amazon Aurora용 Amazon CloudWatch 지표를 사용합니다.
• 운영 체제(OS) 지표의 세부 정보를 보려면 향상된 모니터링을 사용합니다.
• DB 로드가 최대 vCPU를 초과하는지 확인하려면 데이터베이스 원격 분석 탭의 OS 프로세스 데이터를 사용합니다.

디스크 검색으로 인해 SELECT 문이 느리게 실행될 수 있습니다. 데이터베이스 엔진은 디스크 I/O를 최소화하기 위해 디스크에서 읽은 블록을 캐시합니다. 데이터베이스에 동일한 데이터가 필요한 경우 디스크가 아닌 메모리에서 데이터를 가져옵니다. 특정 쿼리가 디스크와 메모리 중 어디에서 처리는지 확인하려면 다음 지표를 사용합니다.

• 디스크 I/O 작업 수를 보려면 ReadIOPS 지표를 확인합니다. 이 값은 가능한 한 낮게 유지하는 것이 가장 좋습니다.
• 버퍼 캐시가 처리하는 요청 비율을 보려면 BufferCacheHitRatio 지표를 확인합니다. 이 값은 가능한 한 높게 유지하는 것이 가장 좋습니다.
• DB 인스턴스의 사용 가능한 메모리를 보려면 FreeableMemory 지표를 확인합니다. 이 값은 안정적으로 유지하는 것이 가장 좋습니다. 사용 가능한 메모리가 부족하면 BufferCacheHitRatio가 낮아지고 ReadIOPS가 높아질 수 있습니다.
  참고: BufferCacheHitRatio 지표가 떨어지고 SELECT 문이 느리면 엔진이 기본 볼륨에서 쿼리를 처리합니다.

로컬 스토리지의 디스크 검색으로 인해 SELECT 문이 느리게 실행될 수도 있습니다. Aurora MySQL-Compatible은 수동 및 내부 임시 테이블에 로컬 스토리지를 사용합니다. 자세한 내용은 Aurora MySQL 버전 3의 새로운 임시 테이블 동작을 참조하십시오. 로컬 스토리지의 디스크 검색을 모니터링하고 해결하려면 Database Insights의 Rdstemp OS 지표를 확인하십시오. 이러한 값은 가능한 한 낮게 유지하는 것이 가장 좋습니다.

네트워크가 포화 상태가 되면 SELECT 문이 느리게 실행될 수 있습니다. Aurora는 네트워크를 통해 클러스터 볼륨에 대한 I/O 작업을 실행하고 네트워크를 통해 쿼리 결과를 클라이언트에 보냅니다. 네트워크 포화도를 모니터링하고 해결하려면 NetworkThroughput 및 StorageNetworkThroughput 지표를 확인하십시오. 총 네트워크 처리량은 DB 인스턴스의 네트워크 대역폭보다 작아야 합니다.

워크로드로 인해 리소스 사용량이 DB 인스턴스 유형의 용량을 초과할 경우 DB 인스턴스 클래스를 업그레이드하십시오.

교착 상태 및 차단 잠금 식별

교착 상태는 둘 이상의 트랜잭션이 서로를 차단하여 계속할 수 없을 때 발생합니다. 데이터베이스의 교착 상태를 식별하려면 파라미터 그룹에서 innodb_print_all_deadlocks 파라미터를 활성화하십시오. 자세한 내용을 보려면, MySQL 웹 사이트에서 innodb_print_all_deadlocks를 참조하십시오. 그런 다음 Amazon RDS 콘솔, AWS Command Line Interface 또는 API에서 mysql-error.log를 모니터링합니다.

(선택 사항) 교착 상태를 식별하려면 MySQL 관리자 계정에 로그인한 후 다음 명령을 실행합니다.

SHOW ENGINE INNODB STATUS\G;

참고: MySQL Workbench의 예상 출력에서 최근 감지된 교착 상태 섹션을 확인합니다.

교착 상태가 없더라도 잠금을 유지하는 긴 트랜잭션 하나로 인해 잠금이 차단되고 있는 것일 수 있습니다. 지속적인 잠금 차단을 식별하려면 다른 활성 세션이 없는데 Amazon RDS for MySQL DB 인스턴스에 대한 쿼리가 차단된 이유는 무엇입니까?를 참조하십시오.

쿼리에서 인덱스를 사용하는지 확인

인덱스가 없거나 전체 테이블 스캔을 수행하는 쿼리는 느리게 실행됩니다. 인덱스를 사용하면 SELECT 문을 더 빠르게 실행할 수 있습니다. 쿼리에서 인덱스를 사용하는지 확인하려면 EXPLAIN 문을 사용합니다. 자세한 내용은 MySQL 웹 사이트에서 EXPLAIN 문을 참조하십시오.

EXPLAIN 출력에서 엔진이 쿼리 중에 스캔하는 테이블 이름, 키 및 행 수를 확인합니다. 출력에 사용 중인 키가 표시되지 않으면 WHERE 절의 열에 인덱스를 만듭니다. 테이블에 필요한 인덱싱이 있는 경우 통계 테이블이 최신 상태인지 확인하십시오. 통계를 업데이트하려면 ANALYZE 절을 사용하십시오. 자세한 내용은 MySQL 웹 사이트의 INFORMATION_SCHEMA STATISTICS 테이블을 참조하십시오.

느린 SELECT 문을 식별하려면 slow_query_log를 사용하십시오. 느린 쿼리를 기록하려면 DB 클러스터의 느린 쿼리 로깅을 활성화하십시오.

HLL 확인

InnoDB는 다중 버전 동시성 제어(MVCC)를 사용합니다. MVCC는 읽기 일관성 유지를 위해 동일한 레코드에 대해 여러 개의 복사본을 유지합니다. HLL은 기록 목록의 수정 사항이 포함된 실행 취소 로그의 총 수입니다. 데이터를 쓰거나 읽는 장기 실행 트랜잭션이 있는 경우 트랜잭션이 완료되거나 롤백될 때까지 HLL이 증가합니다. 다른 트랜잭션은 장기 실행 트랜잭션에서 사용하는 테이블을 수정합니다. 워크로드에 여러 개의 공개 또는 장기 실행 트랜잭션이 필요한 경우 데이터베이스의 HLL이 높을 수 있습니다. 또한 제거 스레드가 DB 변경 사항을 따라가지 못하면 HLL이 높을 수 있습니다. HLL이 높으면 리소스 사용량이 많아지며 SELECT 문 성능이 느려지고 일관성이 떨어지며 스토리지가 늘어납니다. 극단적인 경우 HLL이 높아서 데이터베이스가 중단될 수 있습니다.

HLL을 모니터링하려면 라이터 인스턴스의 RollbackSegmentHistoryListLength 지표를 사용하십시오. 또는 다음 명령을 실행합니다.

SHOW ENGINE INNODB STATUS;

출력 예시:

------------ TRANSACTIONS ------------Trx id counter 26368570695  
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Aurora MySQL-Compatible의 경우 HLL이 클러스터 수준에 있습니다. 클러스터 수준에서 HLL을 확인하려면 라이터 인스턴스에 연결하고 다음 문을 실행합니다.

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec, oldest_read_view_trx_id, oldest_read_view_lsn from mysql.ro_replica_status;

참고: 이전 문을 사용하여 리더 노드와 라이터 노드 간의 복제 지연을 식별할 수 있습니다. DB가 스토리지에서 읽을 때 사용하는 가장 오래된 로그 시퀀스 번호(LSN)와 DB 인스턴스의 가장 오래된 읽기 뷰 트랜잭션 ID(Trx ID)를 확인합니다. 인스턴스 중 하나에 이전 읽기 뷰가 포함되어 있는지 확인합니다.

이전 읽기 뷰가 포함된 인스턴스에 연결하려면 다음 문을 실행합니다.

SELECT a.trx_id, a.trx_state, a.trx_started,  TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open",    
a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state    
from information_schema.innodb_trx a, information_schema.processlist b    
where a.trx_mysql_thread_id=b.id    
order by trx_started;

참고: 이전 문을 사용하여 가장 오래된 trx_id가 있는 세션 또는 트랜잭션을 식별하십시오. 제거 작업의 차단을 해제하려면 세션을 종료할 수 있는지 여부를 결정하십시오.

높은 HLL을 해결하려면 다음 조치를 취하십시오.

• DML 쓰기로 인해 HLL이 증가할 경우 트랜잭션을 롤백하여 쿼리를 취소하십시오. 롤백해야 하는 업데이트 수가 많기 때문에 이 프로세스는 시간이 오래 걸립니다.
• READ로 인해 HLL이 증가하면 mysql.rds_kill_query를 사용하여 쿼리를 취소하십시오.
  참고: 데이터베이스 관리자에게 연락하여 쿼리를 취소할 수 있는지 확인하십시오.

높은 HLL을 방지하려면 데이터를 소규모 배치로 커밋하는 것이 가장 좋습니다. 또한 DB 클러스터 또는 인스턴스를 재부팅하지 마십시오. 버퍼 풀의 메모리 데이터에 액세스할 수 있을 때 HLL을 제거하십시오. 데이터베이스를 재부팅하면 남아 있는 페이지 캐시가 손실될 수 있습니다. 남아 있는 페이지 캐시가 손실된 경우 클러스터 볼륨의 데이터 페이지를 읽어서 HLL을 제거해야 합니다. 이 프로세스는 메모리 제거보다 속도가 느리며 추가 I/O 청구 비용이 발생합니다.

관련 정보

Amazon CloudWatch를 사용하여 Amazon Aurora MySQL, Amazon RDS for MySQL 및 MariaDB 로그 모니터링

Aurora MySQL 튜닝