Contacts
IBSurgeon
Cette page a été traduite automatiquement. Lisez l'original en anglais. English

Bibliothèque IBSurgeon

IBAnalyst : astuces et conseils

Ce texte a été écrit à l’origine en 2012, il est valable pour les versions 1.0 - 2.5, dans les versions 3.0-5.0 il y a eu de nombreux changements, qui n’ont pas pu être reflétés. Veuillez lire la documentation ou nous contacter pour obtenir de l’aide : [email protected].

Quelques questions sans réponse dans les recommandations et/ou l’aide d’IBAnalyst :

1. Comment reconstruire les index sur les contraintes PRIMARY, FOREIGN ou UNIQUE ?

R : Pour les versions Firebird 1.0-2.5. Oui, vous ne pouvez pas utiliser ALTER INDEX xxx INACTIVE/ACTIVE sur les index de contraintes. Si vous constatez un index profond ou fragmenté sur cette contrainte, vous pouvez utiliser une astuce spéciale (utilisée par gbak lors de la restauration) :

RDB$INDICES possède un indicateur RDB$INDEX_INACTIVE qui est nul ou 0 si l’index est actif (après CREATE INDEX ou ALTER INDEX ACTIVE). 1 signifie que l’index est inactif (après ALTER INDEX INACTIVE). Mais il existe aussi la valeur 3 qui est utilisée pour indiquer les index inactifs sur les contraintes. Vous pouvez donc définir RDB$INDEX_INACTIVE=3 pour cet index, faire un COMMIT, puis remettre la valeur à 0 et valider à nouveau - l’index sera reconstruit.

Pour Firebird 3.0-5.0 - faites simplement ALTER INDEX nomindex ACTIVE

2. J’ai utilisé toutes les recommandations d’IBAnalyst mais cela n’aide pas à accélérer les requêtes.

R : C’est un problème distinct, où IBAnalyst ne peut pas aider. Il peut y avoir 2 causes à ce problème :

  1. Les index ont des statistiques obsolètes. Vous pouvez actualiser les statistiques des index avec la commande SET STATISTICS INDEX xxx (voir plus de détails http://www.ibase.ru/proc_selectivity/).

  2. Il n’existe tout simplement pas d’index approprié pour certaines conditions utilisées dans la requête.

  3. Les requêtes sont très complexes, ou l’optimiseur ne peut pas optimiser la requête, il est donc nécessaire de refactoriser la requête.

  4. Dans certains cas, vous verrez des « tables fragmentées » juste après la restauration.

Normalement, Firebird et InterBase (sans le paramètre -use_all_space) réservent environ 25 % d’espace sur les pages de données pour les insertions, mises à jour ou suppressions futures (pour placer les versions d’enregistrements). Mais, avec n’importe quelle taille de page de base de données (1, 2, 4 ou 8 ko), vous verrez environ 50 % de fragmentation pour les tables ayant une petite taille d’enregistrement (environ 12-20 octets, par exemple, une table avec 2 champs entiers a une taille moyenne d’enregistrement = 12 octets).

C’est normal, considérez cela comme un nombre magique du serveur (ou un comportement).

Donc, si vous avez de telles tables à petits enregistrements, vous pouvez :

a) ignorer l’avertissement « fragmenté » pour ces tables

b) abaisser le « % fragmenté » à 45 %, par exemple, dans la boîte de dialogue Options d’IBAnalyst.

4. Versions d’enregistrements pour une table qui ne doit pas être mise à jour

Si vous voyez des versions d’enregistrements sur une table qui ne doit pas être mise à jour (par exemple, une table avec un journal d’événements) - ne vous inquiétez pas, ces versions sont générées par les suppressions.

Ainsi, vous saurez combien d’enregistrements actuels se trouvent dans la table, et combien d’enregistrements ont été supprimés.

Cela n’est vrai que si MaxVer = 1. S’il est > 1, alors cette table est mise à jour par une application. Si vous êtes vraiment sûr que cette table ne doit jamais être mise à jour, il est préférable de définir un déclencheur « before update » avec une exception pour trouver quelle application effectue les mises à jour.

5. Les blobs peuvent provoquer une fragmentation des tables.

Le moteur stocke les blobs de 3 manières différentes :

  1. Si le contenu du blob tient sur la page de données (espace libre suffisant), il sera stocké sur cette page de données près de son enregistrement (ou de sa version).

  2. Si le contenu du blob ne tient pas sur la page de données, il sera stocké sur une page séparée.

  3. Si dans le cas 2 le blob ne tient pas sur une seule page de données, une page pointeur est créée pour pointer vers les pages de blob appropriées.

Le cas 1 se produit en fonction de la taille du blob stocké et de la taille de la page de base de données. Par exemple, si vous aviez une taille de page de 4 Ko et des blobs d’une taille moyenne d’environ 5 Ko, ils ne sont pas stockés sur les pages de données, mais sur des pages de blob supplémentaires.

Mais si vous sauvegardez votre base de données et la restaurez avec une taille de page de 8 Ko, les blobs tiendront sur la page de données, et ils seront stockés avec les enregistrements, provoquant une fragmentation élevée des enregistrements.

IBAnalyst marque ces tables comme Pale (colonne Enregistrements) et l’info-bulle montre le nombre estimé d’enregistrements pour cette table (basé sur le nombre de pages de données) et la valeur de remplissage moyenne réelle (%).

Si votre requête lit tous les champs sauf les blobs de cette table, une analyse naturelle, une jointure ou un agrégat s’exécutera très lentement.

La seule solution pour l’éviter : créer une table supplémentaire (liée 1-1 à la table d’origine) et y déplacer toutes les colonnes blob dont la taille moyenne est inférieure à la taille de la page.

Dans ce cas, n’essayez pas de sauvegarder/restaurer avec une taille de page plus grande ! Cela entraînera le placement des blobs qui ne pouvaient pas tenir sur les pages de données avec la taille de page actuelle, sur les pages de données lors de la restauration avec une taille de page plus grande. Ainsi, vos tables avec blobs seront plus fragmentées qu’avant.

Il n’est pas non plus recommandé de restaurer avec une taille de page plus petite, car cela peut diminuer les performances pour les index et les tables sans blobs.

Vous ne devez pas non plus essayer de changer les champs blob en champs varchar - les champs varchar sont toujours stockés comme faisant partie d’un enregistrement, donc un enregistrement peut avoir 2 fragments ou plus (être placé sur 2 pages de données ou plus) s’il ne tient pas sur une page de données.

p.s. IBAnalyst peut signaler ces tables « par erreur », par exemple, une table avait des champs blob avec des données, mais ils ont été supprimés de la structure de la table. Malheureusement, il n’y a pas d’option configurable pour cet avertissement, car nous le calculons exactement à partir des données rapportées par le serveur (statistiques).

6. Relation VerLen et RecLength

a) VerLen >= 90 % de RecLength : les versions que vous voyez dans la colonne Version sont principalement des suppressions d’enregistrements. Plus il y a d’enregistrements supprimés, plus RecLength sera petit (jusqu’à 0 octet). VerLen peut également être supérieur à RecLen si vous mettez à jour votre table avec des données chaîne plus grandes que celles stockées dans les enregistrements d’origine.

b) VerLen <= 80 % de RecLength : les versions sont principalement des mises à jour d’enregistrements.

Nous ne pouvons pas différencier ces cas plus précisément car les statistiques montrent la taille moyenne des enregistrements et des versions pour toute la table, tandis que le nombre de versions visibles pour les transactions concurrentes peut varier.

7. Pourquoi IBAnalyst qualifie-t-il certains index de « mauvais » ?

Les index ayant une valeur de sélectivité inférieure à 0,01 sont marqués comme « mauvais » dans IBAnalyst (voir l’aide de la vue Index). Il y a plusieurs causes pour qualifier un index particulier de mauvais :

  1. La sélectivité de cet index est inférieure à 0,01. Théoriquement, l’optimiseur ne doit pas utiliser cet index, mais il le fait si aucun autre index n’existe (pour where, order by ou la clause de jointure, au moins).

  2. Un tel index provoque une collecte des ordures très lente. Ce problème n’existe pas dans InterBase 7.1/7.5, et sera corrigé dans Firebird 2.0.

  3. Cet index rend le processus de restauration très lent, et il est créé très lentement (create/alter index active). Cela est dû au fait que la chaîne de numéros d’enregistrements est grande pour une clé d’index.

  4. Si cet index est utilisé dans une clause where, l’utilisation de la mémoire dépendra de la valeur recherchée (taille du masque binaire). Comme la chaîne d’enregistrements peut être grande (beaucoup de doublons de clés), la consommation de mémoire sera également importante.

  5. Si cet index est utilisé dans « order by », et qu’il y a beaucoup de doublons principalement dans les valeurs de clés inférieures (selon l’ordre de tri de l’index), il y aura beaucoup de lectures de pages d’index, ce qui ralentira la requête.

C’est parce qu’IBAnalyst ne peut pas ignorer l’existence de tels index.

Le pire cas pour un index est lorsqu’il a une colonne Uniques = 1, c’est-à-dire que toutes les valeurs de la colonne indexée sont identiques. Ces index sont répertoriés dans « Index inutiles » sur la page Résumé.

Bien sûr, pour votre application, un tel index peut être « bon ». Par exemple, si les enregistrements ont un indicateur « archive » dans une colonne, et que votre application recherche par index sur cette colonne uniquement les données actuelles, pas les données archivées. Ainsi, c’est à vous de décider si nous avons raison de qualifier cet index de « mauvais » ou non.

8. Que faire si un « mauvais » index est créé par une contrainte de clé étrangère ?

Eh bien, le paragraphe précédent montre qu’il est préférable de supprimer les « mauvais » index (si vous ne les utilisez pas pour rechercher des clés ayant moins de doublons que d’autres clés). Mais, si un tel index est créé par une clé étrangère, vous ne pouvez le supprimer qu’en supprimant la clé étrangère. La suppression de la clé étrangère désactivera la contrainte de vérification de relation, ce qui peut être inacceptable.

Vous pouvez remplacer la FK par des déclencheurs, mais avec certaines restrictions. La FK contrôle les relations entre enregistrements à l’aide de l’index, et l’index « voit » toutes les clés de tous les enregistrements indépendamment de l’état des transactions. Mais les déclencheurs ne fonctionnent que dans le contexte de la transaction du client. Ainsi, en remplaçant la FK par des déclencheurs, vous devez vous assurer que :

  • Les enregistrements ne seront pas supprimés de la table maître, ou seront supprimés en mode « snapshot table reserving »
  • La colonne, utilisée par la PK dans la table maître, ne sera jamais modifiée. Vous pouvez restreindre cela par un déclencheur before update.

Si vous maintenez ces conditions, vous pouvez supprimer une clé étrangère particulière. Bien sûr, ne créez pas manuellement d’index sur cette colonne.

9. Pourquoi dans la ligne de pourcentage de version des données n’y a-t-il que 12 mégaoctets de données, alors que j’ai une base de données de 140 mégaoctets ?

  1. IBAnalyst montre ici le volume de données « pur », sans compter les autres structures de la base de données (index, métadonnées…) et la fragmentation des pages.

  2. Après la restauration, InterBase et Firebird laissent un espace libre (15-25 %) sur les pages de données pour accélérer les futures mises à jour/suppressions.

  3. Il existe un comportement spécifique du serveur qui laisse les pages de données fragmentées à environ 50 %, si la taille des enregistrements de cette table est faible, environ 11-22 octets.

10. Comment améliorer les performances de l’optimiseur en cas de mises à jour fréquentes

Les statistiques d’index sont stockées dans la colonne RDB$INDICES.RDB$STATISTICS, et sont mises à jour de 3 manières :

  1. SET STATISTICS INDEX

  2. ALTER INDEX ACTIVE, ou CREATE INDEX …

  3. Le processus de restauration (tous les index sont reconstruits ainsi que « ALTER INDEX ACTIVE »)

L’optimiseur utilise ces informations de statistiques pour préparer les requêtes. En utilisant les valeurs de statistiques, l’optimiseur peut décider qu’un index est « assez bon » ou « inutile » pour récupérer des enregistrements.

Si les statistiques n’ont pas été mises à jour pendant une longue période, l’optimiseur peut produire un mauvais plan car les valeurs de statistiques existantes ne correspondent pas à l’état réel des choses, car les données de la table peuvent avoir été considérablement modifiées (par exemple, la quantité d’enregistrements a été multipliée par 5 à 10, ou vice versa, tous les enregistrements ont été supprimés).

Vous pouvez remplacer le mauvais plan de requête automatique par un PLAN explicite pour une requête particulière, mais ce n’est pas une bonne approche, car les données peuvent être considérablement modifiées après l’élaboration du plan.

Une autre façon (et la bonne) est d’actualiser périodiquement les statistiques en appliquant l’instruction SET STATISTICS pour tous les index. Vous pouvez planifier l’exécution d’un script SQL pour actualiser les statistiques à l’aide d’ISQL ou de l’outil prêt à l’emploi gidx (Windows uniquement).

Si vous avez des tables avec des enregistrements différents rechargés périodiquement, cette approche ne vous aidera pas. Prenons l’exemple :

  • La table A est chargée avec des données 4 à 5 fois par jour.
  • Après le traitement des données chargées, tous les enregistrements de la table A sont supprimés.

Dans ce cas, nous pouvons voir 2 valeurs de statistiques correctes pour les index de la table A - lorsqu’elle est chargée avec des données, et lorsqu’elle est vide. Ainsi, les statistiques recalculées sur la table chargée seront inutiles lorsque la table est vide, et vice versa.

Pour éviter cela, vous devez recalculer les statistiques pour les index de la table A uniquement lorsque la table est remplie de données. Le mieux est de le faire avant que les requêtes sur cette table ne soient exécutées.

Depuis la version 1.91, IBAnalyst montre la différence de statistiques d’index et vous permet de les recalculer à tout moment. Vous devez d’abord examiner les informations sur les enregistrements de la table - s’agit-il du nombre moyen habituel d’enregistrements ou non. Si oui, vous pouvez recalculer la sélectivité de l’index en toute confiance. Sinon - il serait peut-être préférable de ne pas toucher aux statistiques d’index, car cela pourrait amener l’optimiseur à produire des plans de requête encore pires.