[완료] DB에서 빈번한 COMMIT이 프로세스의 성능저하를 불러올 수 있는지 궁금합니다.
글쓴이: ljs0766 / 작성시간: 목, 2008/07/03 - 6:13오후
현재 MSSQL DB에 저장되어 있는 수십만건의 데이터를 초당 100건씩 가져와서
처리하고 1건 처리가 끝날때마다 COMMIT하는 방식으로 프로세스를 작성하였습니다.
해당 프로세스는 상시 돌면서 테이블을 모니터링하다가 데이터를 가져가서 처리하는 일을 합니다.
우선 사내에서 사이트에서 제게 제공한 장비와 거의 비슷한 장비에서 테스트할 때는
초당 100건씩 처리를 잘하였으며 그 결과를 믿고
사이트에 와서 프로세스를 기동시키고 테스트를 하는데 성능이 정확히 절반으로 떨어지더군요. -ㅁ-;
그래서 제 노트북을 사이트에서 제공한 테스트 장비와 유사하게 환경을 꾸미고
테스트 해보았는데 초당 100건씩 처리를 잘하더군요 -ㅁ-;
혹시나 해서 윈도우즈 성능 모니터링 툴을 통해서 관찰한 결과 사이트에서 제공 해준 테스트 장비의
I/O가 엄청나게 발생하고 있었으며 제 노트북은 I/O가 거의 발생하지 않더군요.
그래서 SQLIO를 통해서 I/O 성능 테스트를 해본 결과 서버측 성능이 정확히 2개가 좋은 걸로 나왔습니다.
저번에 들어간 사이트도 건당 COMMIT을 하다가 이런 일이 있었는데...즉 실제 사내 테스트 할 당시는
잘되는데 사이트들어가서 테스트하는 순간 성능 저하문제가 생겼습니다.
어떻게든 원인 분석을 해서 현상을 해결해야 할것 같은데 참 막막합니다.
혹시 이런 현상 겪으신분이나 해결방법 알고 계신분 답변 부탁드립니다.
Forums:
예, 빈번한 commit은 상당한 성능 저하를 불러올 수 있습니다
commit을 시키면 디스크에 한번 쓰기가 완료되어야 commit이 완료되기 때문에, "작업 => commit => commit 끝날 때까지 대기 => 다음 작업 => ..." 이런 식으로 작업할 경우 성능이 매우 저하될 수 있습니다.
하드디스크가 대단히 좋다면 또 모르겠지만, 일반적인 PC급 하드디스크라면 commit 한번에 수십 ms 정도는 잡아먹더군요. 혹시 노트북에서는 commit할 때 디스크에 쓰지 않도록 설정이 되어 있는 것이 아닌지 한번 확인해 보세요. (이러면 commit 속도는 비약적으로 빨라지지만 durability를 보장 못하므로 제대로 된 DB라고는 할 수 없죠.)
여러 개의 데이터를 모아서 한번에 commit을 하거나, 아니면 thread를 사용해서 이전 commit이 끝날 때까지 기다리지 않고 별도의 session에서 다음 데이터를 처리하게만 해도 상당한 성능 향상을 불러올 수 있을 것입니다.
* DISCLAIMER: 이상은 일반론입니다. MSSQL을 써본 적이 없으므로 MSSQL 특유의 문제점이나 해결 방법이 있는데 제가 모르는 걸 수도 있습니다.
일반론이라고 하셔서 질문을 드립니다.
일반론이라고 하셔서 질문을 드립니다.
저는 commit을 한다고 해서 실제 물리적 하드디스크에 기록이 반드시 발생하는 것은 아니라고 알고 있었는데요, 커밋을 하면 SCN이 증가하고 관련 컨트롤 정보를 하드디스크에 기록이 되긴 하지만 그 동안 DB에 작업한 내용들이 반드시 그 시점에 하드디스크로 기록되는 것은 아니라고 봅니다.
하나의 예로 수만건의 업데이트를 하나의 쿼리를 통해 날리더라도 '몇 건이 업데이트되었습니다.'란 메시지가 뜨기까지가 오래 걸리지, 커밋을 하는것은 순식간에 수행됩니다. 커밋은 세션, 작업의 단위에 따라 수행되어야 하는 것이라고 생각하는데요...
한 번의 작업 단위가 고작 레코드 하나를 수정하는 것과 같이 비용이 적은 작업이고, 또 그러한 작업들이 수 많이 수행된다면, 그 때 하나하나의 작업 사이에 커밋을 하는 것이 문제가 된다고 생각합니다.
말씀하신데로, 여러 개의 작업을 모아 한 번에 커밋을 하면 해결책이 될 수 있습니다. 하지만 스레드를 통해 다른 작업의 커밋에 관계없이 다른 세션으로 마구 삽입, 갱신 작업을 하는 것은 좋지 않다고 생각합니다. 오라클에서도 이러한 경우에는 예기치 않게 snapshot too old 메시지가 떨어지는 걸로 알고 있는데요..
처음 질문하신 분의 경우에는 100건씩 데이터를 클라이언트로 가져와 절차적인 프로세스로 그 데이터를 가공하는게 아니라, SQL로써 DB 서버 측에서 처리되도록 하면 옵티마이저가 알아서 IO를 상당히 줄일 수 있을 거라고 생각합니다.
정확히 말씀드리자면...
commit이 완료되는 시점에서 보았을 때 해당 transaction에서 한 일이 *모두* 디스크에 저장되어 있어야 하는 것이 맞습니다. 좀 더 정확히 말하면, "commitdl 완료된 바로 그 시점"에 전기가 나가서 서버가 죽었을 때 디스크에 저장된 정보만 보고서 해당 transaction에서 데이터를 어떻게 고쳤는지 100% 알아내서 DB에 반영하는 것이 가능해야 합니다. 이른바 ACID property의 Durability에 해당하는 것이죠.
수만 건의 업데이트를 날릴 경우라면 DB가 자체적으로 판단해서 업데이트를 하면서 그 내용을 바로 disk에 반영하거나 (물론 이 경우 "아직 transaction 진행중임"이라고 적어넣습니다) 아니면 cache에 담고 있다가 commit 시점에 반영하거나 할 수 있습니다. 전자의 방법을 택했다면 commit 시에는 "그 transaction 이제 끝났음"이라는 한 마디만 적으면 되니 시간이 별로 안 걸리는 것처럼 보이죠. 하지만 아무리 사람이 보기엔 빠른 것처럼 보여도 disk write를 한번 완료해야 하고 컴퓨터 입장에선 적은 시간이 아닙니다.
다시 말해 "commit => 완료까지 기다림 => 다음 작업 => commit => ..." 이런 식으로 반복하는 것은 disk I/O를 병목으로 만드는 것이고, 각각의 transaction이 해야 하는 작업이 작을 경우 CPU는 사실상 손가락빨고 기다리게 됩니다. 요즘 나오는 DBMS라면 성능 향상을 위해서 "동시에 여러 개의 세션이 작업하며 각자 commit하는 경우" 성능을 최대한 낼 수 있도록 여러 수단을 강구하고 있습니다. 이렇게 하지 않는 한 방금 말씀드린 것과 같은 병목 현상을 피할 수가 없기 때문입니다.
즉 "다른 작업의 커밋에 관계없이 다른 세션으로 삽입, 갱신 작업을 한느 것"은 잘못 쓰면 문제가 될 수도 있겠지만 원칙적으로는 안좋은 방법이 아니라 매우 훌륭한 방법이고 DB를 정석적으로 쓰는 것입니다. 현대 DBMS의 개념 자체가 이렇게 "여러 개의 세션이 각자 알아서 작업하고 각자 알아서 커밋하는 것"을 최대한 효율적으로 지원하는 것을 지상목표로 삼고 있습니다.
* 사실 말씀하신 것처럼 (가능한 상황이라면) 그냥 100건씩 모아서 커밋만 해도 병목이 사라질 것 같기는 합니다.
모아서 처리하는게 좀 불가능한게...
어느 한건의 메시지를 보내고 거기에 대한 결과가 돌아오면 바로바로 실시간으로 반영해서
과금데이터를 만들어야 하는 성격의 업무여서 업무 단위가 메시지 한건이라고 봐야합니다.
이런 경우는 역시나 메모리 DB를 고려해야 하는 것일 까요?
사이트에서 A사의 메모리 DB와 같은 제품은 별로 좋아하지 않고 자기들이 언제나 쓰던
제품만을 고집해서 참 난감합니다.
제가 DB를 잘 알고 쓰는 것도 아니라서 항상 이런 문제 생길때마다 설계를 어떻게 해야하는지도
항상 골치네요.
좋은 답변과 정보 감사합니다 ^^
혹시 관련된 정보를 더 주실 수 있으면 일반론적인 관점에서 좀 더 설명을 해주시면 정말 감사하겠습니다.
무지한 중생이라 ^^;;
음...오라클에선 jick님이 말씀하신 기능을 찾았는데..
COMMIT할 때 저장매체에 실제 기록하는 시점을 조절할 수 있는 옵션을 10g에서 지원하더군요.
9i에서도 그런 기능이 있는지 궁금하네요...
MSSQL쪽은 아무리 찾아도 모르겠습니다. ㅠ.ㅠ
혹시 아시는 분 계시면 도움 부탁드립니다.
Commit의 속도문제
1. 테스트 환경에서는 잘되는데 실 DB에서 잘 안되는 것은 당연한 현상입니다.
테스트환경에서는 DB에 이미 쌓여있는 데이타량이 적을 것이지만 실 환경에서는 적어도 수백만건은 쌓여 있을 수 있고요,
만약 CRUD중 C(INSERT)에 해당하는 작업이라면 DB에 10000건 미만의 데이타가 쌓여 있을 경우 100만건 이상의 데이타가
쌓여 있을 경우 INSERT하는 작업 시간이 심각하게 차이가 날 수 있습니다.
또 해당 테이블에 트리거나 이런 것들이 걸려있을 수도 있구요.
또한 저장매체가 바로 물려있는 디스크가 아니고 SAN이나 NAS일 가능성도 있습니다.
저장매체에 저장하는 시간을 조절해도 어차피 보틀넥(싸이는 속도 < 넣는속도)이 일어나는 것은 마찬가지일테니
원하는 답을 얻을 수는 없을 것입니다.
2. 해결책
1) 아마 해당 테이블의 테이블스페이스(data,index모두)를 손보는 것과 동시에
버퍼 캐쉬(MS SQL에도 있을지는 모르겠지만)을 늘려주는 방법
혹은 temporary table에 결과를 저장하시고 이것을 주기적으로 실제 테이블로 bulk collect
하는 방법이 있을 수 있겠습니다.(실 DB에 바로바로 반영이 안되도 되는 경우라면 고려해볼만한 방법입니다.)
2) 만약 고객의 환경상 이런 것들을 할 수 없는 환경이라면
commit을 10건마다 때리거나 하는 방법이 있을 수 있겠지만 별로 권장하고 싶지는 않습니다.
최악의 경우 10건의 데이타를 잃어버릴 수 있을 뿐만 아니라
commit 자체가 문제의 원인인지도 확실하지 않습니다.
왜 노트북에서 쌩쌩 잘 돌아가던 작업이 Legacy 가면 잘 안될까요? Legacy System사양이
노트북보다 나쁘지는 않을텐데 말이지요...
해결봤습니다.
사내장비/노트북은 윈도우즈의 디스크 쓰기 캐쉬 기능이 활성화 되어 있었고
테스트장비는 활성화가 되어 있지 않더군요.
옵션 변경도 못하게 되어 있고요...
사내장비에서 디스크 쓰기 캐쉬를 끄고 테스트해보니
사이트 환경에서 보여준 성능과 거의 비슷하게 나옵니다.
이거때문에 얼마나 고민을 했던지 ㅠ.ㅠ
아무튼 답변 달아주신분들 모두 너무 감사드립니다.
댓글 달기