[MySQL] MySQL DB에서 Select 쿼리가 늦어지는 이유

ㅁ 들어가며

  MySQL DB에서 Select 쿼리가 늦어지는 이유에 대해서 정리하였습니다.

 

 

1.  시스템 리소스를 초과하는 경우

   MySQL DB가 처리 가능한 워크로드 용량을 초과하여 CPU 사용량이 많거나, 메모리가 부족하게 됩니다. CPU 사용률과 가용 메모리를 항상 모니터링하는 것이 좋습니다. 가끔 CPU 스파이크가 발생하는 것은 정상이지만 오랜 기간 동안 지속적으로 많은 CPU가 사용되면  SELECT 쿼리가 느리게 실행될 수 있습니다. 
  SELECT 쿼리는 디스크 탐색으로 인해 실행 속도가 저하될 수 있습니다. 디스크 I/O를 최소화하기 위해 데이터베이스 엔진은 디스크에서 읽은 블록을 캐시하려고 합니다. 즉, 다음에 데이터베이스가 동일한 데이터 블록을 필요로 할 때 디스크가 아닌 메모리에서 해당 블록을 가져옵니다. 

 

2. 데이터베이스가 잠금 상태

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

 

mysql> 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

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

 

 

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

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

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

 

 

4. 기록 목록 길이(HLL) 확인

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

 

  InnoDB 기록 목록 길이는 플러시되지 않은 변경 횟수를 나타냅니다. 워크로드에 여러 열린 트랜잭션이나 장기 실행 트랜잭션이 필요한 경우 데이터베이스에서 HLL이 높게 표시될 것으로 예상할 수 있습니다. HLL의 크기를 모니터링하지 않으면 시간이 지남에 따라 성능이 저하됩니다. HLL의 크기가 증가하면 리소스 사용량이 증가하고 SELECT 문 성능이 느려지고 일관되지 않으며 스토리지가 증가할 수도 있습니다. 최악의 경우 이로 인해 데이터베이스 중단이 발생할 수 있습니다.

 

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 상태와 비교).

 

 

SELECT
  NAME AS RollbackSegmentHistoryListLength, 
  COUNT 
FROM
  INFORMATION_SCHEMA.INNODB_METRICS 
WHERE
  NAME = 'trx_rseg_history_len';

Aurora MySQL 인스턴스에 대해 성능 개선 도우미가 활성화된 경우 RollbackSegmentHistoryListLength를 확인할 수 있습니다. 

 

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

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

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

 

장기 실행 트랜잭션 확인 및 종료

HLL 증가로 인해 DB에 병목 현상이 발생하였다면, 장기 실행 트랜잭션을 확인하고 종료해야합니다.

information_schema.innodb_trx 쿼리를 통해 장기 실행 트랜잭션을 찾을 수 있습니다.

 

장기 실행 트랜잭션을 확인하는 방법은 SQL 클라이언트에서 다음 쿼리를 실행합니다.

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 
  AND TIMESTAMPDIFF(SECOND,a.trx_started, now()) > 10 
ORDER BY trx_started

COMMIT 또는 ROLLBACK 명령을 사용하여 각 장기 실행 트랜잭션을 종료합니다.

 

 

ㅁ 함께 보면 좋은 사이트

Aurora MySQL DB 클러스터에서 느린 SELECT 쿼리 문제 해결

Aurora MySQL History Length 상승을 예방하는 방법

 

InnoDB 기록 목록 길이가 크게 늘어남 - Amazon Aurora