Post List

2015년 11월 16일 월요일

DB성능개선방안

I.    데이터 처리Business Agility 수행을 위한 데이터베이스 튜닝(DB Tuning)의 개요
가.데이터베이스 튜닝의 정의
- 환경(파라미터), DB어플리케이션(sql), 구조(Data모델)의 조정을 통하여 Database의 성능을 향상시키는 성능최적화 기술
- 데이터베이스 응용, 데이터베이스 자체, 그리고 운영체제의 조정 등을 통하여 최적의 자원으로 최적의 성능(응답속도)를 얻을 수 있도록 개선하는 작업
- End user인 고객관점에서 빠른 업무 처리를 지원하기 위해 데이터베이스가 처리 성능을 보장하는 모든 개념
나.정보시스템 성능과 관련된 주요 요소
1)    H/W관련
-    CPU, Memory, N/W, Disk …… System configuration resources 부족
2)    S/W관련
-    DBMS: DB Design, Optimizing 전략, SQL 효율
-    Application Architecture: 2-tier, 3-tier, EJB, OLTP, OLAP(adhoc), Batch, c, COBOL, PL/SQL, JSQL
3)    업무프로세스 관련
-    업무 처리방식의 문제, Load Balancing 정책


다.데이터베이스 성능저하의 주요요인
- 대부분의 성능저하 주요 원인은 DB관련 비효율
- 성능저하의 핵심은 CPU/Memory 부하가 아니 비효율적인 I/O

- DB처리 효율화가 핵심


가.    DB의 좋은 성능을 위한 조건
-개선순서: 설계관점 -> DBMS관점 -> SQL관점
주요요소
설 명
사 례
설계관점
(모델링관점)
- 데이터 모델링, 인덱스 설계
- 데이터파일, 테이블 스페이스 설계
- 데이터베이스 용량 산정
- 반정규화
- 분산파일 배치
DBMS관점
- CPU, 메모리 I/O에 관한 관점
- Buffer, Cache크기
SQL관점
- join, Indexing, SQL Execution Plan
- Hash / Join

I.    Database 튜닝의 절차 및 효과
가.   Database 튜닝의 절차
절 차
설 명
수 행

시스템 성능 진단

튜닝/분석 단계
결과 단계

- 시스템의 현 상태와 문제점을 도출하기 위한 과정
- 사용자 인터뷰, 설계검토, 시스템 구성 검토, 자원사용현황 검토, SQL Trace 분석 등 수행
- 사용자 인터뷰
- 설계, 시스템 구성도검토
- 자원사용현황분석
- SQL Trace분석
- 진단결과 도출된 문제점에 대한 원인을 분석하고 해결방안 제시.
- 설계내용 튜닝
- SQL튜닝
- DBMS튜닝
- OS, H/W 튜닝
- 1차적으로 튜닝방안 적용 후 2차 테스트 분석이나 결과 평가를 통한 최종안 적용 및 개선효과 보고자료 작성
- 튜닝 후 자료수집/분석
- 튜닝결과 평가
- 산출물 작성
나.   데이터베이스 튜닝의 효과
- 최종 사용자들에 대한 고객 만족을 실현
- DBMS 시스템을 기반으로 한 Application의 품질 향상에 기여함
- 실무 경험을 통한 튜닝 방법과 관련한 지식 Database 구축으로
  지식 공유 가능

II.   Database 튜닝의 3요소 및 튜닝기법
가.    Database 튜닝의 3요소 
구 분
설 명
기법 예
DB설계
- 데이터베이스 설계 단계에서 성능을 고려하여 설계
Table 분할/통합, 식별자 지정, 인덱스 등
DB환경
- 성능을 고려하여 메모리나 블록 크기 등을 지정
CPU, 메모리 등
SQL문 튜닝
- 성능을 고려하여 SQL 문장을 작성
힌트, 부분범위 등
나.    DB 설계 측면의 튜닝
구 분
내 용
테이블의 분할/통합
- 논리적으로는 통합된 단일 테이블이지만 DBMS가 지원하는 파티션기능 적용함으로써 액세스 효율로DB I/O 를 최적화 할 수 있음
- DBMS에 따라 파티션기능이 제약되는 경우에는 테이블의 수평분할 고려
- 액세스 패턴에 따라 단일 테이블을 1:1 로 수직분할을 고려할 수 있음
식별자 지정
- 본질 식별자와 인조식별자의 선택에 따라 정보의 상속과 단절에 영향을 줄 수 있음
효율적인 인덱스 전략
- 최소한의 인덱스로 최대의 효과를 얻을 수 있는 최적의 인덱스 구조 수립
- 인덱스 분포도 15% 이하 유용 유도(15% 이상이면 Full Scan 유리)
적절한 데이터타입 선택
- 조인 시 연결되는 컬럼의 데이터 타입이 다른 경우 내부적인 변형에 의해 인덱스가 있음에도 불구하고 활용을 못해 조인순서나 조인방식을 달리 선택하는 비효율 발생 할 수 있음
다.    DBMS 환경에 따른 튜닝
- DBMSConfiguration Parameter 값들을 조정함으로써 최적의 DBMS 자원 활용이 이루어지도록 함
구 분
내 용
CPU튜닝
- Peak Time 시에 60~70%의 사용량 유지 권고
- CPU증설 또는 CPU 과다 점유하는 프로세스를 찾아서 해결
메모리튜닝
- DBMS의 사용 메모리의 최적화 필요
- 업무를 가능하게 하기 위한 충분한 메모리 확보
- DBMS 메모리 + Session 메모리
I/O튜닝
- I/O를 분산시킬 수 있도록 데이터 화일의 재배치
- Raid를 이용-> I/O가 많이 일어나는 것은 Raid0,1에 배치
네트워크튜닝
Ping, Ftp를 이용하여 응답시간 분석
라.    SQL 튜닝
구 분
내 용
예 시
옵티마이저에 대한 이해
- RBO (Rule Based Optimizer): 통계정보가 없는 상태서 미리 정해진 Rule에 따라 실행계획 수립
- CBO (Cost Based Optimizer): 통계정보로부터 모든 Access Path 고려하여 실행계획 수립
- 옵티마이저가 선택한 실행계획을 확인하고 최적화된 실행계획 수립이 이루어지도록 Factor 부여

SELECT /*+ rule */: RBO로 강제설정
SELECT /*+ all_rows*/:전체적인 처리능력 최적화(CBO)
힌트사용
- 옵티마이저가 항상 최적화된 실행계획을 수립하는 것은 아니므로 힌트를 사용하여 원하는 실행계획으로 유도
SELECT /*+ hint [ { hint } ... ] */
부분범위 처리
- 조건을 만족하는 전체집합이 아닌 일부분만 액세스하고도 결과를 리턴 할 수 있도록 하여 온라인 프로그램에서 응답시간(Response Time)을 최소화 할 수 있음
- 인덱스를 이용해서 sort 대체
- 인덱스만으로 액세스해도 되는 구조를 만듦
- max값을 구할 경우 인덱스 이용
- exists 활용
- rownum 활용
인덱스 활용
- 인덱스가 있음에도 불구하고 SQL을 잘못 기술함으로써 무용지물로 만드는 오류를 없애야 함
인덱스 활용을 위해 아래 경우 배제
- 인덱스 칼럼의 변형
- not operator
- null, not null 사용

조인방식/ 조인순서
- 동일한 SQL문이라도 조인방식과 조인순서에 따라 처리속도는 매우 큰 차이를 가져올 수 있으므로 작성한 SQL이 어떤 실행계획으로 수립되는 지 반드시 확인 후 조정

다중처리 (Array Processing)
- 배치작업의 경우 한번의 DBMS호출로 여러 건을 동시에 처리할 수 있는 다중처리 활용

병렬쿼리 (Parallel Query)
- 배치작업의 경우 하나의 SQL을 여러 개의 CPU가 병렬로 분할 처리하게 함으로써 처리속도 향상 가져옴
Insert /*+ parallel(member_tmp,8)*/ into member_tmp Select /*+ parallel(member,8)*/ from member;
Dynamic SQL 지양
- 조건절에 입력된 값을 먼저 Binding 한 후 실행계획을 수립하는 Dynamic SQL은 파싱 부하가 커지므로 입력 값을 Binding 하기 전에 실행계획을 수립하는 Static SQL을 가급적 사용하도록 함
Select * From Tab Where Col1 = 1;
Select * From Tab Where Col1 = 2;
Select * From Tab Where Col1 = 3;
è  
Select * From Tab Where Col1 = :v1;

III.  데이터베이스 성능 저하 예방방안
가.   상시 모니터링 관리
- 데이터베이스에 치명적인 문제가 발생 하기 전에 주기적으로 모니터링 되고 관리 되어야 함.
- 향후 튜닝에 필요한 정상적인 상황일 때의 데이터베이스 로그 정보 보관 필요
나.   개발자에 대한 데이터베이스 원리 교육
- 개발자에 대해서SQL 튜닝 기법 및 SQL 수행 원리 교육 필요
- 프로그램 알고리즘 교육 필요, Source Review 제도 도입
다.   1회성 작업이 아닌 지속적인 튜닝 실시
- 데이터베이스 튜닝은 1회성 작업이 아니라 지속적으로 이루어져야 함
라.   튜닝 결과에 대한Historical Information 구축
- 튜닝 수행 시 발생된 문제, 문제분석 결과, 해결 내용에 대해서 기업의 KMS에 등록이 필요, 즉 관리 되어야 함.