Amazon RDS for MySQL에서 실행 속도가 느린 쿼리 문제를 해결하고 쿼리 성능을 개선하려면 어떻게 해야 합니까?

해결 방법

느리게 실행되는 쿼리 문제를 해결하고 쿼리 성능을 개선하려면 다음 단계를 완료하십시오.

리소스 사용률 확인

리소스 사용률을 모니터링하고 쿼리 성능이 언제 저하되는지 식별하려면 다음 작업을 수행하십시오.

• Amazon CloudWatch 지표를 사용하여 지정된 기간 동안의 리소스를 모니터링합니다.
• Amazon RDS 콘솔로 성능 지표를 확인하여 데이터베이스 성능을 모니터링합니다.
• 인스턴스 상태를 확인하여 데이터베이스 성능에 영향을 미칠 수 있는 활성 또는 예약된 프로세스를 식별합니다. 데이터베이스 성능이 좋지 않을 때 발생한 Amazon RDS 이벤트를 확인해야 합니다.

리소스 소비에 영향을 미치는 워크로드 확인

리소스 소비에 영향을 미치는 워크로드를 확인하려면 Amazon RDS의 Performance Insights를 사용합니다. 현재 워크로드가 vCPU 제한을 초과하면 서버에 과부하가 발생합니다. 서버에 과부하가 발생하면 워크로드에 기여하는 쿼리를 확인하고 쿼리를 최적화하십시오. 이후 인스턴스 클래스를 수정합니다.

대기 이벤트를 가장 많이 소비하는 리소스를 조사하려면 DB 로드를 대기 이벤트 수로 나눕니다. 로드 차트에서 색상의 지분이 클수록 워크로드에 가장 큰 영향을 미치는 대기 유형을 나타냅니다. 자세한 내용은 Amazon RDS의 성능 개선 도우미를 통한 DB 로드 모니터링을 참조하십시오.

속도가 느린 쿼리를 식별하려면 파라미터 그룹에서 slow_query_log를 활성화합니다. 인스턴스 워크로드가 증가했는지 확인하려면 CloudWatch 지표를 사용하여 다음을 확인합니다.

• 데이터베이스 연결: DB 인스턴스에 연결된 클라이언트 세션의 수입니다.
• 네트워크 수신 처리량(MB/초): DB 인스턴스로 들어오고 나가는 네트워크 트래픽 속도입니다.
• 쓰기 및 읽기 처리량: 초당 디스크에서 읽거나 디스크에 쓰는 평균 용량(메가바이트)입니다.
• 쓰기 및 읽기 지연 시간: 읽기 또는 쓰기 작업의 평균 시간(밀리초)입니다. 지연 시간 지표와 데이터베이스 연결 또는 처리량 지표 증가 사이의 상관 관계는 워크로드가 쿼리 속도 저하의 원인임을 나타낼 수 있습니다. 자세한 내용은 storage-full이 표시되는 RDS for MySQL 또는 MariaDB 문제를 해결하려면 어떻게 해야 합니까?를 참조하십시오.
• IOPS(읽기 및 쓰기): 초당 평균 디스크 읽기 또는 쓰기 작업 수입니다.
• 여유 스토리지 공간(MB): 현재 DB 인스턴스에서 사용하지 않는 디스크 공간의 양입니다.
• 버스트 밸런스(%) - 사용 가능한 범용 SSD(gp2) 버스트 버킷 I/O 크레딧의 백분율

워크로드의 운영 체제(OS) 목록과 시스템 지표를 보려면 향상된 모니터링을 사용합니다. 기본적으로 향상된 모니터링의 간격은 60초입니다. 더욱 구체적인 데이터 포인트에 대해서는 1~5초 사이의 간격을 설정하는 것이 바람직합니다.

쿼리에 인덱스가 있는지 또는 전체 테이블 스캔을 수행하는지 확인

쿼리에 인덱스가 있거나 전체 테이블 스캔을 수행하는 경우 쿼리 속도가 느려집니다.

쿼리에서 인덱스를 사용하는지 확인하려면 EXPLAIN 쿼리를 실행합니다. 자세한 내용은 MySQL 웹사이트에서 EXPLAIN 명령문을 참조하십시오. EXPLAIN 출력에서 테이블 이름, 사용된 키, 쿼리를 위해 스캔된 행의 수를 확인합니다. 출력에 사용 중인 키가 표시되지 않으면 WHERE 절에 사용된 열에 인덱스를 생성합니다. 테이블에 필요한 인덱싱이 있는 경우 테이블 통계가 최신 상태인지 확인합니다. 자세한 내용은 MySQL 웹사이트의 INFORMATION_SCHEMA STATISTICS 테이블을 참조하십시오. 통계가 최신 상태일 때 쿼리 최적화 프로그램은 올바른 카디널리티를 가진 가장 선별적인 인덱스를 사용합니다. 그에 따라 쿼리 성능이 향상됩니다.

기록 목록 길이 확인

InnoDB는 다중 버전 동시성 제어(MVCC)를 사용합니다. MVCC는 읽기 일관성 유지를 위해 동일한 레코드에 대해 여러 개의 복사본을 유지합니다. 기록 목록 길이는 기록 목록의 수정 사항이 포함된 실행 취소 로그의 총 수입니다. 데이터를 쓰거나 읽는 장기 실행 트랜잭션이 있는 경우 트랜잭션이 완료되거나 롤백될 때까지 기록 목록 길이가 증가합니다. 다른 트랜잭션이 장기 실행 트랜잭션에서 사용하는 테이블을 수정하는 동안에도 기록 목록 길이가 증가합니다.

워크로드에 여러 개의 미완료 또는 장기 실행 트랜잭션이 필요한 경우 데이터베이스의 기록 목록 길이가 증가할 것입니다. 기록 목록 길이를 모니터링하지 않으면 시간에 따라 성능이 저하될 수 있습니다. 기록 목록 길이가 길면 리소스 사용량이 많아지고 SELECT 명령문 성능 속도와 일관성이 저하되고 스토리지가 증가할 수도 있습니다.

참고: 장기 실행 트랜잭션이 기록 목록 길이 증가의 유일한 원인인 것은 아닙니다. 스레드 제거로 DB 변경을 따라가지 못하는 경우 기록 목록 길이가 계속해서 길어집니다. 극단적인 경우에는 데이터베이스가 중단될 수도 있습니다.

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

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

Amazon RDS for MySQL 인스턴스에 대해 Performance Insights가 활성화된 경우 다음 단계를 완료하여 RollbackSegmentHistoryListLength를 확인합니다.

1. Performance Insight 작성기로 이동합니다.
2. 지표 관리를 선택한 다음 데이터베이스 지표를 선택합니다.
3. trx_rseg_history_len 지표를 선택한 다음 그래프 업데이트를 선택합니다.

기록 목록 길이 증가 문제를 해결하려면 다음 작업을 수행하십시오.

• DML 쓰기로 인해 기록 목록 길이가 증가하는 경우 쓰기 명령문을 취소 또는 중지합니다. 여기에는 중단된 트랜잭션의 롤백이 포함되며 업데이트를 롤백하는 데 상당한 시간이 걸립니다.
• READ로 인해 기록 목록 길이가 증가하는 경우 mysql.rds_kill_query를 사용하여 쿼리를 중지합니다.
• 쿼리가 장시간 실행되는 경우 DBA를 사용하여 쿼리를 중지할 수 있는지 확인합니다.

참고: 데이터베이스에서 완료되지 않았거나 장시간 실행되는 트랜잭션이 생기지 않도록 하고 데이터를 소규모 배치로 커밋하는 것이 바람직합니다.

쿼리 성능 개선

쿼리 성능을 개선하려면 다음 모범 사례를 따르십시오.

• 시간을 가장 많이 사용하는 명령문을 찾으려면 속도가 느린 쿼리를 프로파일링합니다. 자세한 내용을 보려면, MySQL 웹사이트의 SHOW PROFILE 명령문을 참조하십시오.

• SHOW FULL PROCESSLIST 명령을 향상된 모니터링과 함께 실행합니다. 함께 사용하면 데이터베이스 서버에서 현재 실시되고 있는 작업 목록을 검토할 수 있습니다.

• SHOW ENGINE INNODB STATUS 명령을 사용하여 트랜잭션 처리, 대기 이벤트 및 교착 상태 관련 정보를 얻을 수 있습니다.

• 차단 쿼리를 찾아 해결합니다. 자세한 내용은 다른 활성 세션이 없는데 Amazon RDS for MySQL DB 인스턴스에 대한 쿼리가 차단된 이유는 무엇입니까?를 참조하십시오.

• MySQL 로그를 CloudWatch Logs에 게시합니다. 로그는 1시간마다 교체되어 할당된 스토리지 공간 임계값의 2% 를 유지합니다. 2주가 넘었거나 전체 크기가 2% 임계값을 초과하는 로그는 제거됩니다.

• 리소스 사용량을 모니터링하고 임계값 초과 시 알림을 받으려면 CloudWatch 경보를 설정합니다.

• 쿼리 작업 계획을 찾아 쿼리가 적절한 인덱스를 사용하는지 확인합니다. EXPLAIN을 사용하여 쿼리를 최적화하고 MySQL의 쿼리 실행에 관한 세부 정보를 볼 수 있습니다. 자세한 내용을 보려면, MySQL 웹사이트에서 EXPLAIN을 사용하여 쿼리 최적화를 참조하십시오.

• ANALYZE TABLE로 쿼리 통계를 최신 상태로 유지합니다. 자세한 내용을 보려면, MySQL 웹사이트의 ANALYZE TABLE 명령문을 참조하십시오.

• MySQL 8.0은 EXPLAIN ANALYZE를 사용합니다. EXPLAIN ANALYZE 명령문에는 MySQL이 쿼리에서 시간을 할당하는 위치와 이 시간이 할당된 이유가 표시됩니다. 쿼리가 완료되면 EXPLAIN ANALYZE에서 계획과 측정값이 인쇄됩니다. 자세한 내용은 MySQL 웹사이트에서 EXPLAIN ANALYZE로 정보 확보를 참조하십시오.

• MySQL 8.0에서 잠금 대기는 data_lock_waits 테이블의 성능 스키마에 나열됩니다. 자세한 내용은 MySQL 웹사이트의 InnoDB transaction 사용 및 잠금 정보를 참조하십시오.

  예시:

  SELECT  r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
  FROM       performance_schema.data_lock_waits w
  INNER JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_engine_transaction_id
  INNER JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_engine_transaction_id;