수안이의 컴퓨터 연구실
정원혁
필라넷 이사
중소기업에는 별도로 DBA가 없는 것이 보통이다. 그 중에 나은 개발자가 DBA로 승격되는 경우가 대부분이다. 하지만 DBA의 역할만 수행하는 것이 아니기에 부족함이 많다. 5년 개발 경력의 이수미씨와의 대화 주제는 효과적으로 SQL 서버를 운용하기 위해 무엇을 갖춰야 하는가에 대한 것이다. 이 글을 통해 자신의 모습은 어떠한가를 생각해보며 DB 전문가로 가는 발판을 만들어보기 바란다.
필자는 여행을 무척 좋아한다. 거의 20개국 정도를 가봤는데 대부분의 여행은 가고 오는 항공편만 확보해두고 계획 없이 떠나는 경우가 많았다. 필자는 그런 계획 없는 여행을 좋아한다. 하지만 홀로 떠나는 것이 아닌 여행, 즉 이수미씨가 동반자(혹은 손님이고 필자가 가이드)이기에 이번 여행은 약간의 계획이 필요했다.
어디로 여행을 떠날 것인가?
이수미씨는 의욕왕성한 경력 5년차의 개발자였다. 그래서 이번 여행에서 가고 싶은 곳이 무척 많았고, 여행 계획서는 방대했다. 하지만 급히 가서 사진만 찍고 다른 여행지로 떠나는 그런 여행보다는 좀 더 차분히 여행을 할 수 있도록 수정했다.
이수미씨의 현재 수준은 이제 막 DBA로 발걸음을 내딛는 상태로, 회사에서 일을 맡겨서 시작한 똑똑한(?) 개발자였다. 수미씨의 목표는 세계일주였지만 아직은 그럴 상황이 아니어서 우리는 세계 일주를 향한 걸음마로서 첫 해외여행을 목표로 했다. 특히 수미씨는 오라클을 쓰다가 SQL 서버로 넘어왔으며, 그래서 특히 SQL 서버가 가진 독특한 특성들에 대해 잘 모르고 있었다. 따라서 이런 부분에 초점을 맞췄다.
맨 처음 수미씨는 테이블 디자인부터 질문을 해왔지만, 이번 여행에서 테이블 디자인을 주로 다루는 팀도 있고 이것만 다루기에도 워낙 방대한 주제이기에 디자인에 대해서 특별히 문제가 있는지만 보고 넘어갔다. 그리고 현재 사용되고 있는 서버의 상황을 살펴보기로 했다. 여기서 발생되는 문제의 유형에 따라 다음 목적지를 변경할 수 있도록 했다. 하지만 흔히 발생하는 동일한 문제가 나올 것이라 예측했는데(실제로 이런 문제들이 나타났다), 그래서 그 다음으로 SQL 서버를 사용할 때 가장 흔히 부딪히는 문제들인 잠금, 차단, 클러스터 인덱스의 특징, 프로필러라는 매우 유용한 도구의 활용에 대해 다루는 것을 이번 여행의 목적지로 정했다.
가볍게 짚고 넘어가는 테이블 디자인
첫 스타트를 하려고 하자 수미씨는 무척 힘들어했다. 그래서 필자는 ‘문제 접근방법’에 대한 조언을 해줬다(<박스> 참조). 이런 상황에서 우리는 먼저 간단히 주로 사용되는 테이블에 대해서 디자인 이슈가 없는지 살펴보기로 했다. 회사에서 사용하는 가장 핵심이 되는 테이블은 다음의 스키마와 일부 데이터만 살펴봤다. 필자는 이에 대해 주요 테이블의 생성 스크립트와 BCP out 데이터를 보내달라고 했다.
우선 수미씨의 질문은 “왜 하필 bcp로 보내야 하는가”란 것이었다. “테이블 수가 몇 개 안 되어 쿼리 분석기에서 select 문을 써서 추출했다. 내가 아는 범위에서는 BCP로 추출해도 테이블을 하나씩 지정해 추출해야 하므로, 손에 익은 방법이 빠를 것 같아 select 문으로 추출했는데, 이 방법에 문제가 있는가?”라는 질문이었다.
BCP를 갖고 추출해 달라고 한 것은 스크립트화할 수 있기 때문이다. 손에 익은 select로 추출했는데 그렇게 하면 테이블이 많아질 때 자동화하지 못하고 수동으로 계속 작업을 해야 한다. 꼭 BCP를 쓰지 않더라도 SQL 서버의 DTS를 활용할 수도 있다. 하지만 이때도 가능한 패키지를 저장해 두고 똑같은 일이 반복될 때 시간을 줄일 수 있는 가능성을 남겨두어야 한다.
DBA가 가져야 하는 중요한 자세 중 하나는 자동화와 반복성을 가질 수 있는 방법의 개발이다. 자주 반복된 작업을 해야 하기 때문에 손에 익었다는 이유로 편한 방법을 쓰다 보면 나중에 그런 작업을 300번 반복해야 하는 경우가 발생하고, 그때의 생산성이란 이루 말할 수 없이 나빠진다. 뿐만 아니라 오류가 발생할 수도 있다는 것이 더 큰 문제다. 그래서 자동화된 스크립트를 생성해서 사용하고 필요에 맞게 고쳐 쓰는 것이 몸에 배야 한다고 생각한다. 수미씨의 회사 테이블에서 가장 많이 사용하는 테이블 스키마와 일부 데이터 그리고 인덱스는 <표 1>, <표 2>와 같다.
<표1>주요 테이블의 스키마
cafe_info
Cafe_ID(PK) int
Cafe_Name varchar
Cafe_Master varchar
Cafe_Class smallint
Cafe_Small_Content varchar
Cafe_Content varchar
Cafe_Main text
Cafe_InsertDay smalldatetime
Cafe_Permit bit
Theme_No int
Cafe_Status smallint
Group_No int
FilePath varchar
Ranking int
New bit
cafe_member
Member_Name varchar
wDate datetime
Years_old smallint
Address1 varchar
Address2 varchar
Address_Permit bit
PostNo char
Job_Name varchar
Job_Name_Permit bit
Introduce text
TelePhone_Permit bit
HandPhone_Permit bit
cafe_free_board
Cafe_ID int
Num int
Writer varchar
Subject varchar
Content text
Ref int
Re_Step int
Re_Level int
UserID varchar
wDate datetime
visited int
cafe_member_class
Member_Class smallint
Member_Class_Name varchar
cafe_group
Group_No int
Group_Name varchar
Group_Content varchar
Insert_Day smalldatetime
Group_OrderNo smallint
<표 2>인덱스
PK_Cafe_Group clustered, unique, primary key Group_No
Cafe_Free_Board_Index_1 nonclustered, unique Cafe_ID, Num(-)
Cafe_Free_Board_Index_2 nonclustered, unique Cafe_ID, Num(-), Ref, Re_Step, Re_Level
PK_Cafe_Free_Board clustered, unique, primary key Num
IX_Cafe_Info nonclustered Group_No
PK_Cafe_Info clustered, unique, primary key Cafe_ID
PK_Cafe_Member_Class clustered, unique, primary key Member_Class
아쉬운 부분은 인덱스
현재의 테이블들은 일단 대충 봐서 정규화 관점에서는 필자가 봤던 웬만한 중소 규모의 업체에서는 탁월하게 잘 된 테이블 디자인이라고 할 수 있다. 보통 중소 규모의 기업에서 테이블 디자인이란 것은 PK(기본 키)도 없거나 인덱스가 하나도 없거나 자료형은 모두 varchar이거나, 3 정규화 위반은 물론이요, 1 정규화 위반에 이르기까지 거의 엉망에 가까운 것이 보통이다. 이에 비하면 자료형도 적절하게 잘 사용하려고 한 노력이 보이며, 정규화도 무척이나 노력해서 한 흔적이 보인다. 특히 Bit, smallint, smalldatetime 등의 자료형을 적절하게 잘 썼다. 작은 자료형을 사용하는 것은 데이터가 늘어났을 때 성능에 영향을 미치는 요소가 되기 때문이다.
눈에 띄게 아쉬운 것은 인덱스 부분이었다. 특히 cafe_member는 아예 인덱스는 물론이고 PK가 없었는데 혹시 스크립트 생성 과정에서 실수한 것은 아닌지 한번 확인해 봤으나 확실히 없었다. 따라서 (Cafe_ID, Member_ID)를 PK로 잡기로 했다. 테이블에 PK가 없다는 건 정말 특별한 상황 아니면 대단히 중대한 실수다. 많은 사람들이 성능을 외치지만 정작 중요한 것은 그 이전의 기초다. PK가 없으면 중복데이터가 들어오게 되고, 그러면 나중에 그 중복 예외 처리를 하려고 프로그램을 짜는 게 복잡해지고 프로그램들은 결국 꼬이기 시작한다. FK(참조 키)가 없어도 마찬가지이다. 불행하게도 수미씨 회사의 테이블들은 FK가 없었다. 그럼 엉뚱한 member_id를 가진 데이터가 주문을 하는 등 문제가 생기고, 나중엔 결국 outer join을 사용해 엄청 성능이 느린 쿼리를 작성하게 된다.
그러니 아무리 강조해도 지나치지 않는 것이 데이터 무결성이다. 혹시 나중에 데이터 웨어하우징을 하거나 데이터 이전 작업을 해보면 실감하게 되니 지금부터 일찌감치 무결성 확보를 위한 노력을 기울여야 할 것 같다.
주요 테이블에 FK를 설정하라!
그래서 수미씨에게 주요 테이블에 FK를 설정해 보도록 숙제를 냈다. 시도해보면 바로 오류가 발생할 것이다. 거의 엉터리 데이터가 있을 거니까(아니라면 정말 축하할 일이지만). 이로 인해 개발자들이 아우성을 쳐도 미래를 위해 고집스럽게 FK와 check constraint와 같은 제약을 설정해 볼 것을 권장한다. 진행과정에서 역시나 PK를 거는 데 중복오류가 발생했다. 이런 경우 삭제는 어찌하는가 물어왔다. 물론 질문하곤 바로 답을 찾아내어 자문자답했다. 자문자답하는 동안 필자는 다음과 같은 방법을 제시했다. “몇 가지 삭제 방법이 있는데 일단 먼저 중복된 데이터를 찾아야 한다. 스스로 한번 풀어보라.”
“키 값에 대해 group by를 하고 그것의 개수(count(*))가 1보다 큰 것은 중복된 키 값이다.”
이렇게 중복된 데이터를 모두 찾아내서 지우면 된다. 중복된 것을 지우는 것도 양이 많다면 약간의 트릭을 쓸 수 있을 것이다.
- 중복데이터를 임시테이블에 넣고
- 최상위 한 행만 남기고 나머지는 지운 다음
- 원 테이블에서 모든 중복 행을 지우고
- 임시 테이블 데이터를 원 테이블에 넣는다.
서버에서는 무슨 일이 벌어지고 있을까?
테이블에 대한 검증이 끝나자 우리는 다음 목적지를 향해 떠났다. 실제 서버에서 어떤 일들이 벌어지고 있는지 ‘관찰(monitor)’해 보는 것이다. 현재 상황을 알아내는 것은 매우 중요하기 때문이다. DBA가 해야 하는 중요한 일은 성능을 향상시키는 것이지만, 이를 위해서 먼저 현재 상황을 알아야 하는 것이다. 따라서 우리는 프로필러라는 좋은 도구를 사용하기로 했다. 물론 윈도우의 성능을 모니터하기 위해서는 성능 카운터라는 좋은 도구가 있지만, 이번 여행에서는 제외시켰다. 너무나 방대하기 때문이다.
프로필러
그럼 이제 정말 이 테이블들에 인덱스가 제대로 사용되고 있는지 검증해보기 위해 프로필러를 사용할 줄 아는지 확인해 봤다. 안다고 해서 20MB 정도 분량이 될 때까지 필자가 주로 사용하는 템플릿을 보내주고 이를 사용해서 추적 데이터를 받아 보내주도록 했다. 그걸 갖고 인덱스가 제대로 위의 테이블에 만들어져 있는지 검증해 보도록 했다.
수미씨는 프로필러가 특정한 옵션을 주지 않았는데, 5MB씩 잘라 저장이 된다고 답해왔다. 또한 커뮤니티 관련 테이블(앞서 디자인에서 살펴본 주요 테이블)만 분석하려고 하니 따로 선택하는 옵션이 없어 전체 데이터베이스(정확히는 전체 서버)에 대한 추적으로 생성됐다고 하면서 부분 분석 옵션 기능이 있는지 물어왔다.
프로필러는 디폴트로 5MB가 되면 자동적으로 rollover가 된다. 그래서 처음 5MB가 차면 xxxx(제일 처음 준 파일이름).tr1, tr2, tr3 이런 식으로 파일명이 생성된다. 혹 실수로 추적을 중지하는 것을 잊으면 무제한의 파일이 쌓이게 되므로 주의해야 한다.
그래서 추적 중지 시간 설정이란 것이 필요하다. 또한 해당하는 테이블만 추적할 기능이 있기는 하지만, 임의의 쿼리(ad-hoc query)일 때만 가능하다. 프로시저라면 어렵다. 왜냐하면 프로시저 안에 테이블 이름이 포함되어 있고, 이를 프로필러가 알 수 있는 방법이 현재로는 없기 때문이다. 임의의 쿼리라면 프로필러에 필터라는 기능을 사용해 textdata에 필터를 걸면 된다. 이때는 와일드카드를 지원하므로, ‘%원하는테이블%’와 같이 설정하면 되지만 권장할 만한 방법은 아니다. 이를 필터하기 위해서 별도의 부하기 걸리기 때문이다. 차라리 추적을 받은 후 나중에 걸러내는 게 더 쓸만한 방법이다.
프로필러 분석1 - 차단
추적을 받아 봤더니 필자가 보낸 템플릿을 사용하지 않았다. 그래서 템플릿을 사용하는 이유에 대해 설명했다. 템플릿을 사용하는 이유는 추적 자료를 받고 나서 일관된 형식으로 보고 분석하고자 하는 목적도 있고, 또한 처음에 받을 때 불필요한 것들을 받지 않으려는 목적도 있다. 거듭 강조하지만 반복되는 일들에 대해 불필요한 ‘노가다’를 줄이는 것도 DBA 혹은 엔지니어들이 해야 할, 아니 모든 인생살이에서 해야 할 중요한 일이다.
<화면 1>을 보면 불필요하게 연결 속성, 로그인, 로그아웃, 애플리케이션 네임, 유저 네임 등이 잡혀 있다. 보통은 이런 것들이 필요 없다. 특별히 무슨 문제가 있어 일부러 그것들을 살펴보지 않는 한 공부를 위해서라도 필자가 보낸 템플릿을 사용해 한번 더 받아보도록 하자. 그 결과를 <화면 1>과 비교해보자. 항상 동일한 순서와 이벤트들이 캡처되며 따라서 이를 갖고 처리하는 다음 과정의 일들도 자동화될 확률이 높아진다.
<화면 2>를 보면 현재 exec sp_cursorfetch 180150000, 16, 4, 1이란 것들이 나타나는 것을 볼 수 있다. 이것은 클라이언트가 서버 측 커서를 사용하기 때문이다. 물론 장단점이 있다. ASP, VB 등의 프로그램에서 처음 연결 속성에서 이것을 정의해주게 되어 있는데, 디폴트로 서버 측 커서를 사용하게 된다(물론 클라이언트 속성에 따라 디폴트가 바뀌기도 한다). 꼭 그렇게 해야 하는 경우가 아니라면 불필요한 CPU 부하와 네트워크 부하를 걸기 때문에, 서버 측 커서를 클라이언트 측 커서로 바꿔야 한다. 추적 파일을 열어보면 50% 이상이 이들로 채워져 있음을 쉽게 발견할 수 있다. 레코드셋 페이징을 위해 커서를 사용한 부분인 것 같은데, 선언을 안 하면 디폴트로 adUseServer(서버 측 커서)가 선언이 되니 adUseClient(클라이언트 측 커서)로 선언을 변경해주면 된다. 또한 <화면 2>에서 선택된 라인을 보면 다음 쿼리가 수행됐다.
앞에서 눈여겨 본 테이블은 아니지만 게시판이라는 것을 쉽게 알 수 있다. 자주 사용하는 테이블, 쿼리일 것이라고 추정한다. 그런데 특이한 것은 Reads가 0(페이지)인데 걸린 시간은 무려 1236ms, 즉 1.2초가 걸렸다. 정말 0인지는 확인해볼 필요가 있는데 추적 자료의 거의 모든 데이터가 0으로 나타나는 것으로 봐서 SQL 서버 추적에 문제가 있는 듯하다(가끔 이런 문제가 생기기도 한다). 어찌 되었건 단 하나의 값을 가져오는 쿼리가 이 정도의 시간이 걸린다는 것은 무지 느린 것이고(제대로 된 추적이라면), 읽기가 0이라는 것에서도 이 쿼리는 거의 10ms 이하가 걸려야 정상이라고 할 수 있다. 따라서 이 쿼리는 전형적인 차단(blocking) 현상이라고 할 수 있다. 차단을 피하기 위해서는 어찌 해야 할까? 이것이 게시판 테이블이라는 것을 염두에 두고 생각해보자.
<화면 3>과 비교해보면 확연히 차이가 난다. 즉, member, cafe_member 등의 테이블이다. 수미씨에게 걸린 시간이 거의 0 혹은 16ms 이하인 것과 비교해 보라고 주문했다. 그리고 <화면 4>는 비정상적으로 보이는 쿼리다. cafe_group 테이블에서 가져오는데 한 번에 다 가져오면 될 텐데 왜 저런 식으로 10번에 걸쳐 가져올까? 이런 쿼리가 자주 반복되는 것을 볼 수 있다. 뭔가 프로그램을 잘못 짠 듯하다. 문제되는 쿼리를 수미씨가 찾아보니 동호회 상단의 각 동에서 현재 위치를 표시해주기 위한 쿼리였다. 소스가 좀 이상하게 구성되어 있어 검토 후 수정하기로 했다.
수미씨는 새로 추적을 받았다. 지난 번에는 최대 파일 크기를 5MB로 설정하고 ‘파일 롤오버 사용’ 옵션을 사용했고, 이번에는 최대 파일 크기를 20MB로 설정하고 ‘파일 롤오버 사용’ 옵션을 사용하지 않았다. 무제한의 파일이 쌓이는 것을 방지하기 위해 추적 중지 시간도 설정했다. 추적 속성에 대해 좀 더 이해하기 위해 SQL 서버 온라인 설명서(BOOKS ONLINE)를 찾아봤다. 필자는 이에 대해 칭찬을 아끼지 않았다. 온라인 설명서는 매우 도움되는 가장 기본적인 참고서이다. 필자는 한때 “온라인 설명서를 첨부터 끝까지 다 읽어보리라”는 결심을 했는데 너무 빨리 새 버전이 나오니 어림도 없었다. SQL 서버 2005가 그나마 가장 오래 끌고 있는데, 요즘은 다른 일들로 바빠서 다 읽을 엄두는 못 내고 틈틈이 참고하고 있다. 뉴스 그룹에 올라오는 질문에 대한 답변의 상당 부분은 “readme.txt를 읽어봐라”, “온라인 설명서를 읽어봐라”이다. 그만큼 중요한 것이니 자주 살펴봐야 한다. 필자 역시 SQL 서버 서비스 팩 3를 설치할 때 문제가 생겨서 질문했는데, 답변이 “readme.txt를 읽어 봐라”였고 읽어보니 정말 있었다. 조금은 창피했다.
차단과 잠금 힌트
다음의 쿼리 구문의 블로킹 현상을 피하는 방법은 잠금 힌트를 사용하는 것이다.
잠금 힌트를 사용하는 방법은 SELECT, INSERT, UPDATE, DELETE 문에서 테이블 이름 뒤에 잠금 힌트를 지정하는 것이다. 다음과 같은 방법이 가능하다.
◆ 방법 1 : 저장 프로시저의 상단에 다음의 구문을 선언하면 매 쿼리의 테이블마다 잠금 해제(read uncommitted)를 주지 않고 세션 전체에 영향을 준다.
Ex) set transaction isolation level readuncommitted
◆ 방법 2 : 차단당하는 쿼리문에 힌트(rad uncommitted)를 삽입한다.
하지만 <방법 2>를 쓸 때는 매 테이블마다 잠금 힌트를 써야 한다.
정확한 잠금 힌트의 사용은 트랜잭션의 수행 속도를 향상시킬 수 있다. 단 잠금 힌트는 잘 사용하면 참으로 좋지만 잘못 사용하면 역효과가 날 수도 있다. 즉 트랜잭션의 일관성을 깨지게 한다.
“NOLOCK과 ReadUncommited의 차이점은 무엇인가?”라는 질문을 흔히 한다. NOLOCK과 ReadUncommited는 차이가 없다. 과거에는 NOLOCK이라는 것만 존재하던 시절이 있었다. 이제는 ReadUncommited가 정식 문장이고 이것은 ANSI 호환이 되는 문장이므로 다른 데이터베이스 엔진에서도 사용되는 문장이다. 그래서 가능하면 ReadUncommited를 써야 한다.
말이 나온 김에 다음과 같은 문장은 어떤 결과를 가져올까? 실제로 어떤 사이트에서 실수로 작성된 문장에 이런 코드가 있었고, 웃음이 나오는 결과가 벌어졌다. 무엇이 문제일까?
답을 아는 사람은 지금 빙긋이 웃을 것이다. 어떤 회사에서 실제로 이렇게 써 놓고는 왜 이게 차단이 될까 고민했다가 나중에 훈수를 둔 사람에 의해 발견하고는 다들 실소를 금치 못했다. 훈수 둘 때는 원래 잘 보이는 법이다. 이 답은 WITH (NOLOCK) 또는 (NOLOCK)이라고 했어야 하는데 그냥 NOLOCK이라고 했기 때문에 테이블의 별칭으로 인식했다. 참고로 SQL 서버 2005에서는 with가 없는 (NOLOCK)은 지원되지 않는다. 그러니 이젠 모든 코드에 with (NOLOCK)처럼 WITH를 꼭 붙여야 한다.
프로필러 분석2 - 인덱스
수미씨가 보내준 프로필러 자료를 테이블에 넣었다. 그리고 그걸 갖고 분석을 해봤다(긴 과정이라 줄여서 요약을 한다). 궁극적으로는 수미씨(뿐만 아니라 모든 DBA)도 이런 과정을 배워서 앞으로 혼자 튜닝을 해나가야 한다. 프로필러 자료를 프로필러에서 그대로 볼 수도 있지만 이것을 SQL 서버에 테이블로 저장해 분석할 수도 있다. 이렇게 하면 우리가 좋아하는 쿼리 문장으로 분석 작업을 할 수 있어서 훨씬 수월해진다. 이 상세한 과정은 생략한다. 해보면 그다지 어렵지 않다는 것을 금방 알게 되고, 각자의 분석 능력과 응용 능력에 따라 활용도는 달라진다.
쿼리 분석기에서 CPU를 가장 많이 차지한 것을 살펴봤다. 최고로 많이 걸린 것이 4초(4781ms)이고, 그 나머지는 300ms 이하니까 양호한 상태라고 할 수 있겠다. 그렇다고 방심할 수는 없다. 가장 문제가 되는 쿼리 두 개만 찾아봤다. Where 절이 어디에 걸리는지 잘 봐야 한다.
<리스트 1> 악성쿼리
유형 1
유형 2
<리스트 1>에서 두가지 유형의 쿼리가 수행되는 횟수를 찾아보니 전체 6만 6321건 중 6366번이나 수행됐다. 테이블에 추적 결과를 저장하면 이런 일이 편해진다. 이 쿼리에서 사용하는 테이블(Cafe Free_Board)에 지금은 다음과 같은 인덱스가 걸려 있다.
우선 눈에 띄는 것은 index_1과 index_2가 중복되어 있다. Index_1은 index_2의 진 부분 집합이다. 중복된 것 중 하나는 지울 필요가 있다. 또한 이것 갖고는 <리스트 1>의 두 번째 유형을 충족시킬 수 없다. <리스트 1>에 대해서도 선택성이 나쁘면 도움이 안 될 듯하다. 넌클러스터 인덱스이기 때문이다. 클러스터 인덱스와 넌클러스터 인덱스의 상세한 내용에 대해서도 여기서는 다루지 않는다. 상세한 것들은 별도로 인덱스를 다루고 있는 수많은 온라인 세미나 자료, 서적 등을 참고하도록 하자.
실제 index_2를 사용하는 쿼리가 있는지 검사해 봤더니, 4% 정도의 쿼리가 사용을 했다. 하지만 컬럼 re_level은 사용하지 않았다. 반면에 추적에 잡힌 쿼리 중에는 Ref와 Cafe_ID를 사용해서 검색하는 쿼리가 존재했다. 이런 것들은 추적 파일을 집중적으로 분석해보면 알게 되는 결과이다.
다음 쿼리를 갖고 통계정보를 보았다. 통계란 SQL 서버가 컬럼에 어떤 데이터가 들어 있는지의 정보를 관리하는 것이다. 이것은 최적화기(optimizer)가 인덱스 결정을 내리고, 조인의 방법과 순서를 정하는 중요한 정보가 된다.
마지막 업데이트 전체 샘플
10 27 2002 1:00AM 368,968 368,968
불행하게도 지난 2002년 10월에 업데이트가 된 후로 전혀 업데이트가 안 됐다. 다시 이야기하면 이 인덱스는 사용하지 않는다는 의미다. 왜냐하면 SQL 서버는 자동으로 사용하는 인덱스나 통계를 업데이트하기 때문이다. 즉 index_2를 사용하기 때문에 중복되고 선택성도 상대적으로 덜 좋은 index_1은 사용하지 않는다는 의미이다. 전체 행은 약 40만 행 정도라는 것도 알 수 있다. 이것 역시 2002년 당시의 이야기이다. 또한 이 인덱스의 밀도는 다음과 같다.
밀도 길이 컬럼
0.010753 4 Cafe_ID
0.000003 8 Cafe_ID,Num
이 밀도를 기초로 이야기해 보면 하나의 cafe_ID를 찾으면 대략 1% 정도의 행을 찾아야 한다는 추정을 할 수 있다. 하지만 실제 통계 분포를 보면 어떤 cafe는 분포가 많고 어떤 것들은 적다. 많은 것은 20% 이상이다(분포 정보는 지면상 생략한다). 이 정도라면 넌클러스터 인덱스로 감당을 못할 것이라고 예측할 수 있다. 그러니 차라리 클러스터 인덱스를 만드는 게 나을 듯하다. 그러므로 num에 걸려있는 클러스터 인덱스보다는 cafe_id를 클러스터 인덱스로 만드는 것을 고려해 보자. 물론 클러스터 인덱스가 바뀌면서 틀림없이 cafe_id의 중간중간에 insert되는 행으로 인해 split으로 인한 오버 헤드와 조인이 걸릴 때 클러스터 인덱스보다 비효율적인 넌클러스터 인덱스를 사용해 조인을 처리해야 한다는 단점이 있기는 하다.
이제 성능 비교를 위해서 다음 쿼리와 같이 복사본을 만들도록 하자. 원본을 갖고 바로 적용하는 것도 방법이지만 온라인으로 사용 중이기 때문에, 그리고 성능 비교를 하기 위해서 별도의 복사본을 사용하도록 하자. 물론 테이블이 크다면 이렇게 하기도 힘들다. 그래서 사실 작은 크기의 테이블 유지가 필요하다.
또한 이것은 전형적인 웹 게시판이므로, 이에 대해 효과적으로 처리하는 몇 가지 알고리즘이 이미 나와 있다(필요하다면 www.en-core.com을 참고해 테이블 구조와 쿼리를 약간만 고치면 탁월한 성능을 얻을 수 있다). 사실 DBA는 이런 일을 추진할 능력과 권한이 있어야 한다. 많은 회사에서 DBA가 별 권한 없이 책임만 지는데, 서버의 성능 저하와 다운은 모두 DBA의 책임이므로 이 정도의 테이블 수정은 감행할 수 있는 추진력과 결단성이 필요하다.
결론적으로 성능 비교를 위해서 새로 생성한 테이블과 기존 테이블을 갖고 <리스트 1>의 쿼리를 수행해보도록 하자. 테이블 구조는 전혀 변경시키지 않았다. 인덱스만의 효과를 알아보기 위해 쿼리 문도 건드리지 않는다. 쿼리는 물론 새로 생성한 테이블 이름으로 바꿔서 수행해야 한다. 수행할 때는 다음과 같이 분석정보를 출력하도록 설정하고 수행할 쿼리를 GO로 구분해서 반복 수행해 결과를 비교해야 한다. 첫 번째 수행은 거의 캐시의 영향으로 느릴 수밖에 없기 때문에 그걸 그대로 믿으면 안 된다. 그래서 매번 캐시를 모두 비우고 테스트를 하든지 아니면 메모리에 올라온 상태에서 테스트를 해야 공정한 테스트가 된다.
<리스트 1>의 유형 1의 경우 8943ms는 5863ms가 되었고, 논리 읽기는 5082페이지에서 287페이지가 됐다. <리스트 1>의 유형 2의 경우는 44ms가 0ms, 즉 1ms 이하로 됐고, 논리 읽기는 471페이지에서 8페이지로 줄었다. 만족스런 결과다. 에이 기껏 3초? 기껏 44ms? 실제로 이런 작은 차이가 엄청난 성능의 차이를 가져온다. 필자는 기껏 30페이지 논리 읽기를 하는 원본 쿼리와 6페이지의 논리 읽기를 하는 수정본 쿼리를 스트레스트 테스트한 결과, 2000명 정도의 동시 접속에서 더 이상 접속이 안 되는 반면 아무 문제없이 성능을 발휘하는 사례도 경험한 바 있다. 따라서 저런 차이들에 대해 무시하면 나중에 많은 사용자, 많은 데이터가 됐을 때 문제가 생긴다.
Cafe_Free_Board가 Cafe_Free_Board_1부터 Cafe_Free_Board_10까지 적어도 10개 이상 되는 것 같은데, 나머지 테이블도 모두 적용해 보도록 했다. 적용된 후의 결과에 대해서는 이 글을 쓰고 있는 시점에서 확인할 수 없다. 아마 한달 후 쯤 적용되면 결과를 알 수 있을 듯. 그리고 그 때 프로필러로 다시 추적해보면 확실하게 성능이 향상된 것을 볼 수 있을 것이다.
“미지의 어떤 곳도 이제 도전할 수 있다”
이번 여행에서 우리는 어디로 떠날 것인지부터 논의하기 시작했고 그래서 명확하지 않은 것에 대해 구체화시켜가는 방법을 다뤘으며 프로필러를 이용해 모니터하고, 그래서 여행의 목적지를 어디로 잡을지 구체화시켰다. 실제 프로필러는 할 수 있는 기능이 엄청나다. 하지만 그보다 더 중요한 것은 이를 사용하는 사람이다. 궁극적으로는 SQL 서버의 최적화기가 동작하는 원리를 이해하고 이를 생각하며 이런 도구를 사용해 나가야 하지만, 첫 출발로 흥미를 갖고 모호한 개념을 구체화시키기에는 아주 좋은 도구이다.
<리스트 2> 성능 비교를 위한 출력
원본 <리스트 1>의 유형 1의 결과
'Cafe_Free_Board' 테이블. 스캔 수 4, 논리적 읽기 수 5082, 물리적 읽기 수 0, 미리 읽기 수 8.
CPU 시간 = 377ms, 경과 시간 = 8943ms.
원본 <리스트 1>의 유형 2
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 471, 물리적 읽기 수 0, 미리 읽기 수 0.
CPU 시간 = 44ms, 경과 시간 = 44ms.
튜닝 후 <리스트 1>의 유형 1의 결과
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 287, 물리적 읽기 수 0, 미리 읽기 수 0.
SQL Server 실행 시간 : CPU 시간 = 63ms, 경과 시간 = 5863ms.
튜닝 후 <리스트 1>의 유형 2
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 8, 물리적 읽기 수 0, 미리 읽기 수 0.
SQL Server 실행 시간 : CPU 시간 = 0ms, 경과 시간 = 0ms.
이런 과정에서 SQL 서버를 처음 접하는 사람들이 흔히 겪는 문제인 차단, 불필요한 쿼리, 클라이언트 커서, 잘못된 인덱스, 제약 없음 등의 문제들을 봤다. 빙산의 일각이라 할지라도 이들을 해결하는 방법도 살펴봤다. 지피지기(知彼知己), 정말 그렇다. 나를 먼저 알아야 하듯, SQL 서버에서는 현재 어떤 문제들이 벌어지고 있는지를 알아야 한다. 물론 방대한 양의 최적화기와 SQL 서버 아키텍처를 공부해야 하지만(전략), 초·중급 DBA들은 출발점으로는 현재 상황을 먼저 분석해야만 이를 근거로 자신이 무엇을 먼저 공부해야 하는지 전술을 세울 수 있다. 필자가 사용한 템플릿을 비롯한 나머지 자료는 ‘이달의 디스켓’으로 제공한다. 하지만 보안상의 이유로 스키마와 데이터 자료는 제외된다.
필자의 열흘간 해외 출장으로 수미씨에게 30일의 시간을 할애하지 못했다. 여행을 가고 싶은 곳은 많았지만 실제 다녀온 곳은 얼마 되지 않는다. 그건 누구나 갖는 생각이다. 하지만 그렇게 한번 여행을 하고 나면 앞으로 가고 싶은 새로운 곳에 대한 동경도 구체화되는 법이다. 이번 여행에서 정작 다녀야 할 곳을 못 다닌 곳도 있겠지만, 이 여행이 기초가 되어 이제 막 시작하는 이수미씨와 같은 DBA에게는 새로운 출발의 기회가 되기를 바란다.
이+달+의+디+스+켓
[ ‘막연할 때’의 문제 접근 방법 ]
뭔가 해야 하는데 막연하다. 이유가 뭘까? 한 번도 해본 적이 없어서 뭘 어찌해야 할지 모르기 때문이라고 생각한다. 또한 사람들이 어떤 결정을 내려야 할 때, 선뜻 결정을 내리지 못하고 우유부단한 이유는 정보 부족이 제일 큰 이유가 아닐까 싶다.
예를 들어 낯선 여행지, 사람의 인적이 드문 곳에서 길을 잃었다고 해보자. 필자는 사막 지역의 달려도달려도 주유소 하나 없는 곳에서 제대로 가는 건지 답답해한 적이 있었다. 해결책은 누군가를 만나서 물어보는 것이었다. 그 누군가는 물론 그 지역에 대해 잘 알고 있어야 한다. 모르는데 엉뚱하게 가르쳐 주는 것만큼 낭패가 없다. 이런 이유로 우리는 컨설팅을 받는다. 혹은 거창하게 컨설팅이라고 안 붙여도 이미 해본 사람들에게 조언을 얻거나 인터넷에서 검색하거나 책을 보거나 하는 이유가 바로 이것이다.
막연하다면 그냥 혼자서 끙끙 앓지 말고 주위에 물어보거나 정보를 더 구하면 될 듯하다. 그러나 때로는 무슨 일을 해야 하는지 명확지 않을 수도 있다. 그럴 때는 일을 지시한 사람과 더 의사소통을 해야 한다. 대충 이건가보다 하고 해서 맞으면 다행이지만(그럴 확률은 그다지 높지 않다), 아니라면 실컷 일하고도 결과는 없는, 혹은 더 나쁜 상황이 된다.
선뜻 결정을 내리지 못하는 것에 대해서도 생각해 보자. 예를 들어 전공을 결정할 때, 그 전공이 무엇인지 그 전공을 하고 났을 때의 진로는 어떻게 되는지 등의 정보가 아주 자세하다면, 전공에 대해 많은 고민을 하지 않고도 결정을 내릴 수 있다. 따라서 일을 할 때는 충분한 정보를 얻는 것이 중요하다고 할 수 있고, 제대로 의사소통을 해서 내가 무얼 해야 하는지 알고 시작하는 것이 중요하다.
문제가 생겼을 때 막연해지곤 한다. 이유는 거기에 대한 지식이 부족해서 정보가 부족해서라고 생각한다. 그래서 know-how와 함께 know-where가 중요하다. 인터넷을 검색해도 넘쳐나는 정보 중에 내가 찾는 것이 어떤 것인지 잘 모르니까. 그래서 우리는 이런저런 공부를 하는 것이다. 단순한 지식의 습득보다 중요한 것이 실제로 어떻게 문제를 해결하는지 어떻게 적용하는 것인지를 아는 것이다. 이번 여행도 그런 맥락에서 접근했으면 한다.
[ 압축을 해라! ]
프로필러는 압축을 하면 무진장 줄어드니 일단 압축을 하도록 하자. 필자가 이메일로 추적 결과를 보내달라고 하면 자주 압축이 안 된 어마어마한 양의 추적결과를 보내온다. 그래서 100MB의 용량을 보내려고 시도하는데, 압축률이 좋아서 압축하면 무척 작아진다. SQL 서버의 백업을 받은 백업 파일도 마찬가지다.
[ 차단과 잠금 옵션의 종류 ]
SQL 서버는 한 트랜잭션이 X(배타) 잠금을 걸었을 때, 다른 트랜잭션은 S(공유) 잠금을 걸 수 없다. 정확히 말하면, 오라클과 달리 SQL 서버에는 before image, after image라는 것이 현재 버전에서 존재하지 않는다. 따라서 트랜잭션이 진행되면 다른 트랜잭션은 기다리거나(차단당한다), 아니면 dirty read 즉 read uncommitted를 해야 한다. 은행 업무와 같이 특별히 트랜잭션 처리가 필요한 경우라면 몰라도 일반적인 게시판 업무나 웹 업무의 경우는 대부분 dirty read를 해도 된다. 따라서 SQL 서버에서는 가능한 디폴트 잠금 수준인 read committed 수준보다 잠금 수준을 낮춰서 dirty read를 허용할 수 있는 모든 상황에서 잠금 수준을 낮추는 것이 절대적으로 필요하다.
◆ 실제 사례 : 초기 화면이 뜨는 데 30초가 넘어 결국 timeout이 걸리던 유명한 사이트에서는 잠금 힌트의 설정만으로 모든 문제가 해결됐다.
출처 : 마이크로소프트웨어[2004년도 7월호]
필라넷 이사
중소기업에는 별도로 DBA가 없는 것이 보통이다. 그 중에 나은 개발자가 DBA로 승격되는 경우가 대부분이다. 하지만 DBA의 역할만 수행하는 것이 아니기에 부족함이 많다. 5년 개발 경력의 이수미씨와의 대화 주제는 효과적으로 SQL 서버를 운용하기 위해 무엇을 갖춰야 하는가에 대한 것이다. 이 글을 통해 자신의 모습은 어떠한가를 생각해보며 DB 전문가로 가는 발판을 만들어보기 바란다.
필자는 여행을 무척 좋아한다. 거의 20개국 정도를 가봤는데 대부분의 여행은 가고 오는 항공편만 확보해두고 계획 없이 떠나는 경우가 많았다. 필자는 그런 계획 없는 여행을 좋아한다. 하지만 홀로 떠나는 것이 아닌 여행, 즉 이수미씨가 동반자(혹은 손님이고 필자가 가이드)이기에 이번 여행은 약간의 계획이 필요했다.
어디로 여행을 떠날 것인가?
이수미씨는 의욕왕성한 경력 5년차의 개발자였다. 그래서 이번 여행에서 가고 싶은 곳이 무척 많았고, 여행 계획서는 방대했다. 하지만 급히 가서 사진만 찍고 다른 여행지로 떠나는 그런 여행보다는 좀 더 차분히 여행을 할 수 있도록 수정했다.
이수미씨의 현재 수준은 이제 막 DBA로 발걸음을 내딛는 상태로, 회사에서 일을 맡겨서 시작한 똑똑한(?) 개발자였다. 수미씨의 목표는 세계일주였지만 아직은 그럴 상황이 아니어서 우리는 세계 일주를 향한 걸음마로서 첫 해외여행을 목표로 했다. 특히 수미씨는 오라클을 쓰다가 SQL 서버로 넘어왔으며, 그래서 특히 SQL 서버가 가진 독특한 특성들에 대해 잘 모르고 있었다. 따라서 이런 부분에 초점을 맞췄다.
맨 처음 수미씨는 테이블 디자인부터 질문을 해왔지만, 이번 여행에서 테이블 디자인을 주로 다루는 팀도 있고 이것만 다루기에도 워낙 방대한 주제이기에 디자인에 대해서 특별히 문제가 있는지만 보고 넘어갔다. 그리고 현재 사용되고 있는 서버의 상황을 살펴보기로 했다. 여기서 발생되는 문제의 유형에 따라 다음 목적지를 변경할 수 있도록 했다. 하지만 흔히 발생하는 동일한 문제가 나올 것이라 예측했는데(실제로 이런 문제들이 나타났다), 그래서 그 다음으로 SQL 서버를 사용할 때 가장 흔히 부딪히는 문제들인 잠금, 차단, 클러스터 인덱스의 특징, 프로필러라는 매우 유용한 도구의 활용에 대해 다루는 것을 이번 여행의 목적지로 정했다.
가볍게 짚고 넘어가는 테이블 디자인
첫 스타트를 하려고 하자 수미씨는 무척 힘들어했다. 그래서 필자는 ‘문제 접근방법’에 대한 조언을 해줬다(<박스> 참조). 이런 상황에서 우리는 먼저 간단히 주로 사용되는 테이블에 대해서 디자인 이슈가 없는지 살펴보기로 했다. 회사에서 사용하는 가장 핵심이 되는 테이블은 다음의 스키마와 일부 데이터만 살펴봤다. 필자는 이에 대해 주요 테이블의 생성 스크립트와 BCP out 데이터를 보내달라고 했다.
우선 수미씨의 질문은 “왜 하필 bcp로 보내야 하는가”란 것이었다. “테이블 수가 몇 개 안 되어 쿼리 분석기에서 select 문을 써서 추출했다. 내가 아는 범위에서는 BCP로 추출해도 테이블을 하나씩 지정해 추출해야 하므로, 손에 익은 방법이 빠를 것 같아 select 문으로 추출했는데, 이 방법에 문제가 있는가?”라는 질문이었다.
BCP를 갖고 추출해 달라고 한 것은 스크립트화할 수 있기 때문이다. 손에 익은 select로 추출했는데 그렇게 하면 테이블이 많아질 때 자동화하지 못하고 수동으로 계속 작업을 해야 한다. 꼭 BCP를 쓰지 않더라도 SQL 서버의 DTS를 활용할 수도 있다. 하지만 이때도 가능한 패키지를 저장해 두고 똑같은 일이 반복될 때 시간을 줄일 수 있는 가능성을 남겨두어야 한다.
DBA가 가져야 하는 중요한 자세 중 하나는 자동화와 반복성을 가질 수 있는 방법의 개발이다. 자주 반복된 작업을 해야 하기 때문에 손에 익었다는 이유로 편한 방법을 쓰다 보면 나중에 그런 작업을 300번 반복해야 하는 경우가 발생하고, 그때의 생산성이란 이루 말할 수 없이 나빠진다. 뿐만 아니라 오류가 발생할 수도 있다는 것이 더 큰 문제다. 그래서 자동화된 스크립트를 생성해서 사용하고 필요에 맞게 고쳐 쓰는 것이 몸에 배야 한다고 생각한다. 수미씨의 회사 테이블에서 가장 많이 사용하는 테이블 스키마와 일부 데이터 그리고 인덱스는 <표 1>, <표 2>와 같다.
<표1>주요 테이블의 스키마
cafe_info
Cafe_ID(PK) int
Cafe_Name varchar
Cafe_Master varchar
Cafe_Class smallint
Cafe_Small_Content varchar
Cafe_Content varchar
Cafe_Main text
Cafe_InsertDay smalldatetime
Cafe_Permit bit
Theme_No int
Cafe_Status smallint
Group_No int
FilePath varchar
Ranking int
New bit
cafe_member
Member_Name varchar
wDate datetime
Years_old smallint
Address1 varchar
Address2 varchar
Address_Permit bit
PostNo char
Job_Name varchar
Job_Name_Permit bit
Introduce text
TelePhone_Permit bit
HandPhone_Permit bit
cafe_free_board
Cafe_ID int
Num int
Writer varchar
Subject varchar
Content text
Ref int
Re_Step int
Re_Level int
UserID varchar
wDate datetime
visited int
cafe_member_class
Member_Class smallint
Member_Class_Name varchar
cafe_group
Group_No int
Group_Name varchar
Group_Content varchar
Insert_Day smalldatetime
Group_OrderNo smallint
<표 2>인덱스
PK_Cafe_Group clustered, unique, primary key Group_No
Cafe_Free_Board_Index_1 nonclustered, unique Cafe_ID, Num(-)
Cafe_Free_Board_Index_2 nonclustered, unique Cafe_ID, Num(-), Ref, Re_Step, Re_Level
PK_Cafe_Free_Board clustered, unique, primary key Num
IX_Cafe_Info nonclustered Group_No
PK_Cafe_Info clustered, unique, primary key Cafe_ID
PK_Cafe_Member_Class clustered, unique, primary key Member_Class
아쉬운 부분은 인덱스
현재의 테이블들은 일단 대충 봐서 정규화 관점에서는 필자가 봤던 웬만한 중소 규모의 업체에서는 탁월하게 잘 된 테이블 디자인이라고 할 수 있다. 보통 중소 규모의 기업에서 테이블 디자인이란 것은 PK(기본 키)도 없거나 인덱스가 하나도 없거나 자료형은 모두 varchar이거나, 3 정규화 위반은 물론이요, 1 정규화 위반에 이르기까지 거의 엉망에 가까운 것이 보통이다. 이에 비하면 자료형도 적절하게 잘 사용하려고 한 노력이 보이며, 정규화도 무척이나 노력해서 한 흔적이 보인다. 특히 Bit, smallint, smalldatetime 등의 자료형을 적절하게 잘 썼다. 작은 자료형을 사용하는 것은 데이터가 늘어났을 때 성능에 영향을 미치는 요소가 되기 때문이다.
눈에 띄게 아쉬운 것은 인덱스 부분이었다. 특히 cafe_member는 아예 인덱스는 물론이고 PK가 없었는데 혹시 스크립트 생성 과정에서 실수한 것은 아닌지 한번 확인해 봤으나 확실히 없었다. 따라서 (Cafe_ID, Member_ID)를 PK로 잡기로 했다. 테이블에 PK가 없다는 건 정말 특별한 상황 아니면 대단히 중대한 실수다. 많은 사람들이 성능을 외치지만 정작 중요한 것은 그 이전의 기초다. PK가 없으면 중복데이터가 들어오게 되고, 그러면 나중에 그 중복 예외 처리를 하려고 프로그램을 짜는 게 복잡해지고 프로그램들은 결국 꼬이기 시작한다. FK(참조 키)가 없어도 마찬가지이다. 불행하게도 수미씨 회사의 테이블들은 FK가 없었다. 그럼 엉뚱한 member_id를 가진 데이터가 주문을 하는 등 문제가 생기고, 나중엔 결국 outer join을 사용해 엄청 성능이 느린 쿼리를 작성하게 된다.
그러니 아무리 강조해도 지나치지 않는 것이 데이터 무결성이다. 혹시 나중에 데이터 웨어하우징을 하거나 데이터 이전 작업을 해보면 실감하게 되니 지금부터 일찌감치 무결성 확보를 위한 노력을 기울여야 할 것 같다.
주요 테이블에 FK를 설정하라!
그래서 수미씨에게 주요 테이블에 FK를 설정해 보도록 숙제를 냈다. 시도해보면 바로 오류가 발생할 것이다. 거의 엉터리 데이터가 있을 거니까(아니라면 정말 축하할 일이지만). 이로 인해 개발자들이 아우성을 쳐도 미래를 위해 고집스럽게 FK와 check constraint와 같은 제약을 설정해 볼 것을 권장한다. 진행과정에서 역시나 PK를 거는 데 중복오류가 발생했다. 이런 경우 삭제는 어찌하는가 물어왔다. 물론 질문하곤 바로 답을 찾아내어 자문자답했다. 자문자답하는 동안 필자는 다음과 같은 방법을 제시했다. “몇 가지 삭제 방법이 있는데 일단 먼저 중복된 데이터를 찾아야 한다. 스스로 한번 풀어보라.”
“키 값에 대해 group by를 하고 그것의 개수(count(*))가 1보다 큰 것은 중복된 키 값이다.”
이렇게 중복된 데이터를 모두 찾아내서 지우면 된다. 중복된 것을 지우는 것도 양이 많다면 약간의 트릭을 쓸 수 있을 것이다.
- 중복데이터를 임시테이블에 넣고
- 최상위 한 행만 남기고 나머지는 지운 다음
- 원 테이블에서 모든 중복 행을 지우고
- 임시 테이블 데이터를 원 테이블에 넣는다.
서버에서는 무슨 일이 벌어지고 있을까?
테이블에 대한 검증이 끝나자 우리는 다음 목적지를 향해 떠났다. 실제 서버에서 어떤 일들이 벌어지고 있는지 ‘관찰(monitor)’해 보는 것이다. 현재 상황을 알아내는 것은 매우 중요하기 때문이다. DBA가 해야 하는 중요한 일은 성능을 향상시키는 것이지만, 이를 위해서 먼저 현재 상황을 알아야 하는 것이다. 따라서 우리는 프로필러라는 좋은 도구를 사용하기로 했다. 물론 윈도우의 성능을 모니터하기 위해서는 성능 카운터라는 좋은 도구가 있지만, 이번 여행에서는 제외시켰다. 너무나 방대하기 때문이다.
프로필러
그럼 이제 정말 이 테이블들에 인덱스가 제대로 사용되고 있는지 검증해보기 위해 프로필러를 사용할 줄 아는지 확인해 봤다. 안다고 해서 20MB 정도 분량이 될 때까지 필자가 주로 사용하는 템플릿을 보내주고 이를 사용해서 추적 데이터를 받아 보내주도록 했다. 그걸 갖고 인덱스가 제대로 위의 테이블에 만들어져 있는지 검증해 보도록 했다.
수미씨는 프로필러가 특정한 옵션을 주지 않았는데, 5MB씩 잘라 저장이 된다고 답해왔다. 또한 커뮤니티 관련 테이블(앞서 디자인에서 살펴본 주요 테이블)만 분석하려고 하니 따로 선택하는 옵션이 없어 전체 데이터베이스(정확히는 전체 서버)에 대한 추적으로 생성됐다고 하면서 부분 분석 옵션 기능이 있는지 물어왔다.
프로필러는 디폴트로 5MB가 되면 자동적으로 rollover가 된다. 그래서 처음 5MB가 차면 xxxx(제일 처음 준 파일이름).tr1, tr2, tr3 이런 식으로 파일명이 생성된다. 혹 실수로 추적을 중지하는 것을 잊으면 무제한의 파일이 쌓이게 되므로 주의해야 한다.
그래서 추적 중지 시간 설정이란 것이 필요하다. 또한 해당하는 테이블만 추적할 기능이 있기는 하지만, 임의의 쿼리(ad-hoc query)일 때만 가능하다. 프로시저라면 어렵다. 왜냐하면 프로시저 안에 테이블 이름이 포함되어 있고, 이를 프로필러가 알 수 있는 방법이 현재로는 없기 때문이다. 임의의 쿼리라면 프로필러에 필터라는 기능을 사용해 textdata에 필터를 걸면 된다. 이때는 와일드카드를 지원하므로, ‘%원하는테이블%’와 같이 설정하면 되지만 권장할 만한 방법은 아니다. 이를 필터하기 위해서 별도의 부하기 걸리기 때문이다. 차라리 추적을 받은 후 나중에 걸러내는 게 더 쓸만한 방법이다.
프로필러 분석1 - 차단
추적을 받아 봤더니 필자가 보낸 템플릿을 사용하지 않았다. 그래서 템플릿을 사용하는 이유에 대해 설명했다. 템플릿을 사용하는 이유는 추적 자료를 받고 나서 일관된 형식으로 보고 분석하고자 하는 목적도 있고, 또한 처음에 받을 때 불필요한 것들을 받지 않으려는 목적도 있다. 거듭 강조하지만 반복되는 일들에 대해 불필요한 ‘노가다’를 줄이는 것도 DBA 혹은 엔지니어들이 해야 할, 아니 모든 인생살이에서 해야 할 중요한 일이다.
<화면 1> 템플릿 없어 캡처된 추적 자료
<화면 1>을 보면 불필요하게 연결 속성, 로그인, 로그아웃, 애플리케이션 네임, 유저 네임 등이 잡혀 있다. 보통은 이런 것들이 필요 없다. 특별히 무슨 문제가 있어 일부러 그것들을 살펴보지 않는 한 공부를 위해서라도 필자가 보낸 템플릿을 사용해 한번 더 받아보도록 하자. 그 결과를 <화면 1>과 비교해보자. 항상 동일한 순서와 이벤트들이 캡처되며 따라서 이를 갖고 처리하는 다음 과정의 일들도 자동화될 확률이 높아진다.
<화면 2> 템플릿을 갖고 캡처된 보기 편한 정돈된 자료
<화면 2>를 보면 현재 exec sp_cursorfetch 180150000, 16, 4, 1이란 것들이 나타나는 것을 볼 수 있다. 이것은 클라이언트가 서버 측 커서를 사용하기 때문이다. 물론 장단점이 있다. ASP, VB 등의 프로그램에서 처음 연결 속성에서 이것을 정의해주게 되어 있는데, 디폴트로 서버 측 커서를 사용하게 된다(물론 클라이언트 속성에 따라 디폴트가 바뀌기도 한다). 꼭 그렇게 해야 하는 경우가 아니라면 불필요한 CPU 부하와 네트워크 부하를 걸기 때문에, 서버 측 커서를 클라이언트 측 커서로 바꿔야 한다. 추적 파일을 열어보면 50% 이상이 이들로 채워져 있음을 쉽게 발견할 수 있다. 레코드셋 페이징을 위해 커서를 사용한 부분인 것 같은데, 선언을 안 하면 디폴트로 adUseServer(서버 측 커서)가 선언이 되니 adUseClient(클라이언트 측 커서)로 선언을 변경해주면 된다. 또한 <화면 2>에서 선택된 라인을 보면 다음 쿼리가 수행됐다.
앞에서 눈여겨 본 테이블은 아니지만 게시판이라는 것을 쉽게 알 수 있다. 자주 사용하는 테이블, 쿼리일 것이라고 추정한다. 그런데 특이한 것은 Reads가 0(페이지)인데 걸린 시간은 무려 1236ms, 즉 1.2초가 걸렸다. 정말 0인지는 확인해볼 필요가 있는데 추적 자료의 거의 모든 데이터가 0으로 나타나는 것으로 봐서 SQL 서버 추적에 문제가 있는 듯하다(가끔 이런 문제가 생기기도 한다). 어찌 되었건 단 하나의 값을 가져오는 쿼리가 이 정도의 시간이 걸린다는 것은 무지 느린 것이고(제대로 된 추적이라면), 읽기가 0이라는 것에서도 이 쿼리는 거의 10ms 이하가 걸려야 정상이라고 할 수 있다. 따라서 이 쿼리는 전형적인 차단(blocking) 현상이라고 할 수 있다. 차단을 피하기 위해서는 어찌 해야 할까? 이것이 게시판 테이블이라는 것을 염두에 두고 생각해보자.
<화면 3> 차단이 아닌 정상적인 쿼리
<화면 3>과 비교해보면 확연히 차이가 난다. 즉, member, cafe_member 등의 테이블이다. 수미씨에게 걸린 시간이 거의 0 혹은 16ms 이하인 것과 비교해 보라고 주문했다. 그리고 <화면 4>는 비정상적으로 보이는 쿼리다. cafe_group 테이블에서 가져오는데 한 번에 다 가져오면 될 텐데 왜 저런 식으로 10번에 걸쳐 가져올까? 이런 쿼리가 자주 반복되는 것을 볼 수 있다. 뭔가 프로그램을 잘못 짠 듯하다. 문제되는 쿼리를 수미씨가 찾아보니 동호회 상단의 각 동에서 현재 위치를 표시해주기 위한 쿼리였다. 소스가 좀 이상하게 구성되어 있어 검토 후 수정하기로 했다.
<화면 4> 이상한 쿼리
수미씨는 새로 추적을 받았다. 지난 번에는 최대 파일 크기를 5MB로 설정하고 ‘파일 롤오버 사용’ 옵션을 사용했고, 이번에는 최대 파일 크기를 20MB로 설정하고 ‘파일 롤오버 사용’ 옵션을 사용하지 않았다. 무제한의 파일이 쌓이는 것을 방지하기 위해 추적 중지 시간도 설정했다. 추적 속성에 대해 좀 더 이해하기 위해 SQL 서버 온라인 설명서(BOOKS ONLINE)를 찾아봤다. 필자는 이에 대해 칭찬을 아끼지 않았다. 온라인 설명서는 매우 도움되는 가장 기본적인 참고서이다. 필자는 한때 “온라인 설명서를 첨부터 끝까지 다 읽어보리라”는 결심을 했는데 너무 빨리 새 버전이 나오니 어림도 없었다. SQL 서버 2005가 그나마 가장 오래 끌고 있는데, 요즘은 다른 일들로 바빠서 다 읽을 엄두는 못 내고 틈틈이 참고하고 있다. 뉴스 그룹에 올라오는 질문에 대한 답변의 상당 부분은 “readme.txt를 읽어봐라”, “온라인 설명서를 읽어봐라”이다. 그만큼 중요한 것이니 자주 살펴봐야 한다. 필자 역시 SQL 서버 서비스 팩 3를 설치할 때 문제가 생겨서 질문했는데, 답변이 “readme.txt를 읽어 봐라”였고 읽어보니 정말 있었다. 조금은 창피했다.
차단과 잠금 힌트
다음의 쿼리 구문의 블로킹 현상을 피하는 방법은 잠금 힌트를 사용하는 것이다.
잠금 힌트를 사용하는 방법은 SELECT, INSERT, UPDATE, DELETE 문에서 테이블 이름 뒤에 잠금 힌트를 지정하는 것이다. 다음과 같은 방법이 가능하다.
◆ 방법 1 : 저장 프로시저의 상단에 다음의 구문을 선언하면 매 쿼리의 테이블마다 잠금 해제(read uncommitted)를 주지 않고 세션 전체에 영향을 준다.
Ex) set transaction isolation level readuncommitted
◆ 방법 2 : 차단당하는 쿼리문에 힌트(rad uncommitted)를 삽입한다.
하지만 <방법 2>를 쓸 때는 매 테이블마다 잠금 힌트를 써야 한다.
정확한 잠금 힌트의 사용은 트랜잭션의 수행 속도를 향상시킬 수 있다. 단 잠금 힌트는 잘 사용하면 참으로 좋지만 잘못 사용하면 역효과가 날 수도 있다. 즉 트랜잭션의 일관성을 깨지게 한다.
“NOLOCK과 ReadUncommited의 차이점은 무엇인가?”라는 질문을 흔히 한다. NOLOCK과 ReadUncommited는 차이가 없다. 과거에는 NOLOCK이라는 것만 존재하던 시절이 있었다. 이제는 ReadUncommited가 정식 문장이고 이것은 ANSI 호환이 되는 문장이므로 다른 데이터베이스 엔진에서도 사용되는 문장이다. 그래서 가능하면 ReadUncommited를 써야 한다.
말이 나온 김에 다음과 같은 문장은 어떤 결과를 가져올까? 실제로 어떤 사이트에서 실수로 작성된 문장에 이런 코드가 있었고, 웃음이 나오는 결과가 벌어졌다. 무엇이 문제일까?
답을 아는 사람은 지금 빙긋이 웃을 것이다. 어떤 회사에서 실제로 이렇게 써 놓고는 왜 이게 차단이 될까 고민했다가 나중에 훈수를 둔 사람에 의해 발견하고는 다들 실소를 금치 못했다. 훈수 둘 때는 원래 잘 보이는 법이다. 이 답은 WITH (NOLOCK) 또는 (NOLOCK)이라고 했어야 하는데 그냥 NOLOCK이라고 했기 때문에 테이블의 별칭으로 인식했다. 참고로 SQL 서버 2005에서는 with가 없는 (NOLOCK)은 지원되지 않는다. 그러니 이젠 모든 코드에 with (NOLOCK)처럼 WITH를 꼭 붙여야 한다.
프로필러 분석2 - 인덱스
수미씨가 보내준 프로필러 자료를 테이블에 넣었다. 그리고 그걸 갖고 분석을 해봤다(긴 과정이라 줄여서 요약을 한다). 궁극적으로는 수미씨(뿐만 아니라 모든 DBA)도 이런 과정을 배워서 앞으로 혼자 튜닝을 해나가야 한다. 프로필러 자료를 프로필러에서 그대로 볼 수도 있지만 이것을 SQL 서버에 테이블로 저장해 분석할 수도 있다. 이렇게 하면 우리가 좋아하는 쿼리 문장으로 분석 작업을 할 수 있어서 훨씬 수월해진다. 이 상세한 과정은 생략한다. 해보면 그다지 어렵지 않다는 것을 금방 알게 되고, 각자의 분석 능력과 응용 능력에 따라 활용도는 달라진다.
쿼리 분석기에서 CPU를 가장 많이 차지한 것을 살펴봤다. 최고로 많이 걸린 것이 4초(4781ms)이고, 그 나머지는 300ms 이하니까 양호한 상태라고 할 수 있겠다. 그렇다고 방심할 수는 없다. 가장 문제가 되는 쿼리 두 개만 찾아봤다. Where 절이 어디에 걸리는지 잘 봐야 한다.
<리스트 1> 악성쿼리
유형 1
유형 2
<화면 5> 악성 쿼리
<리스트 1>에서 두가지 유형의 쿼리가 수행되는 횟수를 찾아보니 전체 6만 6321건 중 6366번이나 수행됐다. 테이블에 추적 결과를 저장하면 이런 일이 편해진다. 이 쿼리에서 사용하는 테이블(Cafe Free_Board)에 지금은 다음과 같은 인덱스가 걸려 있다.
우선 눈에 띄는 것은 index_1과 index_2가 중복되어 있다. Index_1은 index_2의 진 부분 집합이다. 중복된 것 중 하나는 지울 필요가 있다. 또한 이것 갖고는 <리스트 1>의 두 번째 유형을 충족시킬 수 없다. <리스트 1>에 대해서도 선택성이 나쁘면 도움이 안 될 듯하다. 넌클러스터 인덱스이기 때문이다. 클러스터 인덱스와 넌클러스터 인덱스의 상세한 내용에 대해서도 여기서는 다루지 않는다. 상세한 것들은 별도로 인덱스를 다루고 있는 수많은 온라인 세미나 자료, 서적 등을 참고하도록 하자.
실제 index_2를 사용하는 쿼리가 있는지 검사해 봤더니, 4% 정도의 쿼리가 사용을 했다. 하지만 컬럼 re_level은 사용하지 않았다. 반면에 추적에 잡힌 쿼리 중에는 Ref와 Cafe_ID를 사용해서 검색하는 쿼리가 존재했다. 이런 것들은 추적 파일을 집중적으로 분석해보면 알게 되는 결과이다.
다음 쿼리를 갖고 통계정보를 보았다. 통계란 SQL 서버가 컬럼에 어떤 데이터가 들어 있는지의 정보를 관리하는 것이다. 이것은 최적화기(optimizer)가 인덱스 결정을 내리고, 조인의 방법과 순서를 정하는 중요한 정보가 된다.
마지막 업데이트 전체 샘플
10 27 2002 1:00AM 368,968 368,968
불행하게도 지난 2002년 10월에 업데이트가 된 후로 전혀 업데이트가 안 됐다. 다시 이야기하면 이 인덱스는 사용하지 않는다는 의미다. 왜냐하면 SQL 서버는 자동으로 사용하는 인덱스나 통계를 업데이트하기 때문이다. 즉 index_2를 사용하기 때문에 중복되고 선택성도 상대적으로 덜 좋은 index_1은 사용하지 않는다는 의미이다. 전체 행은 약 40만 행 정도라는 것도 알 수 있다. 이것 역시 2002년 당시의 이야기이다. 또한 이 인덱스의 밀도는 다음과 같다.
밀도 길이 컬럼
0.010753 4 Cafe_ID
0.000003 8 Cafe_ID,Num
이 밀도를 기초로 이야기해 보면 하나의 cafe_ID를 찾으면 대략 1% 정도의 행을 찾아야 한다는 추정을 할 수 있다. 하지만 실제 통계 분포를 보면 어떤 cafe는 분포가 많고 어떤 것들은 적다. 많은 것은 20% 이상이다(분포 정보는 지면상 생략한다). 이 정도라면 넌클러스터 인덱스로 감당을 못할 것이라고 예측할 수 있다. 그러니 차라리 클러스터 인덱스를 만드는 게 나을 듯하다. 그러므로 num에 걸려있는 클러스터 인덱스보다는 cafe_id를 클러스터 인덱스로 만드는 것을 고려해 보자. 물론 클러스터 인덱스가 바뀌면서 틀림없이 cafe_id의 중간중간에 insert되는 행으로 인해 split으로 인한 오버 헤드와 조인이 걸릴 때 클러스터 인덱스보다 비효율적인 넌클러스터 인덱스를 사용해 조인을 처리해야 한다는 단점이 있기는 하다.
이제 성능 비교를 위해서 다음 쿼리와 같이 복사본을 만들도록 하자. 원본을 갖고 바로 적용하는 것도 방법이지만 온라인으로 사용 중이기 때문에, 그리고 성능 비교를 하기 위해서 별도의 복사본을 사용하도록 하자. 물론 테이블이 크다면 이렇게 하기도 힘들다. 그래서 사실 작은 크기의 테이블 유지가 필요하다.
또한 이것은 전형적인 웹 게시판이므로, 이에 대해 효과적으로 처리하는 몇 가지 알고리즘이 이미 나와 있다(필요하다면 www.en-core.com을 참고해 테이블 구조와 쿼리를 약간만 고치면 탁월한 성능을 얻을 수 있다). 사실 DBA는 이런 일을 추진할 능력과 권한이 있어야 한다. 많은 회사에서 DBA가 별 권한 없이 책임만 지는데, 서버의 성능 저하와 다운은 모두 DBA의 책임이므로 이 정도의 테이블 수정은 감행할 수 있는 추진력과 결단성이 필요하다.
결론적으로 성능 비교를 위해서 새로 생성한 테이블과 기존 테이블을 갖고 <리스트 1>의 쿼리를 수행해보도록 하자. 테이블 구조는 전혀 변경시키지 않았다. 인덱스만의 효과를 알아보기 위해 쿼리 문도 건드리지 않는다. 쿼리는 물론 새로 생성한 테이블 이름으로 바꿔서 수행해야 한다. 수행할 때는 다음과 같이 분석정보를 출력하도록 설정하고 수행할 쿼리를 GO로 구분해서 반복 수행해 결과를 비교해야 한다. 첫 번째 수행은 거의 캐시의 영향으로 느릴 수밖에 없기 때문에 그걸 그대로 믿으면 안 된다. 그래서 매번 캐시를 모두 비우고 테스트를 하든지 아니면 메모리에 올라온 상태에서 테스트를 해야 공정한 테스트가 된다.
<리스트 1>의 유형 1의 경우 8943ms는 5863ms가 되었고, 논리 읽기는 5082페이지에서 287페이지가 됐다. <리스트 1>의 유형 2의 경우는 44ms가 0ms, 즉 1ms 이하로 됐고, 논리 읽기는 471페이지에서 8페이지로 줄었다. 만족스런 결과다. 에이 기껏 3초? 기껏 44ms? 실제로 이런 작은 차이가 엄청난 성능의 차이를 가져온다. 필자는 기껏 30페이지 논리 읽기를 하는 원본 쿼리와 6페이지의 논리 읽기를 하는 수정본 쿼리를 스트레스트 테스트한 결과, 2000명 정도의 동시 접속에서 더 이상 접속이 안 되는 반면 아무 문제없이 성능을 발휘하는 사례도 경험한 바 있다. 따라서 저런 차이들에 대해 무시하면 나중에 많은 사용자, 많은 데이터가 됐을 때 문제가 생긴다.
Cafe_Free_Board가 Cafe_Free_Board_1부터 Cafe_Free_Board_10까지 적어도 10개 이상 되는 것 같은데, 나머지 테이블도 모두 적용해 보도록 했다. 적용된 후의 결과에 대해서는 이 글을 쓰고 있는 시점에서 확인할 수 없다. 아마 한달 후 쯤 적용되면 결과를 알 수 있을 듯. 그리고 그 때 프로필러로 다시 추적해보면 확실하게 성능이 향상된 것을 볼 수 있을 것이다.
<화면 6> 인덱스 튜닝 전과 후의 쿼리 플랜
“미지의 어떤 곳도 이제 도전할 수 있다”
이번 여행에서 우리는 어디로 떠날 것인지부터 논의하기 시작했고 그래서 명확하지 않은 것에 대해 구체화시켜가는 방법을 다뤘으며 프로필러를 이용해 모니터하고, 그래서 여행의 목적지를 어디로 잡을지 구체화시켰다. 실제 프로필러는 할 수 있는 기능이 엄청나다. 하지만 그보다 더 중요한 것은 이를 사용하는 사람이다. 궁극적으로는 SQL 서버의 최적화기가 동작하는 원리를 이해하고 이를 생각하며 이런 도구를 사용해 나가야 하지만, 첫 출발로 흥미를 갖고 모호한 개념을 구체화시키기에는 아주 좋은 도구이다.
<리스트 2> 성능 비교를 위한 출력
원본 <리스트 1>의 유형 1의 결과
'Cafe_Free_Board' 테이블. 스캔 수 4, 논리적 읽기 수 5082, 물리적 읽기 수 0, 미리 읽기 수 8.
CPU 시간 = 377ms, 경과 시간 = 8943ms.
원본 <리스트 1>의 유형 2
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 471, 물리적 읽기 수 0, 미리 읽기 수 0.
CPU 시간 = 44ms, 경과 시간 = 44ms.
튜닝 후 <리스트 1>의 유형 1의 결과
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 287, 물리적 읽기 수 0, 미리 읽기 수 0.
SQL Server 실행 시간 : CPU 시간 = 63ms, 경과 시간 = 5863ms.
튜닝 후 <리스트 1>의 유형 2
'Cafe_Free_Board' 테이블. 스캔 수 1, 논리적 읽기 수 8, 물리적 읽기 수 0, 미리 읽기 수 0.
SQL Server 실행 시간 : CPU 시간 = 0ms, 경과 시간 = 0ms.
이런 과정에서 SQL 서버를 처음 접하는 사람들이 흔히 겪는 문제인 차단, 불필요한 쿼리, 클라이언트 커서, 잘못된 인덱스, 제약 없음 등의 문제들을 봤다. 빙산의 일각이라 할지라도 이들을 해결하는 방법도 살펴봤다. 지피지기(知彼知己), 정말 그렇다. 나를 먼저 알아야 하듯, SQL 서버에서는 현재 어떤 문제들이 벌어지고 있는지를 알아야 한다. 물론 방대한 양의 최적화기와 SQL 서버 아키텍처를 공부해야 하지만(전략), 초·중급 DBA들은 출발점으로는 현재 상황을 먼저 분석해야만 이를 근거로 자신이 무엇을 먼저 공부해야 하는지 전술을 세울 수 있다. 필자가 사용한 템플릿을 비롯한 나머지 자료는 ‘이달의 디스켓’으로 제공한다. 하지만 보안상의 이유로 스키마와 데이터 자료는 제외된다.
필자의 열흘간 해외 출장으로 수미씨에게 30일의 시간을 할애하지 못했다. 여행을 가고 싶은 곳은 많았지만 실제 다녀온 곳은 얼마 되지 않는다. 그건 누구나 갖는 생각이다. 하지만 그렇게 한번 여행을 하고 나면 앞으로 가고 싶은 새로운 곳에 대한 동경도 구체화되는 법이다. 이번 여행에서 정작 다녀야 할 곳을 못 다닌 곳도 있겠지만, 이 여행이 기초가 되어 이제 막 시작하는 이수미씨와 같은 DBA에게는 새로운 출발의 기회가 되기를 바란다.
이+달+의+디+스+켓
[ ‘막연할 때’의 문제 접근 방법 ]
뭔가 해야 하는데 막연하다. 이유가 뭘까? 한 번도 해본 적이 없어서 뭘 어찌해야 할지 모르기 때문이라고 생각한다. 또한 사람들이 어떤 결정을 내려야 할 때, 선뜻 결정을 내리지 못하고 우유부단한 이유는 정보 부족이 제일 큰 이유가 아닐까 싶다.
예를 들어 낯선 여행지, 사람의 인적이 드문 곳에서 길을 잃었다고 해보자. 필자는 사막 지역의 달려도달려도 주유소 하나 없는 곳에서 제대로 가는 건지 답답해한 적이 있었다. 해결책은 누군가를 만나서 물어보는 것이었다. 그 누군가는 물론 그 지역에 대해 잘 알고 있어야 한다. 모르는데 엉뚱하게 가르쳐 주는 것만큼 낭패가 없다. 이런 이유로 우리는 컨설팅을 받는다. 혹은 거창하게 컨설팅이라고 안 붙여도 이미 해본 사람들에게 조언을 얻거나 인터넷에서 검색하거나 책을 보거나 하는 이유가 바로 이것이다.
막연하다면 그냥 혼자서 끙끙 앓지 말고 주위에 물어보거나 정보를 더 구하면 될 듯하다. 그러나 때로는 무슨 일을 해야 하는지 명확지 않을 수도 있다. 그럴 때는 일을 지시한 사람과 더 의사소통을 해야 한다. 대충 이건가보다 하고 해서 맞으면 다행이지만(그럴 확률은 그다지 높지 않다), 아니라면 실컷 일하고도 결과는 없는, 혹은 더 나쁜 상황이 된다.
선뜻 결정을 내리지 못하는 것에 대해서도 생각해 보자. 예를 들어 전공을 결정할 때, 그 전공이 무엇인지 그 전공을 하고 났을 때의 진로는 어떻게 되는지 등의 정보가 아주 자세하다면, 전공에 대해 많은 고민을 하지 않고도 결정을 내릴 수 있다. 따라서 일을 할 때는 충분한 정보를 얻는 것이 중요하다고 할 수 있고, 제대로 의사소통을 해서 내가 무얼 해야 하는지 알고 시작하는 것이 중요하다.
문제가 생겼을 때 막연해지곤 한다. 이유는 거기에 대한 지식이 부족해서 정보가 부족해서라고 생각한다. 그래서 know-how와 함께 know-where가 중요하다. 인터넷을 검색해도 넘쳐나는 정보 중에 내가 찾는 것이 어떤 것인지 잘 모르니까. 그래서 우리는 이런저런 공부를 하는 것이다. 단순한 지식의 습득보다 중요한 것이 실제로 어떻게 문제를 해결하는지 어떻게 적용하는 것인지를 아는 것이다. 이번 여행도 그런 맥락에서 접근했으면 한다.
[ 압축을 해라! ]
프로필러는 압축을 하면 무진장 줄어드니 일단 압축을 하도록 하자. 필자가 이메일로 추적 결과를 보내달라고 하면 자주 압축이 안 된 어마어마한 양의 추적결과를 보내온다. 그래서 100MB의 용량을 보내려고 시도하는데, 압축률이 좋아서 압축하면 무척 작아진다. SQL 서버의 백업을 받은 백업 파일도 마찬가지다.
[ 차단과 잠금 옵션의 종류 ]
SQL 서버는 한 트랜잭션이 X(배타) 잠금을 걸었을 때, 다른 트랜잭션은 S(공유) 잠금을 걸 수 없다. 정확히 말하면, 오라클과 달리 SQL 서버에는 before image, after image라는 것이 현재 버전에서 존재하지 않는다. 따라서 트랜잭션이 진행되면 다른 트랜잭션은 기다리거나(차단당한다), 아니면 dirty read 즉 read uncommitted를 해야 한다. 은행 업무와 같이 특별히 트랜잭션 처리가 필요한 경우라면 몰라도 일반적인 게시판 업무나 웹 업무의 경우는 대부분 dirty read를 해도 된다. 따라서 SQL 서버에서는 가능한 디폴트 잠금 수준인 read committed 수준보다 잠금 수준을 낮춰서 dirty read를 허용할 수 있는 모든 상황에서 잠금 수준을 낮추는 것이 절대적으로 필요하다.
◆ 실제 사례 : 초기 화면이 뜨는 데 30초가 넘어 결국 timeout이 걸리던 유명한 사이트에서는 잠금 힌트의 설정만으로 모든 문제가 해결됐다.
출처 : 마이크로소프트웨어[2004년도 7월호]
"MSSQL" 카테고리의 다른 글
- 검색 제한자 깊이보기 (0)2007/05/21
- 뷰를 실체화하기 (0)2007/05/21
- 지금 SQL 서버에서는 어떤 문제들이 벌어지고 있을까? (1)2007/05/21
- SQL Server 2000에서 varchar와 char 데이터 타입 (0)2007/05/21
- SQL 서버 2005 보안 (0)2007/05/21
Leave your greetings.
내용은 좋은데 글꼴이 눈아프고 잘 안보이네요. ^^
2009/07/17 09:46 [ Permalink : Modify/Delete : Reply ]