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

Vous trouverez ici une liste de conseils de performance pour la base 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 sur la façon d’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 un service complet d’optimisation des performances de base de données.

1. Placez la base de données sur SSD

Placez votre base de données sur SSD. Un disque SSD offre des E/S aléatoires bien meilleures que les disques traditionnels. Les E/S aléatoires sont critiques pour la lecture et l’écriture de 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 - il 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, encore plus lentement que les disques SATA habituels. Généralement, 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 une BBU installée (et un serveur avec onduleur), vérifiez que son cache est réglé en write-back (et non 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 son cache de lecture est activé.

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

Vérifiez vos disques pour détecter les blocs défectueux et autres problèmes matériels (y compris la surchauffe). Les problèmes matériels peuvent réduire considérablement 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 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 une migration vers Firebird 3.0 SuperServer, qui peut désormais utiliser plusieurs cœurs et les combiner avec les avantages du cache partagé.

9. Augmentez le cache des pages tampons

Augmentez la taille du cache des pages tampons (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 réglez pas la valeur du cache des pages tampons trop haut - la synchronisation du cache a un coût, et l’idée de placer 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. Là encore, utilisez les fichiers de configuration optimisés du point #9.

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

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

12. Augmentez le nombre de slots 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 CPU Affinity pour Super Server 2.5

Si vous utilisez SuperServer 2.5, réglez le paramètre CPUAffinity à une valeur égale au nombre de bases de données utilisées : 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

Réglez la première partie du paramètre TempDirectory dans firebird.conf sur le disque rapide - SSD ou lecteur 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 le 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 sont disponibles dans le « Firebird Hardware Guide ».

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 des Global Temporary Tables pour des insertions rapides

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

18. Évitez les index inutiles

Utilisez moins d’index pour les tables à insertions et mises à jour intensives. Chaque index ajoute une surcharge significative pour les opérations d’insertion, de mise à jour, de suppression et de garbage collection - 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, offrant 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 un mode d’isolation = read committed. De telles transactions ne retiennent pas les versions d’enregistrements de la garbage collection 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 débarrassez-vous de TOUTES les transactions de longue durée

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

Plus une transaction d’écriture est courte, mieux c’est. Les transactions courtes retiennent proportionnellement moins de versions d’enregistrements de la garbage collection que les transactions de longue durée. Malheureusement, même une seule transaction de longue durée (laissée ouverte par l’outil de développement, par exemple) peut ruiner 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 plus ancienne transaction active 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 des 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 plus longue chaîne d’enregistrements, vous pouvez utiliser l’outil HQbird IBAnalyst, onglet Tables, trier sur « Max Version »). Utilisez la 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 un 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 COMMITez pas trop souvent pendant une opération d’insertion/mise à jour en masse

Dans le cas d’une opération INSERT/UPDATE/DELETE en masse, 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 sur 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 perturber l’optimiseur Firebird. Transformez les IN imbriqués en jointures.

27. Utilisez LEFT JOIN de la bonne manière

Si vous utilisez des jointures LEFT OUTER, 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 sortie volumineuse 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 suffit généralement 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 le SELECT, la taille des fichiers de tri peut être vraiment importante (plusieurs gigaoctets). Réduire le nombre de champs uniquement à ceux qui doivent être triés et joindre tardivement les grands champs à afficher peut considérablement (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 en VARCHAR, les longues en BLOB

Pour stocker des données caractères courtes, utilisez des VARCHAR, pour stocker de longs textes, utilisez des BLOB. Les varchars sont plus rapides pour les petits morceaux 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 la page de base de données, l’enregistrement entier est stocké sur la même page de base de données. Les BLOB sont stockés en dehors de l’enregistrement et nécessitent un cycle d’E/S supplémentaire pour être lus, et ils montrent leur avantage pour 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-selects pour afficher sélectivement les informations des BLOB (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 VARCHAR pour les identifiants sauf si c’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 soumises à des modifications fréquentes ou massives avec la commande SET STATISTICS, cela permet à l’optimiseur Firebird de choisir de meilleurs plans SQL. HQbird Firebird DataGuard peut effectuer ce recalcul des statistiques d’index automatiquement selon la planification souhaitée (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 maintenir 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, dans le cas de la jointure de grandes et de petites tables, HASH JOIN pourrait être beaucoup plus rapide qu’une jointure normale qui utilise « nested loop » avec index. Pour amener l’optimiseur Firebird à utiliser 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 renvoient 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 courante.

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 des fonctions de fenêtre (analytiques) - c’est plus rapide que la 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 de 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 <>’. Elle est plus rapide que ‘where rb$db_key = :v_db_key’ et beaucoup plus rapide que la recherche avec une clé primaire ou unique.

43. Évitez les requêtes fréquentes sur les tables de monitoring

N’exécutez pas de requêtes sur les tables de monitoring Firebird (MON$) trop souvent - ces requêtes consomment des ressources importantes et peuvent considérablement diminuer les performances de la logique métier principale. Nous recommandons d’exécuter les requêtes MON$ pas plus souvent qu’une fois par minute. Pour une surveillance continue des requêtes/transactions/connexions Firebird, utilisez l’outil HQbird PerfMon qui prend en charge l’API Trace (voir page 66 du HQbird User Guide 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 (undo-log) de chaque commande avec le journal d’annulation 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 le journal d’annulation 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 authentification SRP s’établit 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 d’engager un service professionnel d’optimisation des performances de base de données.

Contactez-nous

Vous avez des questions ? N’hésitez pas à nous contacter par email !