[MsSQL] 마지막 페이지 Latch 경합 (Insert 지연 튜닝) - 1. 원인 찾기

Insert 지연 튜닝

회사 중요 데이터 중 많게는 하루에 수억건의 CUD가 발생하는 테이블이 있다.

이상하게 CUD 중 Delete가 발생 할 때에만 지연이 발생하게 되어, 튜닝을 위해 이것저것 확인 중 

 

해당 테이블에서 Delete가 발생 시 삭제 된 데이터의 히스토리를 남기고자 트리거로 히스토리 테이블에 Insert를 하는데,

트리거를 통한 히스토리 테이블에 Insert가 지연되어 원본 테이블의 Delete 함께 지연된다는 사실을 알게되었다.

 

Insert가 지연되었던 이유가 무엇이며, 어떻게 튜닝했는지 초보 DBA의 의식의 흐름대로 어떻게 진행되었는지 남겨보도록하겠다.

 

트리거가 설정되어있는 기존의 테이블을 Table_O(수억건의 CUD발생) / 히스토리를 남기기 위한 테이블을 Table_H(Insert만 발생) 이라고 칭하며 설명하도록 하겠다.

 

Insert 지연 이유에 대한 예상

Insert가 지연되는 이유는 무엇일까?

너무나 단순하게 생각든게 몇가지가 있었다. 

 

1. Disk I/O

- 해당 서버는 RAID-10으로 구성된 물리 디스크 1개에 데이터를 저장하고있다. (로그 디스크는 별도)

- 아무리 RAID-10으로 구성되어 있다지만, 하나의 디스크에 쓰기에 Disk I/O 때문에 느려지는가?

2. mdf(ndf) 파일 크기 확장

- 데이터 변경량이 어마어마해서 데이터 파일 크기가 계속 증가해야해서 파일 크기 확장 때문에 느려지는가?

3. 과도한 Index 설정

- Insert되는 테이블에 인덱스가 많아서, Insert 반영에 지연이 걸리는 건가?

4. Page Split 발생

- 데이터가 Insert 만 되기에 Page가 계속 꽉차기에 Split이 과하게 발생하여 느려지나?

 

이 정도의 단순한 생각을 하게 되었다. 하나하나 무엇이 정답인지 더 생각해보고 알아보았다. (알아보지 못한 것도 있다.)

 

1. Disk I/O

=> 물리적인 IO는 우선 해결 할 수 없다. 같은 디스크를 쓰는 Table_O는 delete만 느려지는데?

2. mdf(ndf) 파일 크기 확장

=> Table_H 만 따로 ndf 파일로 설정해 두었다. 기본 증가량을 200MB / 일배치를 통해 하루치 데이터를 삭제하여 빈공간이 생긴다.

3. 과도한 Index 설정

=> 해당 테이블에는 Clustered Index (getdate 를 키로 가짐) / Non Clustered Index (특정 컬럼 1개를 가짐) 총 2개밖에 존재 하지 않는다.

4. Page Split 발생

=> Split이 어느 이유에서 느리게 되는지는 정확히 몰랐다. 클러스터 인덱스로 마지막 페이지에 데이터가 쌓일텐데, 마지막 페이지만 Split이 발생하니 별 상관없지 않을까? 라고 생각했다.

 

반응형

진짜 이유를 찾기위해 - 증거 찾기

위에 나열한 이유만으론 아무런 답이 나오지 않았다. 그냥 생각이였을 뿐...

진짜 증거를 찾기위해 하나하나 자세하게 뜯어보자.

 

Table_O의 Trigger 실행계획 찾아보기

우선 트리거가 어떻게 동작 하고 있는지 실행계획을 찾아보기로 했다.

select s.creation_time, 
       s.last_execution_time,
       s.execution_count,
       substring(t.text, 
                 (s.statement_start_offset/2) + 1, 
                 ((case s.statement_end_offset 
                     when -1 then datalength(t.text) 
                     else s.statement_end_offset  
                    end - s.statement_start_offset)/2) + 1) as query_text, 
       t.text,
       p.query_plan
  from SYS.DM_EXEC_QUERY_STATS s
 cross apply SYS.DM_EXEC_SQL_TEXT(s.sql_handle) t
 cross apply SYS.DM_EXEC_QUERY_PLAN(s.plan_handle) p
 where t.text like '%[찾고 싶은 쿼리]%'

트리거 내부에 Table_H에 Insert되는 문장이 있고, 해당 Table_H는 히스토리용으로 다른곳에서 Insert되지 않기에 where절에 Table_H를 쓰고 비교적 쉽게 찾았다.

3번 행의 Insert문이 내가 찾던 쿼리이며, 해당 plan이 무엇인지 보도록하자. query_plan의 링크를 눌러 확인하였다.

이리저리 트리거의 로직도 포함되어 있지만, Insert하는 부분에서는 결국 Clustered Index Insert에서 비용이 발생하는 것을 확인하였다.

 

Clustered Index는 데이터를 물리적으로 정렬하여 Insert하기에 getdate값을 가지는 컬럼이 키이므로...

결국 제일 마지막 페이지에 순차적으로 Insert가 될 것이다. 마지막 페이지에만 Insert가 몰리니까 느려지나? 라는 생각이 들었다.

 

Insert 문 지연  Wait_Type 은 무엇인가?

결국 우리가 알아야 할 것은 무엇 때문에 지연인가라는 생각이드는데, 무엇이 Insert 문을 Wating 하게 만드는지 한번 알아보았다.

select r.session_id, 
       r.blocking_session_id,
       r.status, 
       r.start_time, 
       r.wait_type,
       r.command, 
       r.wait_time,
       substring(t.text, 
                 (r.statement_start_offset/2) + 1, 
                 ((case r.statement_end_offset 
                     when -1 then datalength(t.text) 
                     else r.statement_end_offset  
                    end - r.statement_start_offset)/2) + 1) as query_text, 
       t.text,
       r.sql_handle
  from SYS.DM_EXEC_REQUESTS r
 cross apply SYS.DM_EXEC_SQL_TEXT(r.sql_handle) t

현재 실행중인 세션의 쿼리들의 상태를 확인해 보았다.

Table_H 에 대한 Insert가 지연으로 엄청 몰려있어 비교적 찾기 쉬웠다.

이런식으로 정리해보았는데, 1568 세션의 Insert를 대기하느라 나머지 Insert들이 PAGELATCH_EX 대기상태임을 확인했다.

결국, 모든 순차값을 가지는 모든 Insert들이 마지막 페이지에 쓰기 위해 PAGELATCH 경합 중이라는 결론을 내게 되었다.

 

 

마지막 페이지 LATCH 경합

 

빙글빙글 돌았지만, 결국에 마지막 페이지에 모든 Insert들이 몰려 LATCH획득을 위한 경합 중이라는 사실을 어렴풋하게 알게되었다.

 

간단하게 LATCH에 대한내용과 MsSQL의 Insert 동작에 대해서 정리해보고 가도록 하겠다.

 

LATCH

- 메모리의 데이터에 물리적인 일관성을 보장하기 위해 사용되는 잠금 (경량화 된 Lock)

- 여러 프로세스가 동시에 동작하더래도 같은 메모리 페이지에 쓸 경우 Latch를 획득한 프로세스가 동작

- Latch획득의 순서를 보장하지 않으며, 획득하기 위해 경합이 발생하여 대기를 하게 됨

 

MsSQL의 Insert 동작 순서

1. 트랜잭션 로그 쓰기

- 이 시점이 Commit

2. 버퍼 캐시에 저장

- Insert할 페이지가 버퍼 캐시에 없을 경우 해당 페이지를 디스크에서 읽어서 버퍼 캐시에 올린 후 동작

- 있을 경우 버퍼 캐시에 그대로 동작

- 버퍼 캐시의 페이지와 Disk의 페이지의 데이터가 다른 상태

3. Disk의 데이터 파일에 저장

- 주기적으로 CheckPoint 발생하여 변경된 데이터를 일괄적으로 Disk 페이지에 반영

- 퍼버 캐시의 페이지와 Disk의 페이지의 데이터가 같은 상태가 됨

 

Insert 하기 위해 버퍼 캐시의 페이지에 데이터를 쓰는데, 너무많은 프로세스가 하나의 페이지에 쓰기위해 LATCH 경합을 하는게 마지막페이지 LATCH 경합이다.

 

이 것을 어떻게 해결하면 될까?

 

 

LATCH 경합을 줄이는 방법

 

클러스터 인덱스로 되어있는 마지막 페이지에서 LATCH 경합이 발생하니 클러스터 인덱스에 집중적으로 안쓰면 되겠구나!

라고 간단히 생각해서 Clustered Index => Non Clustered Index 로 바꾸기만 하면 되겠다 싶었다.

근데 다시 한번 가만히 생각해보니 여러 의문점이 생겼다.

 

의문과 해답

 

과연 클러스터 인덱스를 논클러스터 인덱스로 바꾼다고 해결이 되는가?

마지막 페이지에 쓰기가 이루어지는 것이 문제인데, 근본적인 해결이 될까??? 라는 생각이 들었다.

 

의문점

1. 클러스터 인덱스를 제거하여, 힙 테이블로 변경시 이 것 또한 하나의 페이지에 쓰기가 모두 발생할텐데?

2. 논클러스터 인덱스의 컬럼도 getdate 컬럼을 키로 가질텐데 어차피 마지막 페이지에 또 똑같이 쓰기가 발생할텐데?

 

이러한 의문을 가지고 여러가지 궁금한점이 생겼으며, 이 문제를 해결하려면 좀더 근본적인 부분을 더 알아야겠다는 생각이 들었다.

 

의문에 대한 해답

1. 기존의 클러스터 인덱스를 논클러스터 인덱스로 변경 후, 균등하게 분배 된 새로운 키를 가지는 클러스터 인덱스를 생성

- 마지막 페이지에 쓰기가 몰리지않게 여러 페이지로 균등하게 분배할 수 있는 새로운 키로 클러스터 인덱스를 생성한다.

2. 논클러스터 인덱스의 컬럼도 getdate의 컬럼 값을 가지므로 역시나, 마지막 페이지 LATCH 경합이 발생한다.

- 클러스터 인덱스보다 하나의 행의 크기가 작기 때문에 하나의 페이지에 더 많은 행을 저장 할 수 있다.

- 더 많은 행을 저장하게 되면 페이지가 비교적 천천히 가득차게 되고, 따라서 Page Split이 덜 발생한다.

- Page Split은 많은 메모리와 CPU자원을 사용하며, 나눠지기 전/후 모든 페이지에 LATCH 대기를 발생시킨다.

 

결국에는 마지막 페이지의 LATCH 경합의 주 원인은 Insert 동작만의 문제가 아닌 Page Split이 지속적으로 발생되어 생기는 문제라는 생각이 들었다.

 

Comment

최대한 여러가지 방법으로 명확한 답을 찾기위해 노력해본 것 같다.

테스트한 내용, 실제 업무에 반영한 내용에 대해서는 아래의 포스팅에 이어서 작성 하도록 하겠다.

[MsSQL] 마지막 페이지 Latch 경합 (Insert 지연 튜닝) - 2. 테스트 및 적용

[MsSQL] 마지막 페이지 Latch 경합 (Insert 지연 튜닝) - 2. 테스트 및 적용

 

Reference

PAGELATCH_EX 경합 해결 - SQL Server