트랜잭션 로그 성능을 향상시키는 8가지 방법

트랜잭션 로그 성능을 향상시키는 8가지 단계

http://sqlskills.com/blogs/kimberly/post/8-steps-to-better-Transaction-Log-throughput.aspx

1. 트랜잭션 로그를 물리적인 전용 디스크에 별도로 구성한다. 대용량 OLTP 시스템에서 트랜잭션 로그를 별도로 분리하면 다른 파일과 

물리적 디스크 자원을 사용하기 위해 경합하지 않아도 도니다. 만일 이미 데이터베이스가 존재한다면 sp_detach_db 구문을 사용해서

트랜잭션 로그 파일을 분리하고 sp_attach_db 구문을 사용해서 다시 연결한다.

2. 트랜잭션 로그가 위치하고 있는 디스크를 조각모음한다. 이 작업은 트랜잭션 로그의 내부 조각과 외부 조각 문제를 해결해 줄 것이다. 이러한 조각 모음 작업은 데이터베이스 서버를 오프라인 상태로 만들어야 하지만 파일이 여러 번 자동증가 되었거나 로그가 저장된 디스크에 수정된 파일이 많이 있다면 이 파일들은 많이 조각나 있을 것이다. 이러한 조각모음을 위해서는 SQL Server를 셧다운해야 하며 microsoft나 서드파티의 조각모음을 위한 시스템 도구를 사용해야 한다.

3. 하나의 트랜잭션 로그 파일을 생성한다. 만일 여러 트랜잭션 로그 파일을 생성한다 하더라도 SQL Server는 실제로 여러 트랜잭션 로그 파일들을 병렬로 사용하지는 않는다. 대신, SQL Server는 순차적으로 트랜잭션 로그 파일을 사용한다. 트랜잭션 로그 작업 성능을 향상시키기 원한다면 보다 빠른 디스크와 좋은 성능의 RAID 구성의 디스크에 로그 파일을 구성한다. 트랜잭션 로그는 RAID 0 어레이에 구성할 수 있지만 성능과 fault tolerance를 위한다면 RAID 1 이상을 고려한다.

4. 트랜잭션 로그는 별도의 물리적 디스크에 구성할 뿐 아니라 가능하면 특별한 논리적/물리적 디스크 구성을 갖도록 한다. 특별한 용량 문제가 없다면 별도의 RAID 1 미러링 셋을 사용한다. 최적의 성능과 용량을 필요로 한다면 RAID 0과 RAID 1의 혼합 구성을 고려한다. RAID 0+1이 최적의 성능을 제공하는 반면, RAID 1+0은 최적의 신뢰성을 제공한다.

5. 트랜잭션 로그의 속도에만 집작하지 말고 트랜잭션 로그의 유효성도 고려해야 장애 발생시 도움이 될 수 있다. 데이터가 손상되더라도 트랜잭션 로그를 사용할 수 있고 연속적인 로그 백업을 가지고 있으며 이 백업이 마지막 트랜잭션 로그를 포함한다면 마지막 트랜잭션 로그 백업 이후의 모든 변경 내용을 나타낼 로그를 백업할 수 있다. 이 백업을 성공적으로 수행할 수 있다면 (이를 비상 로그 백업이라고 함) 장애 시점까지 데이터베이스를 복구할 수 있다. 이는 로그가 유용할 경우에만 가능하다. 로그를 미러 디스크에 배치하면 데이터 복구 가능성을 높일 수 있으며 데이터 손실을 최소로 할 수 있다.

6. 트랜잭션 로그를 적절한 초기 크기로 생성한다. 데이터베이스를 생성할 때 데이터 파일과 트랜잭션 로그 파일의 크기를 예상되는 최종 크기로 생성한다. 만일 필요한 트랜잭션 로그 파일의 크기를 예측하기 힘들다면 아래에 트랜잭션 크기에 영향을 줄 수 있는 사항이 있다.

 

 * 작업 유형 - 트랜잭션 처리 또는 의사 결정 지원

 * 작업 주기 - 자주 변경할수록 트랜잭션 로그는 빨리 증가함

 * 복구 모델 - 데이터베이스의 복구 모델

 * 트랜잭션 로그 백업 주기

 * 복제 사용 여부 (로그 판독기는 트랜잭션 로그를 기반으로 함)

만일 데이터베이스 크기만을 예상할 수 있고 로그 크기를 예상하기 힘들다면 데이터베이스의 크기의 10~25%를 로그 크기로 권장한다. 매우 자주 트랜잭션 로그를 백업하는 큰 데이터베이스는 데이터 크기의 1% 만을 가질 수도 있다. 로그 크기를 예측하는 가장 좋은 방법은 프로덕션 환경(백업 작업을 포함)과 비슷한 개발 환경을 구성하고 트랜잭션 로그가 얼마나 증가하는지 살펴보는 것이다.

7. 자동증가를 맹신하면 안된다. 물론 자동 증가를 끄는 것을 권장하는 것은 아니지만 SQL Server의 기본 자동 증가 속성은 권장하지 않는다. 증가율은 일반적으로 1GB 정도를 권장한다. 또한 퍼센트는 권장하지 않는데 이는 계산하기 힘들며 파일이 커질수록 자동 증가율도 커지고 아울러 파일이 증가하는데 필요한 시간도 늘어나기 때문이다.

8 내부 조각 모음을 확인하고 수정한다. 트랜잭션 로그 크기를 미리 최종 크기로 할당하지 않고 잦은 자동 증가가 발생했다면 트랜잭션 로그는 내부적으로 조각날 것이다. 내부적으로 트랜잭션 로그는 여러 작은 조각인 VLF로 조각난다. VLF의 크기는 트랜잭션 로그가 증가할 때 주로 발생한다. 만일 트랜잭션 로그에 자동 증가나 수동 증가를 통해 20MB 크기를 추가했다면 추가된 VLF의 수는 4이다. 만일 64MB보다 크고 1GB보다 작은 크기를 추가하면 8개의 VLF가 추가된다. 1GB보다 큰 크기를 추가면 16VLF가 추가될 것이다. 일반적으로 대부분의 트랜잭션 로그는 20~30VLF를 가지며 전체 트랜잭션 로그 크기에 따라 50개가 적절할 수 있다. 그러나 대부분의 경우 과도한 자동증가는 매우 많은 VLF를 추가하여 수 백개의 VLF를 생성할 것이다. VLF의 수를 확인하려면 DBCC LOGINFO 명령이 리턴하는 행 수를 확인하면 된다. 이 행 수는 트랜잭션 로그 파일이 갖는 VLF의 수와 동일하다.

만일 50개 이상의 VLF를 갖는다면 이를 수정하고 자동 증가를 조정하여 자동증가가 자주 발생하지 않도록 해야 한다. 과도한 VLF를 제거하려면 다음 단계를 수행하여 조각 수를 줄이고 새로운 트랜잭션 로그를 생성한다.

 1. 작업이 별로 없는 시간에 (먼저 데이터베이스를 single user 모드로 구성) 트랜잭션 로그 백업을 통해 모든 트랜잭션 로그 작업을 제거한다. 만일 단순 복구 모델을 사용한다면 로그 백업은 필요 없다. 대신 checkpoint를 수행하여 로그를 제거한다.

     BACKUP Log 데이터베이스이름 To 디바이스이름

2. 로그를 가능한 작은 크기로 줄인다.

     DBCC SHRINKFILE (트랜잭션_로그파일_이름, TRUNCATEONLY)

3. 데이터베이스를 수정하여 트랜잭션 로그를 적절한 크기로 설정한다.

     ALTER DATABASE 데이터베이스이름

     MODIFY FILE

     ( NAME = 트랜잭션로그_파일이름,

       SIZE = 새로운_크기

     )