1.7 테라바이트 Firebird SQL 데이터베이스에 대한 자세한 정보
Alexey Kovyazin, 2014년 5월 26일
며칠 전 우리는 Firebird 성능과 데이터베이스 성장 간의 관계에 관한 테스트 기사를 게시했으며, 그중 1.7 테라바이트 Firebird SQL 데이터베이스도 테스트했습니다. 여기에서 그러한 대용량 데이터베이스에 대한 자세한 내용을 확인할 수 있습니다.
테이블
표 1에서 주요 특성을 포함한 테이블 목록을 확인할 수 있습니다. 이 통계는 gstat -a -r로 수집되었으며, 우리의 IBAnalyst 도구로 해석되었습니다.
| 테이블 | 레코드 수 | 레코드 길이, 바이트 | 데이터 페이지 | 테이블 크기, Mb | 인덱스 크기, Mb | 합계, % |
|---|---|---|---|---|---|---|
| ORDER_LINE | 6300024797 | 60.09 | 38880344 | 607505.3 | 50582.3 | 34 |
| STOCK | 2100000000 | 298.88 | 43783806 | 684121.9 | 15809.61 | 39 |
| ORDERS | 630004141 | 29.00 | 2692324 | 42067.56 | 4250.38 | 2 |
| HISTORY | 630000434 | 48.77 | 3446455 | 53850.86 | 0.00 | 3 |
| CUSTOMER | 630000000 | 577.52 | 24226054 | 378532.0 | 8675.78 | 21 |
| NEW_ORDER | 188999981 | 13.00 | 623764 | 9746.31 | 1260.84 | 1 |
| DISTRICT | 210000 | 103.92 | 1860 | 29.06 | 1.27 | ~0 |
| ITEM | 100000 | 82.73 | 756 | 11.81 | 0.52 | ~0 |
| WAREHOUSE | 21000 | 97.90 | 179 | 2.80 | 0.11 | ~0 |
표 1. 1.7테라바이트 Firebird SQL 데이터베이스의 테이블 및 주요 매개변수
보시다시피, 가장 큰 테이블은 ORDER_LINE으로 60억 개 이상의 레코드를 포함합니다. 크기는 약 600Gb이며, 이 테이블에는 50Gb의 인덱스가 있습니다. 이 테이블은 데이터베이스의 34%를 차지합니다.
STOCK 테이블도 매우 큽니다 - 약 20억 개의 레코드를 포함합니다. 레코드 수는 ORDER_LINE보다 적지만, STOCK의 레코드 길이가 298.88바이트인 반면 ORDER_LINE은 60.09바이트에 불과하므로 STOCK은 데이터베이스에서 약 680Gb(39%)를 차지합니다. 따라서 STOCK의 관련 인덱스 크기는 15Gb에 불과합니다.
세 번째로 큰 테이블인 CUSTOMER는 레코드 길이가 577.52바이트로 더 크며, 6억 3천만 개의 레코드만으로 378Gb(21%)를 차지합니다.
인덱스
이 데이터베이스는 테스트 전용으로 설계되었기 때문에 인덱스가 많지 않습니다 - 대부분의 테이블에는 기본 키인 인덱스가 1개만 있습니다. 실제 애플리케이션에서는 개발자가 특정 사용자 요청을 처리하기 위해 많은 인덱스를 만들지만, 여기서는 인덱스가 가장 빠른 삽입 및 데이터 업데이트에 집중되어 있습니다 - 결과적으로 인덱스 크기는 테이블 크기의 5-10%에 불과하며, 일반적으로는 30-50%입니다(인덱스 크기가 테이블 크기의 50%를 초과하는 경우 데이터베이스 스키마 재설계를 고려하거나, 불필요하거나 비효율적인 인덱스를 삭제하는 것이 좋습니다).
표 2에서 이 데이터베이스의 모든 인덱스 목록과 주요 특성을 확인할 수 있습니다. 이는 gstat 통계에서 가져와 IBAnalyst로 분석한 것입니다:
| 인덱스 | 테이블 | 깊이 | 키 수 | 키 길이, 바이트 | 고유 키 수 | 크기, Mb |
|---|---|---|---|---|---|---|
| ORDER_LINE_PK | ORDER_LINE | 4 | 6300024797 | 1.41 | 6300024797 | 50582.34 |
| STOCK_PK | STOCK | 4 | 2100000000 | 1.00 | 2100000000 | 15809.61 |
| ORDERS_PK | ORDERS | 3 | 630004141 | 1.01 | 630004141 | 4250.38 |
| CUSTOMER_LAST | CUSTOMER | 3 | 630000000 | 0.00 | 1000 | 4029.64 |
| CUSTOMER_PK | CUSTOMER | 3 | 630000000 | 1.01 | 188999981 | 1260.84 |
| NEW_ORDER_PK | NEW_ORDER | 3 | 188999981 | 1.01 | 188999981 | 1260.84 |
| DISTRICT_PK | DISTRICT | 2 | 210000 | 1.37 | 210000 | 1.27 |
| ITEM_PK | ITEM | 2 | 100000 | 1.00 | 100000 | 0.52 |
| WAREHOUSE_PK | WAREHOUSE | 2 | 21000 | 1.00 | 21000 | 0.11 |
표 2. Firebird 1.7Tb 데이터베이스의 인덱스
보시다시피, 모든 인덱스의 키 크기가 매우 작습니다 - 가장 큰 것이 1.41이며, 이는 인덱스가 매우 효과적으로 압축되었음을 의미합니다 - 예를 들어, ORDER_LINE의 기본 키는 50Gb 공간에 60억 개의 키를 포함합니다. 이는 매우 좋은 결과입니다.
깊이가 4인 인덱스가 2개 있습니다. 이는 엔진이 요청된 값을 찾기 위해 인덱스 페이지를 4번 읽어야 함을 의미합니다. 인덱스 깊이는 3을 넘지 않는 것이 권장되며, 더 높다면 데이터베이스 페이지 크기를 늘려야 합니다. 그러나 이 데이터베이스에는 이미 최대 페이지 크기(16Kb)가 있으므로 이를 감수해야 합니다.
워밍업
쿼리를 계속하기 전에 “워밍업"을 수행해 보겠습니다. 데이터베이스가 크면 테이블과 관련된 시스템 데이터도 크므로, Firebird가 필요한 시스템 페이지를 캐시하는 데 시간이 걸립니다. 예를 들어, ORDER_LINE 테이블에는 10110개의 포인터 페이지가 있습니다. 워밍업은 모든 대용량 데이터베이스에 필요합니다.
워밍업 없이 쿼리를 처음 실행하면 워밍업 후보다 훨씬 오래 걸립니다.
데이터베이스 캐시를 워밍업하려면 시스템 데이터를 캐시에 로드하는 일련의 간단한 쿼리를 수행해 보겠습니다:
select first 1 * from ORDER_LINE; select first 1 * from STOCK; select first 1 * from customer; select first 1 * from ORDERS; select first 1 * from ITEM;
이 정도면 이러한 단순한 데이터 스키마에는 충분합니다. 수백 개의 테이블이 있는 크고 복잡한 데이터베이스의 경우 워밍업 스키마가 훨씬 더 복잡할 수 있으며, 신중하게 개발해야 합니다.
결과적으로 Firebird 프로세스(Firebird SuperServer 2.5.2 64비트)의 메모리 사용량이 증가합니다:
그림 1. 데이터베이스 캐시 워밍업
쿼리
워밍업 후 몇 가지 일반적인 쿼리를 실행하여 사용자가 이러한 대용량 Firebird 데이터베이스에서 어떻게 작업할지 추정할 수 있습니다: 일반적인 쿼리 및 읽기 작업의 응답 시간은 얼마나 될까요?
| 쿼리 | 계획 및 통계 | 설명 |
|---|---|---|
select first 10 w_id, w_name, c_id, c_last<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000 |
PLAN JOIN (WAREHOUSE NATURAL,<br> CUSTOMER INDEX (CUSTOMER_PK))<br> STATISTICS<br> Current memory = 166919744<br> Delta memory = 5616<br> Max memory = 166977488<br> Elapsed time= 0.08 sec<br> Buffers = 10000<br> Reads = 1<br> Writes 0<br> Fetches = 51 |
WAREHOUSE 테이블(21000개 레코드)과 CUSTOMER 테이블(6억 3천만 개 레코드)을 특정 창고 조건으로 조인 |
select count(*)<br> from WAREHOUSE, customer<br> where c_w_id = w_id and c_w_id = 10000 |
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> COUNT<br> ============<br> 30000<br> <br> Current memory = 166918560<br> Delta memory = -1184<br> Max memory = 166977488<br> Elapsed time= 0.16 sec<br> Buffers = 10000<br> Reads = 1175<br> Writes 0<br> Fetches = 60040 |
이전 쿼리의 레코드 수 계산. |
SELECT first 10 *<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050 |
PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> STATISTICS<br> Current memory = 167013640<br> Delta memory = 95080<br> Max memory = 167055968<br> Elapsed time= 0.80 sec<br> Buffers = 10000<br> Reads = 10285<br> Writes 0<br> Fetches = 10424 |
가장 큰 테이블 ORDER_LINE(약 60억 개 레코드)에 대한 쿼리로, 지정된 창고 ID에 대한 order_line 레코드를 선택하는 조건입니다. 기본 키 ORDER_LINE_PK는 복합 키이며 창고 ID(CONSTRAINT ORDER_LINE_PK: Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)를 포함하므로 이 쿼리에서 효과적으로 사용됩니다. |
SELECT count(*)<br> FROM ORDER_LINE<br> WHERE OL_W_ID = 10050; |
PLAN (ORDER_LINE INDEX (ORDER_LINE_PK))<br> <br> COUNT<br> ============<br> 299509<br> Current memory = 167125464<br> Delta memory = -7160<br> Max memory = 167267976<br> Elapsed time= 3.41 sec<br> Buffers = 10000<br> Reads = 1994<br> Writes 0<br> Fetches = 599170 |
이전 쿼리의 수 계산. 동일한 매개변수를 사용한 두 번째 쿼리는 훨씬 빠르지만, 다른 매개변수를 사용한 쿼리도 유사한 결과를 보여줍니다. |
Select first 10 w_id, w_name, c_id, c_last<br> rom WAREHOUSE, CUSTOMER<br> where c_w_id = w_id and (c_w_id > 8000)<br> and (c_w_id < 10000) |
PLAN JOIN (WAREHOUSE INDEX (WAREHOUSE_PK),<br> CUSTOMER INDEX (CUSTOMER_PK))<br> <br> STATISTICS<br> Current memory = 167687664<br> Delta memory = -651888<br> Max memory = 168931400<br> Elapsed time= 0.19 sec<br> Buffers = 10000<br> Reads = 31<br> Writes 0<br> Fetches = 63 |
조인과 2개의 조건이 있는 쿼리. |
| select first 10 c1.C_ID, c1.C_FIRST, c1.C_LAST, o1.O_ID,<br> o1.O_OL_CNT, i1.I_NAME, i1.I_PRICE, ol1.OL_AMOUNT<br> from customer c1<br> join orders o1 on (c1.c_w_id = o1.O_w_ID<br> and c1.C_D_ID = o1.O_D_ID and c1.C_ID = o1.O_C_ID)<br> join ORDER_LINE ol1 on (ol1.ol_w_id = c1.C_W_ID<br> and ol1.OL_D_ID = c1.C_D_ID<br> and ol1.OL_O_ID = o1.O_ID)<br> join Item i1 on (ol1.OL_I_ID = i1.i_id)<br> where o1.o_d_id = 1 and o1.o_w_id = 1 and c1.c_id = 10; | PLAN JOIN (C1 INDEX (CUSTOMER_PK),<br> O1 INDEX (ORDERS_PK),<br> OL1 INDEX (ORDER_LINE_PK),<br> I1 INDEX (ITEM_PK))<br> <br> STATISTICS<br> Current memory = 166807128<br> Delta memory = 6016<br> Max memory = 166879200<br> Elapsed time= 0.20 sec<br> Buffers = 9999<br> Reads = 1<br> Writes 0<br> Fetches = 4960 | 특정 창고와 구역에 있는 특정 고객의 세부 정보를 표시하는 쿼리입니다. 더 복잡한 조인의 성능을 보여줍니다. |
요약
보시다시피, 1.7 테라바이트 Firebird 데이터베이스는 일반적인 읽기 쿼리에서 상당히 좋은 성능을 보여줍니다. 쿼리가 잘 설계되고 좋은 인덱스를 사용한다면, 매우 큰 데이터베이스에서도 성능이 좋습니다. 물론 매우 큰 데이터셋의 긴 페치 시간이나 count 연산의 긴 처리 시간(특정 트랜잭션에서 특정 쿼리에 대한 정확한 레코드 수를 얻기 위해 Firebird가 모든 페이지를 방문하기 때문)과 같은 문제점이 있습니다. 그러나 이러한 문제점은 경험 많은 Firebird 개발자들에게 잘 알려져 있으며, 이를 우회하기 위해 데이터베이스를 설계하는 방법이 있습니다.
그렇다면 삽입 및 업데이트의 성능 저하는 어떨까요? Firebird 구성의 스마트한 튜닝으로 이를 보완할 수 있을까요?
다음 기사에서는 Firebird 1.7 테라바이트 데이터베이스의 튜닝 옵션을 살펴보겠습니다 - 우리의 새를 강화하고 날게 만들 것입니다.