45 Möglichkeiten zur Beschleunigung einer Firebird-Datenbank

Hier finden Sie eine Liste von Performance-Tipps für Firebird-Datenbanken in verschiedenen Bereichen – von Hardware/OS und Firebird-Konfigurationstuning bis hin zu SQL-Optimierungsempfehlungen. Diese Liste ist keine vollständige Referenz zur Optimierung von Firebird und setzt voraus, dass Sie die Grundlagen der Funktionsweise von Firebird verstehen, wie z. B. Ausführungspläne, Transaktionsverwaltung und Statistiken zur Abfrageperformance.

Bitte wenden Sie diese Tipps mit Vorsicht an und überprüfen Sie ihre Wirkung, bevor Sie sie in der Produktion einsetzen.

Unser Unternehmen (IBSurgeon) bietet den umfassenden Service zur Optimierung der Datenbankperformance an.

1. Datenbank auf SSD legen

Legen Sie Ihre Datenbank auf einer SSD ab. SSD-Laufwerke bieten ein deutlich besseres Random-IO als herkömmliche Laufwerke. Random-IO ist entscheidend für das Lesen und Schreiben von Daten, die über eine große Datenbankdatei verteilt sind – die Mehrheit der Datenbankoperationen erfordert intensives paralleles Random-IO.

2. RAID 10 verwenden

Wenn Sie RAID1 oder RAID5 verwenden, ziehen Sie RAID10 in Betracht – es ist 15–25 % schneller.

3. BBU prüfen

Wenn Sie einen RAID-Controller verwenden, prüfen Sie, ob eine Backup Battery Unit (BBU) installiert und betriebsbereit ist – einige Hersteller liefern standardmäßig keine BBU mit. Ohne BBU deaktiviert der Controller den Cache, und RAID arbeitet sehr langsam, sogar langsamer als übliche SATA-Laufwerke. Üblicherweise können Sie den BBU-Status im RAID-Konfigurationstool prüfen.

4. Write-Cache auf Write-Back setzen

Wenn Sie einen RAID-Controller mit installierter BBU verwenden (und einen Server mit USV), prüfen Sie, ob dessen Cache auf Write-Back (nicht Write-Through) gesetzt ist. „Write-Back“ aktiviert den Schreib-Cache des Controllers.

5. Read-Cache aktivieren

Wenn Sie einen RAID-Controller verwenden, prüfen Sie, ob der Read-Cache aktiviert ist.

6. Datenträger-Subsystem prüfen

Prüfen Sie Ihre Laufwerke auf fehlerhafte Blöcke und andere Hardwareprobleme (einschließlich Überhitzung). Hardwareprobleme können die IO-Performance erheblich beeinträchtigen und zu Datenbankbeschädigungen führen.

7. SuperClassic oder Classic in Firebird 2.5 verwenden

Wenn Sie Firebird 2.5 SuperServer mit vielen Verbindungen verwenden, versuchen Sie SuperClassic oder Classic – sie können besser skalieren, indem sie alle CPU-Kerne nutzen.

8. SuperServer 3.0 in Firebird 3 verwenden

Wenn Sie Classic oder SuperClassic in 2.5 verwenden, ziehen Sie eine Migration zu Firebird 3.0 SuperServer in Betracht – dieser kann nun mehrere Kerne nutzen und dies mit den Vorteilen des gemeinsamen Caches kombinieren.

9. Page-Buffers-Cache vergrößern

Erhöhen Sie die Größe des Page-Buffers-Caches (Parameter DefaultDBCachePages) gegenüber den Standardwerten. Für 2.5 SuperServer empfehlen wir 10000 Seiten, für 3.0 SuperServer 50000 Seiten, für Classic und SuperClassic 256 bis 2048 Seiten. Setzen Sie den Wert des Page-Buffers-Caches jedoch nicht zu hoch – die Cache-Synchronisierung hat ihre Kosten, und die Idee, die gesamte Datenbank durch Tuning dieses Werts in den RAM zu legen, wird nicht funktionieren. Verwenden Sie hier vorkonfigurierte Firebird-Konfigurationsdateien: /de/optimized-firebird-configuration/

10. Speichergröße für Sortieroperationen erhöhen

Erhöhen Sie den Wert des Parameters TempCacheLimit in firebird.conf – er legt die Größe des Caches für den temporären Speicherplatz zum Sortieren fest. Die Standardwerte sind zu niedrig (8 MB für Classic und 64 MB für SuperServer); verwenden Sie mindestens 64 MB für Classic und 1 GB für SuperServer und SuperClassic. Verwenden Sie auch hier die optimierten Konfigurationsdateien aus #9.

11. Forced Writes auf Off setzen (mit Vorsicht!)

Wenn Sie intensive Insert- oder Update-Aktivität haben (Sie können dies mit HQbird MonLogger prüfen, Details siehe Seite 60 des HQbird User Guide), und wenn Sie eine USV und Replikation zum Schutz vor Hardwareausfällen installiert haben, ziehen Sie in Betracht, die Forced Writes-Einstellung auf OFF zu setzen – dies kann die Geschwindigkeit von Schreiboperationen um bis zu das Dreifache erhöhen.

12. Anzahl der Hash-Slots für Classic/SuperClassic erhöhen

Erhöhen Sie den Wert des Parameters LockHashSlots für Classic und SuperClassic vom Standardwert 1009 auf eine große Primzahl (zum Beispiel 30011) – dies verringert Warteschlangen im internen Sperrmechanismus.

13. CPU Affinity für Super Server 2.5 verwenden

Wenn Sie SuperServer 2.5 verwenden, setzen Sie den Parameter CPUAffinity auf einen Wert, der der Anzahl der verwendeten Datenbanken entspricht: SuperServer in 2.5 kann verschiedene CPU-Kerne nutzen, um Anfragen für bestimmte Datenbanken zu verarbeiten.

14. Schnelles Laufwerk für temporären Speicherplatz verwenden

Setzen Sie den ersten Teil des Parameters TempDirectory in firebird.conf auf das schnelle Laufwerk – SSD oder RAM-Laufwerk. Dies verringert die Zeit für große Sortierungen – zum Beispiel beim Wiederherstellen der Datenbank.

15. Datenbank-Backups auf einem anderen Laufwerk speichern

Speichern Sie Datenbank-Backups auf einem dedizierten physischen Laufwerk (RAID). Dies trennt Lese- und Schreib-IO während des Backups und erhöht die Backup-Geschwindigkeit und verringert die Last für das Hauptlaufwerk. Dies ist besonders wichtig, wenn Backups erstellt werden, während Benutzer mit der Datenbank arbeiten. Weitere Details zur Hardwarekonfiguration für Firebird finden Sie im „Firebird Hardware Guide“.

16. Indizes für Massen-Inserts deaktivieren

Wenn Sie viele Datensätze einfügen oder aktualisieren (mehr als 25 % der Tabelle), deaktivieren Sie die Indizes für die Tabelle, in die Datensätze eingefügt werden, und aktivieren Sie sie nach dem Insert oder Update wieder. Die Index-Neuaufbauoperation kann schneller sein als viele Aktualisierungen des Index.

17. Global Temporary Tables für schnelle Inserts verwenden

Um Inserts und Updates zu beschleunigen, verwenden Sie Global Temporary Tables für Massen-Inserts großer Recordsets und übertragen Sie die Datensätze anschließend in die permanente Tabelle. Es kann sehr effektiv sein, Datensätze in GTT einzufügen, sie vorzuverarbeiten und dann in die persistente Tabelle zu verschieben.

18. Unnötige Indizes vermeiden

Verwenden Sie weniger Indizes für Tabellen mit intensiven Inserts und Updates. Jeder Index verursacht erheblichen Overhead für Insert-, Update-, Delete- und Garbage-Collection-Operationen – es können 3–4 zusätzliche Seitenlese- und -schreibvorgänge auftreten, wenn ein einzelner Datensatz eingefügt/aktualisiert/gelöscht/bereinigt wird, und zwar für jeden Index.

19. UDFs durch Aufrufe eingebetteter Funktionen ersetzen

Ersetzen Sie UDF-Aufrufe durch Aufrufe eingebetteter Funktionen. In den neueren Versionen von Firebird wurden viele eingebettete Funktionen hinzugefügt, die Funktionalität bieten, die zuvor nur in UDF-Bibliotheken verfügbar war. Ersetzen Sie solche Funktionen, wo möglich, da eingebettete Funktionen bis zu dreimal schneller arbeiten als UDFs.

20. Read-only-Transaktionen für Leseoperationen verwenden

Verwenden Sie Read-only-Transaktionen für Operationen, die Datensätze nicht ändern (d. h. SELECTs), mit Isolationsmodus = read committed. Solche Transaktionen halten keine Datensatzversionen von der Garbage Collection zurück und können unbegrenzt laufen: Sie beeinträchtigen die Datenbankperformance nicht.

21. Kurze Schreibtransaktionen verwenden und ALLE langlaufenden beseitigen

Verwenden Sie kurze schreibbare Transaktionen (für INSERT/UPDATE/DELETE-Operationen).

Je kürzer eine schreibbare Transaktion ist, desto besser. Kurze Transaktionen halten proportional weniger Datensatzversionen von der Garbage Collection zurück als langlaufende. Leider kann selbst eine einzelne langlaufende Transaktion (zum Beispiel von einem offen gelassenen Entwicklungstool) den guten Effekt aller anderen kurzen schreibbaren Transaktionen zunichtemachen. Deshalb müssen Sie langlaufende Transaktionen überwachen und die entsprechenden Stellen im Quellcode korrigieren. Verwenden Sie das HQbird DataGuard-Tool, um Benachrichtigungen über die älteste aktive Transaktion in der Firebird-Datenbank zu erhalten (welche Anwendungen sie gestartet haben, welche IP-Adresse, den Zeitstempel ihres Starts), und das HQbird MonLogger-Tool, um die vollständige Liste der langlaufenden aktiven Transaktionen und ihre IO-Statistiken zu sehen. Wenn Sie außerdem Datenbankzugriffskomponenten/-bibliotheken verwenden, die Recordsets cachen können, nutzen Sie Cached Updates.

22. Lange Datensatzketten vermeiden

Vermeiden Sie Situationen, in denen ein Datensatz viele Datensatzversionen hat – Firebird arbeitet mit langen Datensatzketten deutlich langsamer. (Um zu sehen, wie viele Datensatzversionen einige Tabellen haben und welche die längste Datensatzkette ist, können Sie das HQbird IBAnalyst-Tool verwenden, Tabellen-Tab, sortiert nach „Max Version“.) Verwenden Sie eine Kombination aus Inserts und geplantem Löschen alter Datensätze statt mehrfacher Updates desselben Datensatzes.

23. PREPARE korrekt verwenden

Verwenden Sie Prepared Statements, um SQL-Abfragen auszuführen, bei denen nur Parameter geändert werden – führen Sie beispielsweise das Prepare vor der Schleife solcher Abfragen aus. Prepare kann erhebliche Zeit in Anspruch nehmen (insbesondere bei großen Tabellen), und die Abfrage nur einmal vorzubereiten erhöht die Gesamtperformance erheblich.

24. Während Massen-Insert/Update-Operationen nicht zu oft COMMIT ausführen

Bei Massen-INSERT/UPDATE/DELETE-Operationen committen Sie die Transaktion nicht nach jeder Änderung (dies kann passieren, wenn Sie die Auto-Commit-Option in Ihrem Datenbanktreiber verwenden) – committen Sie Transaktionen mindestens nach 1000 Operationen oder mehr. Jeder Transaktions-Commit führt mehrere Lese-/Schreib-IO-Operationen gegen die Datenbank aus, deshalb verringern häufige Commits die Datenbankperformance.

25. Indizes „ausschalten“, wenn Sie IN mit vielen Konstanten verwenden

Wenn Sie das Konstrukt WHERE fieldX IN (Constant1, Constant2,… ConstantN) verwenden und es einen Index auf fieldX gibt, verwendet Firebird einen Index so oft, wie Konstanten in der IN-Liste stehen. Deaktivieren Sie die Indexsuche, indem Sie fieldX in einen Ausdruck +0 umwandeln: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), oder verwenden Sie für Strings fieldX||''

26. IN durch JOIN ersetzen

Vermeiden Sie Abfragen mit verschachteltem WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )), dies kann den Firebird-Optimizer verwirren. Wandeln Sie verschachtelte INs in Joins um.

27. LEFT JOIN korrekt verwenden

Wenn Sie LEFT OUTER Joins verwenden, ordnen Sie die Tabellen im Join explizit von der kleinsten zur größten an.

28. Fetch von SELECT-Abfragen begrenzen

Versuchen Sie immer, die große Ausgabe von SELECT-Abfragen mit FIRST… SKIP- oder ROWS-Klauseln zu begrenzen. Wenn die Abfrage nicht speziell als Bericht konzipiert ist (der alle Datensätze zum Drucken/Exportieren erfordert), reicht es normalerweise aus, die Top 10–100 Datensätze anzuzeigen. Holen Sie nur die notwendigen Datensätze.

29. Weniger Spalten in SELECT mit ORDER BY/GROUP BY angeben

Reduzieren Sie die Anzahl der Spalten und ihre Gesamtbreite in Abfragen mit ORDER BY/GROUP BY sowohl im SELECT-Teil (d. h. den anzuzeigenden Feldern) als auch in der ORDER BY-Klausel. Firebird führt Spalten aus SELECT- und ORDER BY/GROUP BY-Klauseln zusammen und sortiert sie im Speicher (oder, wenn der Speicher nicht ausreicht, auf der Festplatte). Wenn also ein langes VARCHAR im SELECT steht, kann die Größe der Sortierdateien wirklich groß sein (viele Gigabyte). Die Reduzierung der Anzahl der Felder auf nur diejenigen, die sortiert werden müssen, und ein spätes Join mit den anzuzeigenden großen Feldern kann die Geschwindigkeit einer Abfrage mit ORDER BY/GROUP BY erheblich (x3–x10) erhöhen.

30. Abgeleitete Tabellen verwenden, um SELECT mit ORDER BY/GROUP BY zu optimieren

Eine weitere Möglichkeit, eine SQL-Abfrage mit Sortierung zu optimieren, ist die Verwendung abgeleiteter Tabellen, um unnötige Sortieroperationen zu vermeiden. Statt

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

verwenden Sie die folgende Modifikation:

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. Kurze Strings in VARCHAR, lange in BLOBs speichern

Verwenden Sie VARCHARs zum Speichern kurzer Zeichendaten und BLOBs zum Speichern langer Texte. Varchars sind für kleine Datenmengen schneller, weil sie im Datensatz gespeichert werden und der gesamte Datensatz während desselben IO-Zyklus gelesen wird; wenn die Datensatzgröße weniger als 2/3 der Datenbankseitengröße beträgt, wird der gesamte Datensatz auf derselben Datenbankseite gespeichert. BLOBs werden außerhalb des Datensatzes gespeichert und erfordern eine zusätzliche IO-Runde zum Lesen, und sie zeigen ihre Vorteile beim Lesen und Schreiben langer Strings.

32. BLOB-Spalten aus großen SELECTs ausschließen

Schließen Sie BLOB-Spalten aus großen SELECTs aus. Verwenden Sie eine Art Late Binding mit Sub-Selects, um Informationen aus BLOBs selektiv anzuzeigen (zum Beispiel den Inhalt des Dokuments anzuzeigen).

33. BIGINT für Primär- und Unique-Schlüssel verwenden

Verwenden Sie den Typ BIGINT für automatisch inkrementierte Primär- und Unique-Schlüssel sowie für Bezeichner aller Art. Operationen mit BIGINT sind am schnellsten, und BIGINT hat genügend Kapazität, um nahezu alle Datenbereiche zu speichern.

34. Verwenden Sie keine VARCHARs für Schlüssel

Verwenden Sie VARCHAR nicht für Identifikatoren, es sei denn, es ist wirklich notwendig – Operationen damit sind weit weniger effektiv als mit Integer-Spalten. Vermeiden Sie insbesondere GUIDs als Identifikatoren – aufgrund der zufälligen Verteilung der GUID-Werte können INSERT/UPDATE-Operationen mit GUIDs in Primary/Unique Keys 20-mal langsamer sein als mit Integern.

35. Berechnen Sie die Indexstatistiken neu

Berechnen Sie die Indexstatistiken regelmäßig neu. Aktualisieren Sie die Indexstatistiken für Tabellen mit häufigen oder massiven Änderungen mit dem Befehl SET STATISTICS; dies ermöglicht es dem Firebird-Optimizer, bessere SQL-Pläne zu wählen. HQbird Firebird DataGuard kann eine solche Neuberechnung der Indexstatistiken automatisch nach dem gewünschten Zeitplan durchführen (üblicherweise einmal pro Woche).

36. Verwenden Sie einen Connection Pool

Wenn die Datenbankverbindungen zur Firebird-Datenbank kurz sind (typisch für Websites), verwenden Sie einen Connection Pool – verwenden Sie beispielsweise in PHP die Funktion ibase_pconnect statt ibase_connect.

37. Verwenden Sie die Option LINGER in Firebird 3.0

Wenn die Datenbankverbindungen kurz sind und Sie Firebird 3+ verwenden, nutzen Sie die Option LINGER, um den Cache für die angegebene Zeitdauer aktiv zu halten; dadurch bleiben häufig verwendete Seiten im Cache, auch wenn keine weiteren Verbindungen bestehen. Beispielsweise hält ALTER DATABASE SET LINGER TO 60 den Cache 60 Sekunden nach dem Ende der letzten Verbindung.

38. Verwenden Sie HASH JOINs

In Firebird 3.0 kann HASH JOIN beim Verbinden großer und kleiner Tabellen viel schneller sein als ein normaler Join, der «nested loop» mit Index verwendet. Damit der Firebird-Optimizer einen HASH Join verwendet, verwenden Sie +0 in der Join-Bedingung: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Prüfen Sie das Ergebnis der Optimierung, bevor Sie es in die Produktion übernehmen!

39. Kennzeichnen Sie geeignete PSQL-Funktionen als DETERMINISTIC

Kennzeichnen Sie Ihre PSQL-Funktionen (in Firebird 3+), die keine Parameter haben und konstante Werte zurückgeben, mit dem Schlüsselwort DETERMINISTIC. Die deterministischen Funktionen werden im Rahmen der aktuellen Abfrage berechnet und zwischengespeichert.

40. Verwenden Sie analytische (Window-)Funktionen in Firebird 3.0

Wenn Sie ein SELECT mit gleichzeitiger Ausgabe einer Spalte und einer aggregierten Funktion dafür ausführen, verwenden Sie Window-(analytische) Funktionen – das ist schneller als die Unterabfrage oder 2 Abfragen. Zum Beispiel:

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

ersetzen durch

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

41. Verwenden Sie den Schalter -se für gbak

Verwenden Sie den Schalter -se, um die gbak-Sicherungs- und/oder Wiederherstellungsgeschwindigkeit um bis zu 20 % zu erhöhen, zum Beispiel

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

42. WHERE CURRENT OF

Die schnellste Methode zur Verarbeitung von Datensätzen, die vom Cursor in PSQL abgerufen werden, ist die Klausel ‚where current of <>‘. Sie ist schneller als ‚where rb$db_key = :v_db_key‘ und viel schneller als die Suche mit einem Primary- oder Unique-Key.

43. Vermeiden Sie häufige Abfragen an Überwachungstabellen

Führen Sie Abfragen an Firebird-Überwachungstabellen (MON$) nicht zu oft aus – solche Abfragen verbrauchen erhebliche Ressourcen und können die Leistung der Hauptgeschäftslogik stark beeinträchtigen. Wir empfehlen, MON$-Abfragen nicht häufiger als einmal pro Minute auszuführen. Für die kontinuierliche Überwachung von Firebird-Abfragen/Transaktionen/Verbindungen verwenden Sie das HQbird PerfMon-Tool, das die Trace API unterstützt (siehe Seite 66 des HQbird User Guide für Details).

44. Verwenden Sie die Option NO_AUTO_UNDO für Massen-Inserts/Updates

Wenn Sie viele DML-Befehle (Update/Insert/Delete) im Rahmen derselben Transaktion ausführen, führt Firebird das Undo-Log jedes Befehls mit dem Undo-Log der Transaktion zusammen. Um Massen-DML-Operationen zu beschleunigen, starten Sie die Transaktion mit der Option «NO AUTO UNDO», damit die Undo-Logs der einzelnen Befehle nicht mit dem Undo-Log der Transaktion zusammengeführt werden.

45. Verwenden Sie keine SRP-Authentifizierung in Firebird 3, wenn Sie sie nicht benötigen

Verwenden Sie die SRP-Benutzerauthentifizierung (Firebird 3.0+) nicht, wenn Sie sie nicht wirklich benötigen – eine Verbindung mit SRP-Authentifizierung wird langsamer aufgebaut als eine reguläre Verbindung.

Statt einer Zusammenfassung

Die Leistungsoptimierung erfordert die Berücksichtigung mehrerer Faktoren und kann wirklich knifflig sein. Wenn Sie alle oben genannten Punkte ausprobiert haben, ziehen Sie in Betracht, einen professionellen database performance optimization Service zu beauftragen.

Kontaktieren Sie uns

Haben Sie Fragen? Zögern Sie nicht, uns per E-Mail zu kontaktieren!