Amazon RDS for MySQL에서 쿼리가 느리게 실행되는 이유는 무엇입니까?

간략한 설명

쿼리 성능을 향상시키려면 다음 요소를 고려하십시오.

• 리소스 사용률(예: CPU, 메모리, 스토리지)
• 워크로드 분석
• 쿼리 튜닝 및 모니터링

해결 방법

리소스 사용률(예: CPU, 메모리, 스토리지)

데이터베이스 성능 문제의 근본 원인을 이해하려면 인스턴스가 사용 중인 서버 전체의 모든 리소스를 확인합니다. 쿼리 실행 시간이 너무 오래 걸리기 시작했을 때와 비교하여 쿼리 성능이 정상적일 때 워크로드와 조사를 모니터링할 수 있습니다.

Amazon CloudWatch 지표를 사용하여 성능이 정상으로 간주된 날을 포함하여 일정 기간 동안 이러한 리소스를 모니터링할 수 있습니다. Amazon RDS 콘솔에서 성능 지표를 확인하여 데이터베이스 성능을 모니터링할 수도 있습니다.

또한 인스턴스 상태를 확인하여 데이터베이스 성능에 영향을 줄 수 있는 모든 다른 활성 프로세스 또는 예약된 프로세스를 식별할 수 있습니다. Amazon RDS 콘솔에서 데이터베이스가 제대로 작동하지 않았을 때 발생한 이벤트를 확인합니다.

워크로드 분석

리소스 소비의 원인이 되는 워크로드를 분석하려면 성능 개선 도우미를 사용합니다. 성능 개선 도우미는 리소스 소비 증가의 원인이 되는 모든 쿼리와 모든 대기에 대한 그래픽 분석을 제공합니다.

성능 개선 도우미는 인스턴스의 vCPU 수를 사용하는 대신 워크로드를 주요 지표로 사용합니다. 현재 워크로드가 vCPU 한도를 초과하면 서버에 과부하가 걸립니다. 서버에 과부하가 걸린 경우 워크로드의 원인이 되는 쿼리를 확인하고 쿼리를 최적화하는 방법을 식별합니다. 그런 다음 인스턴스 클래스를 수정하는 것이 좋습니다.

성능 개선 도우미 워크로드를 대기 이벤트로 분류할 수도 있습니다. 대기 이벤트 수만큼 DB 로드를 분할하여 리소스를 가장 많이 소비하는 대기를 조사합니다. 로드 차트의 색상 밴드가 더 두꺼우면 그 대기 유형이 워크로드의 가장 큰 원인이 된다는 것을 나타냅니다. 자세한 정보는 Amazon RDS의 성능 개선 도우미로 DB 로드 모니터링을 참조하십시오.

느린 쿼리 로그(사용자 지정 파라미터 그룹에서 활성화됨)를 사용하여 실행 속도가 느린 쿼리를 식별할 수도 있습니다.

그런 다음 Amazon CloudWatch 지표를 사용하여 인스턴스에서 수행된 작업량이 증가했는지 확인할 수 있습니다. 예를 들면 다음과 같습니다.

• 데이터베이스 연결: DB 인스턴스에 연결된 클라이언트 세션 수입니다.
• 네트워크 수신 처리량(MB/초): DB 인스턴스에서 주고받는 네트워크 트래픽 속도입니다.
• 쓰기 및 읽기 처리량: 디스크에서 읽거나 디스크에 쓰는 초당 평균 MB 수입니다.
• 쓰기 및 읽기 대기 시간: 읽기 또는 쓰기 작업의 평균 시간(밀리초)입니다.
• IOPS(읽기 및 쓰기): 초당 평균 디스크 읽기 또는 쓰기 작업 수입니다.
• 사용 가능한 스토리지 공간(MB): 현재 DB 인스턴스에서 사용하지 않는 디스크 공간의 양입니다.

대기 시간 지표는 읽기 또는 쓰기 디스크 I/O 작업을 완료하는 데 걸리는 시간의 양을 나타냅니다. 증가된 데이터베이스 연결 또는 처리량 지표와 대기 시간 지표의 상관 관계는 워크로드가 느린 쿼리 실행의 원인임을 나타낼 수 있습니다. 사용 요소 식별에 대한 자세한 정보는 MySQL을 실행하는 Amazon RDS DB 인스턴스에서 스토리지를 사용하는 항목을 확인하려면 어떻게 해야 합니까?를 참조하세요.

향상된 모니터링을 사용하여 워크로드와 관련된 운영 체제 목록과 기본 시스템 지표를 검색할 수도 있습니다. 기본적으로 향상된 모니터링의 모니터링 간격은 60초입니다. 보다 세분화된 데이터 요소를 위해 이 값을 1~5초 간격으로 설정하는 것이 가장 좋습니다.

쿼리 최적화

느린 쿼리 로그 또는 성능 개선 도우미에서 장기 실행 쿼리를 식별한 후 쿼리 성능을 개선하는 방법을 고려합니다. 쿼리를 조정하려면 다음 접근 방식을 고려하십시오.

• 가장 많은 시간을 소비한 상태를 찾으려면 더 느린 쿼리를 프로파일링하십시오. 자세한 정보는 MySQL 웹 사이트의 SHOW PROFILE 명령문을 참조하세요.
• 향상된 모니터링과 함께 SHOW FULL PROCESSLIST 명령을 실행합니다. 함께 사용하면 현재 데이터베이스 서버에서 수행 중인 작업 목록을 검토할 수 있습니다.
• SHOW ENGINE INNODB STATUS 명령을 사용하여 트랜잭션 처리, 대기 및 교착에 대한 정보를 얻을 수 있습니다.
• 모든 차단 쿼리를 찾아서 차단을 해결합니다. 자세한 정보는 다른 활성 세션이 없을 때 Amazon RDS for MySQL DB 인스턴스에 대한 쿼리가 차단된 이유는 무엇입니까?를 참조하십시오.
• MySQL 로그를 Amazon CloudWatch에 게시합니다. 로그는 할당된 스토리지 공간 임계값인 2%를 유지하기 위해 매시간 순환됩니다. 그런 다음 2주를 초과했거나 합친 크기가 2% 임계값을 초과하면 삭제됩니다.
• 리소스 사용량을 모니터링하고 임계값이 초과될 때마다 알림을 받을 수 있도록 Amazon CloudWatch 경보를 설정합니다.
• 쿼리에 대한 실행 계획을 찾고 쿼리가 적절한 인덱스를 사용하고 있는지 확인합니다. EXPLAIN 계획을 사용하여 쿼리를 최적화하고 MySQL이 쿼리를 실행하는 방법에 대한 세부 정보를 검토할 수 있습니다.
• ANALYZE TABLE 명령문을 사용하여 쿼리 통계를 계속 업데이트합니다. 쿼리 최적화 프로그램은 오래된 통계로 인해 간혹 잘못된 실행 계획을 선택할 수 있습니다. 이로 인해 테이블과 인덱스의 카디널리티 추정치가 부정확하기 때문에 쿼리 성능이 저하될 수 있습니다.
• MySQL 8.0은 이제 EXPLAIN ANALYZE 명령문을 사용합니다. EXPLAIN ANALYZE 명령문은 쿼리를 위한 프로파일링 도구로, MySQL이 쿼리에 시간을 소비하는 위치와 그 이유를 보여줍니다. EXPLAIN ANALYZE를 사용하면 MySQL은 행을 계산하고 실행 계획의 다양한 지점에서 소요된 시간을 측정하면서 쿼리를 계획, 계측 및 실행합니다. 쿼리가 완료되면 EXPLAIN ANALYZE가 쿼리 결과 대신 계획과 측정값을 프린트합니다.
• MySQL 버전 8에서는 잠금 대기가 data_lock_waits 테이블의 성능 스키마에 나열되어 있다는 점에 유의하십시오. 예를 들면 다음과 같습니다.

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;

자세한 정보는 MySQL 웹 사이트의 InnoDB 트랜잭션 및 잠금 정보 사용을 참조하세요.

---

관련 정보

성능 개선 도우미를 통한 Amazon RDS for MySQL 튜닝

Amazon RDS for MySQL, MariaDB 또는 Aurora for MySQL 인스턴스에서 높은 CPU 사용률 문제는 어떻게 해결합니까?