Amazon Aurora MySQL DB 클러스터에서 느린 SELECT 쿼리가 실행되는 이유는 무엇입니까?

최종 업데이트 날짜: 2020년 12월 10일 

MySQL DB 클러스터용 Amazon Aurora를 SELECT 쿼리를 사용하여 데이터베이스에서 데이터를 선택하고 싶습니다. DB 클러스터에서 SELECT 쿼리를 실행하면 쿼리가 느립니다. SELECT 쿼리 실행 속도가 저하된 원인을 분석하고 이를 수정하려면 어떻게 해야 합니까?

간략한 설명

MySQL용 Amazon Aurora DB 클러스터에서 SELECT 쿼리 실행 속도가 느린 이유는 여러 가지가 있습니다.

  • Amazon Relational Database Service(Amazon RDS) 시스템 리소스가 과도하게 활용됩니다. 이는 CPU, 메모리 부족 또는 DB 인스턴스 유형에서 워크로드 처리 가능 용량이 초과하는 경우 발생할 수 있습니다.
  • 데이터베이스가 잠금 상태이고 결과 대기 이벤트로 인해 SELECT 쿼리가 제대로 수행되지 않습니다.
  • SELECT 쿼리가 용량이 테이블에서 전체 테이블 스캔을 수행하거나 쿼리에 필요한 인덱스가 없습니다.
  • InnoDB 기록 목록 길이(HLL)가 장기 실행 트랜잭션으로 인해 크게 증가했습니다.

해결 방법

지표를 사용하여 Amazon RDS 시스템 리소스 모니터링

Amazon Aurora 클러스터에서 CPU 사용률과 가용 메모리를 항상 모니터링해야 합니다. CPU 스파이크가 정상이지만 오랜 기간 동안 지속적으로 높은 CPU가 사용되면 SELECT 쿼리 실행 속도가 저하될 수 있습니다. 다음 도구를 사용하여 CPU를 사용하는 방법과 위치를 결정하세요.

  1. Amazon CloudWatch 지표는 CPU 사용률을 모니터링하는 가장 쉬운 방법입니다. Amazon Aurora에 사용할 수 있는 지표에 대한 자세한 내용은 Amazon Aurora DB 클러스터 지표 모니터링을 참조하세요.
  2. Enhanced monitoring을 통해 더 낮은 세부 단위로 OS 수준 지표를 자세히 살펴볼 수 있습니다. 프로세스가 CPU를 사용하는 방법에 대한 자세한 분석을 제공합니다.
  3. 성능 개선 도우미는 DB 부하를 정확하게 결정합니다. DB 인스턴스에 대한 성능 개선 도우미를 활성화한 다음 로드가 최대 vCPU를 초과하는지 확인합니다. 대기별로 부하 베어링 쿼리 및 SQL을 모니터링하고 최대 대기를 발생시키는 사용자를 식별할 수도 있습니다.

SELECT 쿼리는 디스크 탐색으로 인해 실행 속도가 저하될 수 있습니다. 디스크 I/O를 최소화하기 위해 데이터베이스 엔진은 디스크에서 읽은 블록을 캐시하려고 합니다. 즉, 다음에 데이터베이스가 동일한 데이터 블록을 필요로 할 때 디스크로 이동하지 않고 메모리에서 가져올 수 있습니다.

아래 지표를 사용하여 디스크 또는 메모리에서 특정 쿼리를 제공하는지 확인하세요.

  • VolumeReadsIOps: 이 지표는 청구된 볼륨 수준 [디스크] 읽기 작업의 수이며 가능한 한 낮아야 합니다.

  • BufferCacheHitRatio: 이 측정 단위는 버퍼 캐시가 제공하는 요청의 백분율이며 가능한 한 높아야 합니다. BufferCacheHitRatio가 삭제되고 SELECT 문 속도가 저하된 경우 기본 볼륨에서 쿼리를 처리하는 것입니다.

SELECT 문 속도 저하를 식별하는 또 다른 중요한 리소스는 느린 쿼리 로그입니다. DB 클러스터에 대해 느린 쿼리 로깅을 활성화하여 이러한 쿼리를 기록하고 나중에 조치를 취할 수 있습니다. MySQL 5.6 호환 버전을 사용하는 경우 MySQL 성능 스키마를 사용하여 쿼리 성능을 지속적으로 모니터링합니다.

교착 상태 및 대기 이벤트 식별

Amazon RDS는 특정 시간에 하나의 사용자 세션만 행을 쓰거나 업데이트할 수 있도록 데이터베이스의 데이터를 잠급니다. 이 행이 필요한 다른 트랜잭션은 보류 상태로 유지됩니다. 공유 잠금에서는 읽기 트랜잭션이 데이터를 읽는 동안 쓰기/업데이트 트랜잭션이 보류 상태로 유지됩니다. 쿼리가 다른 쿼리에 의해 잠긴 행에 액세스하기를 기다리는 경우 교착 상태가 발생할 수 있습니다.

데이터베이스에서 교착 상태를 식별하려면 파라미터 그룹에서 innodb_print_all_deadlocks 파라미터를 활성화합니다.

그런 다음 교착 상태를 식별 하려면 최신 교착 상태 섹션에서 이 명령을 실행 합니다.

SHOW ENGINE INNODB STATUS\G;

참고: 리더는 기본적으로 ON으로 설정된 MySQL innodb_read_only 파라미터로 구성되어 있으므로 Aurora Reader에서 이 명령을 실행할 수 없습니다. Aurora MySQL이 클러스터 유형에 따라 DB 인스턴스의 읽기 전용 및 읽기/쓰기 상태를 관리하므로 DB 인스턴스에 대해 이 파라미터를 수정할 수 없습니다.

INFORMATION_SCHEMA INNODB_TRX 테이블을 조회할 수도 있습니다. 이를 통해 대기를 포함하여 INNODB에서 실행 중인 모든 트랜잭션에 대한 정확한 세부 정보를 얻을 수 있습니다.

쿼리가 인덱스를 사용하고 있는지 확인

쿼리에 인덱스가 없거나 전체 테이블 스캔을 수행하는 경우 일반적으로 속도가 느려집니다. 인덱스는 SELECT 쿼리 속도를 높이는 데 도움이 됩니다.

쿼리가 인덱스를 사용하고 있는지 확인하려면 EXPLAIN 쿼리를 사용합니다. 이는 느린 쿼리 문제를 해결하는 데 유용한 도구입니다. EXPLAIN 출력에서 테이블 이름, 사용된 키 및 쿼리 중에 스캔된 행 수를 확인합니다. 출력에 사용 중인 키가 표시되지 않으면 WHERE 절에 사용된 열에 인덱스를 만듭니다.

테이블에 인덱싱이 필요한 경우 테이블 통계가 최신 상태인지 확인합니다. 통계가 정확한지 확인하는 것은 쿼리 최적화 프로그램이 올바른 카디널리티를 가진 가장 선택적 인덱스를 사용한다는 것을 의미합니다. 이렇게 하면 쿼리 성능이 향상됩니다.

기록 목록 길이(HLL) 확인

InnoDB는 다중 버전 동시성 제어(MVCC)라는 개념을 사용합니다. MVCC는 읽기 일관성을 유지하기 위해 동일한 레코드의 여러 복사본을 유지합니다. 즉, 트랜잭션을 커밋한 후에 InnoDB가 이전 복사본을 제거합니다. 그러나 트랜잭션이 실행 취소 세그먼트의 증가로 인해 오랜 시간 동안 커밋되지 않으면 기록 목록 길이(HLL)가 증가합니다. InnoDB 기록 목록 길이는 플러시되지 않은 변경 횟수를 나타냅니다.

워크로드에 장기 실행 또는 열린 트랜잭션이 많이 필요한 경우 데이터베이스에서 높은 HLL을 기대할 수 있습니다.

참고: 장기 실행 트랜잭션이 HLL 스파이크의 유일한 원인은 아닙니다. 퍼지 스레드가 DB의 변경 사항을 따라잡을 수 없는 경우에도 HLL은 높게 유지될 수 있습니다.

HLL의 크기를 모니터링하지 않으면 시간 경과에 따른 성능 회귀, 리소스 소비량 증가, 느리고 일관성이 없는 SELECT 문 성능 및 스토리지 사용량 증가가 발생합니다. 최악의 경우 이로 인해 데이터베이스 중단이 발생할 수 있습니다.

기록 목록 길이를 확인하려면 다음 명령을 실행합니다.

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의 경우 공유 스토리지의 볼륨 특성으로 인해 기록 목록 길이는 개별 인스턴스 수준이 아니라 클러스터 수준입니다. 라이터에 연결하고 다음 쿼리를 실행합니다.

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 인스턴스의 가장 오래된 읽기 뷰 TRX ID에 대해서도 자세히 설명합니다. 이 정보를 사용하여 리더 중 하나가 이전 읽기 뷰를 보유하고 있는지 확인합니다(라이터의 엔진 InnoDB 상태와 비교).

참고: Aurora MySQL 1.19 및 2.06부터 CloudWatch의 RollbackSegmentHistoryListLength 지표를 사용하여 HLL을 모니터링할 수 있습니다. 또는, 이전 버전에서는 trx_rseg_history_len을 사용하여 다음 명령으로 HLL을 확인합니다.

select NAME AS RollbackSegmentHistoryListLength, 
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

HLL 증가 문제를 해결하려면 다음 방법을 사용하세요.

  • DML(쓰기)로 인해 HLL 증가가 발생하는 경우: 이 문을 취소하거나 종료하면 중단된 트랜잭션의 롤백이 포함됩니다. 이 시점까지 수행된 모든 업데이트가 롤백되기 때문에 상당한 시간이 걸립니다.

  • READ로 인해 HLL 증가가 발생하는 경우: mysql.rds_kill_query를 사용하여 쿼리를종료합니다.

  • 쿼리가 실행되는 시간에 따라 DBA와 함께 작업하여 저장된 프로시저를 사용하여 쿼리를 종료할 수 있는지 확인합니다.

증가를 방지하는 가장 좋은 방법은 위의 방법을 사용하여 HLL을 모니터링하고 데이터베이스에서 장기 실행 또는 열린 트랜잭션을 방지하는 것입니다. 또한 데이터를 더 작은 배치로 커밋하는 것이 좋습니다.

중요: DB 클러스터 또는 인스턴스를 재부팅하지 마세요. 버퍼 풀의 데이터에 액세스할 수 있는 경우 HLL을 제거하는 것이 더 효율적입니다. 데이터베이스를 재부팅하면 정리하기 위해 디스크에서 모든 데이터를 읽습니다.


이 문서가 도움이 되었나요?


결제 또는 기술 지원이 필요합니까?