'SQL Server'에 해당되는 글 2건

  1. 2013.08.08 MS SQL 병목 탐지 (5)
  2. 2013.02.07 Deadlock 의 진단과 해결 (1)

시스템 인프라 담당자에게는 미안한 말이지만, 시스템 파라메터 열심히 튜닝하는 것보다, 쿼리 한줄, DB 옵션 하나 수정하는게 훨씬 성능이 잘 나온다는 이야기 들이 있다. 맞는 말이다. (나도 시스템 엔지니어지만, 맞는 건 맞는 거니까...)

 

 그런데 솔직히 SQL 을 제대로 튜닝하는 DBA 를 만나기가 쉽지 않다. 튜닝은 고사하고 SQL 에 뭐거 문제인지 진단하는 방법론을 가지는 엔지니를 만나는 것도 꽤 어려운 일이다.

 

 그렇다면, MS SQL 을 다루는 사람들은 왜 이렇게 튜닝을 힘들어 할까? 물론 오라클 만큼이나 시장이 성숙하지 않은 것은 사실이지만, 적지 않은 기간 동안 운영되어 온 SQL 은 왜 이렇게 튜닝 파워가 낮을까?

 

 그 이유는 아마도 툴의 부재라고 보인다. (이건 아마도 가장 큰 책임은 MS 에 있지 않나 생각된다.)

 

 오라클에는 무수히 많은 툴들과 개발 환경을 만들어 내는 기업들이 존재한다. 개발 환경만 해도 Dell software 의 TOAD, ORANGE... 모니터링 튜닝 툴은 국산 툴만 해도 MaxGage, Shelpa... 해외 툴까지 하면, 이루 셀 수가 없다.

 

 하지만, MS SQL 의 개발 환경을 판매하는 기업은 없다. 그럴 수 밖에, 설치만 하게 되면 무제한 라이센스의 SQL Management studio 가 무재한으로 제공되고 그 품질 조차도 기가 막히게 좋은 마당에 엔진의 소스 코드를 가진 제조사에 도전할 솔루션 업체가 있다는게 말도 안되는 소리다.

 

하지만... 프로파일러가 치명적인 문제가 있는데,

 

 정원혁 씨의 이야기를 들어 보면 가장 강력하고 뛰어난 툴로서 프로파일러를 이야기 한다. 맞는 말이다. 프로파일러만 제대로 쓴다면, 그 어떤 문제에 대한 정보도 정확하게 찾아 낼 수 있다. 하지만... 프로파일러가 치명적인 문제가 있는데,

 

1. 상시적 수집을 할 경우 서버의 성능 저하, 저장 공간의 고갈 등의 이슈가 발생하므로 상시 수집이 불가능하다.

2. 수집된 Data 를 분석 가능하게 가공하는데 많은 시간과 노력이 투입된다.

3. 성능이 이슈가 되는 서버에 프로파일러를 적용하기 위해서는 매우 조심성 높은 모니터링이 요구된다.

 

프로파일러를 그냥 사용해도 문제가 없다구?

 

따라서 튜너가 사용하거나 재발 예측이 가능한 시점의 병목을 추적할 때 고수준의 DBA 가 사용하기에는 손색이 없지만, 재발 예측 가능, 고수준의 DBA 라는 두가지 조건을 만족하는 Tool 을 개발하기 어려운 것도 사실이다.

프로파일러를 그냥 사용해도 문제가 없다구?

  • Run Profiler remotely instead of directly on server
  • Avoid including events that occur frequently (e.g. Lock:Acquired) unless absolutely needed
  • Include only event classes needed
  • Specify limiting filters to reduce the number of events
  • Avoid redundant data (e.g. SQL:BatchStarting and SQL:BatchCompleted)
  • Avoid running large traces with Profiler; consider a server-side SQL Trace instead
  • Limit server-side trace file size and manage space usage

이거 MS SQL books online 에 있는 말이다. 내 의견이 아니라...  

 

 '어제밤에 2시경에 갑자시 DB 가 느렸는데, 지금은 괞찮아.. 이유가 뭐야?'

 

 특히 아무리 뛰어난 DBA 라도

 '어제밤에 2시경에 갑자시 DB 가 느렸는데, 지금은 괞찮아.. 이유가 뭐야?'

 라는 질문에는 대답할 재간이 없다.

 

 Dell software Performance Analysis

 

 MS SQL 의 프로파일러가 튜닝의  처음이자 마지막이라고 생각하는 이여, Dell software 의 Performance Analysis 를 한번 보자. 국내 제품이 있긴 한데... 몇몇 제품은 Tracking Data 수집에 Profiler 를 통하여 수집하고 있기에 그 한계를 동일하게 지닌다. 그리고 협소한 개발 인력의 한계를 그대로 가지기도 하고...

다 떠나서 수집 방법이 기존의 프로파일러와 달라서 상시 수집이 가능하고, Data 를 따로 저장해서 충분히 사용이 가능한 형태로 가공된 Data 를 뿌려 준다.

(모든 그림은 누르면 커집니다.)

[통합 성능 관리 화면]

- 첫화면을 열면 통합 성능 관리 화면이 나온다. 에게? 이 정도는 성능 모니터도 하는데...

물론 그렇다. 그런 느낌이 든다면...Advisory Tab 을 클릭해 보자.

 

[Advisory Tab]

 

오잉? 특정한 Table 에 읽기가 너무 발생하니, 인덱스를 좀 잡으시오? 오호... 누구나 알 수 있는 작업이지만, 이렇게 추천만 해 줘도 작업이 훨씬 쉬워지고, 노가다가 없어진다.. 야호!!!

 뿐만 아니라 경합, 비정상적으로 증가한 부하 분석도 같이 진행해 준다.

 

[Real time]

 말 그대로 현재 시점이다. 어떤 리소스가 가장 많이 사용되고 있는지, 그로 인한 병목은 어디서 발생하는지.. 등등등... 하지만, 여기까지는 별로 볼 게 없다. 그리고 노련한 운영자라면 벌써 보고 있을지도 모른다.

[History]

-Advisory

 벌써 본 advisory  다. 하지만 이 advisory 는 현재 시점을 이야기 하는 것이 아니라, 이전 시점의 주요 정보를 알려 준다. 기본은 지금 부터 특정 시간 이전을 보여 주지만 -- 부터 -- 까지를 자유롭게 탐색해 가며 성능적인 이슈와 해당 시점의 advisory 를 분석할 수 있다.

-Overview

 전체 overview 는 real time 부분을 해당 시점 정보로 분석할 수 있는 뷰를 제공한다.

 CPU, I/O, Network, Lock 등의 다양한 리소스에 대하여 점유와 병목 분석 화면을 시점 기간 별로 분석할 수 있다. 당연히 보다 시피 리소스 별 MS SQL 의 주요 점유 대상을 정의하고 있다.

 

-SQL Statement

 개인적으로 가장 활용도가 높아 보이는 화면...

 특정 시점의 SQL 문의 리소스 점유 비율을 자세히 보여 준다. 해당 시점의 SQL 중 리소스를 가장 많이 점유한 SQL 이 어떤 SQL 이며, 전체의 얼마에 점유율을 보였는지... 그리고 몇 회나 실행되고, 해당 시점의 리소스 점유 시간이 어떠했는지를 한눈에 볼 수 있다.

 뿐만 아니라, 전체 리소스를 나타내는 workload 라는 항목에서 아래 처럼 CPU, Lock, Latch 등 다양한 리소스로 Break down 하여 분석할 수 있다.

 아래를 보면 현재 서버는 CPU 병목으로 인한 속도 저하가 발생할 확률이 높다는 것을 알 수 있다. 이 경우 CPU 를 많이 소요하는 Sort, Join 등을 수정해 주여야 할 수 있음을 알 수 있다.

-병목 리소스 분석

 

-개별 SQL 분석

 전체 중에 특정 SQL 이 차지하는 리소스를 분석하기 위한 기능이다. 위의 경우는 가장 많은 리소스를 점유하는 SQL 이외에는 별로 신경 쓸 대상이 아니라는 것을 알 수 있다.

 가장 중요한 최상위 SQL 를 튜닝하기 위해 노력하자, 경험 상으로 악성 쿼리를 2~3 개만 튜닝하여도 전반적인 성능에 대하여 2배 이상 체감적인 향상을 경험할 수 있다.

 

 늦다는데, 뭐가 어떻게 얼마나 늦어 졌는지, 뭐가 더 모자라서 또는 평소보다 뭘 더 써서 그런지...

 

-Compare

 운영자에게 내려 준 Dell software  의 선물... Compare 기능...

 늦다는데, 뭐가 어떻게 얼마나 늦어 졌는지, 뭐가 더 모자라서 또는 평소보다 뭘 더 써서 그런지... 도무지 모를 때, 그리고 개선 요청을 한 이후에 SQL 이 얼마나 달라졌는지, 얼마나 빨라 졌는지를 알 수 없을 때...

 비교 분석을 리포트 작성을 지원한다.

 리소스별, 쿼리별 Stored procedure  별로 모두 특정 기간 대비 분석이 가능하다.

 기가 막히지 않는가? 더 중요한 건, 이 작업을 아무런 추가적인 설정없이 바로 수집이 가능하다는 것...

 업무의 30% 이상 부하가 줄 것이다.

 

[Report]

 

 당연히 리포트는 지원된다. 리소스 사용량 부터 특정 기간 가장 많은 리소스를 점유하는 사용자, 세션, SQL 문까지, 그리고 앞에 말했듯이 기존보다 더, 덜 쓰는 리소스 그리고, 사용자 분석까지 완벽하게 지원하며, 특히 리소스의 경우 평소에 통상적으로 점유하는 리소스 범위를 스스로 학습해서 통계적으로 정하고, 그 범위를 벗어나는 것을 직관적으로 알 수 있는 그래프를 제공한다. 

그래프는... 위 화면 처럼 다양하게 제공한다. 그리고, 주기적인 리포트 발행 스케쥴링 기능과 함께 PDF, 엑셀 형식의 파일을 제공하여 쉽게 편집을 하여 보고, 분석 자료로 활용할 수 있다.

 

  이거 어디서 구하냐구?

 http://support.quest.com  에서 Performance Analysis 로 찾아 보자.

 당연히 유료 제품이나 그다지 고가의 제품이 아니다. (국산보다 비교도 안되게 싸다.) 게다가 지금 한참 프로모션 중이니, 한번 신청해 보자. 영업 담당자에게 요청하면 시리얼 키 한달 정도는 어렵지 않게 받을 수 있다.

Posted by 전략과 비젼

RDBMS 에서 Deadlock 이라는 단어가 종종 등장한다. 대부분의 DB 안내서에 자주 등장하는 이야기 지만.. 의외로 해당 문제 때문에 trouble shooting 을 수행하여야 하는 경우는 드뭅니다. 대부분의 dealock 이 개발 단계에서 탐지되고, 수정되기 때문이죠.

 

그렇다면, 여기서 '대부분' 이라는 키워드를 사용하는 경우는 어떤 경우이며, '대부분' 에 포함되지 않는 예외 Case 는 어떤 것들이 있는지, 그리고 그에 대한 해결은 어떻게 접근하는지에 대하여 생각해 보기로 하겠습니다..

 

Deadlock 의 정의에 대해서는 따로 설명하지 않겠습니다. 정의는 적당한 문서가 많이 있습니다..

Deadlock 의 탐지

 

1. SQL 이벤트

2. 과도한 CPU 의 사용

3. 작업 모니터에 CXPacket 의 기록

 

1 의 경우 대부분의 개발 과정에서 탐지 됩니다. 그리고 안되더라도... 왠만큼 작은 서비스가 아닌 담에야 초짜 DBA 또는 개발자에게 DML 이나 SQL Query 를 통째로 맡길 강심장 프로젝트 메니져는 많지 않습니다.

 

뭐 실제로 그렇다 하더라도, 그런 SQL 이야 좀 Deadlock 이 걸리면 어떠랴...

 

Lock 은 성능을 저하 시키는 가장 큰 부분이라 튜닝의 대상이지만, Deadlock 은 application time out 같은 장애 상황을 유발하는 오류이므로 튜닝이 아닌 Trouble shooting 의 대상이라는 것을 기억하여야 합니다.

 

해결의 방법은 SQL coding 에서 해결하는 부분이니 다른 참고 자료를 참고하시고...

 

개발 이후 운영 중에 발생하는 deadlock 의 탐지와 해결에 대해서 한번 이야기해 보겠습니다.

 

별로 사용하는 쿼리나 Data 도 없는데, CPU 의 사용이 급격하게 증가하고, 심지어 Application 의 Timeout 이 발생할 경우 deadlock 을 의심해 볼 수 있습니다.

 

많은 경우 CPU 를 대량으로 사용하거나 하는 경우는 일반적으로 DML 이라 쿼리 문을 잘못 작성한 경우가 많습니다.

 

하지만 작업 모니터 (activity monitor) 에서 CXPACKET 이 발견되는 경우는 Deadlock 때문입니다. 

 

 - 작업 모니터에서 대량으로 발견되는 CXPACKET... 이 정도면 대부분의 사용자들이 성능 저하 또는 Time out 을 경험하게 됩니다

 

그런데...개발 발견되지 않은 CXPACKET 이 왜 운영 환경에거 발견될까요?

 

MAXDOP

 

이유는 개발 환경보다 운영 환경의 HW Spec 이 높은 경우, 특히 CPU 의 Core 개수가 많은 경우 발생합니다.

 

대중의 CPU 가 리소스 점유율이 높은 Query 를 동시에 실행할 경우 CPU 자원의 사용 교착 상태로 인하여 해당 문제가 발생할 수 있습니다.

 

이 경우 SQL 인스턴스의 CPU 병렬 처리 값을 조정해 주면 간단히 해결됩니다.

 

서버 속성/고급/병렬처리에서 '최대 병렬 처리 수준' 의 값을 4 이하로 조정해야 합니다.

경우에 따라서는 2 또는 극단적인 경우 1이 될 수도 있으며, 실제적인 성능 저하 (예를 들어 10 msec 걸리는 query 가 500 msec 로 걸리는) 가 없다면, 해당 값을 조정해도 문제가 없습니다.

 

이는 영문 표기로 Max degree of Parallelism 으로 표기되며, 해당 정보는 MS 에서도 공식적으로 4 이하로 권고하고 있습니다. 기본 값은 0 으로 무한대로 사용하게 되어 있습니다. 특히 복잡한 쿼리를 많이 쓰는 경우 Deadlock 문제가 발생할 확률이 높습니다.

Posted by 전략과 비젼