IBAnalyst: 데이터베이스 이해하기

Dmitri Kuzmenko, [email protected], 마지막 업데이트 2014년 3월 31일

저는 1994년부터 InterBase를 사용해 왔습니다. 그 당시 대부분의 데이터베이스는 규모가 작았고 튜닝이 필요하지 않았습니다. 물론 서버에서 ibconfig를 변경하거나 하드웨어 및 OS를 재구성해야 하는 경우도 있었지만, 성능을 튜닝하기 위해 제가 할 수 있는 일은 거의 그것이 전부였습니다.

4년 전, 우리 회사는 InterBase 사용자에게 기술 지원과 교육을 제공하기 시작했습니다. 많은 운영 데이터베이스를 다루면서 다양한 것을 배울 수 있었습니다. 하지만 제가 배운 대부분은 애플리케이션, 즉 트랜잭션 매개변수 사용, 쿼리 및 결과 집합 최적화에 관한 것이었습니다.

물론 저는 데이터베이스 통계 정보를 제공하는 도구인 gstat에 대해 오래전부터 알고 있었습니다. gstat 출력을 살펴보거나 opguide.pdf에서 이에 대해 읽어본 적이 있다면, 통계 출력이 단지 숫자들의 모음에 불과하다는 것을 알 수 있을 것입니다. 특정 테이블이나 인덱스의 단편화 정보를 확인할 수는 있지만, 그 외에 어떤 유용한 정보를 얻을 수 있을까요?

다행히도 InterBase를 사용하기 전에 저는 다양한 데이터 구조, 그것들이 저장되는 방식, 그리고 사용하는 알고리즘에 관심이 있었습니다. 이는 gstat 출력을 해석하는 데 도움이 되었습니다. 그때 저는 gstat 출력을 분석하여 데이터베이스 튜닝을 돕거나 최소한 성능 문제의 원인을 식별할 수 있는 도구를 작성하기로 결정했습니다.

긴 이야기지만, 그 결과 IBAnalyst가 탄생했습니다. 제 경험에도 불구하고, 이 도구는 여전히 다양한 데이터베이스에서 매우 흥미로운 사실이나 성능 문제를 발견하게 해줍니다.

실제 시스템의 런타임 성능은 파도처럼 출렁입니다. 이러한 ‘파도’의 진폭은 낮거나 높을 수 있어서 성능이 하루(또는 시간 단위)마다 어떻게 달라지는지 확인할 수 있습니다. 실제 성능은 애플리케이션 설계, 서버 구성, 트랜잭션 동시성, 데이터베이스의 버전 가비지 등 많은 요소에 따라 달라집니다. 데이터베이스에서 무슨 일이 일어나고 있는지(성능의 긍정적 및 부정적 측면 모두) 파악하려면 최소한 수시로 데이터베이스 통계를 살펴봐야 합니다.

IBAnalyst의 기능을 살펴보겠습니다. IBAnalyst는 gstat 또는 Services API에서 통계를 가져와 데이터베이스, 테이블 및 인덱스에 대한 완전한 정보를 제공하는 보고서로 컴파일할 수 있습니다. 통계를 탐색하는 동안 사용할 수 있는 즉각적인 경고가 있으며, 힌트 주석과 권장 사항 보고서도 포함되어 있습니다.

데이터베이스 정보

그림 1 데이터베이스 통계 요약

그림 1에 표시된 요약은 데이터베이스에 대한 일반 정보를 제공합니다. 표시된 경고나 주석은 대규모 실제 운영 데이터베이스에서 얻은 신중하게 수집된 지식을 기반으로 합니다.

참고: 이 기사의 모든 그림에는 실제 운영 데이터베이스(소유자의 허락을 받아)에서 가져온 gstat 통계가 포함되어 있습니다.

앞서 말했듯이, 원시 데이터베이스 통계는 난해해 보이고 해석하기 어렵습니다. IBAnalyst는 잠재적인 문제를 노란색 또는 빨간색으로 명확하게 강조하며, 문제의 세부 사항은 해당 항목 위에 커서를 올리고 표시되는 힌트를 읽기만 하면 됩니다.

위 그림에서 무엇을 발견할 수 있을까요? 이것은 페이지 크기가 4096바이트인 dialect 3 데이터베이스입니다. 6~8년 전에는 개발자들이 기본 페이지 크기로 1024바이트를 사용했지만, 최근에는 그러한 작은 페이지 크기가 많은 성능 문제를 일으킬 수 있습니다. 이 데이터베이스는 페이지 크기가 4k이므로 이 페이지 크기가 괜찮기 때문에 경고가 표시되지 않습니다.

다음으로, Forced Write 매개변수가 OFF로 설정되어 빨간색으로 표시된 것을 볼 수 있습니다. InterBase 4.x 및 5.x는 기본적으로 이 매개변수가 ON이었습니다. Forced Writes 자체는 쓰기 캐시 방법입니다. ON이면 변경된 데이터를 즉시 디스크에 쓰지만, OFF이면 운영 체제의 파일 캐시에 쓰기가 알 수 없는 시간 동안 저장됩니다. InterBase 6은 Forced Writes OFF로 데이터베이스를 생성합니다.

이것이 IBAnalyst 보고서에서 왜 빨간색으로 표시될까요? 답은 간단합니다. 비동기 쓰기를 사용하면 정전, OS 또는 서버 장애 시 데이터베이스 손상이 발생할 수 있기 때문입니다.

팁: 흥미롭게도 최신 HDD 인터페이스(ATA, SATA, SCSI)는 Forced Write를 ON 또는 OFF로 설정해도 성능에 큰 차이를 보이지 않습니다(1).

보고서의 다음 항목은 수수께끼 같은 “sweep interval"입니다. 양수이면 엔진이 자동 가비지 수집을 시작해야 한다는 경고를 받는 가장 오래된(2) 트랜잭션과 가장 오래된 스냅샷 트랜잭션 사이의 간격 크기를 설정합니다. 일부 시스템에서는 이 임계값에 도달하면 “갑작스러운 성능 저하” 효과가 발생할 수 있으며, 그 결과 sweep interval을 0으로 설정(자동 스위핑 완전 비활성화)하는 것이 때때로 권장됩니다. 여기서 sweep interval은 노란색으로 표시되는데, 이는 sweep 간격 값이 음수이기 때문입니다. 이는 InterBase 6.0, Firebird 및 Yaffil 통계에서는 발생할 수 있지만 InterBase 7.x에서는 발생하지 않습니다. sweep 간격 값이 sweep interval보다 큰 경우(sweep interval이 0이 아닌 경우) sweep interval 항목은 적절한 힌트와 함께 빨간색으로 표시됩니다.

다음 8개 행은 모두 데이터베이스의 트랜잭션 상태 측면을 표시하므로 하나의 그룹으로 살펴보겠습니다:

가장 오래된 트랜잭션(Oldest transaction)은 커밋되지 않은 가장 오래된 트랜잭션입니다. 이보다 낮은 트랜잭션 번호는 커밋된 트랜잭션에 대한 것이며, 해당 트랜잭션에 대한 레코드 버전은 없습니다. 가장 오래된 트랜잭션보다 높은 트랜잭션 번호는 어떤 상태에든 있을 수 있는 트랜잭션에 대한 것입니다. 이는 “가장 오래된 관심 트랜잭션"이라고도 합니다. 트랜잭션이 롤백으로 종료되면 고정되고 서버가 그 순간 변경 사항을 취소할 수 없기 때문입니다.
가장 오래된 스냅샷(Oldest snapshot) - 현재 가장 오래된 “관심” 트랜잭션인 트랜잭션 시작 시 존재했던 가장 오래된 활성(즉, 아직 커밋되지 않은) 트랜잭션입니다. 레코드 버전에 관심이 있는 가장 낮은 스냅샷 트랜잭션 번호를 나타냅니다.
가장 오래된 활성 트랜잭션(Oldest active) - 현재 활성 상태인 가장 오래된 트랜잭션(3)입니다.
다음 트랜잭션(Next transaction) - 새 트랜잭션에 할당될 트랜잭션 번호입니다.
활성 트랜잭션(Active transactions) - 가장 오래된 활성 트랜잭션 번호가 일일 트랜잭션 수보다 30% 낮으면 IBAnalyst가 경고를 표시합니다. 통계는 가장 오래된 활성 트랜잭션과 다음 트랜잭션 사이에 다른 활성 트랜잭션이 있는지 알려주지 않지만, 그러한 트랜잭션이 존재할 수 있습니다. 일반적으로 가장 오래된 활성 트랜잭션이 멈추면 두 가지 가능한 원인이 있습니다: a) 일부 트랜잭션이 오랫동안 활성 상태이거나 b) 애플리케이션 설계상 트랜잭션이 오랫동안 실행되도록 허용하는 경우입니다. 두 원인 모두 가비지 수집을 방해하고 서버 리소스를 소비합니다.
일일 트랜잭션 수(Transactions per day) - 다음 트랜잭션을 데이터베이스 생성 시점부터 통계를 가져온 시점까지 경과된 일수로 나누어 계산됩니다. 이는 운영 데이터베이스 또는 백업에서 주기적으로 복원되어 트랜잭션 번호가 재설정되는 데이터베이스에 대해서만 정확할 수 있습니다.

이미 배웠듯이, 경고가 있으면 색상이 있는 줄로 표시되며 문제를 해결하거나 예방하는 방법에 대한 명확하고 설명적인 힌트가 제공됩니다.

데이터베이스 통계가 항상 유용한 것은 아니라는 점에 유의해야 합니다. 작업 및 유지 관리 작업 중에 수집된 통계는 의미가 없을 수 있습니다.

다음과 같은 경우에는 통계를 수집하지 마십시오:

데이터베이스를 방금 복원한 경우
-g 스위치 없이 백업(gbak -b db.gdb) 수행
최근 수동 스윕(gfix -sweep) 수행

이런 경우에 얻는 통계는 실질적으로 쓸모가 없을 것입니다. 또한 정상적인 작업 중에도 데이터베이스가 완벽한 상태일 수 있는 때가 있다는 것도 맞습니다. 예를 들어, 애플리케이션이 평소보다 데이터베이스 부하를 덜 발생시킬 때(사용자들이 점심을 먹거나 업무 시간 중 한가한 시간)입니다.

데이터베이스에 문제가 있을 때 어떻게 알 수 있나요?

애플리케이션이 트랜잭션과 데이터를 항상 올바르게 처리하도록 잘 설계되어 스윕 갭을 만들지 않고, 활성 트랜잭션을 많이 축적하지 않으며, 오래 실행되는 스냅샷을 유지하지 않을 수도 있습니다. 하지만 보통은 그렇지 않습니다(죄송합니다, 동료 여러분).

가장 흔한 이유는 개발자들이 두세 명의 동시 사용자만으로 애플리케이션을 테스트하기 때문입니다. 이후 애플리케이션이 15명 이상의 동시 사용자가 있는 프로덕션 환경에서 사용되면 데이터베이스가 예측할 수 없게 동작할 수 있습니다. 물론, 대부분의 다중 사용자 충돌은 두세 개의 동시 실행 애플리케이션으로 테스트할 수 있기 때문에 다중 사용자 모드가 정상적으로 작동할 수 있습니다. 그러나 사용자 수가 많아지면 가비지 컬렉션 문제가 발생할 수 있습니다. 이러한 잠재적 문제는 올바른 시점에 데이터베이스 통계를 수집하면 발견할 수 있습니다.

테이블 정보

IBAnalyst의 또 다른 샘플 출력을 살펴보겠습니다.

![](/images/article_IBAnalyst (1).jpg)

그림 2 테이블 통계

IBAnalyst 테이블 통계 뷰도 매우 유용합니다. 어떤 테이블에 레코드 버전이 많은지, 대량의 업데이트/삭제가 발생한 위치, 업데이트/삭제 또는 블롭으로 인한 단편화가 있는 테이블 등을 보여줄 수 있습니다. 어떤 테이블이 자주 업데이트되는지, 테이블 크기가 메가바이트 단위로 얼마인지도 확인할 수 있습니다. 이러한 경고의 대부분은 사용자 정의가 가능합니다.

이 데이터베이스 예제에는 몇 가지 문제가 있습니다. 먼저, VerLen 열의 노란색은 레코드 버전이 차지하는 공간이 레코드 자체가 차지하는 공간보다 크다는 경고입니다. 이는 레코드의 많은 필드를 업데이트하거나 대량 삭제로 인해 발생할 수 있습니다. MaxVers 열이 파란색으로 표시된 행을 참조하세요. 이는 레코드당 하나의 버전만 저장되므로 문제가 대량 삭제 때문임을 보여줍니다. Versions 열의 값은 삭제된 레코드 수를 나타냅니다.

가비지 컬렉션을 방해하는 오래 지속되는 활성 트랜잭션이 성능 저하의 주요 원인입니다. 일부 테이블의 경우 여전히 “사용 중"인 버전이 많이 있을 수 있습니다. 활성 트랜잭션이 이러한 버전 중 하나 또는 전부를 잠재적으로 필요로 할 수 있기 때문에 서버는 실제로 사용 중인지 여부를 결정할 수 없습니다. 따라서 서버는 이러한 버전을 가비지로 간주하지 않으며, 트랜잭션이 레코드를 읽을 때마다 많은 버전에서 올바른 레코드를 구성하는 데 점점 더 오랜 시간이 걸립니다. 그림 2에서 레코드 수보다 버전 수가 3배 더 높은 두 테이블을 볼 수 있습니다. 이 정보를 사용하여 애플리케이션이 이러한 테이블을 그렇게 자주 업데이트하는 것이 설계상 의도된 것인지 아니면 오류 때문인지도 확인할 수 있습니다.

인덱스 뷰

인덱스는 데이터베이스 엔진이 기본 키, 외래 키 및 고유 제약 조건을 적용하는 데 사용됩니다. 또한 데이터 검색 속도를 높입니다. 고유 인덱스는 데이터 검색에 가장 좋지만, 비고유 인덱스의 이점 수준은 인덱스된 데이터의 다양성에 따라 달라집니다.

예를 들어, ADDR_ADDRESS_IDX6을 살펴보겠습니다. 먼저, 인덱스 이름 자체가 수동으로 생성되었음을 암시합니다. 메타데이터 정보가 포함된 Services API로 통계를 수집한 경우 인덱스된 열을 확인할 수 있습니다(IBAnalyst 1.83 이상). 검토 중인 인덱스의 경우 34999개의 키, TotalDup은 34995, MaxDup은 25056임을 확인할 수 있습니다. 두 중복 열 모두 빨간색으로 표시됩니다. 이는 Uniques 열에서 볼 수 있듯이 이 인덱스의 모든 키 중 고유 키 값이 4개뿐이기 때문입니다. 또한 가장 큰 중복 체인(동일한 열 값을 가진 레코드를 가리키는 키)은 25056입니다. 즉, 거의 모든 키가 4개의 고유 값 중 하나를 저장합니다. 결과적으로 이 인덱스는 다음과 같은 영향을 미칠 수 있습니다:

복원 프로세스 속도를 저하시킵니다. 물론 35,000개의 키는 현대 데이터베이스와 하드웨어에 큰 문제는 아니지만, 그 영향은 주목해야 합니다.
가비지 컬렉션을 느리게 합니다. 고유 값 수가 적은 인덱스는 완전히 고유한 인덱스에 비해 가비지 컬렉션을 최대 10배까지 지연시킬 수 있습니다. 이 문제는 InterBase 7.1/7.5 및 Firebird 2.0에서 해결되었습니다.
옵티마이저가 인덱스를 읽을 때 불필요한 페이지 읽기를 생성합니다. 특정 쿼리에서 검색되는 값에 따라 달라집니다. MaxDup 값이 큰 인덱스로 검색하면 더 느려집니다. 중복 값이 적은 열의 값으로 검색하면 더 빨라지지만, 해당 열이 인덱스되어 있다는 것은 사용자만 알고 있습니다.

이것이 IBAnalyst가 이러한 인덱스를 빨간색과 노란색으로 표시하고 권장 사항 보고서에 포함시켜 주의를 끄는 이유입니다. 불행히도 대부분의 “나쁜” 인덱스는 외래 키 제약 조건을 적용하기 위해 자동으로 생성됩니다. 어떤 경우에는 트리거를 사용하여 조회 테이블의 기본 키 삭제나 업데이트를 방지함으로써 이 문제를 해결할 수 있습니다. 그러나 이러한 변경을 구현할 수 없다면, IBAnalyst는 통계를 볼 때마다 외래 키의 “나쁜” 인덱스를 표시할 것입니다.

보고서

매번 전체 보고서를 살펴보면서 셀 색상을 확인하고 새 경고에 대한 힌트를 읽을 필요는 없습니다. IBAnalyst의 권장 사항 기능을 사용하면 더 직접적이고 상세한 정보를 얻을 수 있습니다. 통계를 로드하고 Reports/View Recommendations 메뉴로 이동하기만 하면 됩니다. 이 보고서는 강제 쓰기, 스윕 간격, 데이터베이스 활동, 트랜잭션 상태, 데이터베이스 페이지 크기, 스윕, 트랜잭션 인벤토리 페이지, 단편화된 테이블, 레코드 버전이 많은 테이블, 대량 삭제/업데이트, 깊은 인덱스, 옵티마이저에 비우호적인 인덱스, 쓸모없는 인덱스, 심지어 빈 테이블에 대한 더 자세한 설명 경고를 포함한 단계별 분석을 제공합니다. 이 모든 정보와 함께 제공되는 제안은 로드된 통계를 기반으로 동적으로 생성됩니다.

보고서 출력의 예로, 이 문서의 앞부분에서 본 데이터베이스 통계에 대해 생성된 보고서를 살펴보겠습니다:

“트랜잭션 인벤토리 페이지(TIP)의 전체 크기가 큽니다 - 94킬로바이트 또는 23페이지입니다. Read_committed 트랜잭션은 전역 TIP를 사용하지만, 스냅샷 트랜잭션은 메모리에 자체 TIP 복사본을 만듭니다. 큰 TIP 크기는 성능을 저하시킬 수 있습니다. TIP 크기를 줄이려면 수동으로 스윕(gfix -sweep)을 실행해 보세요.”

다음은 보고서의 테이블/인덱스 부분에서 인용한 또 다른 내용입니다:

“버전이 있는 테이블 수: 8. 많은 양의 레코드 버전은 일반적으로 성능을 저하시킵니다. 테이블에 레코드 버전이 많으면 가비지 컬렉션이 작동하지 않거나 레코드가 어떤 select 문에서도 읽히지 않는 것입니다. 해당 테이블에서 select count(*)를 실행하여 가비지 컬렉션을 강제할 수 있지만, 이는 오랜 시간이 걸릴 수 있으며(버전이 많고 비고유 인덱스가 있는 경우) 이러한 버전에 관심이 있는 트랜잭션이 하나라도 있으면 실패할 수 있습니다.

버전/레코드 비율이 3보다 큰 테이블 목록은 다음과 같습니다:


테이블	레코드	버전	레코드/버전 크기
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

요약

IBAnalyst는 사용자가 Firebird 또는 InterBase 데이터베이스 통계를 상세히 분석하고 성능, 유지 관리 및 애플리케이션이 데이터베이스와 상호 작용하는 방식 측면에서 데이터베이스의 잠재적 문제를 식별하는 데 도움을 주는 귀중한 도구입니다. 난해한 데이터베이스 통계를 가져와 이해하기 쉬운 그래픽 방식으로 표시하며, 데이터베이스 성능 개선 및 유지 관리 용이성에 대한 합리적인 제안을 자동으로 제공합니다.

1 InterBase 7.5 및 Firebird 1.5에는 Forced Writes가 Off인 경우 저장되지 않은 페이지를 주기적으로 플러시할 수 있는 특수 기능이 있습니다.

2 가장 오래된 트랜잭션은 어디서나 언급되는 가장 오래된 관심 트랜잭션(Oldest interesting transaction)과 동일합니다. Gstat 출력에는 이 트랜잭션이 “interesting"으로 표시되지 않습니다.

3 Ann Harrison은 가장 오래된 활성 트랜잭션(Oldest active)을 현재 가장 오래된 활성 트랜잭션이 시작되었을 때 활성 상태였던 가장 오래된 트랜잭션으로 정의합니다. 애플리케이션의 경우 여기서 큰 차이는 없습니다.