Optimiser les performances de la base de données en comparant les instantanés de performances

Ce document explique comment identifier et atténuer les problèmes de performances de la base de données AlloyDB Omni à l'aide d'un rapport qui compare les instantanés des métriques système entre deux points dans le temps. Les métriques système capturées dans chaque instantané incluent l'utilisation des processeurs virtuels (vCPU), l'utilisation de la mémoire, les E/S de disque, le nombre de transactions et les événements d'attente.

Fonctionnement des rapports "Instantané des performances"

Les rapports "Instantané des performances" sont un outil AlloyDB Omni intégré qui capture et analyse les données de performances pour vous aider à identifier la cause des problèmes de performances. Cet outil complète les autres fonctionnalités d'observabilité AlloyDB Omni, telles que les insights sur les systèmes, les insights sur les requêtes et l'explorateur de métriques, qui fournissent des métriques en temps réel sur votre instance.

Les rapports "Performance Snapshot" affichent les métriques de base de données entre deux horodatages dans un seul rapport. Vous pouvez utiliser les informations du rapport sur l'instantané des performances pour identifier les problèmes de performances de votre instance de rapport sur l'instantané des performances, comme une diminution des performances de la base de données à certaines heures de la journée ou une diminution des performances sur une période donnée.

Le rapport "Instantané des performances" vous permet de comparer les métriques à un niveau de référence pour obtenir des insights sur les métriques de performances de la charge de travail, que vous pouvez utiliser pour optimiser ou résoudre les problèmes de performances de la base de données. Une ligne de base est un ensemble personnalisé d'instantanés de base de données qui mesure les performances et le comportement standards d'une base de données pour une configuration et une charge de travail spécifiques.

Pour en savoir plus sur les événements d'attente dans le rapport "Instantané des performances de la base de données", consultez la documentation de référence sur le rapport "Instantané des performances de la base de données".

Rôles requis

Assurez-vous de disposer du rôle alloydbsuperuser. Par défaut, AlloyDB attribue le rôle pg_monitor à alloydbsuperuser. Pour en savoir plus, consultez la section Rôles prédéfinis PostgreSQL.

Si vous préférez utiliser vos autres rôles définis par vous-même, exécutez d'abord GRANT pg_monitor TO my_user en tant que alloydbsuperuser. Pour en savoir plus, consultez Modifier un compte Identity and Access Management (IAM) avec le rôle approprié.

Installer le rapport "Instantané des performances"

perfsnap est le nom du schéma qui contient des fonctions SQL permettant aux utilisateurs de capturer des instantanés ou de générer des rapports. Ce schéma fait partie de l'extension g_stats d'AlloyDB Omni. Utilisez le rôle super-utilisateur pour installer le rapport "Performance Snapshot".

Pour utiliser les API perfsnap, connectez-vous à une base de données dans laquelle les utilisateurs souhaitent installer l'extension, puis créez l'extension g_stats à l'aide de la commande suivante:

CREATE EXTENSION IF NOT EXISTS g_stats;

Créer un instantané des métriques système

Créez un instantané au début et à la fin de la charge de travail qui vous intéresse. L'intervalle de temps entre les deux instantanés permet à la charge de travail de progresser suffisamment pour que le système puisse accumuler des métriques qui reflètent la charge de travail. Une fois que vous avez obtenu les métriques du rapport sur l'instantané des performances, vous pouvez prendre un autre ensemble d'instantanés et répéter le processus.

  1. Connectez un client psql à une instance AlloyDB.
  2. Exécutez SELECT perfsnap.snap(). La sortie ressemble à ceci :

    postgres=# select perfsnap.snap();
     snap
    ------
        1
    (1 row)
    

Afficher la liste des instantanés

  1. Connectez un client psql à une instance AlloyDB Omni.
  2. Exécutez SELECT * FROM perfsnap.g$snapshots. La sortie ressemble à ceci:

    postgres=# select * from perfsnap.g$snapshots;
     snap_id |           snap_time           | instance_id | node_id | snap_description | snap_type | is_baseline
    ---------+-------------------------------+-------------+---------+------------------+-----------+-------------
           1 | 2023-11-13 22:13:43.159237+00 | sr-primary  |         | Manual snapshot  | Manual    | f
           2 | 2023-11-13 22:53:40.49565+00  | sr-primary  |         | Automatic snapshot| Automatic | f
    (2 rows)
    

Générer un rapport instantané

Par exemple, pour générer un rapport qui capture la différence entre les instantanés 1 et 2, exécutez
SELECT perfsnap.report(1,2).

Le deuxième instantané d'une opération différentielle n'a pas besoin de suivre immédiatement le premier. Toutefois, assurez-vous de capturer le deuxième instantané dans la différence après le premier.

Le rapport d'instantané des performances généré ressemble à l'exemple abrégé suivant:

Exemple de rapport "Instantané des performances"

$ psql -d postgres -U alloydbsuperuser
postgres=> select perfsnap.report(22, 23);

report
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 PGSNAP DB Report for:

 Snapshot details
 --------------------------------------
 Host                   i841-sr-primary-2a34f46e-06bc
 Release                14.12
 Startup Time           2024-10-08 03:23:15+00

              Snap Id    Snap Time
 ------------ ---------- ------------------------
 Begin Snap:          22 24.10.2024 04:33:56 (UTC) Automatic snapshot
   End Snap:          23 25.10.2024 04:38:56 (UTC) Automatic snapshot
    Elapsed:                      1 day 00:04:59.979321

 Database Cache sizes
 ~~~~~~~~~~~~~
            Shared Buffers:       31 GB        Block Size:         8192
      Effective Cache Size:       25 GB       WAL Buffers:        16384

 Host CPU
 ~~~~~~~~~~
       %User   %Nice %System   %Idle    %WIO    %IRQ   %SIRQ  %Steal  %Guest
     ------- ------- ------- ------- ------- ------- ------- ------- -------
        1.07    0.22    0.91   97.40    0.09    0.00    0.31    0.00    0.00

 Host Memory
 ~~~~~~~~~~~~
              Total Memory:       63 GB
          Available Memory:       11 GB
               Free Memory:      726 MB
            Buffers Memory:     3706 MB

 Load profile (in bytes)
 ~~~~~~~~~~~~~~~~~~~~~~~            Per Second         Per Transaction
                                    ------------       ---------------
                     Redo size:         63083.64               4489.93
                 Logical reads:          1961.21                139.59
                 ...

 Response Time Profile (in s)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 CPU time:               5399 (   0.39%)
 Wait time:           1386906 (  99.61%)
 Total time:           1392306

 Backend Processes Wait Class Breakdown (in s)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 IO                   119.300 (  98.91%)
 LWLock                 1.305 (   1.08%)
 IPC                     .010 (   0.01%)
 Lock                    .000 (   0.00%)

 Backend Processes Wait Information
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Event                                          Class         Waits      Time (us)      Avg (us)
 -------------------------------------- ------------- ------------- -------------- -------------
 CPU                                                                    1995948632
 WALInsert                                     LWLock             1           6656          6656

 Vacuum Information
 ~~~~~~~~~~~~~~~~~~~
             Num Analyze operations:             1976
              Num Vacuum operations:             3435

 Per Database Information
 ~~~~~~~~~~~~~~~~~~~~~~~~~
 Name                      Commits       Rollbacks     BlkRds        Blkhits       TempFiles     TempBytes
 ------------------------- ------------- ------------- ------------- ------------- ------------- -------------
 bench                             27939             0             0       7823038             0       0 bytes
 postgres                          39792             0             7      11089243             0       0 bytes

 Per Database DML & DQL Information
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Name                      Tuples returned  Tuples fetched   Tuples inserted  Tuples updated   Tuples deleted   Index splits     Index Only heap fetches   HOT updates
 ------------------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ------------------------- ----------------
 bench                             16119481          4843262                0                0                0                0                        16                0
 postgres                          25415473          6327188                0               10                0                0                         0                8

 Per Database Conflict Information
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Name                      Lock Timeout  Old Snapshot  Buffer Pins   Deadlock
 ------------------------- ------------- ------------- ------------- -------------
 bench                                 0             0             0             0
 postgres                              0             0             0             0

 Per Database Vacuum Information
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Name                      Frozen XID    % Consumed    Aggregate Vacuum Gap
 ------------------------- ------------- ------------- --------------------
 bench                         179460916         9.00%         20539084
 postgres                      179339239         9.00%         20660761

 Per Database Sizing Information
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                    Conn.
 Name                 Collation     Limit   Tablespace           DB Size    Growth
 -------------------- ------------- ------- -------------------- ---------- ----------
 bench                C.UTF-8            -1 pg_default                80 GB    0 bytes
 postgres             C.UTF-8            -1 pg_default               135 MB    0 bytes

 Backend Wait Event Histogram
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Event                                          Class       Waits    <= 1us    <= 2us    <= 4us    <= 8us   <= 16us   <= 32us   <= 64us  <= 128us  <= 256us  <= 512us
 -------------------------------------- ------------- ----------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------
 WALInsert                                  LWLock             1         0         0         0         0         0         0         0         0         0         0

 Background Wait Event Histogram
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Event                                          Class       Waits    <= 1us    <= 2us    <= 4us    <= 8us   <= 16us   <= 32us   <= 64us  <= 128us  <= 256us  <= 512us
 -------------------------------------- ------------- ----------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------
 WALInsert                                  LWLock           542       107       174        39       113        93         8         1         1         0         1

 Write Ahead Log (WAL) Statistics
 --------------------------------
 Records       Full Page Images   Bytes        Buffers Full   Write         Sync          Write Time    Sync Time
 -----------   ----------------   -----------  ------------   -----------   -----------   -----------   -----------
     2936305                100     805989345             0             0             0             0             0

 Summary Stats (across all databases)
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Name                             Value
 -------------------------------- ----------------------------------
 Buffers evicted                  0
 Commits                          1216693
 ...

 Parameter Settings
 ~~~~~~~~~~~~~~~~~~~
 Parameter                         Value
 --------------------------------- --------------------------------------------------------------
 DateStyle                            ISO, MDY
 TimeZone                             UTC
 autovacuum                           on
 work_mem                             4096

 Columnar Engine available size  Columnar Engine configured size
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                       14959MB                         19293MB

 Columnar Engine Statistics
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 name                                                       count
 ---------------------------------------------------------- ------------
 CU Populations/Refreshes                                          13197
 CU Auto Refreshes                                                 10975
 ...
 Columnar Engine Ultra-fast Cache Statistics
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Ultra-fast Cache Size (MB):                        19200
 Ultra-fast Cache Used Size (MB):                       0
 Ultra-fast Cache Block Size (MB):                     80

 ----------------------------------------------------
 Created by G_STATS v1.0.100
 ----------------------------------------------------
(xxx rows)

  

Pour en savoir plus sur les champs de rapport et les recommandations d'optimisation des performances, consultez la section Recommandations d'optimisation des performances des bases de données. Pour en savoir plus sur les événements d'attente dans les rapports sur les instantanés de performances, consultez la documentation de référence sur les rapports sur les instantanés de performances de la base de données.

Supprimer un instantané

Avant de pouvoir supprimer des instantanés qui font partie d'une référence existante, vous devez effacer la référence .

Pour supprimer un instantané, exécutez SELECT perfsnap.delete(n). Une fois un instantané supprimé, vous ne pouvez pas le récupérer.

Marquer un instantané comme référence de performances

Par exemple, pour marquer tous les instantanés dont l'ID est compris entre 1 et 3 comme référence des performances système, exécutez
SELECT perfsnap.make_baseline(1, 3).

Effacer les références de performances

Pour effacer toutes les références avec des ID compris entre 1 et 3, par exemple, exécutez SELECT perfsnap.clear_baseline(1, 3).

Optimiser les performances de la base de données à l'aide des résultats du rapport "Instantané"

Pour optimiser les performances de la base de données AlloyDB, procédez comme suit:

  1. Créez des instantanés de référence lorsque votre base de données est inactive ou qu'elle subit une charge moyenne.
  2. Démarrez la charge de travail ou la requête dont vous souhaitez améliorer les performances.
  3. Lorsque la charge de travail ou la requête atteint le pic d'utilisation des ressources, créez un autre ensemble d'instantanés. Nous vous recommandons d'utiliser le même intervalle pour les deux rapports.
  4. Comparez les rapports que vous avez créés avec les deux ensembles d'instantanés et identifiez les modifications susceptibles d'améliorer les performances. Pour en savoir plus sur les recommandations de performances, consultez la section Recommandations d'optimisation des performances de la base de données.

Recommandations d'optimisation des performances de la base de données

Le tableau suivant liste les sections du rapport "Instantané des performances" et les améliorations recommandées pour chaque section. Pour en savoir plus sur les sections du rapport sur les instantanés de performances et les événements d'attente, consultez la documentation de référence sur le rapport sur les instantanés de performances de la base de données.

Section Champ du rapport Description des champs des rapports Recommandations d'optimisation
Détails de l'instantané Détails de l'instantané Indique l'hôte, la version compatible avec PostgreSQL et l'heure à laquelle la machine est opérationnelle. N/A
ID de l'instantané Indique l'ID et le point temporel des instantanés utilisés pour créer ce rapport. N/A
Insights sur le système Processeur hôte Détails de l'utilisation du processeur hôte. Si l'utilisation du processeur est supérieure à 80%, nous vous recommandons de passer à un système comportant davantage de processeurs virtuels.
Mémoire de l'hôte Détails de l'utilisation de la mémoire de l'hôte. Si la mémoire disponible est inférieure à 15%, nous vous recommandons d'augmenter la taille à la prochaine taille disponible.
Profil de charge Répertorie les compteurs qui aident à caractériser votre charge de travail en termes de journalisation WAL (Write-Ahead Logging) générée, d'exigences d'E/S et de gestion des connexions. Si les lectures physiques sont supérieures aux lectures logiques, envisagez d'augmenter la taille à la prochaine taille disponible pour permettre un cache de données plus important.
Répartition du temps de réponse et des classes d'attente Répartition du temps passé par les processus Postgres pendant l'exécution de la charge de travail. Concentrez-vous sur le raccourcissement de l'attente d'E/S si les processus sont principalement dans un état d'attente, par exemple.
Informations sur la charge de travail de la base de données Informations sur la charge de travail par base de données Métriques clés pour chaque base de données, y compris les commits, les rollbacks, le ratio de réussite et des informations sur les tables temporaires et les opérations de tri. Si les rollbacks sont élevés, envisagez de diagnostiquer votre application.
Informations sur le LMD et le DQL par base de données Compteurs pour les opérations de requête. Déterminez si votre charge de travail est axée sur la lecture ou l'écriture.
Informations sur les conflits de base de données Compteurs pour les problèmes courants d'application et de base de données. Recherchez les problèmes dans votre application en cas d'interblocage.
Informations sur la taille de la base de données
Indique la croissance de la base de données pendant l'intervalle entre deux instantanés. Ce champ indique également si des limites de connexion ont été définies pour la base de données. Recherchez les problèmes dans votre application si la croissance de la base de données est trop importante.
Informations sur l'aspirateur Informations sur l'aspirateur Détails des E/S et des compteurs pour les opérations d'aspiration. Par défaut, AlloyDB effectue un nettoyage adaptatif. Vous pouvez remplacer certains des paramètres d'aspiration en fonction de votre charge de travail. Par exemple, réduisez les opérations d'aspiration si trop d'E/S sont consacrées à ces requêtes.
Informations sur l'aspiration par base de données Affiche les informations suivantes:
  • Âge actuel de datfrozenxid (XID les plus anciens non figés) de chaque base de données, ou nombre de transactions entre datfrozenxid et le XID de la transaction en cours.
  • ID de transaction non figés consommés sur l'ensemble des ID de transaction.
  • Résultat de autovacuum_freeze_max_age - age(pg_database.datfrozenxid), qui indique les écarts d'âge approximatifs (en transactions) au moment du deuxième instantané, lorsque l'autovacuum est déclenché pour éviter les retours à la ligne au niveau de l'agrégation de la base de données.
Si l'âge du champ XID congelé est trop ancien ou si le pourcentage de transactions consommées est proche de 90%, envisagez d'effectuer un nettoyage. Si l'écart de vide agrégé diminue, cela signifie qu'un vide sera appliqué par Postgres pour éviter le retour à la ligne.
Détails des temps d'attente des processus de base de données Informations détaillées sur le backend et les processus en arrière-plan
Détails de toutes les attentes par processus backend et en arrière-plan au cours de l'intervalle de rapport. Les informations incluent le temps d'attente cumulé, le temps CPU et le temps moyen par type d'attente. Pour réduire l'attente sur WALWrite, par exemple, augmentez le nombre de wal_buffers disponibles pour la base de données.
Histogramme détaillé des événements d'attente de backend et en arrière-plan Il est inclus dans le rapport "Instantané des performances" par défaut. La liste contient l'histogramme des événements d'attente pour les processus backend et en arrière-plan, qui sont divisés en 32 buckets, de 1 µs à plus de 16 secondes. Recherchez les événements d'attente et déterminez s'il y en a trop dans le bucket de temps d'attente le plus long. Il peut y avoir un problème avec un trop grand nombre d'événements d'attente ou avec chaque temps d'attente consommé.
Statistiques diverses Statistiques sur les journaux de transaction (WAL) Résumé des statistiques WAL. Si le temps de synchronisation est trop long, ajustez les indicateurs de base de données associés (GUC) pour améliorer votre charge de travail. GUC est le sous-système PostgreSQL qui gère la configuration du serveur.
Statistiques récapitulatives (pour toutes les bases de données) Résumé de toutes les opérations de base de données qui se produisent pendant l'intervalle de l'instantané. N/A
Détails du paramètre Détails du paramètre Principaux paramètres de configuration Postgres au moment de l'instantané de fin. Vérifiez les paramètres des paramètres GUC (les indicateurs de base de données Postgres) pour déterminer si les valeurs ne sont pas attendues ou ne sont pas recommandées.

Limites

  • Pour éviter l'encombrement de l'espace, vous pouvez créer manuellement jusqu'à 2 500 instantanés sur une instance. La gonflure de l'espace garantit que les instantanés n'occupent pas trop d'espace de stockage dans votre base de données.

  • Si le nombre d'instantanés dépasse la limite, AlloyDB Omni supprime tous les instantanés manuels datant de plus de 90 jours. Pour respecter la limite d'instantanés, vous devez nettoyer les instantanés inutiles avant d'en créer un autre.

  • AlloyDB Omni nettoie périodiquement les instantanés manuels datant de plus de 90 jours.

Étape suivante