IBAnalyst : Comprendre votre base de données

Dmitri Kuzmenko, [email protected], dernière mise à jour 31 mars 2014

Je travaille avec InterBase depuis 1994. À l’époque, la plupart des bases de données étaient petites et ne nécessitaient aucun réglage. Bien sûr, il y avait des occasions où je devais modifier ibconfig sur un serveur et reconfigurer le matériel ou le système d’exploitation, mais c’était à peu près tout ce que je pouvais faire pour optimiser les performances.

Il y a quatre ans, notre entreprise a commencé à fournir un support technique et une formation aux utilisateurs d’InterBase. Travailler avec de nombreuses bases de données de production m’a également appris beaucoup de choses différentes. Cependant, la plupart de ce que j’ai appris concernait les applications - l’utilisation des paramètres de transaction, l’optimisation des requêtes et des ensembles de résultats.

Bien sûr, je connaissais depuis longtemps gstat - l’outil qui fournit des informations sur les statistiques de la base de données. Si vous avez déjà consulté la sortie de gstat ou lu opguide.pdf à ce sujet, vous sauriez que la sortie statistique ressemble à un ensemble de chiffres et rien d’autre. D’accord, vous pouvez découvrir des informations sur la fragmentation pour une table ou un index particulier, mais quelles autres informations utiles peuvent être obtenues ?

Heureusement, avant de travailler avec InterBase, je m’intéressais aux différentes structures de données, à leur stockage et aux algorithmes qu’elles utilisent. Cela m’a aidé à interpréter la sortie de gstat. À ce moment-là, j’ai décidé d’écrire un outil capable d’analyser la sortie de gstat pour aider à optimiser la base de données ou au moins à identifier la cause des problèmes de performance.

Pour faire court, le résultat a été la création d’IBAnalyst. Malgré mon expérience, il me permet encore de trouver des choses très intéressantes ou des problèmes de performance dans différentes bases de données.

Les systèmes réels ont des performances d’exécution qui fluctuent comme une vague. L’amplitude de ces « vagues » peut être faible ou élevée, vous pouvez donc voir comment les performances diffèrent d’un jour à l’autre (ou d’heure en heure). Les performances réelles dépendent de nombreux facteurs, notamment la conception de l’application, la configuration du serveur, la concurrence des transactions, les déchets de version dans la base de données, etc. Pour savoir ce qui se passe dans une base de données (aspects positifs et négatifs des performances), vous devriez au moins jeter un œil aux statistiques de la base de données de temps en temps.

Examinons les capacités d’IBAnalyst. IBAnalyst peut prendre les statistiques de gstat ou de l’API Services et les compiler dans un rapport vous donnant des informations complètes sur la base de données, ses tables et ses index. Il contient des avertissements intégrés disponibles lors de la navigation dans les statistiques ; il inclut également des commentaires d’indices et des rapports de recommandations.

Informations sur la base de données

Figure 1 Résumé des statistiques de la base de données

Le résumé présenté dans la Figure 1 fournit des informations générales sur votre base de données. Les avertissements ou commentaires affichés sont basés sur des connaissances soigneusement rassemblées à partir d’un grand nombre de bases de données de production réelles.

Remarque : Toutes les figures de cet article contiennent des statistiques gstat provenant d’une base de données de production réelle (avec l’autorisation de ses propriétaires).

Comme je l’ai dit précédemment, les statistiques brutes de la base de données semblent cryptiques et difficiles à interpréter. IBAnalyst met en évidence tout problème potentiel clairement en jaune ou en rouge, et le détail du problème peut être lu simplement en plaçant le curseur sur l’entrée concernée et en lisant l’indice affiché.

Ensuite, nous pouvons voir que le paramètre Forced Write est défini sur OFF et marqué en rouge. InterBase 4.x et 5.x avaient ce paramètre activé par défaut. Forced Writes est une méthode de cache d’écriture : lorsqu’il est activé, il écrit les données modifiées immédiatement sur le disque, mais lorsqu’il est désactivé, les écritures seront stockées pendant un temps inconnu par le système d’exploitation dans son cache de fichiers. InterBase 6 crée des bases de données avec Forced Writes désactivé.

Pourquoi cela est-il marqué en rouge dans le rapport IBAnalyst ? La réponse est simple - l’utilisation d’écritures asynchrones peut provoquer une corruption de la base de données en cas de panne de courant, du système d’exploitation ou du serveur.

Conseil : Il est intéressant de noter que les interfaces HDD modernes (ATA, SATA, SCSI) ne montrent aucune différence majeure de performance avec Forced Write activé ou désactivé(1).

Ensuite, dans le rapport, il y a le mystérieux « intervalle de balayage ». S’il est positif, il définit la taille de l’écart entre la transaction la plus ancienne (2) et la transaction snapshot la plus ancienne, à laquelle le moteur est alerté de la nécessité de démarrer une collecte automatique des déchets. Sur certains systèmes, atteindre ce seuil provoquera un effet de « perte soudaine de performance », et par conséquent, il est parfois recommandé de définir l’intervalle de balayage sur 0 (désactivant complètement le balayage automatique). Ici, l’intervalle de balayage est marqué en jaune, car la valeur de l’écart de balayage est négative, ce qui peut être le cas dans les statistiques d’InterBase 6.0, Firebird et Yaffil, mais pas dans InterBase 7.x. Lorsque la valeur de l’écart de balayage est supérieure à l’intervalle de balayage (si l’intervalle de balayage n’est pas 0), l’entrée du rapport pour l’intervalle de balayage sera marquée en rouge avec un indice approprié.

Nous examinerons les 8 lignes suivantes comme un groupe, car elles affichent toutes des aspects de l’état des transactions de la base de données :

La transaction la plus ancienne est la transaction non validée la plus ancienne. Tous les numéros de transaction inférieurs correspondent à des transactions validées, et aucune version d’enregistrement n’est disponible pour ces transactions. Les numéros de transaction supérieurs à la transaction la plus ancienne correspondent à des transactions qui peuvent être dans n’importe quel état. C’est ce qu’on appelle aussi la « transaction intéressante la plus ancienne », car elle se fige lorsqu’une transaction se termine par un rollback, et le serveur ne peut pas annuler ses modifications à ce moment-là.
Le snapshot le plus ancien - la transaction active (c’est-à-dire non encore validée) la plus ancienne qui existait au début de la transaction qui est actuellement la transaction « intéressante » la plus ancienne. Indique le numéro de transaction snapshot le plus bas qui s’intéresse aux versions d’enregistrement.
La transaction active la plus ancienne - la transaction actuellement active la plus ancienne (3).
La transaction suivante - le numéro de transaction qui sera attribué à une nouvelle transaction.
Transactions actives - IBAnalyst donnera un avertissement si le numéro de transaction active la plus ancienne est 30 % inférieur au nombre quotidien de transactions. Les statistiques ne disent pas s’il existe d’autres transactions actives entre la transaction active la plus ancienne et la transaction suivante, mais de telles transactions peuvent exister. En général, si la transaction active la plus ancienne reste bloquée, il y a deux causes possibles : a) une transaction est active pendant une longue période ou b) la conception de l’application permet aux transactions de s’exécuter pendant une longue période. Les deux causes empêchent la collecte des déchets et consomment des ressources serveur.
Transactions par jour - cela est calculé à partir de la transaction suivante, divisée par le nombre de jours écoulés depuis la création de la base de données jusqu’au moment où les statistiques sont récupérées. Cela ne peut être correct que pour les bases de données de production, ou pour les bases de données qui sont périodiquement restaurées à partir d’une sauvegarde, ce qui réinitialise la numérotation des transactions.

Comme vous l’avez déjà appris, s’il y a des avertissements, ils sont affichés sous forme de lignes colorées, avec des indices clairs et descriptifs sur la façon de corriger ou de prévenir le problème.

Il convient de noter que les statistiques de la base de données ne sont pas toujours utiles. Les statistiques recueillies pendant le travail et les opérations de maintenance peuvent être dénuées de sens.

Ne recueillez pas de statistiques si vous :

Vient de restaurer votre base de données
Avez effectué une sauvegarde (gbak -b db.gdb) sans le commutateur -g
Avez récemment effectué un balayage manuel (gfix -sweep)

Les statistiques obtenues dans de telles occasions seront pratiquement inutiles. Il est également vrai que pendant le travail normal, il peut y avoir des moments où la base de données est dans un état parfait, par exemple, lorsque les applications génèrent moins de charge sur la base de données que d’habitude (les utilisateurs sont à déjeuner ou c’est une période calme dans la journée de travail).

Comment savoir quand il y a un problème avec la base de données ?

Vos applications peuvent être si bien conçues qu’elles fonctionneront toujours correctement avec les transactions et les données, sans créer d’écarts de balayage, sans accumuler beaucoup de transactions actives, sans maintenir de longs snapshots, etc. En général, cela ne se produit pas (désolé, collègues).

La raison la plus courante est que les développeurs testent leurs applications avec seulement deux ou trois utilisateurs simultanés. Lorsque l’application est ensuite utilisée dans un environnement de production avec quinze utilisateurs simultanés ou plus, la base de données peut se comporter de manière imprévisible. Bien sûr, le mode multi-utilisateur peut fonctionner correctement car la plupart des conflits multi-utilisateurs peuvent être testés avec deux ou trois applications s’exécutant simultanément. Cependant, avec un plus grand nombre d’utilisateurs, des problèmes de collecte des déchets peuvent survenir. Ces problèmes potentiels peuvent être détectés si vous recueillez les statistiques de la base de données aux bons moments.

Informations sur les tables

Examinons un autre exemple de sortie d’IBAnalyst.

![](/images/article_IBAnalyst (1).jpg)

Figure 2 Statistiques des tables

La vue des statistiques des tables d’IBAnalyst est également très utile. Elle peut montrer quelles tables ont beaucoup de versions d’enregistrement, où un grand nombre de mises à jour/suppressions ont été effectuées, les tables fragmentées, avec une fragmentation causée par des mises à jour/suppressions ou par des blobs, etc. Vous pouvez voir quelles tables sont mises à jour fréquemment et quelle est la taille de la table en mégaoctets. La plupart de ces avertissements sont personnalisables.

Dans cet exemple de base de données, il y a plusieurs problèmes. Tout d’abord, la couleur jaune dans la colonne VerLen avertit que l’espace occupé par les versions d’enregistrement est plus grand que celui occupé par les enregistrements eux-mêmes. Cela peut résulter de la mise à jour de nombreux champs dans un enregistrement ou de suppressions en masse. Voir les lignes où la colonne MaxVers est marquée en bleu. Cela montre qu’une seule version par enregistrement est stockée et, par conséquent, que le problème est dû à des suppressions en masse. La valeur dans la colonne Versions montre combien d’enregistrements ont été supprimés.

Les transactions actives de longue durée empêchant la collecte des déchets sont la principale raison de la dégradation des performances. Pour certaines tables, il peut y avoir beaucoup de versions qui sont encore « en cours d’utilisation ». Le serveur ne peut pas décider si elles sont réellement en cours d’utilisation, car les transactions actives peuvent potentiellement avoir besoin de l’une ou de toutes ces versions. Par conséquent, le serveur ne considère pas ces versions comme des déchets, et il faut de plus en plus de temps pour construire un enregistrement correct à partir de nombreuses versions chaque fois qu’une transaction le lit. Dans la Figure 2, vous pouvez voir deux tables dont le nombre de versions est trois fois supérieur au nombre d’enregistrements. En utilisant ces informations, vous pouvez également vérifier si le fait que vos applications mettent à jour ces tables si fréquemment est intentionnel ou dû à une erreur.

La vue des index

Les index sont utilisés par le moteur de base de données pour appliquer les contraintes de clé primaire, de clé étrangère et d’unicité. Ils accélèrent également la récupération des données. Les index uniques sont les meilleurs pour la récupération des données, mais le niveau de bénéfice des index non uniques dépend de la diversité des données indexées.

Par exemple, regardez ADDR_ADDRESS_IDX6. Tout d’abord, le nom de l’index lui-même suggère qu’il a été créé manuellement. Si les statistiques ont été prises par l’API Services avec des informations de métadonnées, vous pouvez voir quelles colonnes sont indexées (dans IBAnalyst 1.83 et supérieur). Pour l’index examiné, vous pouvez voir qu’il a 34999 clés, TotalDup est 34995 et MaxDup est 25056. Les deux colonnes de doublons sont marquées en rouge. Cela est dû au fait qu’il n’y a que 4 valeurs de clé uniques parmi toutes les clés de cet index, comme on peut le voir dans la colonne Uniques. De plus, la plus grande chaîne de doublons (clé pointant vers des enregistrements avec la même valeur de colonne) est 25056 - c’est-à-dire que presque toutes les clés stockent l’une des quatre valeurs uniques. Par conséquent, cet index pourrait :

Réduire la vitesse du processus de restauration. D’accord, trente-cinq mille clés n’est pas un gros problème pour les bases de données et le matériel modernes, mais l’impact doit être noté quand même.
Ralentir le garbage collection. Les index avec un faible nombre de valeurs uniques peuvent entraver le garbage collection jusqu’à dix fois par rapport à un index complètement unique. Ce problème a été résolu dans InterBase 7.1/7.5 et Firebird 2.0.
Produire des lectures de pages inutiles lorsque l’optimiseur lit l’index. Cela dépend de la valeur recherchée dans une requête particulière - rechercher par un index qui a une valeur plus grande pour MaxDup sera plus lent. Rechercher par valeur sur une colonne qui a moins de valeurs dupliquées sera plus rapide, mais seul vous savez que la colonne est indexée.

C’est pourquoi IBAnalyst attire votre attention sur de tels index, les marquant en rouge et jaune, et les incluant dans le rapport Recommandations. Malheureusement, la plupart des « mauvais » index sont automatiquement créés pour appliquer les contraintes de clés étrangères. Dans certains cas, ce problème peut être résolu en empêchant, à l’aide de déclencheurs, les suppressions ou mises à jour de clés primaires dans les tables de référence. Mais s’il n’est pas possible de mettre en œuvre de tels changements, IBAnalyst vous montrera les « mauvais » index sur les clés étrangères à chaque fois que vous consulterez les statistiques.

Rapports

Il n’est pas nécessaire de parcourir tout le rapport à chaque fois, en repérant la couleur des cellules et en lisant les astuces pour de nouveaux avertissements. Des informations plus directes et détaillées peuvent être obtenues en utilisant la fonctionnalité Recommandations d’IBAnalyst. Chargez simplement les statistiques et allez dans le menu Rapports/Afficher les recommandations. Ce rapport fournit une analyse étape par étape, y compris des avertissements descriptifs plus détaillés sur les écritures forcées, l’intervalle de sweep, l’activité de la base de données, l’état des transactions, la taille des pages de la base de données, le sweeping, les pages d’inventaire des transactions, les tables fragmentées, les tables avec beaucoup de versions d’enregistrements, les suppressions/mises à jour massives, les index profonds, les index défavorables à l’optimiseur, les index inutiles et même les tables vides. Toutes ces informations et les suggestions qui les accompagnent sont créées dynamiquement en fonction des statistiques chargées.

À titre d’exemple de la sortie du rapport, examinons un rapport généré pour les statistiques de base de données que vous avez vues plus tôt dans cet article :

« La taille globale des pages d’inventaire des transactions (TIP) est importante - 94 kilo-octets ou 23 pages. La transaction Read_committed utilise le TIP global, mais les transactions snapshot font leurs propres copies du TIP en mémoire. Une grande taille de TIP peut ralentir les performances. Essayez d’exécuter un sweep manuellement (gfix -sweep) pour réduire la taille du TIP. »

Voici une autre citation de la partie table/index du rapport :

« Nombre de tables versionnées : 8. Une grande quantité de versions d’enregistrements ralentit généralement les performances. S’il y a beaucoup de versions d’enregistrements dans une table, alors le garbage collection ne fonctionne pas, ou les enregistrements ne sont pas lus par une instruction select. Vous pouvez essayer select count(*) sur ces tables pour forcer le garbage collection, mais cela peut prendre du temps (s’il y a beaucoup de versions et des index non uniques) et peut échouer s’il existe au moins une transaction intéressée par ces versions.

Voici la liste des tables avec un ratio version/enregistrement supérieur à 3 :


Table	Enregistrements	Versions	Taille Enr/Vers
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Résumé

IBAnalyst est un outil inestimable qui aide un utilisateur à effectuer une analyse détaillée des statistiques de base de données Firebird ou InterBase et à identifier les problèmes potentiels d’une base de données en termes de performances, de maintenance et d’interaction de l’application avec la base de données. Il prend des statistiques de base de données cryptiques et les affiche de manière graphique facile à comprendre, et fera automatiquement des suggestions judicieuses pour améliorer les performances de la base de données et faciliter sa maintenance.

1 InterBase 7.5 et Firebird 1.5 disposent de fonctionnalités spéciales qui peuvent périodiquement vider les pages non enregistrées si les écritures forcées sont désactivées.

2 La transaction la plus ancienne est la même que la transaction intéressante la plus ancienne, mentionnée partout. La sortie de gstat ne montre pas cette transaction comme « intéressante ».

3 Ann Harrison dit que la transaction active la plus ancienne est la transaction la plus ancienne qui était active lorsque la transaction active la plus ancienne actuelle a démarré. Pour les applications, ce n’est pas une grande différence ici.