IBAnalyst: 팁과 요령

이 글은 원래 2012년에 작성되었으며, 버전 1.0~~2.5에 유효합니다. 버전 3.0~~5.0에서는 많은 변경 사항이 있어 반영할 수 없었습니다. 문서를 읽거나 [email protected]으로 문의하여 지원을 받으시기 바랍니다.

IBAnalyst 권장 사항 및/또는 도움말에서 답변되지 않은 몇 가지 질문:

1. PRIMARY, FOREIGN 또는 UNIQUE 제약 조건에 대한 인덱스를 어떻게 재구축합니까?

A: Firebird 버전 1.0-2.5의 경우. 네, 제약 조건 인덱스에는 ALTER INDEX xxx INACTIVE/ACTIVE를 사용할 수 없습니다. 이 제약 조건에 깊거나 단편화된 인덱스가 보이면 특별한 트릭(복원 시 gbak이 사용하는)을 사용할 수 있습니다:

RDB$INDICES에는 RDB$INDEX_INACTIVE 플래그가 있으며, 인덱스가 활성 상태이면(CREATE INDEX 또는 ALTER INDEX ACTIVE 후) null 또는 0입니다. 1은 인덱스가 비활성 상태임을 의미합니다(ALTER INDEX INACTIVE 후). 그러나 제약 조건의 비활성 인덱스를 나타내는 데 사용되는 값 3도 있습니다. 따라서 해당 인덱스에 RDB$INDEX_INACTIVE=3을 설정하고 COMMIT한 다음 값을 0으로 되돌리고 다시 커밋하면 인덱스가 재구축됩니다.

Firebird 3.0-5.0의 경우 - 간단히 ALTER INDEX indexname ACTIVE를 실행하세요.

2. IBAnalyst 권장 사항을 모두 사용했지만 쿼리 속도를 높이는 데 도움이 되지 않습니다.

A: 이것은 IBAnalyst가 도움을 줄 수 없는 별도의 문제입니다. 문제의 원인은 두 가지가 있을 수 있습니다:

인덱스의 통계가 오래되었습니다. SET STATISTICS INDEX xxx 명령으로 인덱스 통계를 새로 고칠 수 있습니다(자세한 내용은 http://www.ibase.ru/proc_selectivity/ 참조).
쿼리에 사용된 일부 조건에 적합한 인덱스가 단순히 없습니다.
쿼리가 매우 복잡하거나 옵티마이저가 쿼리를 최적화할 수 없어 쿼리를 리팩토링해야 합니다.
어떤 경우에는 복원 직후 “단편화된 테이블"이 표시됩니다.

일반적으로 Firebird와 InterBase는(-use_all_space 매개변수 없이) 향후 삽입, 업데이트 또는 삭제(레코드 버전을 배치하기 위해)를 위해 데이터 페이지에 약 25%의 공간을 예약합니다. 그러나 어떤 데이터베이스 페이지 크기(1, 2, 4 또는 8k)에서도 레코드 크기가 작은 테이블(약 12-20바이트, 예를 들어 정수 필드 2개가 있는 테이블의 평균 레코드 크기는 12바이트)의 경우 약 50%의 단편화가 표시됩니다.

이것은 정상입니다. 서버의 마법 같은 숫자(또는 동작)로 간주하세요.

따라서 이러한 작은 레코드 테이블이 있는 경우 다음을 수행할 수 있습니다:

a) 해당 테이블에 대한 “단편화” 경고를 무시합니다.

b) 예를 들어 IBAnalyst 옵션 대화 상자에서 “단편화 %“를 45%로 낮춥니다.

4. 업데이트되지 않아야 하는 테이블의 레코드 버전

업데이트되지 않아야 하는 테이블(예: 이벤트 로그가 있는 테이블)에 레코드 버전이 표시되면 걱정하지 마세요. 이러한 버전은 삭제로 생성됩니다.

따라서 테이블에 현재 레코드가 몇 개인지, 삭제된 레코드가 몇 개인지 알 수 있습니다.

이것은 MaxVer = 1인 경우에만 해당됩니다. 1보다 크면 이 테이블이 일부 응용 프로그램에 의해 업데이트되고 있는 것입니다. 이 테이블이 절대 업데이트되지 않아야 한다고 확신한다면 “before update” 트리거에 예외를 설정하여 어떤 응용 프로그램이 업데이트를 수행하는지 찾는 것이 좋습니다.

5. Blob은 테이블 단편화를 유발할 수 있습니다.

엔진은 blob을 3가지 다른 방식으로 저장합니다:

blob 내용이 데이터 페이지에 맞으면(충분한 여유 공간이 있는 경우) 해당 레코드(또는 버전) 근처의 데이터 페이지에 저장됩니다.
blob 내용이 데이터 페이지에 맞지 않으면 별도의 페이지에 저장됩니다.
경우 2에서 blob이 하나의 데이터 페이지에 맞지 않으면 적절한 blob 페이지를 가리키는 포인터 페이지가 생성됩니다.

경우 1은 저장된 blob 크기와 데이터베이스 페이지 크기에 따라 발생합니다. 예를 들어 페이지 크기가 4K이고 평균 크기가 ~5K인 blob이 있으면 데이터 페이지가 아닌 추가 blob 페이지에 저장됩니다.

그러나 데이터베이스를 백업하고 8K 페이지 크기로 복원하면 blob이 데이터 페이지에 맞게 되어 레코드와 함께 저장되어 높은 레코드 단편화가 발생합니다.

IBAnalyst는 이러한 테이블을 Pale(레코드 열)로 표시하고 힌트는 해당 테이블의 예상 레코드 수(데이터 페이지 수 기준)와 실제 평균 채움 값(%)을 보여줍니다.

쿼리가 해당 테이블에서 blob을 제외한 필드를 읽으면 자연 스캔, 조인 또는 집계가 매우 느리게 실행됩니다.

이를 피할 수 있는 유일한 해결책: 추가 테이블(원래 테이블에 1-1로 연결)을 만들고 페이지 크기보다 작은 평균 크기를 가진 모든 blob 열을 그 테이블로 이동합니다.

이 경우 더 큰 페이지 크기로 백업/복원을 시도하지 마세요! 이렇게 하면 현재 페이지 크기로 데이터 페이지에 맞지 않았던 blob이 더 큰 페이지 크기로 복원하는 동안 데이터 페이지에 배치됩니다. 따라서 blob이 있는 테이블은 이전보다 더 단편화됩니다.

또한 더 작은 페이지 크기로 복원하는 것도 권장되지 않습니다. 인덱스 및 비-blob 테이블의 성능을 저하시킬 수 있기 때문입니다.

또한 blob 필드를 varchar 필드로 변경하려고 시도해서는 안 됩니다. varchar 필드는 항상 레코드의 일부로 저장되므로 데이터 페이지에 맞지 않으면 레코드가 2개 이상의 조각(2개 이상의 데이터 페이지에 배치)을 가질 수 있습니다.

추신: IBAnalyst는 “실수로” 이러한 테이블을 보고할 수 있습니다. 예를 들어 테이블에 blob 필드가 있었지만 테이블 구조에서 제거된 경우입니다. 불행히도 이 경고에 대한 구성 가능한 옵션은 없습니다. 서버(통계)가 보고하는 데이터에서 정확히 계산하기 때문입니다.

6. VerLen과 RecLength의 관계

a) VerLen >= RecLength의 90%: 버전 열에 표시되는 버전은 대부분 레코드 삭제입니다. 삭제된 레코드가 많을수록 RecLength는 더 작아집니다(0바이트까지). 또한 원래 레코드에 저장된 것보다 더 큰 문자열 데이터로 테이블을 업데이트하면 VerLen이 RecLen보다 클 수 있습니다.

b) VerLen <= RecLength의 80%: 버전은 대부분 레코드 업데이트입니다.

통계는 전체 테이블의 평균 레코드 및 버전 크기를 보여주지만 동시 트랜잭션에 대한 표시 버전 수는 다를 수 있으므로 이러한 경우를 더 정확히 구분할 수 없습니다.

7. IBAnalyst가 일부 인덱스를 “나쁜” 것으로 이름을 지정하는 이유는 무엇입니까?

선택도 값이 0.01보다 낮은 인덱스는 IBAnalyst에서 “나쁜” 것으로 표시됩니다(인덱스 보기 도움말 참조). 특정 인덱스를 나쁜 것으로 지정하는 데는 여러 가지 원인이 있습니다:

해당 인덱스의 선택도가 0.01보다 낮습니다. 이론적으로 옵티마이저는 해당 인덱스를 사용하지 않아야 하지만 다른 인덱스가 없으면(where, order by 또는 join 절에 대해 최소한) 사용합니다.
이러한 인덱스는 가비지 수집을 매우 느리게 만듭니다. 이 문제는 InterBase 7.1/7.5에는 존재하지 않으며 Firebird 2.0에서 수정될 예정입니다.
이 인덱스는 복원 프로세스를 매우 느리게 만들고 생성도 매우 느립니다(create/alter index active). 이는 하나의 인덱스 키에 대한 레코드 번호 체인이 크기 때문입니다.
이 인덱스가 where 절에서 사용되면 메모리 사용량은 검색되는 값(비트마스크 크기)에 따라 달라집니다. 레코드 체인이 클 수 있으므로(키 중복이 많음) 메모리 소비도 커집니다.
해당 인덱스가 “order by"에서 사용되고 대부분 낮은 키 값에 중복이 많은 경우(인덱스 정렬 순서에 따라) 인덱스 페이지 읽기가 많아져 쿼리가 느려집니다.

이것은 IBAnalyst가 이러한 인덱스의 존재를 무시할 수 없기 때문입니다.

인덱스의 최악의 경우는 Uniques 열 = 1, 즉 인덱스된 열의 모든 값이 동일한 경우입니다. 이러한 인덱스는 요약 페이지의 “유용하지 않은 인덱스"에 나열됩니다.

물론 응용 프로그램의 경우 이러한 인덱스가 “좋은” 것일 수 있습니다. 예를 들어 레코드에 일부 열에 “아카이브” 플래그가 있고 응용 프로그램이 해당 열의 인덱스로 아카이브된 데이터가 아닌 현재 데이터만 검색하는 경우입니다. 따라서 해당 인덱스를 “나쁜” 것으로 이름을 지정하는 것이 옳은지 여부는 사용자에게 달려 있습니다.

8. “나쁜” 인덱스가 외래 키 제약 조건에 의해 생성된 경우는 어떻게 합니까?

음, 이전 단락에서 “나쁜” 인덱스는(다른 키보다 중복이 적은 키를 검색하는 데 사용하지 않는 경우) 삭제하는 것이 좋습니다. 그러나 이러한 인덱스가 외래 키에 의해 생성된 경우 외래 키를 삭제해야만 인덱스를 삭제할 수 있습니다. 외래 키를 삭제하면 관계 검사 제약 조건이 비활성화되어 허용되지 않을 수 있습니다.

FK를 트리거로 대체할 수 있지만 몇 가지 제한 사항이 있습니다. FK는 인덱스를 사용하여 레코드 관계를 제어하며 인덱스는 트랜잭션 상태와 무관하게 모든 레코드의 모든 키를 “볼” 수 있습니다. 그러나 트리거는 클라이언트의 트랜잭션 컨텍스트에서만 작동합니다. 따라서 FK를 트리거로 대체할 때 다음을 확인해야 합니다:

마스터 테이블에서 레코드가 삭제되지 않거나 “스냅샷 테이블 예약” 모드로 삭제되는 경우
기본 테이블의 PK에 사용되는 컬럼은 절대 수정되지 않습니다. before update 트리거로 이를 제한할 수 있습니다.

이러한 조건을 유지한다면 특정 외래 키를 삭제할 수 있습니다. 물론 해당 컬럼에 수동으로 인덱스를 생성하지 마십시오.

9. 데이터 버전 비율 행에 12MB만 표시되는데, 데이터베이스가 140MB인 이유는 무엇인가요?

IBAnalyst는 여기서 다른 데이터베이스 구조(인덱스, 메타데이터 등)와 페이지 단편화를 제외한 “순수한” 데이터 용량만 표시합니다.
복원 후 InterBase와 Firebird는 향후 업데이트/삭제를 더 빠르게 하기 위해 데이터 페이지에 일부 여유 공간(15-25%)을 남겨둡니다.
테이블 레코드 크기가 약 11-22바이트로 작은 경우, 서버가 데이터 페이지를 약 50% 단편화된 상태로 남겨두는 특정 동작이 있습니다.

10. 빈번한 업데이트가 있을 때 옵티마이저 성능을 개선하는 방법

인덱스 통계는 RDB$INDICES.RDB$STATISTICS 컬럼에 저장되며, 세 가지 방식으로 업데이트됩니다:

SET STATISTICS INDEX
ALTER INDEX ACTIVE 또는 CREATE INDEX …
복원 프로세스(모든 인덱스가 “ALTER INDEX ACTIVE"와 함께 재구축됨)

옵티마이저는 이 통계 정보를 사용하여 쿼리를 준비합니다. 통계 값을 사용하여 옵티마이저는 인덱스가 레코드 검색에 “충분히 좋은지” 또는 “유용하지 않은지"를 결정할 수 있습니다.

오랜 시간 동안 통계가 업데이트되지 않으면 기존 통계 값이 실제 상태와 일치하지 않기 때문에 옵티마이저가 잘못된 실행 계획을 생성할 수 있습니다. 예를 들어 테이블 데이터가 크게 변경된 경우(레코드 수가 5-10배 증가하거나 반대로 모든 레코드가 삭제된 경우)입니다.

특정 쿼리에 대해 잘못된 자동 쿼리 계획을 명시적 PLAN으로 대체할 수 있지만, 계획이 수립된 후 데이터가 크게 변경될 수 있으므로 좋은 방법이 아닙니다.

대안적(그리고 올바른) 방법은 모든 인덱스에 SET STATISTICS 문을 적용하여 주기적으로 통계를 새로 고치는 것입니다. ISQL을 사용하거나 바로 사용할 수 있는 도구인 gidx(Windows 전용)를 사용하여 통계를 새로 고치는 SQL 스크립트 실행을 예약할 수 있습니다.

주기적으로 다른 레코드를 다시 로드하는 테이블이 있는 경우 이 방법은 도움이 되지 않습니다. 예를 들어 보겠습니다:

테이블 A에 하루에 4-5회 데이터가 로드됩니다.
로드된 데이터를 처리한 후 테이블 A의 모든 레코드가 삭제됩니다.

이 경우 테이블 A의 인덱스에 대해 두 가지 올바른 통계 값이 있을 수 있습니다 - 데이터가 로드된 경우와 비어 있는 경우입니다. 따라서 로드된 테이블에서 재계산된 통계는 테이블이 비어 있을 때 쓸모없게 되며, 그 반대의 경우도 마찬가지입니다.

이를 방지하려면 테이블 A의 인덱스 통계를 테이블에 데이터가 채워진 경우에만 재계산해야 합니다. 가장 좋은 시점은 해당 테이블에 대한 쿼리가 실행되기 전입니다.

버전 1.91부터 IBAnalyst는 인덱스 통계 차이를 표시하고 언제든지 재계산할 수 있게 해줍니다. 먼저 테이블 레코드 정보를 확인하십시오 - 일반적인 평균 레코드 수인지 여부를 확인합니다. 그렇다면 인덱스 선택도를 확실히 재계산할 수 있습니다. 그렇지 않다면 인덱스 통계를 건드리지 않는 것이 더 나을 수 있습니다. 옵티마이저가 더 나쁜 쿼리 계획을 생성할 수 있기 때문입니다.