[SQL Server] 운영 개선건 - 특정 시점 슬로우 쿼리 증가 현상

특정 시점 슬로우 쿼리 증가 현상을 확인

1. 개요 : 새벽 01시 슬로우 쿼리 대량 발생
2. 분석 
   
   • 다양한 슬로우 쿼리들이 발견됨
   • 해당 쿼리들의 reads, cpu 값에 비해 duration이 높아 보임
   • 평소 시간대에는 쿼리들이 슬로우 쿼리로 잡히지 않음
   • 개발팀을 통해 해당 시간에 API 배치 등 서비스에 특수한 작업은 없는걸로 파악 함
   • 특정 계정(A)으로 실행되는 쿼리가 reads, cpu 값이 비교적 높음
   • 01시 시점에 다른 시간대에 비해 A 계정 쿼리가 다수 잡힘
3. 작업
   • A 계정의 쿼리 분석 및 수정 (BI 데이터를 위한 이관 쿼리로 인덱스 사용할 수 있도록 조건 부분을 조정)
4. 결과 : 해당 시점 슬로우 쿼리 수 감소
5. 특이사항 : A 계정의 쿼리가 비교적 reads와 cpu가 높으나 서버에 전체적인 지연을 야기할 정도로 보이진 않았음. 또한 IOPS를 많이 사용하는 풀백업 시간에도 Read 지연은 발생하지 않았음.
   AWS 문의결과 gp2 처리량을 다 사용하여 (해당 서버의 경우 과거 성능 제한인 160MiB/s 로 적용됨) DB 엔진에서 요청한 데이터에 효율적 처리를(클러스터드 인덱스 스캔) 위해 I/O 요청 대기가 늘어나게 된것으로 파악. 

시간	슬로우 쿼리 수	A계정 쿼리 수
12	60	4
13	30	4
14	40	4
15	16	4
16	1021	20
17	316	4
18	23	4
19	46	4
20	57	4

 

 

 

 

 

이하는 AWS 엔지니어와 현상에 대한 확인을 정리한 내용

RDS SQL Server 인스턴스의 DB내 워크로드 특징에 따라 latency와 DiskQueueDepth가 일시적으로 증가했던 것으로 확인 수행된 집계 처리의 쿼리패턴에 따른 자연스러운 현상으로 보이며, DB 인스턴스의 운영을 방해할정도로의 심각한 저하는 아니었던 것으로 조사.

먼저 AWS 인프라 계층에서의 문제를 확인해보았지만 당시 모든 계층에서 정상.
Multi-AZ 설정된 Primary, Standby 인스턴스 각각의 호스트와, 각각의 EBS 스토리지에서의 상태 또한 모두 healthy로 확인.
네트워크 패킷드랍 또한 없었으며, AWS 모니터링 시스템 로그 등에서도 특이 사항은 확인하지 못함.

그럼에도 별다른 메모리, CPU 압박 없이 latency가 증가하였던 패턴이 보이므로 어떤 부분에서 기인했는지 조사했습니다.
Read & Write latency의 증가는 DiskQueueDepth 의 증가로부터 기인했습니다.

DiskQueueDepth 는 스토리지 성능 한계치(인스턴스, 네트워크 대역폭의 한계치 또한 고려됨)에 따라서 증가 가능.
주로 1) IOPS 처리 한계치에 도달, 2) Throughput 이 한계까지 사용되거나 3) I/O 패턴 을 원인으로 변화.

이번 케이스는 I/O 사이즈가 컸기에 스토리지에서 DiskQueueDepthd와 latency가 증가했던 것으로 파악했습니다.

I/O 사이즈는 스토리지 내 데이터 분포도, 그리고 요청되는 데이터의 특징에 따라서 크게 차이날 수 있다.

latency가 I/O 요청이 수행될때까지의 시간이므로, 처리할 데이터 세트 단위가 커질 수록 I/O당 지연이 증가할 수밖에 없다.

 

예를 들어 전송 크기가 클수록(I/O 단위가 커져서) 많은 양의 데이터를 읽는 작업에 더 효율적이게 되지만, 지연 시간이 허용가능한 범위내로 더 길어진다.
반대로 작은 단위의 랜덤 읽기가 자주 호출되는 작업의 경우에는 IOPS가 훨씬 커지지만 latency는 줄어들고, throughtput 또한 IOPS 수준에 비해 훨씬 작기도 하다.

 

-> 즉 테이블 대량 스캔으로 인하여 I/O의 사이가 커지고 이로 인하여 
Throughput(처리량 ) = I/O단위(한번의IO에 요청되는 크기) * IOPS(초당 I/O 요청) 이므로 처리량도 커지며 이로 인하여 QueueDepth의 증가로 Latency가 증가

I/O 사이즈가 커지면, 하나의 I/O 처리에 지연이 자연스럽게 생기고 다른 I/O 요청 대기도 늘어날 수 있다

--> 불필요한 테이블 스캔으로 인하여 RDS 와 EBS 타입에 따른 Throughput 과 IOPS 제한으로 인한 Latency 증가였다.