Database Optimizer 2
대용량 데이터베이스 솔루션 II 의 공통저자인 조광원님의 사례.
1. 보험사의 Data Modeling 개선
子(놈 자) Table이 너무 많다.
왠만한 Data 설계에서 놈 자 Table은 하나면 족하다.
계약자, 납입자, 피보험자, 수혜자 등등....
모두 고객이라는 Table 하나로 합치고 이것과 Relation을 맺는 것으로 해결이 된다.
2. 한 카드사의 매월 청구서 발행을 위한 결산을 하는데 2주가 걸리는 시스템이 있다.
매월 평균 고객정보 1600만건
주문 2000만건 의 정보가 Input 된다.
카드시스템의 특성상 매일 각종 이벤트, 할인율, 무이자할부 여부 등이 달라서 하루 단위로 결산금액을 측정해서
30일치를 합쳐야 한다.
위 작업을 10일 이내로 계산될수 있도록 수정해달라고 컨설팅 받음
CUST : 고객 Table
#custno : P.K. 고객번호
- zip-code : Index
ORDER : 주문 Table
#ord_num : PK, 주문번호
- custno + ord_date : Index
SELECT B.custno, A.ord_date ...
FROM ORDER A, CUST B
WHERE A.custno = B.custno (+)
AND B.zip-code = :v1
AND A.ord_date like '2014.06.23%'
위의 Query문을 실행시키기 위해서는 당연히 CUST와 ORDER Table에 Index를 위와 같이 잡았을 것이다.
이제 위의 실행문을 한번 분석해보자.
Outer Join 인 경우 Outer Join이 아닌 쪽으로 무조건 Driving 된다.
어찌보면 당연한 말이다. Outer Join 이라는 것이 Left 에는 있고 Right에는 없는 항목들에 대해서 NULL로 처리해서라도 값을 보여주는 것이기 때문에 Right를 Driving 할 수는 없는 것이다.
당연히 B Table을 Driving 하면서 A를 Scan 해야한다. B 에서 특정 zip-code 의 사용자들을 찾아서 B.custno를 Constant 처리 후 A를 Scan 하는데 A에 ord_date로 시작하는 Index가 없다.
2000만건을 Full-Scan 하여 6월23일자 주문을 10만건을 찾았다고 가정해보자. 그리고 A 의 고객중 특정 zip-code의 고객이 한 100명이라고 가정하고 위의 Query의 결과가 200건 정도가 나왔다고 치자. 그리고 그 시간은 1시간 가량 걸렸다.
하지만 그 과정에서 2000만건을 Full-Scan 하여 뽑은 10만건으로 100건과 Join 하여 200건이 나왔다면 얼마나 비효율 적인가 ?
그럼 해결책은 뭔가 ?
Outer Join을 남겨둔체 어떤 짓을 해도 A가 Driving 될 수없으므로 답이 없다. 위의 Query에서 Outer Join만 없에도 0.1초만에 바로 결과가 나올 뿐더러 그 결과는 똑같다.
왜 Outer Join을 남겨놓았을까 ?
한번 유추해보자면 주문쪽을 개발하는 개발자가 있었을테고, 개발당시 고객정보 Table이 Migration이 되지 않아서 깡통 Table로 Test를 하였을 것이다. 그때 고객정보가 NULL이라도 개발진행에 지장이 없게 하기 위해서 Outer Join을 걸었던 것으로 예상된다. 그럼 개발이 완료된 뒤에 풀어주면 되지 않느냐 ? 대한민국 SI 특성상 대부분 갑을병정무기.... 로 진행되며 거기 투입된 개발자들은 뒤도 안돌아보고 바로 나가고 위에서는 현재 잘 돌아가고 있는 시스템 괜히 건드려서 답 틀리게 나올까봐 건드리지 못하게 한다.
이럴 경우 개발 당시부터 CUST Table에 9999 Unknown 등의 Dummy 값을 넣어서 Test를 하였으면 저런 일이 줄어들지 않았을까 ?
결과적으로 기존 Program으로는 답이 없어서 결제쪽 S/W 개발을 새로 해서 2주 정도가 걸렸으며, 기존에 2주 걸리던 작업을 19시간으로 줄였다고 한다.
수행속도 향상을 위한 필수 항목
1. 병렬 처리
Program을 Parallel로 여러개 띄우라는 말이 아니라 SQL문 자체를 D/B Optimizer가 Parallel하게 수행 할 수 있도록 설계
2. Multi-thread, Multi-server
옛날 Dedicated-Server의 경우 Dedicate 하나당 Client를 전담해서 Background Process를 사용했다. 결과적으로는 Peer-to-peer 방식과 같은 자원을 소비하게 된다.
3. Memory의 최적 활용
아무리 Memory를 크게 하더라도 그걸로는 해결이 안된다.
예를 들어서 온라인쇼핑몰의 경우 당일의 주문 Data가 가장 많이 사용될 것이다. 당일 주문에 대한 결제, 발주, 배송, 조회 등의 작업이 가장 많이 이루어 진다. 그래서 당일 주문 Data가 메모리에 항상 올라와 있으면 Hit-Ratio가 높아서 시스템이 원할하게 돌아가겠지만...
어디선가 누군가에 무슨일이 생겨서 갑자기 주문 Table을 Full-Scan하는 일이 발생한다면 ???
메모리는 전혀 사용하지 않는 시절의 주문 Data로 채워질 것이며, 그 이후 당일 주문Data 관련 작업을 하는데는 엄청난 시간이 들것이다.
4. LOCKING의 해소
Transaction의 적절한 사용
5. Stored Procedure의 활용
6. 시스템 진달 툴의 활용
7. 부분범위 처리
8. 인덱스의 활용
10. 액세스 효율의 향상
11. JOIN의 최적화
12. 클러스터링
13. VIEW의 최적 활용
14. ARRAY PROCESSING
15. I/O의 분산
16. SORT의 튜닝
17. 적절한 Data Type의 사용
18. SQL의 활용
19. 반 정규화(Denormalize)
대용량 Database의 도전
Data는 점점 커지고 있으며 그 처리는 본질절인 차이가 있다.
1억건의 Data에서 100만건을 찾기 위해서 과연 Index-Scan이 빠른가 ? 대부분의 대용량 데이터베이스 이론서적에는 Index-Scan 과 Full-Scan의 손익분기점을 10% 이내로 정의하고 있다. 하지만 간과해서는 안될 문제가 하나 있다.
Index-Scan은 1 Block I/O 이고 Full-Scan의 경우는 Multi-Block I/O 이다. Index로 100만번 1 Block I/O를 하는것보다는 1억건에 대해서 Full-Scan Multi-Block I/O를 하는것이 훨씬 더 빠를 수가 있다.
원하는 Performance를 위해서는
- Speed UP : 보우 환경하에서 보다 빠른 처리
- Scale UP : 추가적인 H/W 구입
- Size UP : 보다 대용량의 H/W 필요
가 필요하다.
하지만 가장 중요한 기술적 돌파구는 ?
올바른 Data Modeling 과 Database Design 이다. Optimizer의 원리를 정확히 이해하고 Optimizer가 제대로 동작할 수 있도록 떡을 던져주어서 최적의 성능을 낼 수 있도록 해 주어야 한다.
Optimizer를 제대로 이해 못하는 사람이라면 SQL문을 실행 후 10초가 지나도 답이 안나와도 계속 나올때까지 기다리게된다.
하지만 Optimizer를 제대로 아는 사람이라면 ? 해당 Query문의 조건과 실행계획 등을 파악해서 SQL문을 실행했는데 2초가 지나도 안나오면 바로 취소하고 자신이 원한 실행계획대로 제대로 되었는지를 분석한다. 그리고 자신이 원하는 방향으로 Optimizer가 실행이 안 됬을 경우 그렇게 할 수 있게끔 조치를 취한다.
Project 성공 3 요소
1. 단순 명료한 설계
RDMBS에 맞게 올바른 Data Modeling 과 Database Design
2. 기술 리더 확보
핵심 문제를 해결 할 수 있는 리더(DBA)의 양성. DBA는 단순 잡무를 해주는 사람이 아니다.
3. 개발자 인식전환
확실히 RDBMS의 Optimizer를 이해하고, 100건/1000건에서 Test 한 결과로만 판단하지 말고, 3천만건/1억건에 대해서도 문제없는 시스템을 개발할 수 있는 개발자가 되어야 한다.
대용량 데이터베이스 솔루션 II 의 공통저자인 조광원님의 사례.
1. 보험사의 Data Modeling 개선
子(놈 자) Table이 너무 많다.
왠만한 Data 설계에서 놈 자 Table은 하나면 족하다.
계약자, 납입자, 피보험자, 수혜자 등등....
모두 고객이라는 Table 하나로 합치고 이것과 Relation을 맺는 것으로 해결이 된다.
2. 한 카드사의 매월 청구서 발행을 위한 결산을 하는데 2주가 걸리는 시스템이 있다.
매월 평균 고객정보 1600만건
주문 2000만건 의 정보가 Input 된다.
카드시스템의 특성상 매일 각종 이벤트, 할인율, 무이자할부 여부 등이 달라서 하루 단위로 결산금액을 측정해서
30일치를 합쳐야 한다.
위 작업을 10일 이내로 계산될수 있도록 수정해달라고 컨설팅 받음
CUST : 고객 Table
#custno : P.K. 고객번호
- zip-code : Index
ORDER : 주문 Table
#ord_num : PK, 주문번호
- custno + ord_date : Index
SELECT B.custno, A.ord_date ...
FROM ORDER A, CUST B
WHERE A.custno = B.custno (+)
AND B.zip-code = :v1
AND A.ord_date like '2014.06.23%'
위의 Query문을 실행시키기 위해서는 당연히 CUST와 ORDER Table에 Index를 위와 같이 잡았을 것이다.
이제 위의 실행문을 한번 분석해보자.
Outer Join 인 경우 Outer Join이 아닌 쪽으로 무조건 Driving 된다.
어찌보면 당연한 말이다. Outer Join 이라는 것이 Left 에는 있고 Right에는 없는 항목들에 대해서 NULL로 처리해서라도 값을 보여주는 것이기 때문에 Right를 Driving 할 수는 없는 것이다.
당연히 B Table을 Driving 하면서 A를 Scan 해야한다. B 에서 특정 zip-code 의 사용자들을 찾아서 B.custno를 Constant 처리 후 A를 Scan 하는데 A에 ord_date로 시작하는 Index가 없다.
2000만건을 Full-Scan 하여 6월23일자 주문을 10만건을 찾았다고 가정해보자. 그리고 A 의 고객중 특정 zip-code의 고객이 한 100명이라고 가정하고 위의 Query의 결과가 200건 정도가 나왔다고 치자. 그리고 그 시간은 1시간 가량 걸렸다.
하지만 그 과정에서 2000만건을 Full-Scan 하여 뽑은 10만건으로 100건과 Join 하여 200건이 나왔다면 얼마나 비효율 적인가 ?
그럼 해결책은 뭔가 ?
Outer Join을 남겨둔체 어떤 짓을 해도 A가 Driving 될 수없으므로 답이 없다. 위의 Query에서 Outer Join만 없에도 0.1초만에 바로 결과가 나올 뿐더러 그 결과는 똑같다.
왜 Outer Join을 남겨놓았을까 ?
한번 유추해보자면 주문쪽을 개발하는 개발자가 있었을테고, 개발당시 고객정보 Table이 Migration이 되지 않아서 깡통 Table로 Test를 하였을 것이다. 그때 고객정보가 NULL이라도 개발진행에 지장이 없게 하기 위해서 Outer Join을 걸었던 것으로 예상된다. 그럼 개발이 완료된 뒤에 풀어주면 되지 않느냐 ? 대한민국 SI 특성상 대부분 갑을병정무기.... 로 진행되며 거기 투입된 개발자들은 뒤도 안돌아보고 바로 나가고 위에서는 현재 잘 돌아가고 있는 시스템 괜히 건드려서 답 틀리게 나올까봐 건드리지 못하게 한다.
이럴 경우 개발 당시부터 CUST Table에 9999 Unknown 등의 Dummy 값을 넣어서 Test를 하였으면 저런 일이 줄어들지 않았을까 ?
결과적으로 기존 Program으로는 답이 없어서 결제쪽 S/W 개발을 새로 해서 2주 정도가 걸렸으며, 기존에 2주 걸리던 작업을 19시간으로 줄였다고 한다.
수행속도 향상을 위한 필수 항목
1. 병렬 처리
Program을 Parallel로 여러개 띄우라는 말이 아니라 SQL문 자체를 D/B Optimizer가 Parallel하게 수행 할 수 있도록 설계
2. Multi-thread, Multi-server
옛날 Dedicated-Server의 경우 Dedicate 하나당 Client를 전담해서 Background Process를 사용했다. 결과적으로는 Peer-to-peer 방식과 같은 자원을 소비하게 된다.
3. Memory의 최적 활용
아무리 Memory를 크게 하더라도 그걸로는 해결이 안된다.
예를 들어서 온라인쇼핑몰의 경우 당일의 주문 Data가 가장 많이 사용될 것이다. 당일 주문에 대한 결제, 발주, 배송, 조회 등의 작업이 가장 많이 이루어 진다. 그래서 당일 주문 Data가 메모리에 항상 올라와 있으면 Hit-Ratio가 높아서 시스템이 원할하게 돌아가겠지만...
어디선가 누군가에 무슨일이 생겨서 갑자기 주문 Table을 Full-Scan하는 일이 발생한다면 ???
메모리는 전혀 사용하지 않는 시절의 주문 Data로 채워질 것이며, 그 이후 당일 주문Data 관련 작업을 하는데는 엄청난 시간이 들것이다.
4. LOCKING의 해소
Transaction의 적절한 사용
5. Stored Procedure의 활용
6. 시스템 진달 툴의 활용
7. 부분범위 처리
8. 인덱스의 활용
10. 액세스 효율의 향상
11. JOIN의 최적화
12. 클러스터링
13. VIEW의 최적 활용
14. ARRAY PROCESSING
15. I/O의 분산
16. SORT의 튜닝
17. 적절한 Data Type의 사용
18. SQL의 활용
19. 반 정규화(Denormalize)
대용량 Database의 도전
Data는 점점 커지고 있으며 그 처리는 본질절인 차이가 있다.
1억건의 Data에서 100만건을 찾기 위해서 과연 Index-Scan이 빠른가 ? 대부분의 대용량 데이터베이스 이론서적에는 Index-Scan 과 Full-Scan의 손익분기점을 10% 이내로 정의하고 있다. 하지만 간과해서는 안될 문제가 하나 있다.
Index-Scan은 1 Block I/O 이고 Full-Scan의 경우는 Multi-Block I/O 이다. Index로 100만번 1 Block I/O를 하는것보다는 1억건에 대해서 Full-Scan Multi-Block I/O를 하는것이 훨씬 더 빠를 수가 있다.
원하는 Performance를 위해서는
- Speed UP : 보우 환경하에서 보다 빠른 처리
- Scale UP : 추가적인 H/W 구입
- Size UP : 보다 대용량의 H/W 필요
가 필요하다.
하지만 가장 중요한 기술적 돌파구는 ?
올바른 Data Modeling 과 Database Design 이다. Optimizer의 원리를 정확히 이해하고 Optimizer가 제대로 동작할 수 있도록 떡을 던져주어서 최적의 성능을 낼 수 있도록 해 주어야 한다.
Optimizer를 제대로 이해 못하는 사람이라면 SQL문을 실행 후 10초가 지나도 답이 안나와도 계속 나올때까지 기다리게된다.
하지만 Optimizer를 제대로 아는 사람이라면 ? 해당 Query문의 조건과 실행계획 등을 파악해서 SQL문을 실행했는데 2초가 지나도 안나오면 바로 취소하고 자신이 원한 실행계획대로 제대로 되었는지를 분석한다. 그리고 자신이 원하는 방향으로 Optimizer가 실행이 안 됬을 경우 그렇게 할 수 있게끔 조치를 취한다.
Project 성공 3 요소
1. 단순 명료한 설계
RDMBS에 맞게 올바른 Data Modeling 과 Database Design
2. 기술 리더 확보
핵심 문제를 해결 할 수 있는 리더(DBA)의 양성. DBA는 단순 잡무를 해주는 사람이 아니다.
3. 개발자 인식전환
확실히 RDBMS의 Optimizer를 이해하고, 100건/1000건에서 Test 한 결과로만 판단하지 말고, 3천만건/1억건에 대해서도 문제없는 시스템을 개발할 수 있는 개발자가 되어야 한다.
댓글 없음:
댓글 쓰기