본문 바로가기

IT Professional/Microsoft SQL Server

멀쩡하게 돌아가던 SQL 이 갑자기 Lock 이 발생하며 멈춘다면...(1) Ghost Cleanup

 

 

주구 장창 높은 시스템 점유율을 나타내면서 시스템의 속도를 떨어뜨리는 문제를 해결하는 것도 문제지만... 한번씩 그것도 주기적으로 시스템을 무응답 상태로 빠뜨리는 상황이 종종 있다.

이 경우에 Batch 형태로 돌아가는 작업을 잡아 내게 되면 정말 Happy 하지만, 유감스럽게도 대부분의 경우 Batch 가 없는 경우가 많다. (실제로 Batch 가 있다면 벌써 개발자 수준에서 처리를 하는 경우가 대부분이다.)

각각의 Case 가 있지만.. 일단 첫번째 Case Ghost Clean up 에 대하여 알아 보자.

 

Ghost Cleanup

갑자기 Lock 이 발생하면서 단 몇 ms 면 끝날 Select 문이 짧게는 1분 길에는 수십분에 걸쳐서 실행되는 경우가 있다. 물론 중요 서비스라는 난리가 아닐 것이고....

 문제는 주구 장창 발생하는 것이 아니라 일부 발행했다가 안했다가 한다는 것이 문제...

 이 때는 sp_who2  를 이용하여 잽싸게 현재 상황을 보자.

 그러면 위 그림 처럼 Ghost Cleanup 이 진행되는 상황일 수 있다.

 이 경우 해당 Table 단위의 Lock 이 발생하며, DB 의 특정 Table 에 접근하는 Query 는 응답 불능 수준의 속도 저하를 경험하게 되며, time-out 까지도 경험할 수 있다.

 

실제로는 지워야 할 Data 가 물리적인 HDD 상에 남아 있는 상태... 이를 Ghost Row 하고 한다.

 

그렇다면 Ghost Clean up 은 무엇인가?

 

 깊은 인프라 수준의 설명이 필요한 분이라면, Google 에서 Ghost Cleanup 을 찾아 보면 자세하게 잘 나와 있다. 그리고 이 글은 전문가 수준의 엔지니어를 대상으로 하는 글이 아므로 개략적인 설명만 한다. (솔직히 이 정도만 설명해도, 충분히 고객이나 상부에 보고가 가능하다.)

 

 MS SQL 은 단순히 DB 에 걸려 있는 Data 이외에 index 라는 중요한 Data 를 같이 가지고 있다. 이는 Data 검색의 속도 향상을 위해 가장 중요한 성능을 발휘하는 것이고, Data 와 index 는 항상 동기화되어야 한다.

 문제는 이 둘 과의 정합성 확보가 생각처럼 쉽지 않다는데 있다. 하나는 보관을 위해, 하나는 검색 속도 향상을 위한 구조 이므로 관리의 방법이 완전히 다른데, 이 둘의 완벽한 정합성을 유지 한다는 것은 매우 어려운 과제다.

 그래서...

 둘의 정합성 유지를 위해서 Data 의 삭제가 발생한 경우 즉시 인덱스 Data 를 삭제하기 않고 삭제 대상 Flag 를 설정하여 물리적인 부하는 경감시키는 방법을 사용하는데... 실제로는 지워야 할 Data 가 물리적인 HDD 상에 남아 있는 상태... 이를 Ghost Row 하고 한다.

 

 그리고 이 Ghost row 를 일괄 삭제하는 작업이 Ghost Cleanup 이라는 건데... 쉽게 말하 Java 의 gabage collection 이라고 생각하면 된다.

 

문제는...

Java 도 마찬가지 지만 이런 형태의 작업이 수행될 때 Application 의 속도 저하가 발생한다. 그렇지 않아도 index update 에는 적지 않은 부하가 발생하는데, 이런 로직을 없앨 수도 없는 일... 적당히 참을만 하면 bakground 정도로 마감하겠지만... 속도가 무지막지하게 느려지게 되면 어떻게든 해결해야 할 문제가 된다.

 

DBCC Traceon (661, -1) 옵션으로 쉽게 off 시킬 수 있다

 

 해결은...

  이 문제의 해결은 몇가지 나누어 생각할 수 있다.

1) 버그 일 수 있다. 여러가지 버그가 있을 수 있지만, 그 중에서 비 정상적인 Ghost raw 의 증가가 가장 큰 문제의 원인일 수 있다. 일단 아래의 버그 픽스와 버젼을 분석해 보자.

http://support.microsoft.com/kb/2622823

 

그런데 말이다... 끄라구 해 놓구 그 아래에 달린 전제가 묘하다...

이 동작은 공간 소비 및 검색 작업 성능에 영향을 줍니다.

 

2) Ghost Cleanup 을 끈다.

 뭐? 꼭 필요하다며? 근데 끄라고?

 그렇다. 끈다.

http://support.microsoft.com/kb/920093 (고성능 작업 부하를 실행할 때 SQL Server 대 한 튜닝 옵션) 에 명시되어 있다. 끄라고...

DBCC Traceon (661, -1) 옵션으로 쉽게 off 시킬 수 있다. 자동으로 하는 작업이라고 늘 좋은 건 아니다. 우리가 생각도 하지 않고 있는 통게 업데이트도 대용량의 경우 수동으로 해야 하는 경우 있으니까...

 

그런데 말이다... 끄라구 해 놓구 그 아래에 달린 전제가 묘하다...

 이 동작은 공간 소비 및 검색 작업 성능에 영향을 줍니다. 
 

 주기적으로 수도으로 Ghost Cleanup 을 수행한다. DBCC ForceGhostCleanup 을 수행하면 된다. 물론 부하 없는 시간에... 그리고 부하 없는 시간이 정확하게 정의되어 있다면.. Scheduling 을 사용할 수도 있다.

 뭐냐? 어쩌라구? 이거에 대한 답이 있어야 할 거 아녀?

 1) 주기적으로 수도으로 Ghost Cleanup 을 수행한다.

- DBCC ForceGhostCleanup 을 수행하면 된다. 물론 부하 없는 시간에... 그리고 부하 없는 시간이 정확하게 정의되어 있다면.. Scheduling  을 사용할 수도 있다.

2) 주기적으로 Indexrebuild 를 한다. - 이건 GhostCleanup 이 아니라도 기본중의 기본이다.

 

 이제 이 방법으로 Ghostcleanup 으로 인한 장애는 졸업하도록 하자.