45 manieren om een Firebird-database te versnellen

Hier vindt u een lijst met prestatietips voor de Firebird-database op verschillende gebieden - van hardware/OS- en Firebird-configuratietuning tot SQL-optimalisatieaanbevelingen. Deze lijst is niet de volledige referentie voor het optimaliseren van Firebird, en veronderstelt dat u de basis van de werking van Firebird begrijpt, zoals uitvoeringsplannen, transactiebeheer en prestatiestatistieken van query’s.

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

Ons bedrijf (IBSurgeon) biedt de uitgebreide database performance optimization service aan.

1. Plaats de database op SSD

Plaats uw database op SSD. Een SSD-schijf biedt veel betere random IO dan traditionele schijven. Random IO is cruciaal voor het lezen en schrijven van gegevens die verspreid zijn over een groot databasebestand - de meerderheid van databasebewerkingen vereist intensieve parallelle random IO.

2. Gebruik RAID 10

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

3. Controleer BBU

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

4. Stel write cache in op write-back

Als u een RAID-controller met geïnstalleerde BBU gebruikt (en een server met UPS), controleer dan of 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 of de read cache is ingeschakeld.

6. Controleer het schijfsysteem

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

7. Gebruik SuperClassic of Classic in Firebird 2.5

Als u Firebird 2.5 SuperServer met veel verbindingen gebruikt, probeer dan SuperClassic of Classic, deze kunnen beter schalen door alle cores van de CPU 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 cores gebruiken en dit combineren met de voordelen van de gedeelde cache.

9. Vergroot de page buffers cache

Vergroot de grootte van de page buffers cache (parameter DefaultDBCachePages) ten opzichte van 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 waarde van de page buffers cache echter niet te hoog in - cachesynchronisatie heeft zijn prijs, en het idee om de hele database in RAM te plaatsen door deze waarde te tunen zal niet werken. Gebruik hier vooraf geoptimaliseerde Firebird-configuratiebestanden: /nl/optimized-firebird-configuration/

10. Vergroot de geheugengrootte voor sorteerbewerkingen

Verhoog de waarde van de parameter TempCacheLimit in firebird.conf - deze specificeert de grootte van de cache van de tijdelijke ruimte voor sorteren. De 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. Stel Forced Writes uit (met voorzichtigheid!)

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

12. Vergroot het aantal hash slots voor Classic/SuperClassic

Verhoog de waarde van de parameter LockHashSlots voor Classic en SuperClassic van de standaardwaarde 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 de parameter CPUAffinity dan in op de waarde gelijk aan het aantal databases in gebruik: SuperServer in 2.5 kan verschillende CPU-cores gebruiken om verzoeken voor bepaalde databases te verwerken.

14. Gebruik een snelle schijf voor tijdelijke ruimte

Stel het eerste deel van de parameter TempDirectory in firebird.conf in op de 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 de toegewijde fysieke schijf (RAID). Dit scheidt lees- en schrijf-IO tijdens het back-uppen, en 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 indices voor bulk-inserts

Als u veel records invoegt of bijwerkt (meer dan 25% van de tabel), deactiveer dan de indices voor de tabel waarin records worden ingevoegd en reactiveer ze na de insert of update. De index-rebuildbewerking 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 draagt u de records daarna over naar de permanente tabel. Het kan zeer effectief zijn om records in GTT in te voegen, ze voor te verwerken en ze vervolgens naar de persistente tabel te verplaatsen.

18. Vermijd onnodige indices

Gebruik minder indices 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 pagina-lees- en schrijfbewerkingen zijn wanneer het enkele record wordt ingevoegd/bijgewerkt/verwijderd/opgeruimd 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 read-only transacties voor leesbewerkingen

Gebruik read-only transacties voor bewerkingen die records niet wijzigen (dat wil zeggen 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 langdurige

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

Hoe korter de schrijfbare transactie, hoe beter. De korte transacties behouden proportioneel minder recordversies van de garbage collection dan langdurige. Helaas kan zelfs de enkele langdurige transactie (bijvoorbeeld achtergelaten door de ontwikkelingstool) het goede effect van alle andere korte schrijfbare transacties tenietdoen. Daarom moet u langdurige transacties monitoren en de betreffende 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, het tijdstip van starten), en de HQbird MonLogger-tool om de volledige lijst van langdurige actieve transacties en hun IO-statistieken te zien. Als u database-accesscomponenten/bibliotheken gebruikt die recordsets kunnen cachen, gebruik dan bovendien cached updates.

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 Tables, sorteren op “Max Version”). Gebruik de combinatie van inserts en geplande delete van oude records in plaats van meerdere updates van hetzelfde record.

23. Gebruik PREPARE correct

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

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

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

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

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

26. Vervang IN door JOIN

Vermijd het gebruik van query’s met geneste WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )), dit kan de Firebird-optimizer in de war brengen. Transformeer geneste IN’s naar joins.

27. Gebruik LEFT JOIN op de juiste manier

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

28. Beperk het ophalen van SELECT-query’s

Probeer altijd de grote uitvoer voor SELECT-query’s te beperken met FIRST… SKIP- of ROWS-clausules. Als de query niet specifiek als rapport is ontworpen (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. Geef minder kolommen op in SELECT met ORDER BY/GROUP BY

Verminder het aantal kolommen en hun totale breedte in query’s met ORDER BY/GROUP BY, zowel in het SELECT-gedeelte (dat wil zeggen de weer te geven velden) als in de ORDER BY-clausule. Firebird voegt kolommen uit de 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, kunnen de sorteerbestanden echt groot zijn (vele gigabytes). Het verminderen van het aantal velden tot alleen die welke moeten worden gesorteerd en het later joinen met grote velden die moeten worden getoond, kan de snelheid van een query met ORDER BY/GROUP BY sterk (x3-x10) verhogen.

30. Gebruik derived tables om SELECT met ORDER BY/GROUP BY te optimaliseren

Een andere manier om een SQL-query met sortering te optimaliseren is het gebruik van derived tables 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, lange in BLOBs

Gebruik VARCHAR’s om korte karaktergegevens op te slaan, gebruik BLOBs om lange teksten op te slaan. Varchars zijn sneller voor kleine stukjes gegevens omdat ze in het record worden opgeslagen, en het hele record wordt gelezen tijdens dezelfde IO-cyclus, en als de recordgrootte kleiner is dan 2/3 van de paginagrootte van de database, 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 de grote SELECT’s

Sluit BLOB-kolommen uit van de grote SELECT’s. Gebruik een vorm van 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 type BIGINT voor automatisch verhoogde primaire en unieke sleutels en voor identifiers van alle soorten. Bewerkingen met BIGINT zijn het snelst, en BIGINT heeft voldoende capaciteit om bijna alle gegevensbereiken op te slaan.

34. Gebruik geen VARCHARs voor sleutels

Gebruik geen VARCHAR voor identifiers tenzij het echt noodzakelijk is - bewerkingen ermee zijn veel minder effectief dan met integer-kolommen. Vermijd vooral GUIDs als identifiers - door de willekeurige verdeling van GUID-waarden kunnen INSERT/UPDATE-bewerkingen met Primary/Unique Keys GUIDs 20 keer langzamer zijn dan met integers.

35. Herbereken indexstatistieken

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

36. Gebruik een connection pool

Als databaseverbindingen naar een Firebird-database kort zijn (typisch voor websites), gebruik dan een connection pool - 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 gebruikt Firebird 3+, gebruik dan de LINGER-optie om de cache actief te houden gedurende de opgegeven tijdsduur; hierdoor blijven 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 actief na het einde van de laatste verbinding.

38. Gebruik HASH JOINs

In Firebird 3.0 kan HASH JOIN, bij het joinen van grote en kleine tabellen, veel sneller zijn dan een normale join die «nested loop» met index gebruikt. Om de Firebird-optimizer een HASH-join te laten gebruiken, gebruikt u +0 in de join-conditie: 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 sleutelwoord 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 ervoor, gebruik dan window (analytische) functies - dat is sneller dan de 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 schakelaar -se voor gbak

Gebruik schakelaar -se om de gbak-back-up- en/of restore-snelheid met 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 <>’. Het 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$) - zulke queries verbruiken aanzienlijke resources en kunnen de prestaties van de belangrijkste bedrijfslogica sterk verminderen. We raden aan MON$-queries niet vaker dan eens per minuut uit te voeren. Voor continue monitoring van Firebird-queries/transacties/attachments gebruikt u de HQbird PerfMon-tool die de 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 dezelfde transactie, voegt Firebird het undo-log van elk commando samen met het undo-log van de transactie. Om bulk-DML-bewerkingen te versnellen, start u de transactie met de optie «NO AUTO UNDO», om te voorkomen dat undo-logs van elk commando worden samengevoegd met het undo-log van de transactie.

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

Gebruik geen SRP-gebruikersauthenticatie (Firebird 3.0+) als u het niet echt nodig hebt - een verbinding met SRP-authenticatie wordt langzamer tot stand gebracht dan een gewone verbinding.

In plaats van een samenvatting

Prestatieoptimalisatie vereist het overwegen van meerdere factoren en kan echt lastig zijn. Als u alle bovenstaande zaken hebt geprobeerd, overweeg dan een professionele database performance optimization-dienst in te huren.

Neem contact met ons op

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