45 façons d'accélérer une base de données Firebird

Vous trouverez ici la liste des conseils de performance pour les bases de données Firebird dans différents domaines - du matériel/système d’exploitation et du réglage de la configuration Firebird aux recommandations d’optimisation SQL. Cette liste n’est pas la référence complète pour optimiser Firebird, et elle suppose que vous comprenez les bases du fonctionnement de Firebird, telles que les plans d’exécution, la gestion des transactions et les statistiques de performance des requêtes.

Veuillez appliquer ces conseils avec prudence et vérifier leur effet avant de les mettre en production.

Notre société (IBSurgeon) propose le service complet d’optimisation des performances de bases de données.

1. Placez la base de données sur un SSD

Placez votre base de données sur un SSD. Le disque SSD offre des E/S aléatoires bien meilleures que les disques traditionnels. Les E/S aléatoires sont essentielles pour lire et écrire des données réparties dans un grand fichier de base de données - la majorité des opérations de base de données nécessitent des E/S aléatoires parallèles intensives.

2. Utilisez RAID 10

Si vous utilisez RAID1 ou RAID5, envisagez RAID10 - c’est 15 à 25 % plus rapide.

3. Vérifiez la BBU

Si vous utilisez un contrôleur RAID, vérifiez qu’il dispose d’une unité de batterie de secours (BBU) installée et opérationnelle - certains fournisseurs ne fournissent pas de BBU par défaut. Sans BBU, le contrôleur désactive le cache, et le RAID fonctionne très lentement, même plus lentement que les disques SATA habituels. En général, vous pouvez vérifier l’état de la BBU dans l’outil de configuration RAID.

4. Réglez le cache d’écriture en write-back

Si vous utilisez un contrôleur RAID avec BBU installée (et un serveur avec onduleur), vérifiez que son cache est réglé en write-back (pas en write-through). « Write-back » active le cache d’écriture du contrôleur.

5. Activez le cache de lecture

Si vous utilisez un contrôleur RAID, vérifiez que le cache de lecture est activé.

6. Vérifiez le sous-système de disques

Vérifiez vos disques pour les blocs défectueux et autres problèmes matériels (y compris la surchauffe). Les problèmes matériels peuvent considérablement réduire les performances d’E/S et entraîner des corruptions de base de données.

7. Utilisez SuperClassic ou Classic dans Firebird 2.5

Si vous utilisez Firebird 2.5 SuperServer avec de nombreuses connexions, essayez d’utiliser SuperClassic ou Classic, ils peuvent mieux évoluer en utilisant tous les cœurs du CPU.

8. Utilisez SuperServer 3.0 dans Firebird 3.

Si vous utilisez Classic ou SuperClassic en 2.5, envisagez la migration vers Firebird 3.0 SuperServer, il peut désormais utiliser plusieurs cœurs et combiner cela avec les avantages du cache partagé.

9. Augmentez le cache des pages de tampon

Augmentez la taille du cache des pages de tampon (paramètre DefaultDBCachePages) par rapport aux valeurs par défaut. Pour 2.5 SuperServer, nous recommandons 10000 pages, pour 3.0 SuperServer - 50000 pages, pour Classic et SuperClassic - de 256 à 2048 pages. Cependant, ne définissez pas la valeur du cache des pages de tampon trop élevée - la synchronisation du cache a un coût, et l’idée de mettre toute la base de données en RAM en réglant cette valeur ne fonctionnera pas. Utilisez les fichiers de configuration Firebird pré-optimisés ici : /fr/optimized-firebird-configuration/

10. Augmentez la taille de la mémoire pour les opérations de tri

Augmentez la valeur du paramètre TempCacheLimit dans firebird.conf - il spécifie la taille du cache de l’espace temporaire pour le tri. Les valeurs par défaut sont trop faibles (8 Mo pour Classic et 64 Mo pour SuperServer), utilisez au moins 64 Mo pour Classic et 1 Go pour SuperServer et SuperClassic. Encore une fois, utilisez les fichiers de configuration optimisés du point #9.

11. Désactivez Forced Writes (avec prudence !)

Si vous avez une activité intensive d’insertion ou de mise à jour (vous pouvez la vérifier avec HQbird MonLogger, pour plus de détails voir la page 60 du Guide de l’utilisateur HQbird), et si vous avez un onduleur et une réplication installés pour vous protéger contre les pannes matérielles, envisagez de régler le paramètre Forced Writes sur OFF, cela peut augmenter la vitesse des opérations d’écriture jusqu’à 3 fois.

12. Augmentez le nombre d’emplacements de hachage pour Classic/SuperClassic

Augmentez la valeur du paramètre LockHashSlots pour Classic et SuperClassic de la valeur par défaut 1009 à un grand nombre premier (30011, par exemple), cela diminuera les files d’attente dans le mécanisme de verrouillage interne.

13. Utilisez l’affinité CPU pour Super Server 2.5

Si vous utilisez SuperServer 2.5, définissez le paramètre CPUAffinity à une valeur égale au nombre de bases de données en cours d’utilisation : SuperServer en 2.5 peut utiliser différents cœurs de CPU pour traiter les requêtes de certaines bases de données.

14. Utilisez un disque rapide pour l’espace temporaire

Définissez la première partie du paramètre TempDirectory dans firebird.conf sur un disque rapide - SSD ou disque RAM. Cela diminuera le temps des grands tris - par exemple lors de la restauration de la base de données.

15. Stockez les sauvegardes de base de données sur un autre disque

Stockez les sauvegardes de base de données sur un disque physique dédié (RAID). Cela séparera les E/S de lecture et d’écriture pendant la sauvegarde, augmentera la vitesse de sauvegarde et diminuera la charge sur le disque principal. C’est particulièrement important lorsque les sauvegardes sont effectuées pendant que les utilisateurs travaillent avec la base de données. Plus de détails sur la configuration matérielle pour Firebird peuvent être trouvés dans « Guide matériel Firebird ».

16. Désactivez les index pour les insertions en masse

Si vous insérez ou mettez à jour de nombreux enregistrements (plus de 25 % de la table), désactivez les index de la table où les enregistrements sont insérés et réactivez-les après l’insertion ou la mise à jour. L’opération de reconstruction d’index peut être plus rapide que de nombreuses mises à jour de l’index.

17. Utilisez les tables temporaires globales pour des insertions rapides

Pour accélérer les insertions et les mises à jour, utilisez les tables temporaires globales pour les insertions en masse de grands ensembles d’enregistrements, puis transférez les enregistrements dans la table permanente. Cela peut être très efficace pour insérer des enregistrements dans une GTT, les prétraiter puis les déplacer vers la table persistante.

18. Évitez les index inutiles

Utilisez moins d’index pour les tables avec des insertions et mises à jour intensives. Chaque index ajoute une surcharge significative pour les opérations d’insertion, de mise à jour, de suppression et de collecte des déchets - il peut y avoir 3 à 4 lectures et écritures de pages supplémentaires lorsqu’un seul enregistrement est inséré/mis à jour/supprimé/nettoyé pour chaque index.

19. Remplacez les UDF par des appels de fonctions intégrées

Remplacez les appels UDF par des appels de fonctions intégrées. De nombreuses fonctions intégrées ont été ajoutées dans les versions récentes de Firebird, qui offrent des fonctionnalités auparavant disponibles uniquement dans les bibliothèques UDF. Remplacez ces fonctions lorsque c’est possible, car les fonctions intégrées fonctionnent jusqu’à 3 fois plus vite que les UDF.

20. Utilisez des transactions en lecture seule pour les opérations de lecture

Utilisez des transactions en lecture seule pour les opérations qui ne modifient pas les enregistrements (c’est-à-dire les SELECT) avec le mode d’isolation = read committed. Ces transactions ne conservent pas les versions d’enregistrements de la collecte des déchets et peuvent s’exécuter indéfiniment : elles n’affectent pas les performances de la base de données.

21. Utilisez des transactions d’écriture courtes et éliminez TOUTES les transactions de longue durée

Utilisez des transactions d’écriture courtes (pour les opérations INSERT/UPDATE/DELETE).

Plus la transaction d’écriture est courte, mieux c’est. Les transactions courtes conservent proportionnellement moins de versions d’enregistrements de la collecte des déchets que les transactions de longue durée. Malheureusement, même une seule transaction de longue durée (laissée ouverte depuis un outil de développement, par exemple) peut compromettre le bon effet de toutes les autres transactions d’écriture courtes. C’est pourquoi vous devez surveiller les transactions de longue durée et corriger les endroits appropriés dans le code source. Utilisez l’outil HQbird DataGuard pour recevoir des alertes sur la transaction active la plus ancienne dans la base de données Firebird (quelles applications l’ont démarrée, quelle adresse IP, l’horodatage de son démarrage), et l’outil HQbird MonLogger pour voir la liste complète des transactions actives de longue durée et leurs statistiques d’E/S. De plus, si vous utilisez des composants/bibliothèques d’accès aux bases de données qui peuvent mettre en cache les ensembles d’enregistrements, utilisez les mises à jour en cache.

22. Évitez les longues chaînes d’enregistrements

Évitez les situations où un enregistrement a de nombreuses versions - Firebird fonctionne beaucoup plus lentement avec de longues chaînes d’enregistrements. (pour voir combien de versions d’enregistrements certaines tables ont, et quelle est la chaîne d’enregistrements la plus longue, vous pouvez utiliser l’outil HQbird IBAnalyst, onglet Tables, trier sur « Max Version »). Utilisez une combinaison d’insertions et de suppression planifiée des anciens enregistrements au lieu de multiples mises à jour du même enregistrement.

23. Utilisez PREPARE correctement

Utilisez des instructions préparées pour exécuter des requêtes SQL où seuls les paramètres changent - par exemple, faites le prepare avant la boucle de ces requêtes. Le prepare peut prendre un temps significatif (surtout pour les grandes tables), et préparer la requête une seule fois augmentera considérablement les performances globales.

24. Ne faites pas de COMMIT trop souvent pendant une opération d’insertion/mise à jour en masse

Dans le cas d’une opération en masse INSERT/UPDATE/DELETE, ne validez pas la transaction après chaque modification (cela peut arriver si vous utilisez l’option auto commit dans votre pilote de base de données) - validez les transactions au moins après 1000 opérations ou plus. Chaque validation de transaction exécute plusieurs opérations d’E/S de lecture/écriture contre la base de données, c’est pourquoi des validations fréquentes diminuent les performances de la base de données.

25. « Désactivez » les index si vous utilisez IN avec de nombreuses constantes

Si vous utilisez la construction WHERE fieldX IN (Constant1, Constant2,… ConstantN), et qu’il y a un index sur fieldX, Firebird utilisera un index autant de fois qu’il y a de constantes dans la liste IN. Désactivez la recherche par index en transformant fieldX en expression +0 : WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), ou, pour les chaînes, utilisez fieldX||''

26. Remplacez IN par JOIN

Évitez d’utiliser des requêtes avec des WHERE IN imbriqués (SELECT… WHERE IN (SELECT.. WHERE IN() )), cela peut confondre l’optimiseur Firebird. Transformez les IN imbriqués en jointures.

27. Utilisez LEFT JOIN de la bonne manière

Si vous utilisez des LEFT OUTER joins, placez explicitement les tables dans la jointure de la plus petite à la plus grande.

28. Limitez la récupération des requêtes SELECT

Essayez toujours de limiter la grande sortie des requêtes SELECT avec les clauses FIRST… SKIP ou ROWS. Si la requête n’est pas conçue spécifiquement comme un rapport (qui nécessite que tous les enregistrements soient imprimés/exportés), il est généralement suffisant d’afficher les 10 à 100 premiers enregistrements. Ne récupérez que les enregistrements nécessaires.

29. Spécifiez moins de colonnes dans SELECT avec ORDER BY/GROUP BY

Réduisez le nombre de colonnes et leur largeur totale dans les requêtes avec ORDER BY/GROUP BY à la fois dans la partie SELECT (c’est-à-dire les champs à afficher) et dans la clause ORDER BY. Firebird fusionne les colonnes des clauses SELECT et ORDER BY/GROUP BY et les trie en mémoire (ou, si la mémoire est insuffisante, sur le disque). Ainsi, s’il y a un long VARCHAR dans SELECT, la taille des fichiers de tri peut être vraiment grande (plusieurs gigaoctets). Réduire le nombre de champs uniquement à ceux qui doivent être triés et faire une jointure tardive avec les grands champs à afficher peut grandement (x3-x10) augmenter la vitesse d’une requête avec ORDER BY/GROUP BY.

30. Utilisez des tables dérivées pour optimiser SELECT avec ORDER BY/GROUP BY

Une autre façon d’optimiser une requête SQL avec tri est d’utiliser des tables dérivées pour éviter les opérations de tri inutiles. Au lieu de

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

utilisez la modification suivante :

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. Stockez les chaînes courtes dans VARCHAR, les grandes dans des BLOBs

Pour stocker des données de caractères courtes, utilisez des VARCHAR, pour stocker de longs textes, utilisez des BLOBs. Les VARCHAR sont plus rapides pour les petites quantités de données car ils sont stockés dans l’enregistrement, et l’enregistrement entier est lu pendant le même cycle d’E/S, et si la taille de l’enregistrement est inférieure aux 2/3 de la taille de page de la base de données, l’enregistrement entier est stocké sur la même page de base de données. Les BLOBs sont stockés en dehors de l’enregistrement et nécessitent un cycle d’E/S supplémentaire pour les lire, et ils montrent leur avantage avec la lecture et l’écriture de longues chaînes.

32. Excluez les colonnes BLOB des grands SELECT

Excluez les colonnes BLOB des grands SELECT. Utilisez une sorte de liaison tardive avec des sous-requêtes pour afficher sélectivement les informations des BLOBs (par exemple, afficher le contenu du document).

33. Utilisez BIGINT pour les clés primaires et uniques

Utilisez le type BIGINT pour les clés primaires et uniques auto-incrémentées et pour les identifiants de tous types. Les opérations avec BIGINT sont les plus rapides, et BIGINT a une capacité suffisante pour stocker presque toutes les plages de données.

34. N’utilisez pas de VARCHAR pour les clés

N’utilisez pas de VARCHAR pour les identifiants sauf si cela est vraiment nécessaire - les opérations avec ceux-ci sont bien moins efficaces qu’avec des colonnes entières. Évitez particulièrement les GUID comme identifiants - en raison de la distribution aléatoire des valeurs GUID, les opérations INSERT/UPDATE avec des clés primaires/uniques GUID peuvent être 20 fois plus lentes qu’avec des entiers.

35. Recalculez les statistiques des index

Recalculez régulièrement les statistiques des index. Mettez à jour les statistiques des index pour les tables avec des changements fréquents ou massifs avec la commande SET STATISTICS, cela permet à l’optimiseur Firebird de choisir de meilleurs plans SQL. HQbird Firebird DataGuard peut effectuer automatiquement ce recalcul des statistiques des index selon le calendrier souhaité (généralement une fois par semaine).

36. Utilisez un pool de connexions

Si les connexions à la base de données Firebird sont courtes (c’est typique pour les sites web), utilisez un pool de connexions - par exemple, en PHP, utilisez la fonction ibase_pconnect au lieu de ibase_connect.

37. Utilisez l’option LINGER dans Firebird 3.0

Si les connexions à la base de données sont courtes et que vous utilisez Firebird 3+, utilisez l’option LINGER pour garder le cache actif pendant la durée spécifiée, cela conservera les pages fréquemment utilisées dans le cache même s’il n’y a pas d’autres connexions. Par exemple, ALTER DATABASE SET LINGER TO 60 conservera le cache pendant 60 secondes après la fin de la dernière connexion.

38. Utilisez les HASH JOINs

Dans Firebird 3.0, lors de la jointure de grandes et petites tables, HASH JOIN peut être beaucoup plus rapide qu’une jointure normale qui utilise « nested loop » avec index. Pour que l’optimiseur Firebird utilise le HASH join, utilisez +0 dans la condition de jointure : T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Vérifiez le résultat de l’optimisation avant de le mettre en production !

39. Marquez les fonctions PSQL appropriées comme DETERMINISTIC

Marquez vos fonctions PSQL (dans Firebird 3+) qui n’ont pas de paramètres et retournent des valeurs constantes avec le mot-clé DETERMINISTIC. Les fonctions déterministes sont calculées et mises en cache dans le cadre de la requête en cours.

40. Utilisez les fonctions analytiques (fenêtres) dans Firebird 3.0

Si vous exécutez un SELECT avec la sortie simultanée d’une colonne et d’une fonction agrégée pour celle-ci, utilisez les fonctions de fenêtre (analytiques) - c’est plus rapide qu’une sous-requête ou 2 requêtes. Par exemple :

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

remplacez par

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

41. Utilisez le commutateur -se pour gbak

Utilisez le commutateur -se pour augmenter la vitesse de sauvegarde et/ou de restauration gbak jusqu’à 20%, par exemple

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

42. WHERE CURRENT OF

La façon la plus rapide de traiter les enregistrements récupérés par le curseur en PSQL est la clause « where current of <> ». C’est plus rapide que « where rb$db_key = :v_db_key » et beaucoup plus rapide qu’une recherche avec une clé primaire ou unique.

43. Évitez les requêtes fréquentes aux tables de surveillance

N’exécutez pas de requêtes vers les tables de surveillance Firebird (MON$) trop souvent - ces requêtes consomment des ressources importantes et peuvent grandement diminuer les performances de la logique métier principale. Nous recommandons d’exécuter les requêtes MON$ pas plus d’une fois par minute. Pour la surveillance continue des requêtes/transactions/attachements Firebird, utilisez l’outil HQbird PerfMon qui prend en charge l’API Trace (voir page 66 du Guide de l’utilisateur HQbird pour plus de détails).

44. Utilisez l’option NO_AUTO_UNDO pour les insertions/mises à jour en masse

Si vous exécutez de nombreuses commandes DML (Update/Insert/Delete) dans le cadre de la même transaction, Firebird fusionne le journal d’annulation de chaque commande avec celui de la transaction. Pour accélérer les opérations DML en masse, démarrez la transaction avec l’option « NO AUTO UNDO », afin de ne pas fusionner les journaux d’annulation de chaque commande avec celui de la transaction.

45. N’utilisez pas l’authentification SRP dans Firebird 3 si vous n’en avez pas besoin

N’utilisez pas l’authentification des utilisateurs SRP (Firebird 3.0+) si vous n’en avez pas vraiment besoin - la connexion avec l’authentification SRP est établie plus lentement que la connexion régulière.

Au lieu d’un résumé

L’optimisation des performances nécessite de prendre en compte de multiples facteurs et peut être vraiment délicate. Si vous avez essayé toutes les choses ci-dessus, envisagez de faire appel à un service professionnel d’optimisation des performances de bases de données.

Contactez-nous

Avez-vous des questions ? N’hésitez pas à nous contacter par email !