お問い合わせ
IBSurgeon
このページは機械翻訳されています。英語の原文をお読みください。 English

IBSurgeon ライブラリ

Firebirdデータベースを高速化する45の方法

ここでは、ハードウェア/OSやFirebirdの設定チューニングからSQL最適化の推奨事項まで、さまざまな領域におけるFirebirdデータベースのパフォーマンス向上のヒントを紹介します。このリストはFirebirdを最適化するための完全なリファレンスではなく、実行計画、トランザクション管理、クエリのパフォーマンス統計など、Firebirdの動作の基本を理解していることを前提としています。

これらのヒントは慎重に適用し、本番環境に導入する前にその効果を検証してください。

当社(IBSurgeon)は、包括的なデータベースパフォーマンス最適化サービスを提供しています。

1. データベースをSSDに配置する

データベースをSSDに配置してください。SSDドライブは従来のドライブよりもはるかに優れたランダムIOを提供します。ランダムIOは、大きなデータベースファイル全体に分散したデータの読み書きにとって重要です。データベース操作の大部分は、集中的な並列ランダムIOを必要とします。

2. RAID 10を使用する

RAID1またはRAID5を使用している場合は、RAID10を検討してください。15～25%高速です。

3. BBUを確認する

RAIDコントローラを使用している場合は、バックアップバッテリユニット(BBU)が搭載され、正常に動作していることを確認してください。一部のベンダーはデフォルトでBBUを提供していません。BBUがない場合、コントローラはキャッシュを無効化し、RAIDは非常に遅くなり、通常のSATAドライブよりも遅くなります。通常、BBUの状態はRAID設定ツールで確認できます。

4. 書き込みキャッシュをライトバックに設定する

BBUを搭載したRAIDコントローラ(およびUPSを備えたサーバー)を使用している場合は、そのキャッシュがライトバック(ライトスルーではなく)に設定されていることを確認してください。「ライトバック」はコントローラの書き込みキャッシュを有効にします。

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設定ファイルはこちらをご利用ください: /ja/optimized-firebird-configuration/

10. ソート操作用のメモリサイズを増やす

firebird.confのTempCacheLimitパラメータの値を増やしてください。これはソート用の一時領域のキャッシュサイズを指定します。デフォルト値は低すぎるため(Classicで8Mb、SuperServerで64Mb)、Classicでは少なくとも64Mb、SuperServerおよびSuperClassicでは1Gbを使用してください。ここでも、#9の最適化済み設定ファイルを使用してください。

11. Forced WritesをOffに設定する(注意して!)

集中的な挿入または更新アクティビティがあり(HQbird MonLoggerで確認できます。詳細はHQbird User Guideの60ページを参照)、ハードウェア障害から保護するためにUPSとレプリケーションが導入されている場合は、Forced Writes設定をOFFにすることを検討してください。書き込み操作の速度が最大3倍向上する可能性があります。

12. Classic/SuperClassicのハッシュスロット数を増やす

ClassicおよびSuperClassicのLockHashSlotsパラメータの値を、デフォルトの1009から大きな素数(例えば30011)に増やしてください。内部ロック機構のキューが減少します。

13. Super Server 2.5でCPUアフィニティを使用する

SuperServer 2.5を使用している場合は、CPUAffinityパラメータを使用中のデータベース数と同じ値に設定してください。2.5のSuperServerは、特定のデータベースへのリクエストを処理するために異なるCPUコアを使用できます。

14. 一時領域に高速ドライブを使用する

firebird.confのTempDirectoryパラメータの最初の部分を高速ディスク(SSDまたはRAMドライブ)に設定してください。大規模なソートの時間が短縮されます。例えば、データベースの復元時などです。

15. データベースバックアップを別のドライブに保存する

データベースバックアップを専用の物理ドライブ(RAID)に保存してください。バックアップ中の読み取りIOと書き込みIOが分離され、バックアップ速度が向上し、メインドライブの負荷が軽減されます。これは、ユーザーがデータベースを操作している間にバックアップを取得する場合に特に重要です。Firebirdのハードウェア構成の詳細については、「Firebird Hardware Guide」を参照してください。

16. 一括挿入時にインデックスを無効化する

多数のレコード(テーブルの25%以上)を挿入または更新する場合は、レコードを挿入するテーブルのインデックスを無効化し、挿入または更新後に再有効化してください。インデックスの再構築操作は、インデックスの多数の更新よりも高速になる可能性があります。

17. 高速挿入にグローバル一時テーブルを使用する

挿入と更新を高速化するには、大規模なレコードセットの一括挿入にグローバル一時テーブル(GTT)を使用し、その後レコードを永続テーブルに転送してください。レコードを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. 長いレコードチェーンを避ける

1つのレコードが多数のレコードバージョンを持つ状況を避けてください。Firebirdは長いレコードチェーンではるかに遅く動作します。(一部のテーブルが持つレコードバージョンの数と、最長のレコードチェーンを確認するには、HQbird IBAnalystツールの「Tables」タブで「Max Version」でソートしてください。)同じレコードを複数回更新する代わりに、挿入と古いレコードの定期的な削除を組み合わせて使用してください。

23. PREPAREを正しく使用する

パラメータのみが変更されるSQLクエリを実行する場合は、プリペアドステートメントを使用してください。たとえば、そのようなクエリのループの前にprepareを実行します。prepareには多大な時間がかかる場合があり（特に大きなテーブルの場合）、クエリを一度だけprepareすることで全体のパフォーマンスが大幅に向上します。

24. 一括挿入/更新操作中に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を伴うクエリの速度を大幅に（3〜10倍）向上させることができます。

30. 派生テーブルを使用してORDER BY/GROUP BYを伴うSELECTを最適化する

ソートを伴うSQLクエリを最適化するもう一つの方法は、派生テーブルを使用して不要なソート操作を回避することです。

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

の代わりに、以下のように変更してください:

Code 
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値のランダムな分布により、Primary/Uniqueキーに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つのクエリよりも高速です。たとえば:

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

を以下に置き換えてください:

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

41. gbakには-seスイッチを使用する

-seスイッチを使用してgbakのバックアップおよび/またはリストア速度を最大20%向上させてください。たとえば:

Code 
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分に1回以下で実行することを推奨します。Firebirdのクエリ/トランザクション/アタッチメントの継続的な監視には、Trace APIをサポートするHQbird PerfMonツールを使用してください（詳細はHQbird User Guideの66ページを参照）。

44. 一括挿入/更新にはNO_AUTO_UNDOオプションを使用する

同じトランザクションのフレーム内で多数のDML（Update/Insert/Delete）コマンドを実行する場合、Firebirdは各コマンドのundoログをトランザクションのundoログとマージします。一括DML操作を高速化するには、各コマンドのundoログをトランザクションのundoログとマージしないように、「NO AUTO UNDO」オプションでトランザクションを開始してください。

45. 必要ない場合はFirebird 3でSRP認証を使用しない

本当に必要でない限り、SRPユーザー認証（Firebird 3.0以上）を使用しないでください。SRP認証での接続は通常の接続よりも確立が遅くなります。

概要の代わりに

パフォーマンス最適化には複数の要因を考慮する必要があり、非常に難しい場合があります。上記のすべてを試しても解決しない場合は、プロフェッショナルなデータベースパフォーマンス最適化サービスのご利用をご検討ください。

お問い合わせ

ご質問はありますか？お気軽にメールでお問い合わせください！