Amazon Aurora MySQL DB 클러스터에서 SELECT 쿼리가 느리게 실행되는 이유는 무엇인가요?

6분 분량

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

간략한 설명

SELECT 쿼리가 Aurora MySQL 호환 DB 클러스터에서 느리게 실행되는 데에는 몇 가지 이유가 있습니다.

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

해결 방법

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

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

1. Amazon CloudWatch 지표는 CPU 사용률을 모니터링하는 가장 쉬운 방법입니다. Aurora에 사용할 수 있는 지표에 대한 자세한 내용은 Aurora의 CloudWatch 지표를 참조하세요.

2. 향상된 모니터링을 통해 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 파라미터를 활성화합니다. 그런 다음 RDS 콘솔/CLI/API에서 mysql-error.log를 모니터링합니다.

또는 관리자 계정으로 MySQL에 로그인하고 다음 명령을 실행하여 가장 최근에 감지된 교착 상태 섹션의 명령 출력에서 교착 상태를 식별합니다.

mysql> SHOW ENGINE INNODB STATUS\G;

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

쿼리에 인덱스가 없거나 전체 테이블 스캔을 수행하면 쿼리가 더 느리게 실행됩니다. 인덱스는 SELECT 쿼리 속도를 높이는 데 도움이 됩니다.

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

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

기록 목록 길이(HLL) 확인

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

워크로드에 여러 열린 트랜잭션이나 장기 실행 트랜잭션이 필요한 경우 데이터베이스에서 HLL이 높게 표시될 것으로 예상할 수 있습니다.

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

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';

Aurora MySQL 인스턴스에 대해 성능 개선 도우미가 활성화된 경우 RollbackSegmentHistoryListLength를 확인할 수 있습니다. 작성자 성능 개선 도우미로 이동하여 다음을 수행하세요.

1. Manage metrics(지표 관리)를 선택한 다음 Database metrics(데이터베이스 지표)를 선택합니다.

2. trx_rseg_history_len 지표를 선택한 다음 Update graph(그래프 업데이트)를 선택합니다.

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

DML(쓰기)로 인해 HLL 증가가 발생하는 경우: 이 문을 취소하거나 종료하면 중단된 트랜잭션의 롤백이 포함됩니다. 이 시점까지 수행된 모든 업데이트가 롤백되기 때문에 상당한 시간이 걸립니다.
READ로 인해 HLL 증가가 발생하는 경우: mysql.rds_kill_query를 사용하여 쿼리를 종료합니다.
쿼리가 실행되는 시간에 따라 DBA로 작업하여 저장된 프로시저를 사용하여 쿼리를 종료할 수 있는지 확인합니다.

이러한 방법을 사용하여 HLL을 모니터링하여 증가를 방지하고 데이터베이스에서 열려 있거나 오래 실행되는 트랜잭션을 방지하는 것이 좋습니다. 또한, 데이터를 더 작은 배치로 커밋하는 것이 좋습니다.

중요: DB 클러스터 또는 인스턴스를 재부팅하지 마세요. 버퍼 풀의 메모리에 있는 데이터에 액세스할 수 있는 경우 HLL을 제거하는 것이 더 효율적입니다. 데이터베이스를 재부팅하면 존속 가능한 페이지 캐시가 손실될 수 있습니다. 이 경우 HLL을 제거하려면 클러스터 볼륨의 데이터 페이지를 읽어야 합니다. 이는 메모리에서 수행하는 것보다 느리며 추가 I/O 청구 비용이 발생합니다.