Firebird SQLデータベース1.7テラバイトに関する詳細情報
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はデータベース内で約680GBを占めています（39%）。これは、STOCKのレコード長が298.88バイトであるのに対し、ORDER_LINEはわずか60.09バイトだからです。したがって、STOCKに関連するインデックスのサイズはわずか15GBです。
3番目に大きいテーブルであるCUSTOMERは、さらに大きなレコード長（577.52バイト）を持ち、わずか6億3000万レコードで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億3000万レコード）を、特定の倉庫の条件で結合 |
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 |
前のクエリのカウント。同じパラメータでの2回目のクエリははるかに高速ですが、異なるパラメータでのクエリは同様の結果を示します。 |
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データベースは、一般的な読み取りクエリに対して非常に良好なパフォーマンスを示します。クエリが適切に設計され、優れたインデックスを使用していれば、非常に大きなデータベースでもパフォーマンスは良好です。もちろん、非常に大きなデータセットのフェッチ時間が長いことや、カウント操作に時間がかかること（Firebirdが特定のトランザクション内の特定のクエリに対する正確なレコード数を取得するためにすべてのページを訪問するため）などの問題点はありますが、これらの問題点は経験豊富なFirebird開発者にはよく知られており、それらを回避するためのデータベース設計方法があります。
しかし、挿入や更新のパフォーマンス低下についてはどうでしょうか？Firebird設定のスマートなチューニングでそれを補うことは可能でしょうか？
次の記事では、1.7テラバイトのFirebirdデータベースのチューニングオプションについて検討します - 私たちの鳥を強化し、飛ばせてみましょう。