Firebirdを高速化する23のさらなる方法

Alexey Kovyazin, IBSurgeon, [email protected], 2019年1月8日

翻訳: ポルトガル語

なぜ「さらに23の方法」なのか？

2016年5月に公開された記事「Firebirdを高速化する45の方法」を覚えている方もいるでしょう。今回は、主にFirebirdデータベースおよび多数の接続（1000以上）を持つサーバーの最適化と保守の経験に基づいた、次の一連のヒントとテクニックを公開します。

1. Windows Server 2016および2019で電源オプションを「高パフォーマンス」に設定する

Windows Serverはデフォルトで電源プランが「バランス」に設定されており、データベースサーバーには適していません。「高パフォーマンス」に設定すると、CPUを集中的に使用する操作で約+20%のパフォーマンス向上が得られます。再起動やリブートなしで、オンラインで設定できます。以下の図は、「高パフォーマンス」電源プランの優位性を示すCPUグラフです:

Windowsの電源プランに関するテストの詳細はこちらで確認できます。

2. WindowsのClassicで「デスクトップとの対話を許可する」を有効にする

WindowsでFirebirdをClassicアーキテクチャで使用している場合は、「サービスがデスクトップと対話できるようにする」チェックボックスを有効にしてください。この設定がないと、「デスクトップヒープ」リソースがWindowsによって制限され、Firebirdは250〜300以上の接続を開くことができません（データベースのメタデータと関連するメモリ消費に依存します）。Out Of Memoryエラーが発生します。

3. 注意: ドメインコントローラー

ドメインコントローラーの役割を持つWindowsでは、Active Directoryデータベースが格納されたディスクの書き込みキャッシュが無効になるという問題があります。

これはFirebirdにさまざまな形で影響を及ぼし（もちろん他のアプリケーションにも同様です）、Active Directoryの役割を持たないサーバーと比較して著しくパフォーマンスが低下します。

この問題は、Windows Small Business Server 2011のような人気のあるWindowsバージョンや、DCを持つ他のバージョンにも影響することに注意してください。

4. Linuxで「max open files」の制限を増やす

Linuxをデータベースサーバーとして使用している場合は、Firebirdの制限のチューニングを忘れないでください。以下のコマンドでFirebirdプロセス（SuperServerまたはSuperClassic）の制限を確認してください:

cat /proc//limits

そして、開いているファイルの最大数の行に注目してください。

Firebirdは接続ごとに最大4つのハンドルを使用でき、次のような表示がある場合:

Max open files 4096 4096 files

これは、Firebirdプロセスが処理する接続の総数が約1000に制限されることを意味します。

注意してください - サーバー上に4つのデータベースがある場合、各データベースへの接続がカウントされます。

より大きな値を設定してください - 65535を推奨します。

Firebirdプロセスの再起動後に、適用されたかどうかを再度確認することを忘れないでください。

Classicアーキテクチャの場合は、「firebird」ユーザーの制限を確認し、増やす必要があります。

5. モダンなLinuxを使用する

はい、このアドバイスが些細なものであることは理解していますが、CentOS 6から7、Ubuntu 12から16への移行後（同じハードウェアで！）に良好なパフォーマンス改善を何度も目にしてきました。そのため、250〜300以上の接続を持つデータベースサーバーには必須の推奨事項となっています。モダンなLinuxは、さらなる最適化ステップの前提条件です。

推奨されるLinuxのバージョン: CentOS 7.xおよびUbuntu 16、18。

6. Windowsではファイルキャッシュ用にRAMの40%を確保する

OSのメモリマネージャーはメモリ割り当てに関して影響を与え、デフォルトではWindowsはファイルキャッシュ用にRAMの40%を必要とします。

残念ながら、貧弱なツールであるWindowsタスクマネージャーは、ファイルキャッシュに使用されるメモリを「空き」として表示するため、一部の管理者はFirebirdにこの空きメモリをすべて消費させようとして、firebird.confのDefaultDBCachePageパラメータを非常に高い値に設定し、それが通常スワップを引き起こします。

Windowsでの実際のメモリ使用状況を確認するには、常にRAMMapツールを使用してください。

Windows Server（Firebirdサーバー専用）の経験則は次のとおりです: Firebirdのメモリ（ワーキングセット）は、総RAMの40%未満であるべきです。すべてのプロセスのワーキングセットの合計サイズが50%を超えると、Windowsによってスワップが開始される可能性があります。

注意: ここでの「確保」とは、「Firebirdでページバッファを設定しすぎない」ということだけでなく、他のソフトウェアのメモリ使用量を制限することも重要です。たとえば、Firebirdと同じサーバー上にMS ExchangeやMSSQLがある場合は、それらのメモリ消費を必ず制限してください。

詳細に興味がある場合は、Firebirdのメモリ管理に特化したウェビナーを録画しています:

• パート1. https://www.youtube.com/watch?v=ZBmLgbYt4aM
• パート2, https://www.youtube.com/watch?v=-1.DF2FnU-A

7. Linuxではファイルキャッシュ用にRAMの30%を確保する

LinuxはWindowsとは異なる方法でファイルキャッシュを扱い、一般に、ファイルキャッシュに使用されるRAMの量はWindowsよりも大幅に少なくても、Firebirdのパフォーマンスに目立った低下はありません。ただし、多数の接続を持つシステムの高いパフォーマンスを保証するには、特にClassicおよびSuperClassicでは、ファイルキャッシュ用にRAMの30%を確保するのが良い考えです。

8. Linuxでirqbalanceを使用する

irqbalanceは、コア数の多いサーバーでFirebirdのパフォーマンスとCPU負荷分散を改善することがよくあります。

9. 仮想マシンの場合 - メモリオーバーコミットに注意

仮想マシンは、ホストマシンに物理的に存在する以上のメモリを持つように構成できます - これはメモリオーバーコミットとして知られる機能です（名前は仮想化システムによって異なる場合があります）。これは、メモリ消費のピーク時（データベースサーバーのVMまたは隣接するVMで）にスワップが開始され、大幅な遅延を引き起こす可能性があることを意味します。データベースサーバー用の高性能VMでは、すべてのメモリを静的にする必要があります。

10. 仮想マシンの場合 - VMの制限を確認する

VMはデフォルトのCPUおよびIO制限で作成されることが多く、50 IOPSや10% CPUなど非常に低い場合があります。サーバーVMの設定を確認し、制限をすべて削除してください - 高性能データベースサーバーは、可能な限りのCPU、帯域幅、IOを持つべきです。

11. Firebirdの一時ファイルをクリーンアップする

Firebirdは、ソート、BLOB処理、トレースなど、さまざまな操作のために多くの一時ファイルを作成します。これらのファイルは次の場所に保存されます: WindowsではC:\ProgramData\firebird、Linuxでは**/** tmp / firebird

通常、これらのファイルは自動的にクリーンアップされるはずですが、時にはそうならないことがあります（たとえば、サーバーの再起動時など）。

これらのフォルダを定期的に確認し、古いファイルをクリーンアップしてください - 古いfb_NNNファイルが数GBある可能性があり、それらをクリーンアップすることでシステムドライブの空き容量が確保されます。

12. 大きなFirebirdキャッシュを使用する場合はファイルキャッシュの有効化を忘れない

ご存知のとおり、Firebirdキャッシュ（「ページバッファ」とも呼ばれます）は、firebird.conf/databases.confのDefaultDBCachePagesパラメータ、またはデータベースのヘッダーで直接指定されます。

Firebird 3 SuperServerでは、このキャッシュのサイズを非常に大きく設定できますが、別のパラメータであるFileSystemCacheThresholdについて覚えておくことが重要です。

FileSystemCacheThresholdがDefaultDBCachePagesまたはページバッファよりも小さい場合、オペレーティングシステムのファイルキャッシュは使用されず、パフォーマンスの問題を引き起こす可能性があります。

99%のケースでは、ファイルキャッシュを有効にした方が良いです。

これを確実にするために、常に次のルールに従ってパラメータを設定してください:

• DefaultDBCachePages = X
• FileSystemCacheThreshold = X+ N, N>1

ファイルキャッシュを無効にすることでパフォーマンスが向上する稀なケースがあります - そのような例があれば、私に連絡してください - [email protected]!

13. セキュリティデータベースを高速化する

Firebirdデータベースへのすべての接続は、セキュリティデータベース（Firebird 3の場合はsecurity3.fdb）への接続を確立し、いくつかの読み取りと書き込み（トランザクションページ、ヘッダーページ）を実行します。接続が頻繁な場合、セキュリティデータベースのパフォーマンスが問題になる可能性があります。

最低限、次のことができます：

• securityN.fdb のページバッファを増やす（経験的な最適値は 256 バッファ）
• security3.fdb を高速ドライブに移動する（これは Firebird 3 の標準機能です。2.5 では再インストールが必要になります）

その後、セキュリティデータベースに対して Forced Writes を OFF に設定できます。この場合、破損の可能性がわずかであっても問題にはなりません。

最も極端な方法は、セキュリティデータベースを読み取り専用にすることです。これにより、そこへの書き込みがすべて排除されます。

セキュリティデータベースのユーザーを頻繁に変更しないのであれば、これが最良の解決策です。

14. Firebird 3 で SuperClassic を試す

Firebird 3 では、SuperServer アーキテクチャが究極のパフォーマンスソリューションとして大々的に宣伝されましたが、SuperClassic の方が優れたパフォーマンスを示す負荷の種類もあります（ただし Classic は除きます。Classic は常に SuperServer/SuperClassic より遅く動作します）。

この実験を安全に行うにはどうすればよいでしょうか？以下の手順に従ってください：

SuperClassic を試すには

1. firebird.conf で設定します
2. ServerMode=SuperClassic
3. DefaultDbCachePages=1024
4. gfix -buff 0
5. Firebird を再起動します

SuperServer に戻すには

1. firebird.conf で設定します
2. ServerMode=SuperServer
3. DefaultDbCachePages=N # N*page size*databases_count < 25% RAM
4. FileSystemCacheThreshold = N+1
5. gfix -buff 0
6. Firebird を再起動します

実験の結果について私（[email protected]）までお知らせください。結果を見るのを楽しみにしています。

15. 大きなデータベース？ページサイズを増やしましょう

デフォルトでは、Firebird データベースのページサイズは次のとおりです：

• 2.5 - 4096 バイト
• 3.0 - 8192 バイト

ただし、最大ページサイズは 16K です（4.0 では 32K）。

100Gb を超えるデータベースでは、95% のケースで、次の目的のために利用可能な最大のページサイズを使用する方が適しています：

• インデックスの深さを減らす。インデックスの深さは 3 以下にすることを推奨します。深さ 4 や 5 のインデックスははるかに遅くなります
• RAM の利用率を高める。Firebird のキャッシュはページ単位で指定され、8K ページサイズで 1000 ページなら実メモリは 8Mb、16K なら 16Mb になります。
• システムページの数を減らす。これにより、大きなテーブルのレコードへのアクセスが高速化され（ポインタ→ポインタ→データページというジャンプが減る）、大きな SQL クエリの準備にも役立ちます。データベースのページサイズを増やすには、データベースを gbak ツールでバックアップし、パラメータ -page を付けてリストアする必要があります（ gbak -c -page 16384）。

注意：小さな blob を多数含むデータベースの場合、ページサイズを増やすと断片化が減少することもあれば増加することもあり、パフォーマンスが向上するか低下するかを予測するのは困難です。

16. no_reserve フラグを使用しない

no_reserve フラグは、UPDATE や DELETE の後に発生する可能性のあるレコードバージョンのために、Firebird がデータページ上に空き領域（30%）を確保しないようにします。このフラグにより、データをよりコンパクトに格納できます（データベースサイズも小さくなります）が、UPDATE/DELETE の場合はすべての変更が新しいデータページに書き込まれます。その結果、no_reserve フラグが設定されたデータベースでは UPDATE/DELETE 操作が遅くなります。

したがって、データベースが読み取り専用でない場合は、no_reserve フラグを削除することをお勧めします。

設定されているかどうかを確認する方法は、次の出力の Attributes 行を参照してください

gstat -h database

無効にする方法：

gfix -use reserve database

このコマンドの後、新しいデータページは予約領域付きで作成されます。

ただし、完全な効果を得るには、データベースを gbak でバックアップしてからリストアする必要があります。この場合、すべてのデータページが予約領域付きになります。

注意：no_reserve フラグを削除してバックアップ/リストアすると、データベースサイズは増加します。

17. Firebird ロックテーブルの初期サイズを大きく設定する

ロックテーブルは Firebird のメカニズムで、内部エンジンオブジェクトへのアクセスを同期するために使用されます。

Firebird のロックテーブルは自動的に拡張できますが、その拡張は低速な操作であり、マイクロフリーズを引き起こす可能性があります。ロックテーブルは初期サイズ（firebird.conf で設定）から拡張することしかできません。

ロックテーブルの拡張が何度も繰り返されるのを防ぐには、稼働期間（1 日、1 週間など）の終わりにロックテーブルのサイズを監視し、それを firebird.conf の初期サイズとして設定するのが良い方法です。

LockMemSize=99999999

参考までに：約 1000 ユーザーの高負荷システムでは、LockMemSize は通常 200Mb 未満です。

18. fb_lock_print を使用してデータベースへの接続数を数える

データベース接続数の取得は、データベース開発者にとって頻繁なタスクです。たとえば、ライセンス目的で必要になることがあります。

開発者はこの値を取得するために SELECT count(*) FROM MON$ATTACHMENTS というクエリをよく使用しますが、これは最適な方法ではありません。MON$ テーブルへの頻繁なクエリはデータベースに負担をかける可能性があるため、代わりに次の方法を使用する方がよいでしょう：

次を実行します

fb_lock_print -d database_name | alias

そして Owners の値を確認します。これにより、データベースへの現在の接続数が表示されます。

19. 不要な LEFT JOIN を避ける

次のような構文のクエリをよく見かけます：

T1 LEFT JOIN T2 ON (…) WHERE T2.Field_condition

本質的に、T2 に対する条件は LEFT JOIN T2 の出力から NULL を除外するため、LEFT JOIN を INNER JOIN に変更できます。これはクエリの結果に影響しません。

INNER JOIN は Firebird オプティマイザに多くの自由度を与え、Firebird の現代のバージョンでは LEFT よりもはるかに最適化されます。

特に次の場合に意味があります：

• WHERE 句に T1 に対する条件がない
• T2 が小さなテーブルである

20. 不要なレコードのカウントを避ける

複雑なデータベースクエリやストアドプロシージャでよくあるもう一つの間違いは、レコードの存在を確認するためだけに select count() を使用することです。

次のクエリは condition1 に従ってすべてのレコードを読み取ります：

(select count(*)…. where condition11) >0

代わりに次の構文を使用する方がよいでしょう

Exists(select first 1 id where condition1)

condition1 が 1 件を超えるレコードを返す場合、提案された方法ははるかに高速です。すべてのレコードを読み取るのではなく、最初にフェッチしたレコードの後で停止するからです。

21. ストアドプロシージャでの不要なソートを避ける

ストアドプロシージャ内でのクエリ結果の並べ替えは、ビジネスロジックによって正当化されるべきです。

たとえば、以下のストアドプロシージャの例では、ORDER BY 句はビジネスロジックの観点からは無意味ですが、不要なソート操作を追加しています。

create or alter procedure NEW_PROCEDURE
returns (
    SUMX double precision)
as
declare variable _amount double precision;
begin
for select T1.amount from Table1 t1 where ....
    order by id
    into :_amount
    do
    begin
     sumx=sumx+_amount
    end;
  suspend;
end

同様の状況がないか PSQL コードを確認し、不要な ORDER BY（および distinct や UNION）を削除してください。

22. 必要のない限りクエリを準備済み状態のままにしない

各接続で 500〜1000 個の準備済みステートメントが見られることがよくあります（MON$ クエリで確認できます）。

その大多数は一度だけ実行され、その後は RAM 内に置かれたままになり、Firebird のワーキングセットを大きくし、MON$ クエリを遅くします。

推奨事項は、SQL クエリを準備済み状態のままにしておくのは、何度も起動することを意図している場合、または準備時間が長い場合（多数の結合と巨大なテーブルへのアクセスを伴う非常に大きなクエリではそうなり得ます）のみにすることです。

23. 大きなソートを伴うクエリは常にクローズする

ソート（ORDER BY、GROUP BY、UNION、distinct）を伴う SQL クエリがクローズされるまで、Firebird はソートされたレコードをメモリ内に保持します。ソートに割り当てられるメモリサイズは firebird.conf の TempCacheLimit パラメータで設定され、デフォルトでは 64Mb です。

TempCacheLimit を増やしたとしても、ソートされるレコード数が多い長時間実行されるクエリは、最終的に割り当てられた量をすべて消費し、その結果、ソートは一時ファイル（つまりディスク）に移行します。その結果、大幅な遅延を引き起こす可能性があります。

推奨事項は、そのようなクエリをすべてタイムリーにクローズすることです。

ご質問は？

ご質問があればお気軽にご連絡ください：[email protected]！