REDO 로그·파일의 설정으로 갱신 처리를 튜닝

출처 : KACHISORI

 

전회 「갱신/삽입/삭제의 SQL를 고속화하는 3개의 기술이란?」에서는 갱신 처리의 SQL 튜닝·테크닉에 대해 설명했습니다. 특히 갱신 처리는 데이타베이스측의 설정에도 주의해야 할 점이 있습니다.최종회인 이번은 SQL은 아니지만 갱신 처리의 튜닝을 실시할 때에 필요한 SQL 이외의 부분에서의 튜닝·포인트에 대해 설명합니다.

 

■ 데이타베이스·파일의 I/O분산

 

갱신 처리에서는 실제로 갱신하는 데이터나 색인이 격납되고 있는 테이블 영역 이외에 온라인 REDO 로그·파일, UNDO 테이블 영역에도 많은 I/O가 발생합니다. 디스크 I/O가 넥이 되어 있는 경우, I/O가 집중하고 있는 파일을 조사해 가능한 한 다른 디스크에 분산하도록 해야 합니다.

 

성능의 관점은 물론 내장해성의 관점으로부터도 온라인 REDO 로그·파일은 가능한 한 점유 할 수 있는 디스크를 할당하는 것을 추천합니다. 디스크 점유가 어려운 경우, 가능한 한, I/O발생 빈도가 낮고, Oracle의 데이터·파일이 배치되지 않은 디스크에 배치하도록 조치해야 합니다.

 

I/O의 발생 상황은 OS커멘드 및 Oracle의 데이터 딕셔내리로 확인할 수 있습니다. Windows에서는 관리툴에 있는 「퍼포먼스」, Linux나 UNIX에서는 sar, iostat 커멘드등을 이용해 I/O가 특정의 디스크에 집중하고 있지 않는가를 확인합니다.

 

특정의 디스크에 I/O가 집중하고 있는 경우 동적 퍼포먼스뷰 「V$FILESTAT」나 「V$SYSSTAT」의 「redo size」열을 확인해 어느 파일에 많은 I/O가 발생하고 있는지를 조사합니다.이러한 정보를 기본으로 집중되는 파일을 찾아내, 분산 배치를 검토해야 합니다.

 

■ 온라인 REDO 로그·파일의 사이즈와 수의 확인

 

대량 갱신 처리의 퍼포먼스·튜닝을 실시하는 경우에는 온라인 REDO 로그·파일의 사이즈와 개수에 주의가 필요합니다 .데이타베이스에 대한 변경 정보는 공유 메모리상의 로그·버퍼에 써진 후, 로그·버퍼로부터 온라인 REDO 로그·파일에 기입을 합니다. 쓰고 있던 경향의 온라인 REDO 로그·파일이 가득차면 로그·스윗치가 발생합니다

그림 1　온라인 REDO 로그·파일의 로그·스윗치

 

로그·스윗치에서는 체크 포인트의 발생이나 온라인 REDO 로그·파일의 변환등의 처리를 합니다.로그·스윗치가 발생했을 때 재이용하려고 한 REDO 로그에 포함되는 체크 포인트 처리가 완료하고 있지 않으면 로그·스윗치는 대기 상태가 되어, Alert  로그·파일에 이하의 메세지가 출력됩니다.

 

Thread 1 cannot allocate new log, sequence 8829 Checkpoint not complete

 

Alert 로그·파일에 상기의 메세지가 빈발하고 있는 경우, 트랜잭션(transaction)량에 대해서 온라인 REDO 로그·파일의 사이즈, 또는 개수가 부족함을 나타냅니다.

 

체크 포인트는 메모리상의 갱신이 끝난 데이터(더티 블록)를 디스크상의 데이터·파일에 반영하는 처리입니다. 반영 처리 완료 후에는 제어 파일과 각 데이터·파일·헤더의 갱신 처리도 실시합니다. 체크 포인트의 발생 빈도는 초기화 파라미터의 설정등에 의해도 바뀝니다

 

■ 온라인 REDO 로그·파일의 최적치를 구하기

 

그렇다면 온라인 REDO 로그·파일의 설정이 적절하지 않은 경우, 갱신 처리의 퍼포먼스에 어떠한 영향을 주는지 실제로 예를 들어 봅시다. 이하의 예는 온라인 REDO 로그·파일의 사이즈가 100 Mbytes의 환경과 1 Mbytes의 환경에서 동일한 갱신 처리를 실행해 처리 시간과 실행 통계를 취득한 결과입니다.

그림 2　온라인 REDO 로그·파일 사이즈가 100 Mbytes로 4 그룹의 경우

図그림 3　온라인 REDO 로그·파일 사이즈가 1 Mbytes로 4 그룹의 경우

 

읽은 블록수 발생한 REDO SIZE는 대충 같음에도 불구하고, 온라인 REDO 로그·파일 사이즈가 1 Mbytes의 경우는, 배 가까이의 시간을 필요로 하고 있습니다. 이 차이는 온라인 REDO 로그·파일 사이즈가 너무 작아서 일어나고 있습니다. 로그·스윗치시의 체크 포인트 대기 상황은 동적 성능뷰 「V$SESSION_EVENT」에 의해 확인할 수 있습니다. 온라인 REDO 로그·파일 사이즈가 1 Mbytes의 경우의 확인 결과가 그림 4가 됩니다.

 

4　체크 포인트 완료까지의 대기 시간의 확인 방법

 

그림 4의 「TIME_WAITED」(단위1/100초)을 확인하면 체크 포인트의 완료까지 271.46초의 대기가 발생하고 있던 것을 알수있습니다.

 

상기의 결과로부터도 알 수 있듯이 대량 갱신 처리의 퍼포먼스에는 온라인 REDO 로그·파일의 사이즈가 매우 중요합니다. 온라인 REDO 로그·파일의 적절한 사이즈는 시스템마다 다르기 때문에 로그·스윗치의 회수가 1시간에 23회 이하인 것을 기준으로 사이즈를 조정해야 합니다. 일반적인 시스템에서는 온라인 REDO 로그·파일의 사이즈는, 100 Mbytes 수Gbytes 정도가 됩니다.

 

덧붙여 온라인 REDO 로그·파일의 사이즈를 크게 했을 경우 이하와 같은 영향이 발생하므로 퍼포먼스와 그 다른 영향의 밸런스를 고려하여 사이즈를 결정해야 합니다.

 

• 극단적으로 큰 온라인 REDO 로그·파일을 작성해 초기화 파라미터의 설정 ( 「FAST_START_MTTR_TARGET」에 큰 값을 설정하거나, 「LOG_CHECKPOINT_INTERVAL」를 「0」으로 설정하는 등)에 의해 체크 포인트의 발생 빈도를 낮추면 장해 발생시의 크래쉬·리커버리에 의해 많은 시간을 필요로 해 버린다.
  　

• 만일 현재의 온라인 REDO 로그·파일이 장해를 받았을 경우에 소실하는 트랜잭션(transaction)량이 많아짐 (어카이브(archive)·로그 모드로 운용시만. 어카이브(archive)·로그 모드로 데이타베이스를 운용하고 있는 경우, 어카이브(archive) 처리는 REDO 로그·파일이 만땅이 되지 않으면 발생하지 않기 때문에).
  

■ 불필요한 인덱스의 삭제

 

색인이 작성되어 있는 열을 갱신하면 테이블의 데이터는 물론, 색인 자체도 갱신됩니다. 단순하게 생각해도 색인이 없는 경우에 비해 기입량이 많아지는 만큼 갱신 처리는 늦어집니다.

 

이 때문에 갱신 처리의 퍼포먼스에 문제가 있는 경우 대상테이블에 작성되어 색인을 조사해 삭제할 수 있는 색인이 없는가 검토합니다. 검색의 퍼포먼스등을 고려해 작성된 색인이 실제로는 거의 사용되어 있지 않은 것도 적지는 않습니다. Oracle에서는 이하의 순서에 의해, 색인이 사용되고 있는지 어떤지를 체크하는 것이 가능합니다.

 

그림 5　불요 색인의 확인 방법

모니터링 결과 이용되지 않는 색인을 발견한 경우는 갱신 처리 퍼포먼스의 향상 및 여분의 영역을 개방하기 위해서도 색인의 삭제를 검토해야 합니다. 또 불필요한 색인이 없는 경우에서도 갱신 처리전에 일시적으로 색인을 삭제해 갱신 처리 후에 재작성하는 것이 전체의 처리 시간이 짧아지는 경우도 있기 때문에 필요에 따라서 이러한 방법도 검토해야 합니다.

 

■ 정리

 

지금까지 SQL 튜닝의 기본이 되는 지식과 테크닉을 구체적인 예와 함께 소개했습니다.데이타베이스의 튜닝은 인스턴스(초기화 파라미터)의 튜닝이나, 파일 I/O의 분산등도 있습니다만, 가장 중요하고 효과를 발휘하는 것은 SQL 튜닝입니다. 지금까지 설명해 온 것 같은 기본 테크닉을 마스터해 조합하여 퍼포먼스의 문제를 해소 해야 합니다.