45 manieren om Firebird-database te versnellen

Hier vindt u de lijst met prestatietips voor Firebird-databases op verschillende gebieden - van hardware/OS en Firebird-configuratietuning tot SQL-optimalisatieaanbevelingen. Deze lijst is geen volledige referentie voor het optimaliseren van Firebird en gaat ervan uit dat u de basisprincipes van Firebird begrijpt, zoals uitvoeringsplannen, transactiebeheer en prestatiestatistieken van queries.

Pas deze tips alstublieft met voorzichtigheid toe en verifieer hun effect voordat u ze in productie neemt.

Ons bedrijf (IBSurgeon) biedt de uitgebreide databaseprestatie-optimalisatieservice aan.

1. Plaats de database op SSD

Plaats uw database op een SSD. SSD-schijven bieden veel betere willekeurige IO dan traditionele schijven. Willekeurige IO is cruciaal voor het lezen en schrijven van gegevens die verspreid zijn over een groot databasebestand - de meeste databasebewerkingen vereisen intensieve parallelle willekeurige IO.

2. Gebruik RAID 10

Als u RAID1 of RAID5 gebruikt, overweeg dan RAID10 - dit is 15-25% sneller.

3. Controleer BBU

Als u een RAID-controller gebruikt, controleer dan of deze een Backup Battery Unit (BBU) geïnstalleerd en operationeel heeft - sommige leveranciers leveren BBU niet standaard. Zonder BBU schakelt de controller de cache uit en werkt RAID zeer langzaam, zelfs langzamer dan gewone SATA-schijven. Meestal kunt u de BBU-status controleren in het RAID-configuratietool.

4. Stel write cache in op write-back

Als u een RAID-controller met geïnstalleerde BBU (en server met UPS) gebruikt, controleer dan dat de cache is ingesteld op write-back (niet write-through). “Write-back” schakelt de write cache van de controller in.

5. Schakel read cache in

Als u een RAID-controller gebruikt, controleer dan dat read cache is ingeschakeld.

6. Controleer het schijfsubsysteem

Controleer uw schijven op slechte blokken en andere hardwareproblemen (inclusief oververhitting). Hardwareproblemen kunnen de IO-prestaties aanzienlijk verminderen en leiden tot databasecorruptie.

7. Gebruik SuperClassic of Classic in Firebird 2.5

Als u Firebird 2.5 SuperServer met veel verbindingen gebruikt, probeer dan SuperClassic of Classic te gebruiken; deze kunnen beter schalen door alle CPU-kernen te gebruiken.

8. Gebruik SuperServer 3.0 in Firebird 3.

Als u Classic of SuperClassic in 2.5 gebruikt, overweeg dan migratie naar Firebird 3.0 SuperServer; deze kan nu meerdere kernen gebruiken en combineert dit met de voordelen van de gedeelde cache.

9. Vergroot de page buffers cache

Vergroot de grootte van de page buffers cache (parameter DefaultDBCachePages) vanaf de standaardwaarden. Voor 2.5 SuperServer raden we 10000 pagina’s aan, voor 3.0 SuperServer - 50000 pagina’s, voor Classic en SuperClassic - van 256 tot 2048 pagina’s. Stel de page buffers cache-waarde echter niet te hoog in - cachesynchronisatie heeft zijn kosten, en het idee om de hele database in RAM te plaatsen door deze waarde aan te passen zal niet werken. Gebruik vooraf geoptimaliseerde Firebird-configuratiebestanden hier: /nl/optimized-firebird-configuration/

10. Vergroot de geheugenomvang voor sorteerbewerkingen

Vergroot de waarde van de TempCacheLimit-parameter in firebird.conf - deze specificeert de grootte van de cache van de tijdelijke ruimte voor sorteren. Standaardwaarden zijn te laag (8Mb voor Classic en 64Mb voor SuperServer), gebruik ten minste 64Mb voor Classic en 1Gb voor SuperServer en SuperClassic. Gebruik opnieuw de geoptimaliseerde configuratiebestanden uit #9.

11. Zet Forced Writes Uit (met voorzichtigheid!)

Als u intensieve insert- of update-activiteit heeft (u kunt dit controleren met HQbird MonLogger, voor details zie pagina 60 van de HQbird User Guide), en als u UPS en replicatie geïnstalleerd heeft om te beschermen tegen hardwarestoringen, overweeg dan om de Forced Writes-instellingen op OFF te zetten; dit kan de snelheid van schrijfbewerkingen tot 3 keer verhogen.

12. Vergroot het aantal hash-slots voor Classic/SuperClassic

Vergroot de waarde van de LockHashSlots-parameter voor Classic en SuperClassic van de standaard 1009 naar een groot priemgetal (bijvoorbeeld 30011); dit vermindert wachtrijen in het interne vergrendelingsmechanisme.

13. Gebruik CPU Affinity voor Super Server 2.5

Als u SuperServer 2.5 gebruikt, stel dan de CPUAffinity-parameter in op een waarde gelijk aan het aantal databases in gebruik: SuperServer in 2.5 kan verschillende CPU-kernen gebruiken om verzoeken voor bepaalde databases te verwerken.

14. Gebruik een snelle schijf voor tijdelijke ruimte

Stel het eerste deel van de TempDirectory-parameter in firebird.conf in op een snelle schijf - SSD of RAM-schijf. Dit vermindert de tijd van grote sorteringen - bijvoorbeeld wanneer de database wordt hersteld.

15. Bewaar databaseback-ups op een andere schijf

Bewaar databaseback-ups op een speciale fysieke schijf (RAID). Dit scheidt lees- en schrijf-IO tijdens back-up, verhoogt de back-upsnelheid en vermindert de belasting van de hoofdschijf. Dit is vooral belangrijk wanneer back-ups worden gemaakt terwijl gebruikers met de database werken. Meer details over hardwareconfiguratie voor Firebird vindt u in " Firebird Hardware Guide".

16. Deactiveer indexen voor bulk-inserts

Als u veel records invoegt of bijwerkt (meer dan 25% van de tabel), deactiveer dan indexen voor de tabel waarin records worden ingevoegd en activeer ze opnieuw na insert of update. De index-rebuild-bewerking kan sneller zijn dan veel updates van de index.

17. Gebruik Global Temporary Tables voor snelle inserts

Om inserts en updates te versnellen, gebruikt u Global Temporary Tables voor bulk-inserts van grote recordsets en verplaatst u de records daarna naar de permanente tabel. Het kan zeer effectief zijn om records naar GTT in te voegen, ze voor te verwerken en ze vervolgens naar de persistente tabel te verplaatsen.

18. Vermijd onnodige indexen

Gebruik minder indexen voor tabellen met intensieve inserts en updates. Elke index voegt aanzienlijke overhead toe voor insert-, update-, delete- en garbage collection-bewerkingen - er kunnen 3-4 extra paginalezen en -schrijfbewerkingen zijn wanneer een enkel record wordt ingevoegd/bijgewerkt/verwijderd/opgeschoond voor elke index.

19. Vervang UDF’s door ingebouwde functieaanroepen

Vervang UDF-aanroepen door ingebouwde functieaanroepen. In de recente versies van Firebird zijn veel ingebouwde functies toegevoegd die functionaliteit bieden die voorheen alleen beschikbaar was in UDF-bibliotheken. Vervang dergelijke functies waar mogelijk, aangezien ingebouwde functies tot 3 keer sneller werken dan UDF’s.

20. Gebruik alleen-lezen transacties voor leesbewerkingen

Gebruik alleen-lezen transacties voor bewerkingen die geen records wijzigen (d.w.z. SELECT’s) met isolatiemodus = read committed. Dergelijke transacties behouden geen recordversies van de garbage collection en kunnen onbeperkt draaien: ze beïnvloeden de databaseprestaties niet.

21. Gebruik korte schrijftransacties en verwijder ALLE langlopende transacties

Gebruik korte schrijfbare transacties (voor INSERT/UPDATE/DELETE-bewerkingen).

Hoe korter de schrijfbare transactie, hoe beter. Korte transacties behouden proportioneel minder recordversies van garbage collection dan langlopende. Helaas kan zelfs een enkele langlopende transactie (bijvoorbeeld vanuit een open gelaten ontwikkeltool) het goede effect van alle andere korte schrijfbare transacties verpesten. Daarom moet u langlopende transacties monitoren en de juiste plaatsen in de broncode corrigeren. Gebruik de HQbird DataGuard-tool om meldingen te ontvangen over de oudste actieve transactie in de Firebird-database (welke applicaties deze hebben gestart, welk IP-adres, de starttijdstempel), en de HQbird MonLogger-tool om de volledige lijst van langlopende actieve transacties en hun IO-statistieken te zien. Gebruik ook cached updates als u database-access-componenten/bibliotheken gebruikt die recordsets kunnen cachen.

22. Vermijd lange recordketens

Vermijd situaties waarin één record veel recordversies heeft - Firebird werkt veel langzamer met lange recordketens. (Om te zien hoeveel recordversies sommige tabellen hebben en wat de langste recordketen is, kunt u de HQbird IBAnalyst-tool gebruiken, tabblad Tabellen, sorteren op “Max Version”). Gebruik een combinatie van inserts en geplande verwijdering van oude records in plaats van meerdere updates van hetzelfde record.

23. Gebruik PREPARE correct

Gebruik prepared statements om SQL-queries uit te voeren waarbij alleen parameters veranderen - maak bijvoorbeeld een prepare vóór de lus van dergelijke queries. Prepare kan aanzienlijke tijd kosten (vooral voor grote tabellen), en het slechts één keer voorbereiden van de query zal de algehele prestaties sterk verhogen.

24. COMMIT niet te vaak tijdens bulk-insert/update-bewerkingen

In het geval van bulk INSERT/UPDATE/DELETE-bewerkingen, commit de transactie niet na elke wijziging (dit kan gebeuren als u de auto commit-optie in uw database-stuurprogramma gebruikt) - commit transacties ten minste na 1000 bewerkingen of meer. Elke transactie-commit voert verschillende lees-/schrijf-IO-bewerkingen uit op de database, daarom verminderen frequente commits de databaseprestaties.

25. “Schakel” indexen uit als u IN met veel constanten gebruikt

Als u de constructie WHERE fieldX IN (Constant1, Constant2,… ConstantN) gebruikt en er een index op fieldX is, zal Firebird de index zo vaak gebruiken als er constanten in de IN-lijst staan. Schakel indexzoekopdracht uit door fieldX om te zetten in expressie +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), of gebruik voor strings fieldX||''

26. Vervang IN door JOIN

Vermijd queries met geneste WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )); dit kan de Firebird-optimizer verwarren. Transformeer geneste IN’s naar joins.

27. Gebruik LEFT JOIN op de juiste manier

Als u LEFT OUTER joins gebruikt, plaats dan expliciet tabellen in de join van de kleinste naar de grootste.

28. Beperk het ophalen van SELECT-queries

Probeer altijd de grote uitvoer voor SELECT-queries te beperken met FIRST… SKIP of ROWS-clausules. Als de query niet specifiek is ontworpen als een rapport (waarvoor alle records moeten worden afgedrukt/geëxporteerd), is het meestal voldoende om de top 10-100 records te tonen. Haal alleen noodzakelijke records op.

29. Specificeer minder kolommen in SELECT met ORDER BY/GROUP BY

Verminder het aantal kolommen en hun totale breedte in queries met ORDER BY/GROUP BY, zowel in het SELECT-gedeelte (d.w.z. velden die getoond moeten worden) als in de ORDER BY-clausule. Firebird voegt kolommen uit SELECT en ORDER BY/GROUP BY-clausules samen en sorteert ze in het geheugen (of, als het geheugen niet voldoende is, op de schijf). Dus als er een lange VARCHAR in SELECT staat, kan de grootte van de sorteerbestanden echt groot zijn (vele gigabytes). Het verminderen van het aantal velden tot alleen die welke gesorteerd moeten worden en het laat joinen met grote velden die getoond moeten worden, kan de snelheid van een query met ORDER BY/GROUP BY sterk (x3-x10) verhogen.

30. Gebruik afgeleide tabellen om SELECT met ORDER BY/GROUP BY te optimaliseren

Een andere manier om een SQL-query met sortering te optimaliseren is het gebruik van afgeleide tabellen om onnodige sorteerbewerkingen te vermijden. In plaats van

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

gebruikt u de volgende wijziging:

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. Bewaar korte strings in VARCHAR, grote in BLOBs

Gebruik voor korte karaktergegevens VARCHAR’s en voor lange teksten BLOBs. Varchars zijn sneller voor kleine stukjes gegevens omdat ze in het record worden opgeslagen en het hele record tijdens dezelfde IO-cyclus wordt gelezen, en als de recordgrootte minder is dan 2/3 van de databasepaginagrootte, wordt het hele record op dezelfde databasepagina opgeslagen. BLOBs worden buiten het record opgeslagen en vereisen een extra IO-ronde om ze te lezen, en ze tonen hun voordeel bij het lezen en schrijven van lange strings.

32. Sluit BLOB-kolommen uit van grote SELECT’s

Sluit BLOB-kolommen uit van grote SELECT’s. Gebruik een soort late binding met sub-selects om selectief informatie uit BLOBs te tonen (bijvoorbeeld de inhoud van het document tonen).

33. Gebruik BIGINT voor primaire en unieke sleutels

Gebruik het BIGINT-type voor auto-increment primaire en unieke sleutels en voor identificatoren van alle typen. Bewerkingen met BIGINT zijn het snelst en BIGINT heeft voldoende capaciteit om bijna alle gegevensbereiken op te slaan.

34. Gebruik geen VARCHAR’s voor sleutels

Gebruik geen VARCHAR voor identificaties, tenzij het echt noodzakelijk is - bewerkingen hiermee zijn veel minder efficiënt dan met integer-kolommen. Vermijd vooral GUID’s als identificaties - vanwege de willekeurige verdeling van GUID-waarden kunnen INSERT/UPDATE-bewerkingen met Primary/Unique Keys op GUID’s tot 20 keer langzamer zijn dan met integers.

35. Herbereken indexstatistieken

Herbereken indexstatistieken regelmatig. Werk de indexstatistieken bij voor tabellen met frequente of massale wijzigingen met het commando SET STATISTICS; dit stelt de Firebird-optimalisator in staat betere SQL-plannen te kiezen. HQbird Firebird DataGuard kan deze herberekening van indexstatistieken automatisch uitvoeren volgens het gewenste schema (meestal één keer per week).

36. Gebruik een verbindingspool

Als databaseverbindingen naar de Firebird-database kort zijn (typisch voor websites), gebruik dan een verbindingspool - gebruik bijvoorbeeld in PHP de functie ibase_pconnect in plaats van ibase_connect.

37. Gebruik de LINGER-optie in Firebird 3.0

Als databaseverbindingen kort zijn en u Firebird 3+ gebruikt, gebruik dan de LINGER-optie om de cache gedurende een bepaalde tijd actief te houden; dit houdt veelgebruikte pagina’s in de cache, zelfs als er geen andere verbindingen zijn. Bijvoorbeeld: ALTER DATABASE SET LINGER TO 60 houdt de cache 60 seconden na het einde van de laatste verbinding actief.

38. Gebruik HASH JOINs

In Firebird 3.0 kan HASH JOIN bij het samenvoegen van grote en kleine tabellen veel sneller zijn dan een normale join die gebruikmaakt van een “nested loop” met index. Om de Firebird-optimalisator HASH join te laten gebruiken, voegt u +0 toe aan de join-voorwaarde: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Controleer het resultaat van de optimalisatie voordat u het in productie neemt!

39. Markeer geschikte PSQL-functies als DETERMINISTIC

Markeer uw PSQL-functies (in Firebird 3+) die geen parameters hebben en constante waarden retourneren met het trefwoord DETERMINISTIC. De deterministische functies worden berekend en gecachet binnen het bereik van de huidige query.

40. Gebruik analytische (window) functies in Firebird 3.0

Als u een SELECT uitvoert met gelijktijdige uitvoer van een kolom en een geaggregeerde functie daarop, gebruik dan window (analytische) functies - dit is sneller dan een subquery of 2 queries. Bijvoorbeeld:

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

vervang door:

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

41. Gebruik de schakelaar -se voor gbak

Gebruik de schakelaar -se om de gbak-backup- en/of herstelsnelheid tot 20% te verhogen, bijvoorbeeld:

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

42. WHERE CURRENT OF

De snelste manier om records te verwerken die door de cursor in PSQL zijn opgehaald, is de clausule ‘where current of <>’. Dit is sneller dan ‘where rb$db_key = :v_db_key’ en veel sneller dan zoeken met een primary of unique key.

43. Vermijd frequente queries naar monitoringtabellen

Voer niet te vaak queries uit naar Firebird-monitoringtabellen (MON$) - dergelijke queries verbruiken aanzienlijke resources en kunnen de prestaties van de hoofdbedrijfslogica sterk verminderen. We raden aan MON$-queries niet vaker dan één keer per minuut uit te voeren. Gebruik voor continue monitoring van Firebird-queries/transacties/verbindingen de HQbird PerfMon-tool die Trace API ondersteunt (zie pagina 66 van de HQbird User Guide voor details).

44. Gebruik de NO_AUTO_UNDO-optie voor bulk-inserts/updates

Als u veel DML-commando’s (Update/Insert/Delete) uitvoert binnen het kader van dezelfde transactie, voegt Firebird de undo-log van elk commando samen met de undo-log van de transactie. Om bulk-DML-bewerkingen te versnellen, start u de transactie met de optie «NO AUTO UNDO», zodat de undo-logs van elk commando niet worden samengevoegd met de undo-log van de transactie.

45. Gebruik geen SRP-authenticatie in Firebird 3 als u het niet nodig heeft

Gebruik geen SRP-gebruikersauthenticatie (Firebird 3.0+) als u het niet echt nodig heeft - verbinding maken met SRP-authenticatie verloopt langzamer dan de reguliere verbinding.

In plaats van een samenvatting

Prestatieoptimalisatie vereist dat u rekening houdt met meerdere factoren en kan echt lastig zijn. Als u alle bovenstaande punten heeft geprobeerd, overweeg dan om een professionele database-prestatieoptimalisatie service in te huren.

Neem contact met ons op

Heeft u vragen? Aarzel niet om contact met ons op te nemen via e-mail!