영상버전 : https://youtu.be/4ssE81DwGr4
.jpg)
얼마전에 며칠을 고생해서 원인 쿼리를 찾은 이야기 입니다.
보통 쿼리는 Lock에 걸려서 대기 상태에 있다면 쉽게 찾는데요..
Lock에 걸리지 않은 상태로 대기를 타는 경우가 있습니다.
Sql server 에서 일단 쉽게 찾는 방법은
Sp_who2 를 실행하는 것이죠.
그냥 SSMS에서 서버에 접속해서 새 쿼리창을 열고
Sp_who2 라고 입력하고
실행해버리면
현재 물려있는 모든 세션의 정보가 나타납니다.
여기서 Blkby 라는 필드에서 숫자가 보이면
그 숫자의 SPID가 원인으로
숫자가 쓰여진 곳의 SPID가 멈춰 있다는 이야기 입니다.
이렇게 원인 세션을 찾고
그 세션이 어떤 쿼리를 던지고 있는지를 찾아서
Kill을 할지, 다른 어떤 액션을 취할지를 택하면 됩니다.
Kill을 하고 싶으면
Kill 999 처럼 SPID를 kill 뒤에 적어주면 되구요..
Blkby에 적힌 숫자를 입력하면 원인 쿼리가 강제 종료 되는데요,
이게 INSERT나 UPDATE의 경우가 많아서
데이터 유실이 될 가능성이 높기 때문에
저 같은 경우는 원인 쿼리의 문제를 해결할 방법만 알려주고
자연히 사라질 때 까지 기다리기는 합니다.
그래도 급히 대응이 필요하거나
아무리 기다려도 사라지지 않는 경우에는
Kill밖에 없겠죠.
지난 번에는 개발팀에서
Begin tran을 던지고 쿼리를 던진 뒤에
Commit이나 rollback을 하지 않아서
Blkby가 보여서 문의했더니
자신들의 실수임을 확인해서 쉽게 끝났지만,
이번에는 Blkby가 뜨지 않은채로
많은 세션들이 SUSPEND가 되어버렸죠.
보통 커넥션 풀을 사용한다 하더라도
SUSPEND가 되는 경우가 없고
SOCKET이라면 연결 상태로 정지되어
SUSPEND가 되기도 하지만,
기본적으로 SUSPEND는 뭔가 이상하다고 보시는게 좋습니다.
무언가의 문제로 인해 타임아웃이 발생하거나 하면
SUSPEND가 되는 경우가 많거든요..
일반적인 상태라면 SUSPEND가 없는게 보통입니다.
이번에도 Blkby가 비어있는데도 SUSPEND 된 세션이 많았는데요
기존 DB담당자는 ai에 던져보니 별 문제가 없었다더라 라고 이야기를 하는데,
ai의 의견을 전적으로 신뢰하면 안되는 이유 중 하나이죠.
Ai는 SUSPEND를 그냥 세션이 물려 있다 끊긴 것 정도로 대수롭게 판단하지 않는데요..
SUSPEND가 없는게 보통인데 SUSPEND가 늘어났다면
어디선가 발목을 잡는 놈이 있구나 생각을 하셔야 합니다.
여기서 문제가 있다는 랭킹 페이지를 열어 봤는데
504에러가 나고 있었더라구요..
504에러는 웹서버의 반응이 늦어져서 중간에 timeout이 되어 버리면
나타나는 현상입니다.
원인은 네트워크 문제일 수도 있고, 웹서버의 리소스가 바닥날 수도 있고,
DB의 반응이 늦어져도 발생할 수 있죠.
네트워크나 서버의 이슈는 없다고 하는데,
DB에서도 CPU는 가끔 100%는 치지만 그것도 한 순간이고
DB서버에서 memory나 네트워크의 부하도 없는 상태입니다.
한 순간 100% 되는데
504에러는 오전 9시 부터 13시 정도에 이어져서 발생한다고 합니다.
순간 100%로 4시간이 504에러가 뜬다는게 말이 안되죠.
그래서 Profiler를 연결해서 계속 떠 있는 SQL을 보다가
무언가 찾아냈습니다.
특정 쿼리가 where in (select product_id from #temp_table )
으로 되어 있는데,
이 쿼리가 Logical Read가 5400만건인거죠..
즉, where in 안에 있는 템프테이블이 엄청나게 많았다는 얘기겠죠.
제가 쿼리 튜닝할 떄 이야기 하는 것 중 하나가
Where in 안에 들어가는 개수는 1000개 미만으로 해라..
뭐 스펙별로 차이는 있겠지만,
Where in 에 들어가는 데이터는 모두
Or로 연산하기 때문에 index를 타지 않습니다.
요런 식으로 템프 테이블을 만들어서
Where in 으로 넣을 때는 실제 데이터를 꼭 체크하시기 바랍니다.
그리고 또 별 건 이지만,
여기서도 랭킹 페이지를 실시간으로 가져와야 한다면서
각종 랭킹 쿼리를 가져오다보니 70번 넘는 sql을 한 페이지에서 던지는데요..
그나마 하나의 커넥션 풀에서 던지므로 그나마 낫긴 하지만,
이런 여러번의 쿼리를 던지는 페이지는
절대 처리 요구수 때문에 문제가 발생합니다.
때문에 랭킹 페이지를 한 번씩 캐시메모리나 캐시 파일로 던져서
1분에 한 번씩 갱신하든 하는 식으로 해야지,
모든 유저를 70번씩 쿼리를 던지게 하면
서비스가 뻗겠죠..
게다가 랭킹 쿼리들도 가상 테이블을 작성해서
랭킹으로 만들고 있는데요..
Rank over라는 쿼리를 사용하기 위해서
필요한 데이터를 템프테이블로 가져오는 것은 이해는 하는데,
이번에 발생한 문제가 바로 여기에서 생긴 거죠.
위쪽의 where in 에 넣을 템프테이블을 만드는데 5500만행을 만들다보니
그에 필요한 IO가 대량으로 발생을 하고
템프테이블을 생성하는 동안
정합성 때문에 다른 커넥션에서 시도하려는 템프테이블이 대기를 타게 되었습니다.
이건 템프테이블 작성을 위한 대기이기 때문에
쿼리 대기가 아니라서
Lock에는 표시가 안되는 것이지요.
때문에 지금까지 어느 누구도
Zabbix나 new relic을 봐도 부하가 없어 보이고
Disk io는 신경도 안쓰고
다른 쿼리들은 잘 돌아가는데다가
70개나 되는 쿼리를 하나하나 체크를 못하다보니
어디서 문제가 생기는지 찾을 수가 없었던 거 같습니다.
심지어는 disk io가 늘어나면
당연히 CPU가 늘어나지 않느냐 라는 인프라 팀의 질문이 있었는데요.
보통 PC는 디스크 조작을 많이 하면
CPU가 올라가는 것이 보이죠?
그런데 일부 서버는 disk io가 아무리 늘어나도
CPU는 변화가 없는게 보일 겁니다.
같은 SCSI컨트롤러를 사용하더라도
SATA방식의 디스크를 끼우면
SCSI컨트롤러를 패스하고 CPU가 디스크랑 직접 소통을 하기 때문에
Disk io에 비례해서 CPU도 올라가는데,
SAS디스크를 끼우면 SCSI컨트롤러랑 디스크가 소통하고
SCSI컨트롤러는 CPU에서 단순 명령만 받기 때문에
CPU가 올라가지 않게 됩니다.
요즘은 보통 12~24장 정도의 HDD를 넣는 장비가 많아지므로
CPU가 직접 HDD랑 소통을 안하는게 좋습니다.
SSD를 샀는데 SATA라서 IO패턴에 따라서는
RAID로 구성된 SAS보다 더 느려지는 경우도 있죠.
물론 SATA SSD를 SCSI컨트롤러 위에 RAID구성하면
SCSI 컨트롤러가 중간에 패리티 계산, 저장 알고리즘
, 미러 등의 처리는 모두 하기 때문에
CPU부하도 줄어들기 떄문에
SATA를 쓰더라도 SCSI 컨트롤러를 좋은 걸 쓰고
RAID구성을 제대로 하면 충분히 도움은 됩니다.
즉, 이러한 하드웨어의 깊이 있는 지식이 없으면
왜 CPU가 낮은데도 대기가 생기는지에 대해서
알 수 없었던 내용이었던 거지요.
이렇게 문제점을 이야기 해서
개발팀에게 던졌습니다.
쿼리를 내가 만들어주는게 아니다보니
이젠 개발팀이 어떻게 수정해서
가져오는지를 봐야겠지요.
이번 일을 정리하자면
1. 한 페이지 로드할 때 5번 이상 DB호출 하지 말것.
만약 그 이상 할거면 뭉쳐서 받은 뒤에 흩어낼 수 있는지,
또는 HTML화 를 해서 유저는 못느끼게 DB콜을 줄일 것.
2. Where in 안에 1000개 이상의 데이터를 넣지 말것.
3. 템프테이블에 넣는 양도 최소한으로 할 것.
4. 디스크 종류만으로 CPU부하가 달라짐
SAS디스크는 CPU부하가 안올라가지만
SATA는 CPU부하가 올라감
이런 특징을 알고
비슷한 증상이 나왔을 떄
도움이 되셨으면 합니다.
댓글
댓글 쓰기