박지훈님 안녕하세요. 반갑습니다. 제이름은 김대우라고 합니다.
평소 볼랜드 포럼에 가끔 들려 눈팅도 하고 업계 뉴스나 보다 가다가
여기 자유게시판에서 박지훈님의 글을 보고 늦은 밤이지만... 토끼같은 와이프가
눈비비고 졸립다고 하지만 몇줄 적어 보려 합니다. ^_^
저는 개인적으로
http://sqler.pe.kr 이라는 SQL서버와 DBMS와 관련된 여러 생각을 나누는
작은 사이트를 운영하고 있습니다.
여러 DBMS 공부를 시작한건 98년이니 햇수로 7년이네요. 사이트 운영은 99년부터 했으니
6년이란 시간이 벌써 지났나 봅니다. 지나보니 빠른데 세보니 참 오랜 시간이군요. ^_^
LG텔레콤에서 SQL로 마이그레이션 한다는 말을 듣고 개인적으로는 흐뭇~ 합니다.
지훈님도 마찬가지시겠지만 자신이 가장 관심있는 솔루션이 잘된다는 이야기를 듣는 것이니까요.
그런데 지훈님 시스템에 약간의 문제가 있는듯 해서 짧은 경험이지만 제가 가진 지식을 나눠 드리려 합니다.
여러번의 SQL서버 관련 컨설팅 경험과 문제 해결, 기술 지원 경험이 있으니 믿어 주시길 바랍니다. ^_^
----------------------------------------------------------------
이 디비 서버는 제온 4CPU짜리 서버 한대에서 돌리는데, 바쁠 때는 1~2초 간격으로 두개 이상의 CPU가
100%를 칩니다.
데이터는 백만건 단위로 그렇게 대규모는 아니지만, 트래픽이 대단히 많고 시스템의 특성상 시간이 지날 수록 제곱배로
부하가 커질 수밖에 없는 구조인데요. 이미 4CPU로도 한계에 가까워지고 있어서 곧 디비 클러스터링을 고려해야 할 상황입니다.
----------------------------------------------------------------
우선 Clustering은 기본 컨셉이 High Availability를 위한 솔루션입니다. 만약 성능을 위한 선택이라고 생각 하신다면
조금 어색한 분위기가 될듯 합니다.
설마 Clustering의 개념을 잘못 알고 계신것은 아니리라 생각하며 아마 CPU 성능 이야기와
클러스터링 이야기를 별개로 위에 적으신 것이리라 생각합니다.
클러스터링은 알고 계신듯 하니 넘어갑니다.
CPU가 100을 친다면 왜 치는 것입니까? 그리 많지 않은 데이터에 제온 Quad CPU인데요?
제 소견으론 먼저 어떤 쿼리나 모듈이 CPU 100%을 치게 만드는지 원인을 분석하는게 급선무라 생각 합니다.
이렇게 해 보십시요.
SQL서버 툴들중 프로필러 툴 수행 -> 서버 선택 -> 템플릿을 SQL Profiler Tuning으로 선택
화일에 저장 선택후 화일 지정-> 데이터 탭에서 CPU, Reads, Writes, 필요시 Application Name, 또는 NT User Name 추가 -> 필요시 필터 탭에서 적절히 CPU에 대해서 수치 필터 지정
그럼 SQL서버에 전달되는 모든 쿼리와 저장 프로시져가 기록 됩니다. 여기서 딱 보시면
CPU가 높은 쿼리가 있을 겁니다. CPU가 높으면 당연히 Disk IO인 Read나 Write가 높을 겁니다.
1. CPU, Duration 높음 : 내부에 복잡한 수치연산, 다양한 변환 함수 튜닝 필요
2. CPU, Disk IO높음 : 대부분의 경우 잘못된 쿼리, 잘못 생성된 인덱스, 의도적인 Full scan 가능성
3. CPU와 Disk IO는 낮고 Duration이 길 경우 : 잠금, 트렌젝션 처리로 인한 일시적인 Blocking, 또는 DeadLock. 상호 상관되는 쿼리 확인후 잠금 처리 필요.
2번일 겁니다. CPU의 문제라기 보다는 개발자, 관리자의 잘못된 쿼리 수행과 인덱스 생성이 문제죠.
분석하는 방법은 헤아릴 수 없이 많습니다. Microsoft에서 무료로 제공하는 분석툴을 이용하면
위의 프로필러로 잡은 쿼리의 재현과 실행 계획도 그자리에서 통계까지 포함해 볼 수 있으며
위의 프로필러 작업도 스크립트로 사실상 백그라운드로 UI없이 저장 및 구성이 가능합니다.
하지만 분석을 하시기 위한 가장 쉬운 방법을 가이드 해 드리면
해당 쿼리를 클릭하면 아래 아래 창에 전체 쿼리가 뜹니다. 복사해서 쿼리 분석기에 붙이신후
컨트롤 + K를 눌러 실행계획을 표시하고 수행해 보세요.
실행계획 탭을 보시면 Index Scan, 또는 Full Scan이 있을 겁니다. 보시면 엄청나게 많은 데이터 PUMP가
일어나 있을 거구요. 자 문제를 찾았습니까? 혹시 어떻게 튜닝을 하는지도 알고 싶으시면 저에게 회신 주세요.
자~~~ 그럼 다음 이야기 전에 제가 지훈님께 질문 올리겠습니다.
쿼리가 빠르게 수행 안되는 이유는 누구의 잘못 입니까?
이쁜 김정은을 전화줄 "앙~~~" 물게하는 어디처럼 64 Way CPU에 수백기가 메모리를 꼽으면 그냥 빨라집니까?
지훈님 회사와 같은 갯수의 CPU로도 어디 사이트는 "안녕하세요. 배철숩니다" 광고까지 하면서
현재 수백억건의 데이터를 가지고 사용자를 끝도 없이 끌어 모으는 이유는 무엇입니까?
아니, 97년부터 테라데이터를 상업용으로 서비스해온 SQL서버는 1997년도에 펜티엄4 3Ghz CPU 였습니까?
정녕 CPU가 100%를 치는것이 SQL서버에 문제가 있는 것입니까?
지금 현업에서 실제로 서버를 운영, 개발하시는 지훈님의 생각은 어떠십니까?
다른 이야기는 접고 트랜젝션 처리 이야기로 넘어가 보시지요.
"설마 SET XACT_ABORT ON 이슈는 아니겠지...
설마... 그래도 사이트 주인장님에 프로그래밍 경력이 있지..."
지금 제 솔직한 심정은 트렌젝션에서 ACID는 기본 상식인걸 아신다는게 다행이라고 생각 합니다.
개념은 알고 계시다는 것이기 때문입니다.
일부러 제작사명 말하고 지훈님께 여쭤보겠습니다.
농심 짜짜로니 - 삼양 사천짜장 두개의 짜장라면이 있습니다.
옷~ 이게 웬걸 짜짜로니는 스프가 세개나 됩니다. 근데 젠장 사천짜장은 비닐에 든 짜장 스프뿐.
좀전에 야식으로 와이프와 아침에 눈 팅팅 붓게 될걸 각오하고 먹었습니다.
제 느낌에 짜짜로니~ 약간 느끼한게 바로 그 짜장맛입니다. 나름대로 제가 최고라 생각하는 짜장라면입니다.
사천짜장은 와이프의 Favorite입니다. 고소한게 짜짜로니보다 훨씬 낫다나요...
뭐 닭살이라고 하셔도 할말 없습니다.
여기서 질문 브레인~ 서바이버~~ 처럼 순식간에 후다닥~ 들어 갑니다.
"왜 짜짜로니는 스프가 세개인데 사천짜장은 하나 뿐입니까?"
제가 생각하는 최고의 대답- 우문현답은 이겁니다.
"만든 업체의 디자인 컨셉과 설계 철학이 틀리기 때문입니다. ~"
많은 "어플리케이션"에서 트렌젝션 사용은 이렇습니다.
//코드 시작
선언문~~
//예외 처리 모듈 선언
예외 처리 시작
//트렌젝션 시작
처리 모듈 1
처리 모듈 2
처리 모듈 3
//트랜젝션 끝
//성공적으로 완료시
모듈 10으로 이동
//실패가 하나라도 있을 때
에러 표시 후 모듈 9로 이동
//완료
모든 모듈이 완료해야 완료로 가며 1개라도 틀리면 오류로 가죠.
그렇다면 DBMS는 어떻습니까?
먼저 오라클에서 역시 위의 방법과 비슷합니다. 결론부터 이야기해
오라클은 Implicit Transaction 방식으로 INSERT, UPDATE, DELETE가 발생하면
"자동"으로 트렌젝션이 시작되는 방식입니다.
SQL Server는
Auto COMMIT 으로 불리는 방식을 쓰며 매 INSERT, UPDATE, DELETE 구문마다 처리 됩니다.
화끈한 이야기 들어 갑니다. 트렌젝션은 하나의 BATCH구문에서 최소한의
자원을 가지고 최소한의 시간동안 수행되는 것이 성능에 엄청난 영향을 줍니다.
SQL서버의 설계자는 이 컨셉을 지상과제로 설계 했으며 개별 데이터 수정 구문에 자동으로 COMMIT을 하게해
높은 완성도와 동시성, 성능을 보장합니다.
오라클의 잠금 철학은 또한 다음과 같습니다.
A사용자가 1을 2로 변경하면 A사용자의 세션에서는 1이 2로 메모리상에 Rollback segment와 함께
저장됩니다.
그럼 이때 B사용자가 들어와서 1을 읽으려 하면? 1이라는 Uncommited 값을 잠금 없이 바로 읽을 수 있습니다.
1을 3으로 바꾸려 하면? A사용자는 명시적으로 COMMIT을 하지 않았기 때문에
B사용자와 경합이 생기며 B사용자는 대기하게 됩니다. A사용자가 COMMIT 또는 ROLLBACK할때까지 대기하게 되지요.
여기서 다시한번 짜장라면을 생각해 봅니다.
왜 그렇게 만든 겁니까? 양쪽 모두 나름대로의 최선의 방법이라고 생각했기 때문 아닐까요?
SQL서버 개발팀의 설계가 그런 것입니다.
그럼 여기서 실질적인 문제를 짚어 봅니다.
2001년 7월에 제가 데브피아라는 곳에서 온라인 세미나를 진행하면서 사용한 구문 입니다.
----------------------------------------------------------------------------------------------------
--프로시져에서 트랜젝션 처리
USE pubs
GO
CREATE TABLE konan_TR_Test(
c1 int not null PRIMARY KEY
, c2 int
)
GO
--데이터 삽입
insert into konan_TR_Test(c1,c2) VALUES(1, 1) --성공
insertT into konan_TR_Test(c1,c2) VALUES(1, 1) --신텍스 에러
insert into konan_TR_TestTTT(c1,c2) VALUES(1, 1) --객체명 에러
insert into konan_TR_Test(c1,c2) VALUES(1, 1) --PK 제약 에러
--데이터 조회
select * from konan_TR_Test
--수행
BEGIN TRAN
insert into konan_TR_Test(c1,c2) VALUES(2, 2) --2라는 값 삽입
insert into konan_TR_Test(c1,c2) VALUES(2, 2) --2라는 값으로 PK오류 생성
insert into konan_TR_Test(c1,c2) VALUES(3, 3)
COMMIT TRAN
GO
--데이터 조회 - 결과는? 2는? 3은?
SELECT * FROM konan_TR_Test
--현업에서 문제.
--학생 테이블을 생성한다.
--drop table 수강
--drop table 학생
--drop table 과목
CREATE TABLE 학생(
학생ID varchar(10) primary key
, 학번 varchar(10) not null
)
GO
CREATE TABLE 과목(
과목ID varchar(5) primary key
, 과목명 varchar(20) not null
)
CREATE TABLE 수강(
수강ID int primary key
, 학생ID varchar(10) not null
FOREIGN KEY REFERENCES 학생 (학생ID)
, 과목ID varchar(5)
FOREIGN KEY REFERENCES 과목 (과목ID)
)
--학생 삽입
BEGIN TRAN
INSERT 학생 VALUES ('KONAN', '1111')
INSERT 학생 VALUES ('JOMIRYO', '2222')
COMMIT TRAN
--과목 삽입
BEGIN TRAN
INSERT 과목 VALUES ('공수', '공업수학')
INSERT 과목 VALUES ('디비', '데이터베이스')
INSERT 과목 VALUES ('게임', '게임프로그래밍')
COMMIT TRAN
--데이터 조회
select * from 학생
select * from 과목
BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수')
INSERT 수강 VALUES (2, 'KONAN', '디비')
INSERT 수강 VALUES (3, 'KONAN', '겜')
COMMIT TRAN
--조회
select * from 학생
select * from 과목
select * from 수강
--겜 과목은??
--데이터 삭제
delete from 수강
select * from 수강
--넣을 경우? - 에러
BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수')
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
INSERT 수강 VALUES (2, 'KONAN', '디비')
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
INSERT 수강 VALUES (3, 'KONAN', '겜')
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
--성공
print '삽입이 성공했습니다.'
COMMIT TRAN
GOTO END_BATCH
ERROR_TRANSACTION:
print '삽입 실패'
ROLLBACK TRAN
END_BATCH:
print 'BATCH가 끝났습니다.'
GO
--넣을 경우? - 성공
BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수')
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
INSERT 수강 VALUES (2, 'KONAN', '디비')
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
INSERT 수강 VALUES (3, 'KONAN', '게임') --게임임.
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
--성공
print '삽입이 성공했습니다.'
COMMIT TRAN
GOTO END_BATCH
ERROR_TRANSACTION:
print '삽입 실패'
ROLLBACK TRAN
END_BATCH:
print 'BATCH가 끝났습니다.'
GO
--데이터 조회
SELECT * FROM 수강
--만약 BATCH가 1000건이라면??
--테이블 데이터 삭제
delete from 수강
--새롭게 바뀐 처리.
SET XACT_ABORT ON
BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수')
INSERT 수강 VALUES (2, 'KONAN', '디비')
INSERT 수강 VALUES (3, 'KONAN', '겜') --에러
COMMIT TRAN
--데이터 조회
SELECT * FROM 수강
--새롭게 바뀐 처리.
SET XACT_ABORT ON
BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수')
INSERT 수강 VALUES (2, 'KONAN', '디비')
INSERT 수강 VALUES (3, 'KONAN', '게임') --정상
COMMIT TRAN
--데이터 조회
SELECT * FROM 수강
--아울러 ASP 또는 COM+에서 에러 처리 루틴
------------------------------------------------------------------------------------------------
자 스크립트 테스트 해 보셨습니까? 여기서 여쭤 봅니다.
SQL서버는 SET XACT_ABORT ON 구문을 이용해야만 합니다. 어떤 경우에 그렇습니까?
"Run-Time 오류"일 경우와 "분산 트렌젝션"일 경우에 필요합니다.
"Run-Time"이 뭡니까?
SQL서버는 빠른 속도로 쿼리를 처리하기 위해 파싱, 개체 검사, 실행
작업을 통해 수행됩니다. 트렌젝션의 처리시 만약 위의 에러 구문에서 보는 것처럼
PK제약 오류와 같은 Run-Time의 오류에 대해서는 처리를 안합니다.
사실상 SQL서버의 BEGIN TRAN은 트렌젝션 처리 모듈 시작 진입 포인트를 명시한다라는 의미입니다.
왜 그렇습니까?
오라클은 작업을 주욱~ 진행하다가 하나만 문제가 생기면 무조건 롤백합니다.
즉, 쿼리 수행 절차가 SQL서버와 틀린 방식으로 진행되며
오라클은 트렌젝션 처리를 진입 포인트가 아닌 선언문처럼 사용하는 개념입니다.
여기서 질문 하나 던집니다. 둘중에 뭐가 더 빠릅니까? 나중에 시간나면 해 보십시요.
방식이 틀려도 성능의 차이는 특수한 상황이 아닌 이상 크지 않습니다.
또 질문 하나 던집니다.
"SQL서버는 왜 그런겁니까?"
"설계의 컨셉이 그렇습니다."
"왜 오라클이나 SQL서버가 똑같지 않습니까?"
"볼랜드 C++ 빌더랑 Visual C++은 왜 틀립니까?"
그럼 박지훈님에게 제가 묻습니다.
모든 SQL서버 "기본" 책들에서 심도있게 다루는 트렌젝션과 잠금 처리.
SQL서버 개발을 하시면서 기초가 되는 책을 한권이라도 보셨다면 모르실리가 없다고 생각 합니다.
당연히 모두가 그렇게 쓰는 부분을 지훈님만 선언문을 빼고 쓰셨고 그걸 SQL서버의 문제 / 버그라고
하신 거군요.
지훈님이 보신다는 프로페셔널이라는 책은 제가 없어서 모르겠습니다만... Step by Step 책이나 기초 책은
안보시고 프로 책으로 넘어가신듯 하네요. 참 글을 읽으면서 궁금한건 도데체 한달동안 튜닝을 하셨다면서
이런 기초적인 부분은 하나도 안 보시고 도데체 뭘 튜닝 하신 것인지 궁금합니다.
그래도 끝까지 "사람을 당황스럽게 하는 SQL서버"라고 하시는걸 보니 개발자 기질과
엔지니어 기질이 보이시네요.
저역시 몇마디만 더 드리겠습니다.
SQL서버를 처음 공부할때 였을 겁니다. 당시 다양한 언어와 그 매력에 푹 빠져서
한참 재미있을때 SQL서버 초급 책을 두어권 끝까지 독파 했습니다.
샘플도 다 돌려 보고 프로그램에 붙여도 보고... SQL서버의 모든걸 안것 같았습니다.
그러다 MCDBA라는게 있다길래 껌이지~~ 하고 시험 공부 하는데
웬걸 난이도가 엄청난겁니다. 열심히 온라인 도움말을 보면서 공부했지만 떨어졌습니다.
부끄럽지만... 하지만 참 감사하게도... 덕분에 SQL서버의 깊이를 알게 되었죠.
SQL서버의 온라인 도움말을 파고 또 파면서 인덱스 부분과 이해가 안가는 부분은 프린트 해서 씹어 먹으면서
이 악물고 공부했습니다. 니가 이기나 내가 이기나 해 보자잉~~~
Microsoft의 KB를 보면서... 또 3rd업체의 SQL서버 관련 Tip들을 보면서
지훈님도 볼랜드 제품에 대한 매력을 느끼시는 것처럼 저역시 SQL서버의
매력에 푹~ 빠지게 되었죠.
덕분에 SQL서버 튜닝, 컨설팅도 해보고... 좋은 기회도 많았구요.
하지만 지금 또 SQL서버를 공부하면서 느끼는건 정말 깊이 들어갈수록 한도 끝도 없다는
것입니다. 특히나 최근 이기종간의 분산 트렌젝션 처리나 이기종 DBMS와 관련된 작업을
많이 하면서 더더욱 그런 느낌을 가지게 되었고 요즘엔 주변의 SQL전문가 분들을 보면서
세상엔 숨어있는 초고수님들이 이리도 많구나 하는 생각만 듭니다.
저역시 SQL서버라는 시스템에 열정이 있습니다. 지훈님도 그러시겠죠.
지훈님이 이렇게 멋지게 꾸며 놓으신 사이트를 보면서 저역시 지훈님의 열정을 잘 보고 있습니다.
제 사이트에서 나름대로 튜닝한 게시판 제작후 열심히 질문 답변도 달고, 강좌도 열심히 무료로
등록 같은 것 안해도 모두 볼 수 있게 무료로 아무런 대가 없이 적었고
더더욱 많은 분들에게 SQL서버의 여러 6만 2천건이 넘는 QnA를
좀더 많은 분들이 손쉽게 검색 할 수 있도록 하기 위해 QnA게시판의 내용을
로컬 오프라인으로도 검색 가능하도록 하는 SQLER 탐쉐이~ 라는 툴도 만들었습니다.
물론 무료죠. 등록도 필요 없습니다. 그냥 다운로드 해서 쓰세요.
MS가 지원 해 줬냐구요? 아니요~ 배너 하나 얼마얼마에~ 해준다는 말 한번도 없었습니다. ㅋㅋㅋ
MS가 지원해 준다고 해도 내가 안했을 겁니다. 그럼 MS에게 하고싶은 말을 제 웹사이트에서
조차 할 수 없었을 테니까요.
이런 무료 봉사 쌩 뻘짓거리를 왜 했냐구요? 그것도 내직장 다니며, 9시에 출근해 11시에 퇴근하면서
새벽까지 강좌쓰고 게시판에 답글달고... 누가 돈주는 것도 아닌데 왜 했냐구요?
이 참 잘 만든 SQL서버를 많은 분들이 너무 몰라 주셔서, 잘못알고 계셔서,
많은 분들이 단순히 MS에 대한 악감정과 억하 감정만을 가지고 접근 하셔서 제가 직접 만들었습니다.
지훈님께 부탁 드립니다.
아주 잘만드신 웹사이트입니다. 저역시 많은 업계의 정보와 볼랜드측 자료를 여기서 봅니다.
항상 감사 드리고 있는 그런 눈팅만 해온 사람입니다.
한마디만 드리자면 지훈님은 웹사이트의 주인장님이신 많은 분들이 주목하는 공인입니다.
저역시 지훈님의 웹사이트에선 한명의 정보를 얻어가는 손님일 뿐이구요.
부디 공인의 입장에서 치우침 없이... 여기 지훈님이 버그 덩어리라고 느끼시는 그 SQL서버에
대한 열정만 있는 제 마음도 좀 편하게 해 주시길 간곡히 부탁 드립니다.
읽어 주셔서 감사합니다. 혹시 기회가 되면 제가 잘익은 생삽겹살 구이에 참이슬 따라드리면서
이런 저런 이야기 나누고도 싶네요.
PS. SQL 서버를 운영하시면서 버그인것 같으시다면... 제게 메일, 게시판에 질문 주세요.
제가 처리 가능한 일이라면 해 드리고 다른 관련된 채널이 있으니 도움 드릴 수 있는 부분은 최대한
도움 드리겠습니다.
이만.
박지훈.임프 님이 쓰신 글 :
: 최근 며칠 사이에 MS SQL서버 2000의 버그로 강하게 의심되는 사례를 두차례나 겪어 글을 써봅니다.
: 마침 방금 올린 IT뉴스 기사중에도 LG텔레콤이 오라클에서 SQL서버로 마이그레이션을 시도한다는 기사도 있고 해서요.
:
: 제가 다니는 회사에서 사용하는 DBMS 제품이 나오는 관계로 회사가 어디인지 공개적으로 밝히진 않겠습니다.
: IT쪽 회사는 아니지만 해당 분야에서는 아주 잘 알려진 중견 기업이구요. 최근 오프모임에서 만난 분들은 물론 아시죠.
:
: 저희 회사에서는 전사적으로 MS SQL서버를 사용하고 있습니다.
: 파트별로 디비를 별개로 운영하고 있기 때문에 디비서버가 꽤 여러 대 되는데, 다 SQL서버를 돌리고 있죠.
: 그중에 제가 직접 관여한 시스템이 가장 많은 트래픽을 기록하는데요.
:
: 이 디비 서버는 제온 4CPU짜리 서버 한대에서 돌리는데, 바쁠 때는 1~2초 간격으로 두개 이상의 CPU가 100%를 칩니다.
: 데이터는 백만건 단위로 그렇게 대규모는 아니지만, 트래픽이 대단히 많고 시스템의 특성상 시간이 지날 수록 제곱배로
: 부하가 커질 수밖에 없는 구조인데요. 이미 4CPU로도 한계에 가까워지고 있어서 곧 디비 클러스터링을 고려해야 할
: 상황입니다.
:
: 사실 저는 디비 전문가는 전혀 아니지만, 그동안 개발을 하면서 당연히 디비쪽도 이래저래 꽤 다루기도 했고요.
: 또 어떻게 하다보니 이 회사에 입사하고 처음 한 일이 SQL서버 튜닝이었는데, 입사후 거의 한달 가까이 디비 튜닝, 특히
: SQL서버 튜닝에 대한 관련 자료들을 뒤적거려서 성공적으로 튜닝을 마쳤습니다.
: 그러니까 디비쪽으로 DBA 수준의 아주 전문은 아니어도 알만큼은 알고 다뤄볼 만큼은 다뤄본 정도라고 할 수 있지요.
:
: SQL서버의 버그로 의심되는 문제가 발생한 것은, 고객 정보를 DB에 등록하는 루틴에서였습니다.
: 고객 정보를 등록할 때 동시에 입력되는 정보가 꽤 많기 때문에 여러 테이블.. 대략 10개 이상의 테이블에 한방에
: 인서트를 합니다. 당연히 트랜잭션을 먼저 걸고 인서트 쿼리들을 날린 후에 커밋을 하지요. 그리고 트랜잭션의 특성상
: 어떤 에러라도 발생하면 전체 트랜잭션내의 모든 쿼리가 취소되지요.
:
: 며칠전.. 그러니까 지난주 금요일부터 갑자기 고객 정보 입력을 하는 ASP 페이지에서 에러가 나면서 입력이 안되는
: 문제가 발생했습니다. 입사한지 얼마 안되는 제가 알기로도 1년 이상 아무 문제없이 써오던 부분이라 꽤 당황스러웠죠.
: 하루에도 수십명씩의 고객을 등록해야 하는데, 입력 작업이 장난아니게 노가다이기 때문에 며칠 정도만 입력이 미뤄져도
: 감당 못하게 입력할 데이터가 쌓이고, 여러가지로 문제가 복잡해집니다.
:
: 보통 오랫동안 잘 동작하던 부분에서 문제가 생기면 어디서부터 뒤져봐야 할지 난감해지잖습니까.
: 월요일에야 문제를 찾았는데요. 문제의 직접적인 원인은 트랜잭션의 롤백이 제대로 이루어지지 않은 거였습니다.
:
: 에러가 발생하기 시작한 시점의 직전에 고객 데이터를 입력하다가 에러로 롤백된 데이터가 있었나보더군요.
: 당연히 고객 정보 전체의 마스터 테이블에서 고객 번호를 딴 후에 그 고객번호로 디테일 테이블에 차례로 데이터를 넣는
: 흐름으로 처리되게 되어있는데, 마스터를 포함한 다른 모든 테이블의 레코드들은 모두 롤백되어 사라졌는데, 세번째
: 디테일 테이블에 추가되었던 레코드가 지워지지 않고 남아있더군요.
:
: 게다가 하필이면 찌꺼기 레코드가 남은 이 디테일 테이블이 마스터 테이블과 1:1 관계에 있는 테이블이었습니다.
: 다시 말해서 마스터 테이블의 PK는 고객번호인데, 이 디테일 테이블의 PK도 고객번호로 되어 있다는 얘깁니다.
: 테이블의 PK인 고객번호 필드에 연결되어 있습니다.
:
: 고객번호를 딴다는 표현에서 짐작하셨겠지만 이 고객번호가 일련번호로 되어있습니다.
: 그러니까 새로 고객을 등록할 때마다 1씩 증가하는 새 번호를 만드는 건데요.
: 먼저 마스터테이블에서 번호를 따고 마스터 테이블에 1:1로 연결된 디테일 테이블에 새 레코드를 인서트하려고 시도할 때,
: 이미 해당 고객번호의 레코드가 있기 때문에 트랜잭션이 커밋되지 못하고 매번 롤백되어버린 거였습니다.
: 마스터테이블에는 해당 고객 번호가 없고 디테일에는 남아있는 상황이죠.
:
: 아시다시피 MySQL과 같이 기본적으로 트랜잭션을 지원하지 않는 디비를 제외하면, 데이터베이스에 있어 트랜잭션의
: 특성은 기본중의 기본입니다. ACID라고 해서 4원칙도 있잖습니까. 그런데 MS가 그렇게 안정적이라고 장담에 장담을 하는
: SQL 서버에서 롤백된 데이터중 일부가 지워지지 않고 남아버리는 어처구니 없는 상황이 벌어져서 며칠동안 대혼란이
: 일어난 겁니다.
:
: 이쯤 되면, SQL 서버를 상당기간 잘 써오신 분들은 제 말에 의심부터 드시겠지요.
: 정상적인 입력작업이 아닌 경로로 추가된 레코드가 아닌가? 라든지 말이죠.
:
: 그런데 이 DB 서버는 웹서버 머신과 저희팀의 네명의 PC 외에는 접근조차 할 수 없도록 IP가 막혀 있고요.
: 또 저도 설마, 혹시나, 하면서 저희 시스템의 전체 소스를 다 뒤져서 이 테이블에 인서트를 하는 다른 루틴이 있나 다시
: 찾아보기도 했습니다만 전혀 없었습니다. 게다가 남아있는 찌꺼기 레코드의 데이터와 입력되지 못하고 대기중이던
: 고객정보 문서들을 대조해본 결과 대기중이던 문서들중 하나에 그 레코드 내용들이 있더군요.
:
: 결과적으로 다르게 어떻게도 변명할 수 없는, SQL서버 자체의 버그가 아니면 있을 수가 없는 문제입니다.
:
: 게다가, 불과 이틀 후에 같은 문제가 다시 재발했습니다. 바로 며칠전에 겪은 일도 있고 해서 입력 에러가 생기기 시작하자
: 마자 바로 찌꺼기 레코드가 있나 찾아봤는데, 역시나 있더군요. 그런데 황당하게도 며칠전에 문제를 일으켰던 것과 똑같은
: 디테일 테이블에 똑같은 형태로 레코드가 남아있더라구요.
:
: 같이 인서트되는 테이블이 디테일 테이블이 열몇개나 되는데 불과 며칠 사이에 유독 이 테이블에만 두차례나 문제가
: 생기는 것이 신기하고 황당해서, 다른 테이블과 다르게 설정된 특이한 점이 있나 해서 이래저래 뒤져봤는데요.
: 한가지 차이점이 있었습니다. 실수로 FK가 안잡혀있더군요. 구조상 FK를 잡아줘야 하는데, 그걸 빼먹은 겁니다.
: 다른 테이블에는 다 잡혀있었고요.
:
: FK가 안잡혀있는 것은 분명히 실수이긴 하지만, FK가 안잡혀있다고 해서 롤백이 제대로 되지 않아서는 안되죠?
: 이번 케이스의 경우 서로 연관된 테이블들을 함께 인서트하는 거지만, 디비를 다루다보면 연관성이 없는 레코드들도
: 하나의 트랜잭션 안에서 커밋/롤백하는 경우도 종종 생깁니다. 트랜잭션의 All or Nothing 원칙에은 FK가 잡혀있건
: 안잡혀있건 무관하게 무조건 지켜져야 하는 겁니다.
:
: 아마도, 어떤 복잡하게 꼬인 조건에만 발생하는 버그가 있어서 그 조건중의 하나로 FK가 안잡혀있다는 조건이 포함되는
: 것 같습니다. 앞에서도 말했듯이 다른 경로로 이 찌꺼기 레코드가 들어갔을 가능성은 전무한 상태이므로 SQL 서버의
: 버그가 아니라면 있을 수가 없는 일이지요.
:
: 다시 한번 확인할 기회를 갖기 위해..
: 그 디테일 테이블에 FK를 잡지 않고 그대로 뒀습니다. 불과 3일만에 두번이나 발생한 문제이므로 다시 발생할 가능성이
: 높겠죠. 세번째까지 확인한 후에 FK를 잡아놓고 그 이후에 더이상 발생하지 않는다면 SQL서버의 버그일 가능성이
: 거의 틀림없겠죠.
:
: 하지만 FK를 잡기 전에 다시 발생하지 않는다거나 FK를 잡은 후에도 발생한다고 해서 SQL서버의 버그가 아니라고
: 할 수도 없습니다. 이런 종류의 버그는 발생하는 패턴을 잡아내기가 대단히 힘드니 예상한 방식으로 다시 나타나지
: 않을 가능성도 충분히 높죠. 그리고 이미 정황상 SQL서버의 버그가 틀림없어보이기도 하고요.
:
: 물론, 대부분의 다른 사이트에서는 잡음이 들리지 않는 걸로 봐서(들리지 않는다고 발생하지도 않는 것은 아니지만)
: SQL서버를 채택한 기업들이 적어도 그럭저럭은 쓰고 있다는 말일텐데요. 그렇다고 안심할 것이 못됩니다.
: 저희 회사에서 발생한 이번 건의 경우 그나마 고객 정보 입력 단계에서 발생한 문제여서 그렇게 시분초를 다툴 정도의
: 큰 문제는 아니었지만, 이런 문제가 만약 1초 1초가 급한 업무에서 발생해버린다면?
: 그야말로 대란이 일어나는 거지요 뭐.
:
: 바로 이런 면 때문에 기업에서 미션 크리티컬한 업무에는 "충분히" 검증되지 않은 DBMS를 쓰지 못한다는 건데..
: 아직 SQL 서버는 충분히 검증되지 않은 상태인데 시스템 구축시에 SQL 서버를 채택한 것이 성급했다고 할 수 있죠.
:
: MS에서 SQL서버는 사실상 7.0에서 완전히 새로 개발했다고 그렇게 자랑을 해대는데...
: 거꾸로 말하면 7.0이 출시된 98년인가 이후로 불과 6년 정도의 검증기회밖에 없었다는 얘기가 되죠.
:
: 어쨌든... 저희 회사에서는 현재 오라클로 마이그레이션할 것을 진지하게 고려하고 있습니다.
: 사실 오라클 마이그레이션 외에 다른 대안이 없죠.
