Firebird 데이터베이스 속도를 높이는 45가지 방법

여기에서 Firebird 데이터베이스의 다양한 영역에 대한 성능 팁 목록을 찾을 수 있습니다 - 하드웨어/OS 및 Firebird 구성 튜닝부터 SQL 최적화 권장 사항까지. 이 목록은 Firebird를 최적화하는 방법에 대한 완전한 참조 자료가 아니며, 실행 계획, 트랜잭션 관리, 쿼리 성능 통계와 같은 Firebird 작동의 기본 사항을 이해하고 있다고 가정합니다.

이 팁들은 주의해서 적용하고, 프로덕션에 적용하기 전에 효과를 검증하시기 바랍니다.

당사(IBSurgeon)는 포괄적인 데이터베이스 성능 최적화 서비스를 제공합니다.

1. 데이터베이스를 SSD에 배치

데이터베이스를 SSD에 배치하세요. SSD 드라이브는 기존 드라이브보다 훨씬 더 나은 임의 IO를 제공합니다. 임의 IO는 대용량 데이터베이스 파일에 분산된 데이터를 읽고 쓰는 데 중요합니다 - 대부분의 데이터베이스 작업은 집중적인 병렬 임의 IO가 필요합니다.

2. RAID 10 사용

RAID1 또는 RAID5를 사용하는 경우 RAID10을 고려하세요 - 15-25% 더 빠릅니다.

3. BBU 확인

RAID 컨트롤러를 사용하는 경우 BBU(Backup Battery Unit)가 설치되어 작동하는지 확인하세요 - 일부 공급업체는 기본적으로 BBU를 제공하지 않습니다. BBU가 없으면 컨트롤러가 캐시를 비활성화하고 RAID가 매우 느리게 작동하며, 일반 SATA 드라이브보다도 느립니다. 일반적으로 RAID 구성 도구에서 BBU 상태를 확인할 수 있습니다.

4. 쓰기 캐시를 write-back으로 설정

BBU가 설치된 RAID 컨트롤러(및 UPS가 있는 서버)를 사용하는 경우 캐시가 write-back(쓰기 저장)으로 설정되어 있는지 확인하세요(write-through가 아닌). «Write-back»은 컨트롤러의 쓰기 캐시를 활성화합니다.

5. 읽기 캐시 활성화

RAID 컨트롤러를 사용하는 경우 읽기 캐시가 활성화되어 있는지 확인하세요.

6. 디스크 서브시스템 확인

드라이브에 불량 블록 및 기타 하드웨어 문제(과열 포함)가 있는지 확인하세요. 하드웨어 문제는 IO 성능을 크게 저하시키고 데이터베이스 손상을 초래할 수 있습니다.

7. Firebird 2.5에서 SuperClassic 또는 Classic 사용

많은 연결이 있는 Firebird 2.5 SuperServer를 사용하는 경우 SuperClassic 또는 Classic을 사용해 보세요. CPU의 모든 코어를 사용하여 더 잘 확장될 수 있습니다.

8. Firebird 3에서 SuperServer 3.0 사용

2.5에서 Classic 또는 SuperClassic을 사용하는 경우 Firebird 3.0 SuperServer로의 마이그레이션을 고려하세요. 이제 여러 코어를 사용할 수 있고 공유 캐시의 장점과 결합할 수 있습니다.

9. 페이지 버퍼 캐시 증가

페이지 버퍼 캐시 크기(DefaultDBCachePages 매개변수)를 기본값에서 늘리세요. 2.5 SuperServer의 경우 10000페이지, 3.0 SuperServer의 경우 50000페이지, Classic 및 SuperClassic의 경우 256~2048페이지를 권장합니다. 그러나 페이지 버퍼 캐시 값을 너무 높게 설정하지 마세요 - 캐시 동기화에는 비용이 들며, 이 값을 조정하여 전체 데이터베이스를 RAM에 넣는 아이디어는 작동하지 않습니다. 사전 최적화된 Firebird 구성 파일을 여기에서 사용하세요: /ko/optimized-firebird-configuration/

10. 정렬 작업을 위한 메모리 크기 증가

firebird.conf에서 TempCacheLimit 매개변수 값을 늘리세요 - 정렬을 위한 임시 공간의 캐시 크기를 지정합니다. 기본값은 너무 낮습니다(Classic의 경우 8Mb, SuperServer의 경우 64Mb). Classic의 경우 최소 64Mb, SuperServer 및 SuperClassic의 경우 1Gb를 사용하세요. 다시 말하지만, #9의 최적화된 구성 파일을 사용하세요.

11. Forced Writes 끄기(주의해서!)

집중적인 삽입 또는 업데이트 활동이 있고(HQbird MonLogger로 확인할 수 있으며, 자세한 내용은 HQbird 사용자 가이드 60페이지 참조), 하드웨어 장애로부터 보호하기 위해 UPS와 복제가 설치된 경우 Forced Writes 설정을 OFF로 설정하는 것을 고려하세요. 쓰기 작업 속도를 최대 3배까지 높일 수 있습니다.

12. Classic/SuperClassic의 해시 슬롯 수 증가

Classic 및 SuperClassic의 LockHashSlots 매개변수 값을 기본값 1009에서 큰 소수(예: 30011)로 늘리세요. 내부 잠금 메커니즘의 대기열이 줄어듭니다.

13. Super Server 2.5에 CPU Affinity 사용

SuperServer 2.5를 사용하는 경우 CPUAffinity 매개변수를 사용 중인 데이터베이스 수와 동일한 값으로 설정하세요. 2.5의 SuperServer는 특정 데이터베이스에 대한 요청을 처리하기 위해 다른 CPU 코어를 사용할 수 있습니다.

14. 임시 공간에 빠른 드라이브 사용

firebird.conf에서 TempDirectory 매개변수의 첫 번째 부분을 빠른 디스크 - SSD 또는 RAM 드라이브로 설정하세요. 큰 정렬 시간이 줄어듭니다 - 예를 들어 데이터베이스가 복원되는 경우입니다.

15. 데이터베이스 백업을 다른 드라이브에 저장

데이터베이스 백업을 전용 물리적 드라이브(RAID)에 저장하세요. 백업 중 읽기 및 쓰기 IO가 분리되어 백업 속도가 빨라지고 기본 드라이브의 부하가 줄어듭니다. 사용자가 데이터베이스로 작업하는 동안 백업을 수행할 때 특히 중요합니다. Firebird의 하드웨어 구성에 대한 자세한 내용은 " Firebird 하드웨어 가이드“에서 확인할 수 있습니다.

16. 대량 삽입 시 인덱스 비활성화

많은 레코드(테이블의 25% 이상)를 삽입하거나 업데이트하는 경우 레코드가 삽입되는 테이블의 인덱스를 비활성화하고 삽입 또는 업데이트 후 다시 활성화하세요. 인덱스 재구축 작업은 인덱스의 많은 업데이트보다 더 빠를 수 있습니다.

17. 빠른 삽입을 위해 Global Temporary Tables 사용

삽입 및 업데이트 속도를 높이려면 대용량 레코드셋의 대량 삽입에 Global Temporary Tables를 사용한 다음 레코드를 영구 테이블로 전송하세요. GTT에 레코드를 삽입하고, 사전 처리한 다음 영구 테이블로 이동하는 것은 매우 효과적일 수 있습니다.

18. 불필요한 인덱스 피하기

집중적인 삽입 및 업데이트가 있는 테이블에는 더 적은 인덱스를 사용하세요. 각 인덱스는 삽입, 업데이트, 삭제 및 가비지 수집 작업에 상당한 오버헤드를 추가합니다 - 단일 레코드가 삽입/업데이트/삭제/정리될 때 각 인덱스에 대해 3-4개의 추가 페이지 읽기 및 쓰기가 발생할 수 있습니다.

19. UDF를 내장 함수 호출로 대체

UDF 호출을 내장 함수 호출로 대체하세요. 최근 Firebird 버전에서는 이전에 UDF 라이브러리에서만 사용할 수 있었던 기능을 제공하는 많은 내장 함수가 추가되었습니다. 가능한 경우 이러한 함수를 대체하세요. 내장 함수는 UDF보다 최대 3배 빠르게 작동합니다.

20. 읽기 작업에 읽기 전용 트랜잭션 사용

레코드를 변경하지 않는 작업(즉, SELECT)에는 격리 모드 = read committed인 읽기 전용 트랜잭션을 사용하세요. 이러한 트랜잭션은 가비지 수집에서 레코드 버전을 유지하지 않으며 무기한 실행될 수 있습니다. 데이터베이스 성능에 영향을 미치지 않습니다.

21. 짧은 쓰기 트랜잭션 사용 및 모든 장기 실행 트랜잭션 제거

짧은 쓰기 가능 트랜잭션(INSERT/UPDATE/DELETE 작업용)을 사용하세요.

쓰기 가능 트랜잭션이 짧을수록 좋습니다. 짧은 트랜잭션은 장기 실행 트랜잭션보다 가비지 수집에서 비례적으로 더 적은 수의 레코드 버전을 유지합니다. 불행히도, 단일 장기 실행 트랜잭션(예: 개발 도구에서 열어 둔)도 다른 모든 짧은 쓰기 가능 트랜잭션의 좋은 효과를 망칠 수 있습니다. 그렇기 때문에 장기 실행 트랜잭션을 모니터링하고 소스 코드의 적절한 위치를 수정해야 합니다. HQbird DataGuard 도구를 사용하여 Firebird 데이터베이스에서 가장 오래된 활성 트랜잭션에 대한 알림을 받고(어떤 응용 프로그램이 시작했는지, 어떤 IP 주소인지, 시작 타임스탬프), HQbird MonLogger 도구를 사용하여 장기 실행 활성 트랜잭션의 전체 목록과 IO 통계를 확인하세요. 또한 레코드셋을 캐시할 수 있는 데이터베이스 액세스 구성 요소/라이브러리를 사용하는 경우 캐시된 업데이트를 사용하세요.

22. 긴 레코드 체인 피하기

하나의 레코드에 많은 레코드 버전이 있는 상황을 피하세요 - Firebird는 긴 레코드 체인에서 훨씬 느리게 작동합니다. (일부 테이블에 몇 개의 레코드 버전이 있는지, 가장 긴 레코드 체인이 무엇인지 확인하려면 HQbird IBAnalyst 도구, Tables 탭, “Max Version” 기준 정렬을 사용할 수 있습니다.) 동일한 레코드의 여러 업데이트 대신 삽입과 오래된 레코드의 예약된 삭제를 조합하여 사용하세요.

23. PREPARE를 올바르게 사용하세요

매개변수만 변경되는 SQL 쿼리를 실행하려면 준비된 문(Prepared Statement)을 사용하세요 - 예를 들어, 이러한 쿼리의 루프 전에 prepare를 수행하십시오. Prepare는 (특히 큰 테이블의 경우) 상당한 시간이 걸릴 수 있으며, 쿼리를 한 번만 준비하면 전체 성능이 크게 향상됩니다.

24. 대량 INSERT/UPDATE 작업 중에는 너무 자주 COMMIT하지 마세요

대량 INSERT/UPDATE/DELETE 작업의 경우, 각 변경 후 트랜잭션을 커밋하지 마십시오(데이터베이스 드라이버에서 자동 커밋 옵션을 사용하는 경우 발생할 수 있습니다) - 최소 1000회 이상의 작업 후에 트랜잭션을 커밋하십시오. 각 트랜잭션 커밋은 데이터베이스에 대해 여러 번의 읽기/쓰기 IO 작업을 실행하므로, 잦은 커밋은 데이터베이스 성능을 저하시킵니다.

25. 많은 상수와 함께 IN을 사용하는 경우 인덱스를 “끄세요”

WHERE fieldX IN (Constant1, Constant2,… ConstantN) 구문을 사용하고 fieldX에 인덱스가 있는 경우, Firebird는 IN 목록에 있는 상수 수만큼 인덱스를 사용합니다. fieldX를 표현식 +0으로 변환하여 인덱스 검색을 비활성화하세요: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), 또는 문자열의 경우 fieldX||‘‘를 사용하세요.

26. IN을 JOIN으로 대체하세요

중첩된 WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() ))이 있는 쿼리는 피하십시오. 이는 Firebird 최적화 프로그램을 혼란시킬 수 있습니다. 중첩된 IN을 조인으로 변환하세요.

27. LEFT JOIN을 올바른 방식으로 사용하세요

LEFT OUTER 조인을 사용하는 경우, 조인에서 테이블을 가장 작은 것부터 가장 큰 것 순서로 명시적으로 배치하십시오.

28. SELECT 쿼리의 페치를 제한하세요

SELECT 쿼리의 큰 출력은 항상 FIRST… SKIP 또는 ROWS 절로 제한하십시오. 쿼리가 특별히 보고서용으로 설계된 경우(모든 레코드를 출력/내보내야 하는 경우)가 아니라면, 일반적으로 상위 10-100개 레코드를 표시하는 것으로 충분합니다. 필요한 레코드만 페치하세요.

29. ORDER BY/GROUP BY가 있는 SELECT에서 더 적은 수의 컬럼을 지정하세요

ORDER BY/GROUP BY가 있는 쿼리에서 SELECT 부분(즉, 표시할 필드)과 ORDER BY 절 모두에서 컬럼 수와 전체 너비를 줄이세요. Firebird는 SELECT와 ORDER BY/GROUP BY 절의 컬럼을 병합하여 메모리(또는 메모리가 충분하지 않은 경우 디스크)에서 정렬합니다. 따라서 SELECT에 긴 VARCHAR가 있는 경우 정렬 파일의 크기가 매우 커질 수 있습니다(수 기가바이트). 정렬해야 하는 필드만으로 필드 수를 줄이고 큰 필드는 나중에 조인하면 ORDER BY/GROUP BY가 있는 쿼리의 속도를 크게(x3-x10) 향상시킬 수 있습니다.

30. 파생 테이블을 사용하여 ORDER BY/GROUP BY가 있는 SELECT를 최적화하세요

정렬이 있는 SQL 쿼리를 최적화하는 또 다른 방법은 불필요한 정렬 작업을 피하기 위해 파생 테이블을 사용하는 것입니다. 다음 대신:

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

다음과 같이 수정하여 사용하세요:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

31. 짧은 문자열은 VARCHAR에, 긴 문자열은 BLOB에 저장하세요

짧은 문자 데이터를 저장하려면 VARCHAR를 사용하고, 긴 텍스트를 저장하려면 BLOB를 사용하세요. VARCHAR는 작은 데이터 조각에 대해 더 빠릅니다. 레코드에 저장되고 전체 레코드가 동일한 IO 주기에서 읽히며, 레코드 크기가 데이터베이스 페이지 크기의 2/3보다 작으면 전체 레코드가 동일한 데이터베이스 페이지에 저장됩니다. BLOB는 레코드 외부에 저장되며 읽기 위해 추가 IO 라운드가 필요하지만, 긴 문자열을 읽고 쓰는 데는 장점이 있습니다.

32. 큰 SELECT에서 BLOB 컬럼을 제외하세요

큰 SELECT에서 BLOB 컬럼을 제외하세요. 서브 셀렉트와 함께 일종의 지연 바인딩을 사용하여 BLOB의 정보를 선택적으로 표시하십시오(예: 문서 내용 표시).

33. 기본 키와 고유 키에 BIGINT를 사용하세요

자동 증가 기본 키와 고유 키 및 모든 유형의 식별자에 BIGINT 유형을 사용하세요. BIGINT 연산이 가장 빠르며, BIGINT는 거의 모든 데이터 범위를 저장할 수 있는 충분한 용량을 가지고 있습니다.

34. 키에 VARCHAR를 사용하지 마세요

정말 필요한 경우가 아니라면 식별자에 VARCHAR를 사용하지 마십시오 - 정수 컬럼보다 훨씬 효율성이 떨어집니다. 특히 GUID를 식별자로 사용하지 마십시오 - GUID 값의 무작위 분포로 인해 GUID 기본/고유 키가 있는 INSERT/UPDATE 작업은 정수보다 20배 느릴 수 있습니다.

35. 인덱스 통계를 재계산하세요

인덱스 통계를 정기적으로 재계산하세요. 빈번하거나 대규모 변경이 있는 테이블의 인덱스 통계를 SET STATISTICS 명령으로 업데이트하면 Firebird 최적화 프로그램이 더 나은 SQL 계획을 선택할 수 있습니다. HQbird Firebird DataGuard는 원하는 일정(보통 주 1회)에 따라 이러한 인덱스 통계 재계산을 자동으로 수행할 수 있습니다.

36. 연결 풀을 사용하세요

Firebird 데이터베이스에 대한 데이터베이스 연결이 짧은 경우(웹사이트에서 일반적), 연결 풀을 사용하십시오 - 예를 들어 PHP에서는 ibase_connect 대신 ibase_pconnect 함수를 사용하십시오.

37. Firebird 3.0에서 LINGER 옵션을 사용하세요

데이터베이스 연결이 짧고 Firebird 3+를 사용하는 경우, LINGER 옵션을 사용하여 지정된 시간 동안 캐시를 활성 상태로 유지하십시오. 다른 연결이 없더라도 자주 사용되는 페이지가 캐시에 유지됩니다. 예를 들어, ALTER DATABASE SET LINGER TO 60은 마지막 연결이 종료된 후 60초 동안 캐시를 유지합니다.

38. HASH JOIN을 사용하세요

Firebird 3.0에서 큰 테이블과 작은 테이블을 조인하는 경우, HASH JOIN이 인덱스가 있는 «중첩 루프»를 사용하는 일반 조인보다 훨씬 빠를 수 있습니다. Firebird 최적화 프로그램이 HASH 조인을 사용하도록 하려면 조인 조건에 +0을 사용하세요: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. 프로덕션에 적용하기 전에 최적화 결과를 확인하세요!

39. 적절한 PSQL 함수를 DETERMINISTIC으로 표시하세요

매개변수가 없고 상수 값을 반환하는 PSQL 함수(Firebird 3+)에 DETERMINISTIC 키워드를 표시하세요. 결정적 함수는 현재 쿼리 범위 내에서 계산되고 캐시됩니다.

40. Firebird 3.0에서 분석(윈도우) 함수를 사용하세요

일부 컬럼과 해당 컬럼의 집계 함수를 동시에 출력하는 SELECT를 실행하는 경우, 윈도우(분석) 함수를 사용하십시오 - 서브쿼리나 2개의 쿼리보다 빠릅니다. 예를 들어:

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

다음으로 대체하세요:

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. gbak에 -se 스위치를 사용하세요

-se 스위치를 사용하여 gbak 백업 및/또는 복원 속도를 최대 20%까지 높이십시오. 예를 들어:

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

PSQL에서 커서로 페치된 레코드를 처리하는 가장 빠른 방법은 ‘where current of <>’ 절입니다. 이는 ‘where rb$db_key = :v_db_key’보다 빠르며 기본 키나 고유 키로 검색하는 것보다 훨씬 빠릅니다.

43. 모니터링 테이블에 대한 빈번한 쿼리를 피하세요

Firebird 모니터링 테이블(MON$)에 대한 쿼리를 너무 자주 실행하지 마십시오 - 이러한 쿼리는 상당한 리소스를 소비하며 주요 비즈니스 로직의 성능을 크게 저하시킬 수 있습니다. MON$ 쿼리는 1분에 한 번 이상 실행하지 않는 것을 권장합니다. Firebird 쿼리/트랜잭션/연결을 지속적으로 모니터링하려면 Trace API를 지원하는 HQbird PerfMon 도구를 사용하십시오(자세한 내용은 HQbird 사용자 가이드 66페이지 참조).

44. 대량 삽입/업데이트에 NO_AUTO_UNDO 옵션을 사용하세요

동일한 트랜잭션 프레임 내에서 많은 DML(Update/Insert/Delete) 명령을 실행하는 경우, Firebird는 각 명령의 실행 취소 로그를 트랜잭션의 실행 취소 로그와 병합합니다. 대량 DML 작업의 속도를 높이려면 각 명령의 실행 취소 로그를 트랜잭션의 실행 취소 로그와 병합하지 않도록 «NO AUTO UNDO» 옵션으로 트랜잭션을 시작하십시오.

45. 필요하지 않다면 Firebird 3에서 SRP 인증을 사용하지 마세요

정말 필요하지 않다면 SRP 사용자 인증(Firebird 3.0+)을 사용하지 마십시오 - SRP 인증으로 연결하는 것은 일반 연결보다 느리게 설정됩니다.

대신 요약

성능 최적화는 여러 요소를 고려해야 하며, 정말 까다로울 수 있습니다. 위의 모든 방법을 시도했다면, 전문 데이터베이스 성능 최적화 서비스를 고용하는 것을 고려해 보세요.

문의하기

질문이 있으신가요? 주저하지 말고 이메일로 문의해 주세요!