차세대 뱅킹시스템 프로젝트의 DBMS 튜닝 이슈 극복 사례 Special Key Note 차세대 뱅킹시스템 프로젝트의 DBMS 튜닝 이슈 극복 사례 (주) 오픈메이드 컨설팅 오 동 규 수석 컨설턴트

DBMS 튜닝 이슈사항 뱅킹 시스템 RDBMS 경험자 전무(AS-IS 는 Network DB) 업무변경 시 SQL튜닝 반영의 문제 CPU 사용율 60% 미만에서 초당 4700 TPS 를 확보하라

과연 우리 직원들이 시스템을 제대로 개발할 수 있을까? 이슈1 : 직원 RDBMS 경험자 전무 AS-IS 는 Network DB 라 직원들의 RDBMS 경험이 전무한 상태인데… RDBMS 를 사용해본 직원은 이번 프로젝트에 참여하지 못한다고 하네… OPEN 시점부터의 유지보수는 기존 직원들이 해야지. 그러면 개발단계부터 적극적으로 참여시켜야 되겠네… 상황 AS-IS 의 DB 가 Network DB 이므로 RDBMS 경험이 없음. RDBMS 를 사용하는 다른시스템 및 Legacy 시스템의 DBA 및 개발자들은 기존 시스템의 유지보수 관계로 차세대 뱅킹 프로젝트에 참여하지 못함. 외부업체의 개발자의 경우 상대적으로 RDBMS 에서 개발경험이 많으나 시스템 OPEN 직후부터의 유지보수는 은행직원들이 수행함으로 개발 시 직원의 적극적인 참여가 필수적으로 요구되는 상황임. 과연 시스템을 제대로 개발할 수 있을 것인가에 관한 의구심이 증폭됨. 과연 우리 직원들이 시스템을 제대로 개발할 수 있을까?

이슈1 : 직원 RDBMS 경험자 전무-해결 한 단계 빠른 ACTION이 중요함 분석단계에서의 모델링 교육 설계초기단계에서의 기본 SQL 교육 설계후반단계에서의 SQL 표준 확립 설계후반단계에서의 SQL 튜닝교육 상황 해결과정 1.분석단계에서 모델링교육을 실시하여 RDBMS 의 개념을 확립시킴 2.설계단계에서 SQL 기본 교육을 실시함 3.설계단계에서 SQL의 표준을 확립함. EX) : 페이징 처리표준 가이드, Dynamic SQL 사용제한, 대용량 데이터 처리 가이드, 다양한 검색조건하의 SQL 작성가이드, UPDATE 시 LOCK 처리 가이드, 공통코드 사용 가이드, MIN/MAX 값 처리 가이드 4.설계단계 후반부 및 개발초기단계부터 SQL 튜닝교육을 실시함 5.DA 팀에서 개발초기단계 부터 직원들의 SQL 품질점검을 실시하여 지속적으로 Feed Back 을 실시함. 품질점검 항목: 조인 누락 건, WHERE 조건이 없는 SQL, 공통코드 사용오류, WHERE 절의 컬럼에 함수사용, WHERE절 Like에 시작부문 %사용, WHERE절 여러 조건 입력으로 UNION ALL 분리 등등 여러 차례의 교육 및 초기 한달 간 의 품질점검 및 지속적인 Feed Back 결과 악성 SQL 이 사라짐은 물론이며 전반적으로 직원들의 SQL 작성 능력이 향상됨 위의 해결과정의 핵심은 한 단계 빠른 교육및 ACTION 을 취하였음을 알수 있음. 예를 들면 분석단계에서 모델링 교육을 해야 설계단계에서 모델링 시 효과가 발휘됨. 이에 따라 단기간의 노력으로 결국 5년 이상 개발경험이 있는 외부업체의 개발자와 대등한 실력을 갖추게 됨. 또한 open단계까지 큰 성능이슈가 발생되지 않았는데 위의 5단계의 과정이 가장 큰 역할을 하였음. 개발초기단계에서의 SQL 품질점검 한 단계 빠른 ACTION이 중요함

이슈2 : DBMS 설치 후 성능관련 파라 미터 튜닝이슈 성능관련 파라 미터의 Setting 이 늦어지고 있다. 일반적인 가이드는 존재하지만 우리 사이트에 딱 맞는 파라 미터는 어디에도 없다. 이대로 방치된다면 개발이 진행된 후에 테이블에 실제 데이터가 이행되면 성능 이슈가 생길 수 밖에 없다. 상황 개발 DB 가 설치된 지 한달 이 지났지만 성능관련 파라미터의 세팅을 완료하지 못함. 지연된 가장 큰 이유는 일반적인 가이드는 존재하지만 그 사이트에 맞는 정확한 파라미터값은 어디에도 존재하지 않음. 3.DBMS 벤더사 조차 초기값을 제시하지 못함. 개발이 진행되고 있는 상황에서 계속 지연될 경우 개발후기에는 실행계획이 대폭 바뀔 수 있으므로 빠른 시일 내에 최적의 성능관련 파라미터의 세팅이 필요함. 4.잘못되었을 경우 전체시스템에 악영향을 끼치므로 책임이 막중하여 DBMS 벤더사, DBA, 인프라팀 누구도 나서지 못함. 아무도 나서지 않으려고 하니 고민이군…

이슈2 : DBMS 설치 후 성능관련 파라미터 튜닝이슈-해결 튜닝경험이 많은 DA 팀이 주도적인 역할을 맡음 DBMS 벤더사는 동종업계의 타사사례를 참조하고 Bug 가 있는 경우 해결방안을 모색함 야간배치 보다는 주간의 거래에 치중한 파라미터를 설정함 악성 야간배치 SQL 은 힌트를 사용하여 튜닝함 두달간의 파라미터 미세조정기간을 두어 만약의 사태에 대비하며 지속적인 모니터링 실시 상황 해결과정 기술적인 문제보다 상황을 잘 파악하고 피해를 최소화 하는데 집중하여야 함. 튜닝경험이 가장 많은 DA 팀의 주도로 DBMS 벤더사및 DBA 팀과의 의견을 조율함. 신용신 시스템은 전형적인 OLTP 시스템 이므로 일단 인덱스 사용을 가장 먼저 고려해야 하므로 그에 맞게 파라미터를 세팅함. DBMS 벤더사에서 동종업계의 타사의 사례를 참조하여 문제점이 없는지 조사하고 버그가 있는경우 페치셋을 본사에 요청함. 4.야간에 실행되는 배치는 SQL 의 수가 많지 않으므로 악성 실행계획이 발견되면 힌트를 사용하여 튜닝 하기로 함. 5.초기 개발단계이므로 약 2달간의 파라미터 미세조정 단계를 두고 PLAN 을 모니터링 하기로 함. 5단계의 과정을 거쳐 파라미터는 성공적으로 세팅이 되었으며 결국 시스템을 OPEN 하고 보니 초기 DA 팀의 결정이 옳았으며 파라미터의 미세조정기간에도 성능관련 파라미터의 조정은 없었다. 책임의 전가 보다는 피해를 최소화 하는데 집중 해야 함

이슈3 : 업무변경 시 SQL튜닝 반영의 문제 당분간 SQL 튜닝을 받지 말아야 하나? DA 팀에서는 튜닝 후 항상 검증을 하므로 그럴 리가 없다고 하네… 최근에 SQL 의 결과가 많이 틀려지는데 누구의 잘못이지? SQL 튜닝 건 때문에 시간이 소모되어 개발 진척률이 저조하군… 상황 개발자가 작성한 SQL을 DA 팀에서 품질점검 및 튜닝을 수행할 경우 SQL을 수정해야만 하는 경우가 발생한다. 그런 다음 튜닝 된 SQL 은 개발자에게 전달되어 SQL 이 수정되게 된다. 이때 개발자가 수정을 잘못하여 SQL 의 결과가 틀릴 수 있다. 2. 또 하나의 경우는 DA 팀에서 원본 SQL 을 튜닝하고 개발자에게 전달하는데 이틀이 걸렸다. 하지만 그사이에 업무가 수정되어 원본 SQL 이 수정되는 경우가 있다. 하지만 DA 팀에서는 업무가 바뀐 것을 알지 못하므로 무조건 개발자에게 수정을 요청하는데 이때 해당 개발자가 업무가 바뀐 것을 확인하지 않고 DA 팀에서 수정한 SQL 로 바꿔버리는 경우가 있다. 이 경우에도 SQL의 결과가 틀리게 된다. 3.이런 일이 자주 발생될 경우 SQL 의 결과가 틀린 건이 많아지며 개발자는 DA팀에게 SQL 튜닝신청을 꺼리게 된다. 실제로 3000 여개의 SQL 중에서 약 100 개정도가 답이 틀리며 업무팀에서는 앞으로 상황이 더욱 악화될 것으로 예상함. 4.튜닝을 하면 값이 잘못 나온다는 소문이 돌기 시작하면서 DA 팀에서 SQL 을 튜닝 해도 개발자가 반영을 하지 않는 일이 발생됨. 심각한 상황이 발생됨. 5.SQL 의 결과 값이 틀릴 경우 개발자는 SQL의 수정을 DA 팀에서 하였으므로 개발자 자신은 잘못이 없음을 강조함. 당분간 SQL 튜닝을 받지 말아야 하나?

이슈3 : 업무변경 시 SQL튜닝 반영의 문제-해결 원인분석결과 업무의 변경이 주원인임 SQL 튜닝 시 검증과정을 보여줌 으로써 상호 신뢰회복 튜닝반영 절차제안 튜닝 반영 시 결과가 일치 하는지 개발자 검증이 필요함 튜닝 반영 시 업무가 바뀌었는지 확인이 필요함 업무가 바뀔 SQL 은 튜닝 요청 금지 업무가 바뀌었다면 해당 SQL을 DA 팀에 통보 해야 함 상황 해결과정 1.원인분석 – 분석결과 대부분이 튜닝 기간중에 업무가 바뀌는 경우 가 대부분이며 가끔 개발자가 수정을 잘못하는 경우로 판명되었다. 2.개발팀에게 DA 팀의 튜닝프로세스(SQL 튜닝 후에 답이 올바른지 검증 과정이 있음)를 실제로 보여주어 상호간의 신뢰를 회복함. 3.DA 팀에서 개발팀의 튜닝요청 시/튜닝반영 시 프로세스를 제안함 1) 튜닝된 SQL을 반영할때 반드시 개발자의 검증과정을 거칠 것 2) 바뀔 것으로 예상되는 SQL은 튜닝요청을 하지 말고 기다릴것 3) 업무가 바뀌었는지 SQL 반영 전에 반드시 확인할 것 4) 업무가 바뀐 SQL 이 있다면 즉시 DA 팀에 통보 할 것 (DA 팀은 그 SQL 을 튜닝 하는 대신에 다른 SQL을 튜닝 하여 효율이 극대화됨) 4.개발팀이 DA팀의 권고안을 받아들여 모든 문제가 해결됨. 신뢰회복이 중요함 튜닝반영시 프로세스 확립

이슈4 : CPU 사용 율 60% 미만에서 초당 4700 TPS 를 확보하라 DA 팀에서는 지속적인 SQL튜닝을 실시 했는데 이상하군… 파티션 정책 적용, 인덱스의 최소화, 전체거래의 70% 에 해당하는 공통, 고객, 상품, 요구불 거래에 대한 SQL은 이미 최적화 되었고… 항상 사용하는 IPPR 서비스에 대한 최적화도 실시 하였는데… 초당 4700 TPS 를 달성해야 하는데 초당 3000 TPS 밖에 나오질 안군 CPU 사용율도 문제네… 상황 성능목표 달성기준 : CPU소모량이 60% 미만에서 초당 4700 TPS 를 확보한다. A은행의 초당 최대 TPS 가 2100 정도임을 감안할 때 이것은 매우 높은 목표임을 인식함. 이를 확보하려면 평균처리시간은 건당 0.12 초를 확보하여야 한다. 참고로 AS-IS 에서는 초당 최대 TPS 는 1800 이었음. 1.OPEN 6개월 전 단위성능 테스트 : 1) 건당 0.12 초가 목표였으나 평균 2.5초가 나왔음. 2) DA 팀이 4월부터 품질점검 및 SQL 튜닝을 하였으므로 이것은 매우 실망스러운 결과임 3) 단말로부터 수신한 전문의 해석과 로깅을 하고 본 서비스를 Forward 시키는 서비스는 모든 거래 시작 시 사용하는 아주 중요한 서비스이므로 집중 튜닝을 실시함(이 서비스에서 사용하는 테이블에 파티션 정책반영, 불필요한 인덱스 제거, 업무팀의 주요거래테이블도 파티션 정책 반영) 2.OPEN 4개월 전 1차 통합 성능 테스트: 1)성능이 초당 2000 TPS 정도밖에 나오지 않고 CPU 사용율도 매우 높음. 2)모든 거래에서 사용하는 고객,상품,공통쪽 SQL 에 대하여 집중 튜닝 실시 3.OPEN 3개월 전 2차 통합 성능 테스트: 1)목표 달성율이 64% 에 그침으로서 OPEN이 3달밖에 남지 않은 상황이었으므로 매우 급박한 상황이었음. 2)성능튜닝은 DB 튜닝만으로는 해결하기 어려워 OS 의 패치적용, OS 의 커널 파라미터 튜닝, 개발 프레임워크의 성정파일 수정등을 통하여 1차 실거래 테스트에 대비함. 3)지속적인 튜닝에도 불구하고 전체적인 Apllication 의 성능이 나오지 않는 원인에 대한 규명이 필요해짐. 목표성능을 포기해야 하나?

이슈4 : CPU 사용율 60% 미만에서 초당 4700 TPS 를 확보하라-해결 AP 성능 개선조직을 새로 구성 CPU를 많이 소모하는 SQL 및 모듈을 집중 튜닝실시 중복된 모듈호출 방지 전체적인 시스템 차원의 튜닝 DISK OS DBMS 네트워크 Gate Way TP(미들웨어) AP 문제해결과정 1.DA 팀의 SQL 튜닝 만으로는 APPLICATION 의 성능을 높이는 것은 한계가 있다. 왜냐하면 일부 개발자들의 경우 집합적 처리(조인) 이 아니라 절차적인 개발방법( LOOP 에의 한 커서의 fetch) 등을 이용하는 경우가 있다. 이 경우 DA 팀에서 전체 프로그램 (PRO*C) 을 조사해야 하는 상황이지만 인원수의 부족(6명)과 시간의 부족(SQL 튜닝 하기에도 벅참), Application 의 전문성 부족(DB 전문가가 Application 전문가는 아님)등의 문제로 현실적으로 불가능함. 2. 반복적인 작업의 제거가 필요하다. 예를 들면 자동이체 거래 시 계좌번호를 검증하는 모듈을 호출하게 되는데 이것은 이미 수신모듈을 call 하는 과정에서 검증이 되었으므로 2번 실행할 필요가 없다. 이런 비효율적인 반복 작업을 줄여야 목표달성이 가능한데 이것을 하려면 프레임웍 전문가 조직이 필요하다. 3.DA 팀에서도 단순히 처리속도 최적화에서 벗어나 CPU 를 많이 소모하는 SQL 을 집중 튜닝함. 4.DA 팀에서 DB 튜닝 만으로는 목표달성이 힘들다고 판단 하여 AP 성능 개선팀을 새로이 구성하여 전체적인 시스템차원의 튜닝이 가능해짐 이후 AP 튜닝팀 에서 발견한 절차적 처리(LOOP 처리) 등을 발견하여 DA 팀에 통보하고 조인으로 바꾼 후에 SQL 튜닝을 실시함 이에 따라 단기간(2달)에 성능목표의 95% 에 도달됨. 5.전체 성능관련팀의 공조가 요구됨으로 지속적인 접촉을 실시함. DISK 성능, 네트워크, OS, TP(미들웨어) AP, 전문을 처리하는 G/W, 대외망팀 등의 협력으로 약 한 달을 남기고 기적적으로 목표를 달성함. 튜닝은 전체적인 시스템 관점에서 팀간 협업및 공조가 중요함

차세대 뱅킹시스템 프로젝트의 DBMS 튜닝 이슈 극복 사례 감사합니다 Special Key Note 차세대 뱅킹시스템 프로젝트의 DBMS 튜닝 이슈 극복 사례 감사합니다 (주) 오픈메이드 컨설팅 오 동 규 수석 컨설턴트 Blog : http://scidb.tistory.com Email : odong91@hanafos.com