Exemple d'analyse des performances

Pour suivre les instructions vidéo, ouvrez l’exemple de rapport ici.

Comment interpréter le rapport de performance

En utilisant HQbird, ou, en tant que service séparé, l’Analyse de Performance IBSurgeon depuis cc.ib-aid.com, vous pouvez générer un rapport de performance à partir des journaux de trace Firebird.

Ce rapport est un outil de diagnostic puissant qui fournit des informations détaillées sur l’exécution des requêtes SQL dans les bases de données Firebird. Ce guide explique comment interpréter et utiliser les rapports de trace pour identifier et résoudre systématiquement les goulots d’étranglement de performance.

1. Structure du rapport de performance

┌─────────────────────────────────────────┐
│         Rapport de performance          │
├─────────────────────────────────────────┤
│ 1. Graphiques de synthèse des performances│
│    ┌────────────────────────┐           │
│    │  Requêtes principales  │           │
│    │     Résumé principal   │           │
│    │     Fréquence principale│           │
│    │     Durées             │           │
│    │     Récupérations      │           │
│    │     Lectures           │           │
│    │     Écritures          │           │
│    │  Graphique de séries temporelles│   │
│    │     Durées             │           │
│    │     Nombre de requêtes │           │
│    │     Récupérations      │           │
│    │     Lectures/Écritures │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 2. Analyse des requêtes principales     │
│    ┌────────────────────────┐           │
│    │ Classements des requêtes│          │
│    │                        │           │
│    │  Par durée ───┐        │           │
│    │               │        │           │
│    │  Par temps ───┤        │           │
│    │  Résumé       │        │           │
│    │               │        │           │
│    │  Par plan ────┤        │           │
│    │  Résumé       │        │           │
│    │               │        │           │
│    │  Par fréquence ┤       │           │
│    │               │        │           │
│    │  Par plan ────┤        │           │
│    │  Fréquence    │        │           │
│    │               │        │           │
│    │  Par récupérations ───┤│           │
│    │               │        │           │
│    │  Par lectures ───┤     │           │
│    │               │        │           │
│    │  Par écritures ───┘    │           │
│    │                        │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 3. Résumé des processus                 │
│    ┌────────────────────────┐           │
│    │ Statistiques par processus│        │
│    │ - Nombre d'exécutions  │           │
│    │ - Récupérations, etc.  │           │
│    │ - Métriques de durée   │           │
│    └────────────────────────┘           │
├─────────────────────────────────────────┤
│ 4. Résumé des adresses                  │
│    ┌────────────────────────┐           │
│    │Statistiques par adresse client│    │
│    │ - Nombre de connexions │           │
│    │ - Durées               │           │
│    │ - Récupérations, etc.  │           │
│    │ - Noms des processus   │           │
│    └────────────────────────┘           │
└─────────────────────────────────────────┘

Structure des détails de requête :
┌────────────────────┐
│ Informations sur la requête│
├────────────────────┤
│ - Texte SQL        │
│ - Informations de transaction│
│ - Plan d'exécution │
│ - Statistiques de durée│
│ - Statistiques de ressources│
│   * Récupérations  │
│   * Lectures       │
│   * Écritures      │
│   * Marques        │
│ - Informations client│
└────────────────────┘

Le rapport de performance fournit une vue hiérarchique de l’activité de la base de données :

1. Graphiques de synthèse des performances

• Représentation visuelle des métriques clés dans le temps - vous pouvez facilement voir les pics d’activité/charge. (Il existe également un rapport d’analyse par minute disponible dans la Surveillance Avancée des Performances dans HQbird, dont la version abrégée est disponible sur l’outil Portail - voir cette vidéo pour plus de détails).

• Aide à identifier les modèles et anomalies : comparer les graphiques des périodes avec de bonnes performances (par exemple, la semaine/le mois dernier) avec les problèmes de performance peut aider à identifier le problème.

2. Analyse des requêtes principales

• Perspectives de classement multiples pour une analyse complète : les requêtes les plus longues, les requêtes les plus fréquentes, les requêtes les plus chronophages (groupées par texte ou plan), et plus encore.

• Chaque dimension révèle différentes opportunités d’optimisation

• Statistiques détaillées pour chaque requête, y compris :

• Métriques de durée (min, max, moyenne, médiane)

• Consommation de ressources (récupérations, lectures, écritures)

• Modèles d’exécution - nombre d’origines pour les requêtes principales.

3. Résumé des processus

• Regroupe les statistiques par processus d’exécution

• Aide à identifier les applications problématiques

• Montre la consommation de ressources et les opérations de base de données (connexions, requêtes, etc.) et les métriques (récupérations, lectures, etc.) par processus

4. Résumé des adresses

• Regroupe les statistiques par connexion client

• Révèle la distribution de la charge entre les clients

• Aide à identifier les problèmes spécifiques aux connexions

Chaque section soutient l’analyse de performance à différents niveaux :

• Modèles à l’échelle du système (Graphiques) - voir quand et où les problèmes se produisent en général.

• Impact le plus notable des requêtes (Requêtes principales) - identifier les requêtes qui devraient être optimisées en premier.

• Problèmes au niveau de l’application (Résumé des processus) - identifier les applications qui produisent des problèmes de performance.

• Problèmes au niveau du client (Résumé des adresses) - identifier les adresses IP (postes de travail, ordinateurs clients) avec le plus grand flux de requêtes.

2. Analyse du résumé temporel et du résumé par plan

Il est recommandé de commencer l’analyse de la situation de performance par les sections Résumé. Cliquez ici pour ouvrir la section Résumé par plan dans le rapport d’exemple.

Le résumé temporel agrège le temps d’exécution total pour chaque modèle d’instruction SQL unique.

Considérez-le comme un rapport de “centre de coûts” qui montre quelles requêtes consomment le plus de ressources de base de données dans le temps.

Si les requêtes ne sont pas paramétrées, c'est-à-dire qu'elles contiennent explicitement les valeurs des paramètres dans le texte SQL au lieu d'un espace réservé de paramètre (:myparam1), il est nécessaire d'utiliser la section "Résumé par plan" pour identifier les requêtes avec la fréquence la plus élevée.
Exemple de requête non paramétrée : 'SELECT * FROM COUNTRY WHERE COUNTRYID=2'
Exemple de requête paramétrée : 'SELECT * FROM COUNTRY WHERE COUNTRYID=:paramid'

Chaque requête dans la section Résumé possède un en-tête avec les parties clés suivantes :

• Résumé : Pourcentage du temps total, il montre quelle partie du temps global de la base de données une requête consomme.

• Fréquence : Combien de fois le modèle de requête apparaît

• Récupération, Lecture, Écriture : Métriques de ressources

Par exemple, s’il y a

Résumé : 19,08 % (3920272 sur 20541791 ms)

Cela nous indique que ce modèle de requête consomme près de 20 % du temps total de la base de données - une part significative qui mérite une attention immédiate.

Sous l’en-tête dans la section Résumé par plan, nous verrons le plan d’exécution SQL qui a été utilisé pour regrouper les requêtes ; dans le Résumé temporel, ce sera le texte de la requête elle-même.

Puisque le modèle de requête représente plus d’une requête spécifique, les informations spécifiques à la connexion sont tirées de la première requête qui correspond au modèle :

Sur la capture d’écran ci-dessus, vous pouvez voir l’en-tête de l’instruction exemple pour le modèle ; il se compose du nom de l’application qui a lancé ce SQL, de l’ID de connexion et de l’ID de transaction, ainsi que de l’adresse IP et des détails de la transaction.

Ensuite viennent le plan (pour le Résumé temporel ; pour le Résumé par plan, il est omis car il est déjà affiché au début), les valeurs des paramètres (dans l’ordre d’apparition) et les statistiques par table :

Veuillez vous rappeler que dans le Résumé par plan, nous regroupons les SQL en utilisant le plan d’exécution, ce qui signifie que seul le plan est persistant pour le modèle ; et pour le Résumé temporel, nous regroupons en utilisant le texte de l’instruction SQL, et d’autres éléments (valeurs des paramètres, temps d’exécution, etc.) peuvent différer. Utilisez ces informations comme exemple du modèle d’exécution (dans 99 % des cas, cela suffit pour reproduire le problème).

Ci-dessous, nous avons un graphique individuel avec les exécutions de cette requête spécifique. Comme vous pouvez le voir, cette requête a été démarrée pendant la période de forte charge que nous avons remarquée sur le graphique d’ensemble.

Et, à la fin, nous avons une collection très importante de statistiques pour TOUTES les requêtes qui correspondent au modèle, ainsi qu’une liste des adresses d’origine :

Dans ces statistiques, nous pouvons voir les temps d’exécution minimum, maximum, moyen et médian, ainsi que les mêmes statistiques pour les récupérations, lectures, écritures et marques (opérations de vidage du cache).

2.1. Comment utiliser le résumé temporel :

• Identifiez d’abord les requêtes qui consomment un temps disproportionné (ce sont les 3 premières de cette section - #1, 2, 3)

• Comparez la consommation de temps avec la fréquence

• Consultez le temps d’exécution moyen (temps total / fréquence) en bas de la section de la requête (voir ci-dessous)

• Recherchez des modèles où :

• Temps élevé + Fréquence faible = Requêtes individuelles inefficaces

• Temps élevé + Fréquence élevée = Requêtes potentiellement inefficaces mais fortement utilisées

3. Analyse de fréquence : Fréquence et Fréquence par plan

Utilisez l’analyse de fréquence pour comprendre à quelle fréquence les requêtes s’exécutent. Considérez cela comme le comptage du nombre de fois où une route particulière est utilisée pendant les heures de pointe.

Si les requêtes ne sont pas paramétrées, c'est-à-dire qu'elles contiennent explicitement les valeurs des paramètres dans le texte SQL au lieu d'un espace réservé de paramètre (:myparam1), il est nécessaire d'utiliser la section "Résumé par plan" pour identifier les requêtes avec la fréquence la plus élevée.
Exemple de requête non paramétrée : 'SELECT * FROM COUNTRY WHERE COUNTRYID=2'
Exemple de requête paramétrée : 'SELECT * FROM COUNTRY WHERE COUNTRYID=:paramid'

3.1. Comprendre l’impact de la fréquence

La représentation du modèle de requête Fréquence est très similaire au Résumé par plan/temporel :

Les requêtes à haute fréquence sont comme des intersections très fréquentées - même si chaque voiture (requête) se déplace rapidement, le volume pur peut provoquer une congestion. Cela affecte :

• Les connexions à la base de données (comme les places de stationnement - limitées en nombre)

• La bande passante réseau (comme la capacité de la route)

• Utilisation du processeur (comme des contrôleurs de trafic submergés)

• Efficacité du cache (comme devoir accéder à plusieurs reprises aux mêmes informations)

Pour estimer l'impact des requêtes à haute fréquence, collectez une trace avec un seuil de paramètre = 0.

3.2. Catégories d’impact de fréquence

3.3. Exemple d’analyse

Examinons un cas réel de notre rapport de trace :

Fréquence : 4 428 exécutions (24,43 % du total)
Impact : Critique - volume élevé de requêtes sur la table SALES
Cause racine : Vérifications répétitives du solde client
Priorité d'optimisation : Élevée

Explication : Cette requête s'exécute des milliers de fois, comme une
intersection très fréquentée. Même si chaque exécution peut être rapide,
l'impact cumulé est significatif. L'application pourrait vérifier les
soldes plus souvent que nécessaire.

4. Analyse des statistiques des requêtes principales dans les sections xx-Summary et Frequency

Lors de l’analyse des rapports de trace Firebird, chaque regroupement de requêtes contient des statistiques agrégées détaillées qui fournissent des informations cruciales sur les schémas de performance. Décomposons chaque métrique et comprenons son importance pour l’optimisation de la base de données.

4.1. Analyse des statistiques agrégées

Examinons cet exemple de statistiques :

Total : 4428 éléments :
Durées : min : 351 ; max : 3919 ; moy : 457,70 ; médiane : 455,00 ; somme : 2026710 (20,29 %) ;
Fetchs : min : 7135 ; max : 7168 ; moy : 7146,86 ; médiane : 7147,00 ; somme : 31646289 (0,75 %) ;
Écritures : min : 0 ; max : 0 ; moy : 0,00 ; médiane : 0,00 ; somme : 0 (0,00 %) ;
Lectures : min : 0 ; max : 6995 ; moy : 3,13 ; médiane : 0,00 ; somme : 13856 (8,22 %) ;
Marques : min : 0 ; max : 0 ; moy : 0,00 ; médiane : 0,00 ; somme : 0 (0,00 %) ;
Depuis 1 adresse unique : TCPv6:::1 (4428)

4.2. Analyse du nombre d’exécutions

4.2.1. Éléments totaux

Total : 4428 éléments

Cela représente le nombre de fois que ce schéma de requête particulier a été exécuté pendant la période de trace.

Comprendre ce nombre vous aide à :

• Calculer l’utilisation des ressources par exécution

• Déterminer si la mise en cache des requêtes pourrait être bénéfique (ou simplement l’exécuter moins fréquemment)

Des nombres d’exécutions élevés peuvent indiquer des opportunités pour :

• Implémenter des instructions préparées (et paramétrées) - la même requête avec la même fréquence, lorsqu’elle est paramétrée et préparée pour une exécution répétitive, nécessitera moins de ressources

• Ajouter une mise en cache des résultats - mettre en cache la valeur résultante pour une utilisation pendant l’opération longue ou même plus longtemps, pour la session de l’utilisateur, peut réduire la nécessité d’exécuter fréquemment la requête

• Regrouper les opérations - envisagez d’exécuter la requête pour retourner ou traiter plusieurs enregistrements à la fois, cela éliminera les frais généraux d’exécution de la requête (préparation, transmission réseau, etc.).

4.3. Métriques de durée

4.3.1. Exemple de composants de durée

Durées : min : 351 ; max : 3919 ; moy : 457,70 ; médiane : 455,00 ; somme : 2026710 (20,29 %) ;

4.3.2. Analyse de la durée

• Médiane et moyenne proches (457,70 vs 455,00 ms) suggèrent des performances constantes

• Le rapport max/min (~11x) indique une certaine variabilité

• 20,29 % du temps total est significatif - cette requête est-elle dans le top 3 de la section Frequency ou Plan-Frequency ? (oui, elle l’est.)

4.4. Métriques d’utilisation des ressources

4.4.1. Opérations de fetch

Fetchs : min : 7135 ; max : 7168 ; moy : 7146,86 ; médiane : 7147,00 ; somme : 31646289 (0,75 %) ;

Les fetchs représentent les récupérations de lignes :

• Des nombres de fetchs cohérents (différence min/max de seulement 33) suggèrent des ensembles de résultats stables

• Des nombres de fetchs relativement élevés (>7000 par exécution) peuvent indiquer :

• Besoin de limitation de l’ensemble de résultats et/ou de pagination, si de nombreux enregistrements sont retournés.

• Potentiel d’optimisation de la requête - particulièrement pertinent si la requête est dans le top 3 de Frequency/Plan-Frequency.

4.5. Opérations de lecture

Lectures : min : 0 ; max : 6995 ; moy : 3,13 ; médiane : 0,00 ; somme : 13856 (8,22 %) ;

Les lectures physiques indiquent l’accès au disque :

• Médiane nulle avec maximum non nul suggère des échecs de cache occasionnels

• 8,22 % du total des lectures indique un impact d’E/S modéré

• Un grand écart entre min (0) et max (6995) suggère une efficacité variable du cache.

4.6. Opérations d’écriture

Écritures : min : 0 ; max : 0 ; moy : 0,00 ; médiane : 0,00 ; somme : 0 (0,00 %) ;

Si la requête n’effectue aucune écriture, il s’agit généralement d’une opération en lecture seule.

4.7. Opérations de marquage

Marques : min : 0 ; max : 0 ; moy : 0,00 ; médiane : 0,00 ; somme : 0 (0,00 %) ;

Les opérations de marquage sont liées à la gestion du cache des pages de données :

• Zéro marque indique qu’aucune page de données n’a été marquée pour vidage, courant pour les requêtes SELECT simples

• Des opérations de marquage non nulles avec le cache

4.8. Analyse de la connexion client

Depuis 1 adresse unique : TCPv6:::1 (4428)

Cela montre la distribution de la source des requêtes :

• Une adresse client unique suggère une requête spécifique à l’application

• Connexion locale (::1 est localhost IPv6)

• Les 4428 exécutions proviennent de la même source

4.9. Utilisation de ces métriques pour l’optimisation

4.9.1. Analyse des schémas de performance

Cohérence d’exécution

• Comparez les durées min/max

• Recherchez les valeurs aberrantes dans l’utilisation des ressources

• Vérifiez la médiane par rapport à la moyenne pour la variabilité

Schémas d’utilisation des ressources

• Fetchs élevés → Examinez la taille de l’ensemble de résultats

• Lectures élevées → Vérifiez la couverture des index

• Marques élevées → Examinez la contention de verrouillage

Analyse de l’impact client

• Plusieurs clients → Dimensionnement du pool de connexions

• Client unique → Optimisation de l’application

4.9.2. Priorités d’optimisation

Sur la base de ces métriques, priorisez :

• Taille de l’ensemble de résultats

• 7000 fetchs par exécution

• Envisagez d’ajouter LIMIT/OFFSET

• Examinez la liste des colonnes SELECT

Stratégie de mise en cache

• Exécution fréquente (4428 fois)

• Taille de résultat cohérente

• Aucune écriture impliquée

Probablement, cette requête peut être exécutée moins fréquemment.

Utilisation des index

• Nombres de lectures variables

• Lectures médianes nulles mais maximum élevé

• Examinez la couverture des index

5. Application pratique

Pour cet exemple spécifique :

Améliorations à court terme :

• Implémentez la mise en cache des résultats (nombre d’exécutions élevé, fetchs cohérents)

• Examinez la taille de l’ensemble de résultats (>7000 fetchs par exécution)

Optimisation à moyen terme :

• Analysez les schémas d’utilisation des index

• Envisagez l’utilisation d’instructions préparées

• Examinez la logique applicative pour la fréquence d’exécution

Considérations à long terme :

• Surveillez les schémas d’exécution au fil du temps

• Planifiez une stratégie de maintenance des index

• Envisagez des changements dans les schémas d’accès aux données

6. Analyse de la durée

L’analyse de la durée examine combien de temps les requêtes individuelles prennent pour s’exécuter. Pensez à la durée comme à un chronomètre qui mesure chaque requête - plus une requête prend de temps, plus elle est susceptible de causer des problèmes de performance.

6.1. Comprendre les métriques de durée

Les métriques de durée sont cruciales car elles affectent directement l’expérience des utilisateurs, c’est-à-dire que les utilisateurs disent “le système est lent”. Tout comme les clients sont frustrés d’attendre dans une longue file, les utilisateurs sont frustrés lorsque les requêtes prennent trop de temps à s’exécuter. Les requêtes de longue durée provoquent :

• Une mauvaise expérience utilisateur lorsque les écrans mettent trop de temps à charger

• Des ressources système mobilisées pendant de longues périodes

• D’autres requêtes qui attendent en file derrière les requêtes lentes

• Des problèmes potentiels de délai d’attente dans les applications

6.2. Catégories d’impact

6.3. Exemple d’analyse

Durée : 77 793 ms
Impact : Critique - une seule requête consommant 77,7 secondes
Cause racine : Agrégation complexe dans PRC_COLLECT_RANKCATEGORY
Priorité d'optimisation : Immédiate

Explication : Cette requête prend plus d'une minute à s'exécuter, ce qui est
comme un arrêt complet de la circulation. La procédure stockée traite
probablement trop de données ou utilise des algorithmes inefficaces.

7. Stratégie d’implémentation

Pensez à l’optimisation comme à l’amélioration d’un système de transport - vous devez identifier les problèmes, planifier les solutions et implémenter les changements avec soin.

7.1. Matrice de priorisation

Cette matrice vous aide à décider ce qui nécessite une attention en premier, comme le triage des problèmes de trafic dans une ville :

7.2. Processus d’optimisation étape par étape

1. Identifiez les requêtes critiques

• Recherchez les plus grands embouteillages (requêtes lentes)

• Trouvez les intersections les plus fréquentées (requêtes à haute fréquence)

• Repérez les itinéraires inefficaces (utilisation élevée des ressources)

2. Analysez les plans d’exécution

• Étudiez les itinéraires actuels (utilisation des index)

• Examinez les schémas de trafic (méthodes de jointure)

• Vérifiez les goulots d’étranglement (opérations de tri)

3. Implémentez les optimisations

• Construisez de nouvelles routes (index)

• Redessinez les itinéraires (restructurer les requêtes)

• Ajoutez des raccourcis (mise en cache)

4. Vérifiez les améliorations

• Mesurez le nouveau flux de trafic (nouveau rapport de trace)

• Comparez les métriques avant/après

• Documentez ce qui a fonctionné

Contactez IBSurgeon pour toute question

N’hésitez pas à nous contacter pour toute question : [email protected].