데이터베이스 성능튜닝
태그 :
- 개념
- DBMS, 데이터베이스 응용, OS자체 분석 등을 통하여 최소의 자원으로 최적의 성능(시간/응답속도)을 얻을 수 있도록 개선 하는 작업.
1. 최적의 자원으로 최적의 성능을 위한 DB성능 튜닝의 개요
가. DB성능 튜닝의 목적
DBMS, 데이터베이스 응용, OS자체 분석 등을 통하여 최소의 자원으로 최적의 성능(시간/응답속도)을 얻을 수 있도록 개선 하는 작업.
DBMS, 어플리케이션, OS, N/W 등의 성능관련 대상을 분석, 조정을 통하여 DB성능을 향상시키는 일련의 과정과 기법
나. 데이터베이스 성능관리의 주요지표
수행시간측면: I/O Time + CPU Time
시스템 자원 사용 측면: CPU, Memory 등의 사용량 관점의 관리
처리량 측면: TPS 관점의 관리
다. 정보시스템 성능과 관련된 주요 요소
주요요소 |
설명 |
H/W 관련 |
-CPU, Memory, N/W, Disk … System config resources 부족 |
S/W 관련 |
-DBMS : DB Design, Optimizing 전략, SQL 효율 -Application Architecture : 2-tier, 3-tier, EJB, OLTP, Batch 등 |
업무프로세스 |
-업무 처리방식의 문제, Load Balancing 정책 등 |
라. 데이터베이스 성능 개선의 주요 항목
주요요소 |
설명 |
사례 |
설계관점 (모델링 관점) |
-데이터 모델링, 인덱스 설계 -데이터파일, 테이블 스페이스 설계 -데이터베이스 용량 산정 |
-반정규화 -분산 파일 배치 |
DBMS 관점 |
-CPU, 메모리 I/O에 대한 관점 |
-Buffer, Cache크기 |
SQL 관점 |
-Join, Indexing, SQL Execution Plan |
-Hash / Join |
H/W관점 |
- CPU, Memory, Network, Disk |
-System Resource개선 |
마. 시스템성능저하요인 및 현상
1) 개선 순서 : 설계 à DBMS à SQL
주요요소 |
설 명 |
사 례 |
설계관점 (모델링관점) |
- 데이터 모델링, 인덱스 설계 - 데이터파일, 테이블 스페이스 설계 - 데이터베이스 용량 산정 |
- 반정규화 - 분산파일 배치 |
DBMS관점 |
- CPU, 메모리 I/O에 관한 관점 |
- Buffer, Cache크기 |
SQL관점 |
- join, Indexing, SQL Execution Plan |
- Hash / Join |
2. 데이터베이스 튜닝의 과정
가. 전체 성능튜닝 진행 프로세스
절 차 |
설 명 |
수 행 |
|
- 시스템의 현 상태와 문제점을 도출하기 위한 과정 - 사용자 인터뷰, 설계검토, 시스템 구성 검토, 자원사용현황 검토, SQL Trace 분석 등 수행 |
- 사용자 인터뷰 - 설계 검토 - 시스템 구성도검토 - 자원사용현황분석 - SQL Trace분석 |
- 진단결과 도출된 문제점에 대한 원인을 분석하고 해결방안 제시. |
- 설계내용 튜닝 - SQL튜닝 - DBMS튜닝 - OS, H/W 튜닝 |
|
- 1차적으로 튜닝방안 적용 후 2차 테스트 분석이나 결과 평가를 통한 최종안 적용 및 개선효과 보고자료 작성 |
- 튜닝 후 자료수집 /분석 - 튜닝결과 평가 - 산출물 작성 |
나. SQL 성능튜닝 진행 프로세스
다. 데이터베이스 튜닝의 3요소
3. 데이터베이스 성능 개선방안
가. 데이터베이스 성능개선(튜닝) 목표
목표 |
내용 |
처리능력 (Throughput) |
-해당작업을 수행하기 위해 소요되는 시간으로 수행되는 작업량을 나눔 -처리능력 = 트랜잭션 수 / 시간 -전체적인 시스템 시각에서 측정되고 평가함 |
처리시간 (Throughput Time) |
-작업이 완료되는데 소요되는 시간 -배치프로그램의 성능목표를 설정함 ※ 배치 작업 시간 단축 ·병행처리(Parallel Processing)를 실시 ·인덱스 스캔보다 FULL 테이블 스캔으로 처리 ·Nest-Loop 조인보다 Hash 조인으로 처리 ·대량 작업을 하기 위한 SORT_AREA, HASH_AREA의 메모리 확보 ·병목을 없애기 위한 작업계획 수립 ·대형 테이블인 경우는 파티션 고려 |
응답시간 (Response Time) |
-사용자가 키를 누른 때부터 시스템이 응답할 때까지의 시간 -일반적으로 OLTP 시스템에서 성능지표가 됨 ※ 응답 시간 향상 ·인덱스를 이용하여 엑세스 경로 단축 ·부분 범위 처리 실시 ·Sort-Merge 조인이나 Hash 조인을 사용하지 않고 Nest-Loop조인 처리 ·불필요한 결과 정렬 작업을 없애거나 인덱스를 이용한 정렬 ·잠김(Locking) 발생 억제. 예를 들어 시퀀스 오븍젝트 이용 ·하드 파싱 억제 |
로드시간 (Load Time) |
-정기적이거나 비정기적으로 발생되는 데이터베이스에 데이터를 로드하는 작업 수행시간 ※ 로드시간 단축 ·로그파일을 생성하지 않고 다이렉트 로드(Direct Load) 사용 ·병렬로드 작업 실시 및 디스크 I/O 경합이 없도록 작업 분산 ·인덱스가 많은 테이블은 인덱스 삭제하고 데이터로드 후 인덱스 재생 ·파티션을 이용하여 작업을 단순화 |
나. HW관점의 성능개선 방안
구분 |
내용 |
CPU 튜닝 |
- Peak Time 시에 60~70%의 사용량 유지 권고 - CPU증설 또는 CPU 과다 점유하는 프로세스를 찾아서 해결 |
메모리 튜닝 |
- DBMS의 사용 메모리의 최적화 필요 - 업무를 가능하게 하기 위한 충분한 메모리 확보 - DBMS 메모리 + Session 메모리 |
I/O 튜닝 |
- I/O를 분산시킬 수 있도록 데이터 파일의 재배치 - Raid를 이용-> I/O가 많이 일어나는 것은 Raid 0/1에 배치 |
네트워크튜닝 |
- Ping, Ftp를 이용하여 응답시간 분석 |
작업수행방식 개선 |
-우선순위, Parallel Processing 구현, Data Sharing 구현 |
다. DBMS관점의 성능개선 방안
구분 |
내용 |
I/O 최소화 |
실제 필요한 Data만을 Read함 (SQL Tuning, Index Tuning, Partitioning 필요) |
Buffer Pool 튜닝 |
추후에 사용한 가능성이 높은 Data는 Buffer Pool에 오래 머물도록 함 |
Commit/Check Point 튜닝 |
Commit/Check Point 주기 조절 |
Thread/Reuse |
동일한 SQL 문의 사용을 통한 Parsing 방지(?) Middleware의 기능과 연동 |
라.DB설계측면의 성능개선 방안
구분 |
내용 |
테이블의 분할/통합 |
- 논리적으로는 통합된 단일 테이블이지만 DBMS가 지원하는 파티션 기능 적용함으로써 액세스 효율화로 DB I/O를 최적화 할 수 있음 - DBMS에 따라 파티션 기능이 제약되는 경우에는 테이블의 수평분할 고려 - 액세스 패턴에 따라 단일 테이블을 1:1로 수직분할 고려할 수 있음 |
식별자 지정 |
- 본질 식별자와 인조 식별자의 선택에 따라 정보의 상속과 단절에 영향을 줄 수 있음 |
효율적인 인덱스 전략 |
- 최소한의 인덱스로 최대의 효과를 얻을 수 있는 최적의 인덱스 구조 수립 - 인덱스 분포도 15% 이하 유용 유도(15% 이상이면 Full Scan 유리) |
적절한 데이터 타입 선택 |
- 조인 시 연결되는 컬럼의 데이터 타입이 다른 경우 내부적인 변형에 의해 인덱스가 있음에도 불구하고 활용을 못해 조인 순서나 조인 방식을 달리 선택하는 비효율 발생 할 수 있음 |
마. SQL을 통한 성능개선 방안
구 분 |
내 용 |
예 시 |
옵티마이저에 대한 이해 |
옵티마이저의 목표 : SQL로 요구된 결과를 최소의 비용으로 처리할 수 있는 처리경로를 결정 - 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; |
4. 데이터베이스 성능 저하 예방방안
가. 상시 모니터링 및 관리
- 데이터베이스에 치명적인 문제가 발생 하기 전에 주기적으로 모니터링 되고 관리 되어야 함.
- 향후 튜닝에 필요한 정상적인 상황일 때의 데이터베이스 로그 정보 보관 필요
나. 개발자에 대한 데이터베이스 원리 교육
- 개발자에 대해서 SQL 튜닝 기법 및 SQL 수행 원리 교육 필요
- 프로그램 알고리즘 교육 필요, Source Review 제도 도입
다. 지속적인 성능검사를 통한 튜닝 실시
- 환경변화와 데이터베이스의 변화성을 감안하여 정기/비정기적으로 지속적으로 수행하여야 함
라. 튜닝 결과에 대한 Historical Information 구축
- 튜닝 수행 시 발생된 문제, 문제분석 결과, 해결 내용에 대한 지식 공유와 관리를 통한 정보공유.