Optimiser en cas d'utilisation élevée de la mémoire dans les instances

Ce document explique comment identifier les cas d'utilisation élevée de la mémoire par des instances Cloud SQL et fournit des recommandations sur la façon de résoudre les problèmes liés à la mémoire.

Pour apprendre à configurer l'utilisation de la mémoire pour une instance Cloud SQL, consultez la page Bonnes pratiques de gestion de l'utilisation de la mémoire.

Identifier l'utilisation élevée de la mémoire

Utiliser l'explorateur de métriques pour identifier l'utilisation de la mémoire

Vous pouvez consulter l'utilisation de mémoire de l'instance avec la métrique database/memory/components.usage dans l'explorateur de métriques.

Utiliser Insights sur les requêtes pour analyser le plan d'exécution des requêtes qui consomment des ressources élevées

Insights sur les requêtes vous aide à détecter, à diagnostiquer et à empêcher les problèmes de performances des requêtes pour les bases de données Cloud SQL. Insights sur les requêtes vous fournit une liste des requêtes de longue durée ainsi que leur plan d'exécution (documentation PostgreSQL). Examinez le plan d'exécution et identifiez la partie de la requête qui utilise une méthode d'analyse de l'utilisation élevée de la mémoire. Quelle que soit la durée d'exécution de la requête, Insights sur les requêtes fournit le plan d'exécution pour toutes les requêtes. Identifiez les requêtes complexes qui prennent plus de temps afin de déterminer celles qui bloquent la mémoire pour des durées plus longues.

Les méthodes courantes PostgreSQL d'analyse de l'utilisation élevée de la mémoire incluent les suivantes :

Analyse des segments de mémoire bitmap
Quick sort
Hash join ou Hash

Utilisation élevée de la mémoire et journaux correspondants pour les instances avec Gemini activé

Si Gemini est activé, au lieu d'un échec en raison d'une mémoire saturée (OOM, Out Of Memory) qui entraîne un temps d'arrêt de la base de données, une connexion exécutant une requête avec une utilisation élevée de mémoire est interrompue, ce qui empêche le temps d'arrêt de la base de données. Pour identifier la requête par défaut, vous pouvez rechercher les entrées suivantes dans les journaux de la base de données :

  (...timestamp….) db=postgres, user=customer FATAL: terminating connection due to administrator command

Le journal de base de données Cloud SQL pour PostgreSQL suivant s'affiche. Il capture la requête d'utilisation élevée de la mémoire qui a été arrêtée pour éviter les problèmes OOM. La requête est une version normalisée de la requête d'origine :

  db=postgres,user=customer LOG:  postgres process with PID 1734 for the query "SELECT COUNT(product) AS item_count FROM test_table WHERE product_type = $1 AND product LIKE $2 AND c6_2 IN ($3,$4,$5,$6,$7)" has been cancelled.

Les notifications sont également affichées sur la page Instances Cloud SQL pour les événements suivants :

Utilisation de la mémoire de l'instance au cours des dernières 24 heures.
Liste des requêtes normalisées qui ont été annulées au cours des dernières 24 heures.
Lien vers la documentation Google sur l'optimisation de l'utilisation de la mémoire.

Utilisation élevée de la mémoire : recommandations

Les recommandations suivantes traitent des problèmes de mémoire courants. Si l'instance continue à utiliser une grande quantité de mémoire, il est fort probable qu'il s'agisse d'un problème out of memory. Si les demandes de mémoire de PostgreSQL ou d'un autre processus entraînent un manque de mémoire du système, un message de noyau Out of Memory s'affiche dans les journaux PostgreSQL et l'instance PostgreSQL est arrêtée. Exemple :

Out of Memory: Killed process 12345 (postgres)

Une valeur de work_mem plus élevée avec un grand nombre de connexions actives est l'un des problèmes OOM les plus courants. Par conséquent, si vous obtenez des OOM fréquents ou pour éviter les OOM dans votre instance Cloud SQL pour PostgreSQL, nous vous recommandons de suivre ces recommandations :

Définir work_mem

Les requêtes qui utilisent le tri rapide sont plus rapides que celles utilisant le tri externe par fusion. Toutefois, la première méthode peut entraîner une saturation de la mémoire. Pour résoudre ce problème, définissez une valeur work_mem suffisamment raisonnable pour équilibrer les deux opérations de tri dans la mémoire et le disque. Vous pouvez également définir work_mem au niveau de la session plutôt qu'au niveau d'une instance entière.
Surveiller les sessions actives

Chaque connexion utilise une certaine quantité de mémoire. Exécutez la requête suivante pour vérifier le nombre de connexions actives :
```
  SELECT
    state,
    usename,
    count(1)
  FROM
    pg_stat_activity
  WHERE
    pid <> pg_backend_pid()
  GROUP BY
    state,
    usename
  ORDER BY
    1;
```
Si vous avez un grand nombre de sessions actives, analysez la cause d'un grand nombre de sessions actives, par exemple, les verrouillages de transactions.
Définir shared_buffers

Si shared_buffers est défini sur une valeur supérieure, envisagez de réduire la valeur shared_buffers afin que la mémoire puisse être utilisée pour d'autres opérations, telles que work_mem, ou pour établir de nouvelles connexions.

Taux d'accès au cache

PostgreSQL tente généralement de conserver les données auxquelles vous accédez le plus souvent dans le cache. Lorsque les données sont demandées par un client, si elles sont déjà mises en cache dans des tampons partagés, elles sont directement transmises au client. C'est ce qu'on appelle un succès de cache. Si les données ne sont pas présentes dans les tampons partagés, elles sont d'abord extraites des tampons partagés à partir d'un disque, puis transmises au client. C'est ce qu'on appelle un défaut de cache (miss). Ce taux mesure le nombre de requêtes de contenu que le cache a traitées par rapport aux requêtes reçues. Exécutez la requête suivante pour vérifier le taux d'accès au cache des requêtes de table dans l'instance PostgreSQL :
```
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
  pg_statio_user_tables;
```
Exécutez la requête suivante pour vérifier le taux d'accès au cache des requêtes d'index dans l'instance PostgreSQL :
```
  SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit)  as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
  FROM
    pg_statio_user_indexes;
```
En général, 95 à 99 % du taux d'accès au cache est considéré comme une valeur correcte.
Activer les Huges Pages : dans Cloud SQL pour PostgreSQL, l'option huge_pages est activée par défaut pour une meilleure gestion de la mémoire. Nous vous recommandons de l'activer. Pour en savoir plus sur huge_pages, consultez la documentation PostreSQL.
Définir max_locks_per_transaction

La valeur max_locks_per_transaction indique le nombre d'objets de base de données pouvant être verrouillés simultanément. Dans la plupart des cas, la valeur par défaut de 64 est suffisante. Toutefois, si vous gérez un ensemble de données volumineux, vous risquez de vous retrouver avec des erreurs OOM. Pensez à augmenter suffisamment la valeur de max_locks_per_transaction pour éviter les OOM.

Remarque :Le verrouillage au niveau de l'objet se produit en mémoire lors d'une transaction en cours. Par conséquent, une augmentation excessive de la valeur max_prepared_transactions peut amener l'instance de base de données à demander plus de mémoire partagée.

La valeur max_locks_per_transaction doit être de type max_locks_per_transaction * (max_connections + max_prepared_transactions). Cela signifie que si vous avez 300 000 objets et que la valeur de max_connections est 200, max_locks_per_transaction doit être 1 500.
Définir max_pred_locks_per_transaction

La transaction peut échouer si certains de vos clients ont accès à de nombreuses tables différentes dans une seule transaction sérialisable. Dans ce cas, envisagez d'augmenter max_pred_locks_per_transaction à une valeur raisonnablement élevée. Comme max_locks_per_transaction, max_pred_locks_per_transaction utilise également de la mémoire partagée. Par conséquent, ne définissez pas une valeur élevée déraisonnable.
Si l'utilisation de mémoire est toujours élevée et que vous estimez que ces requêtes sont du trafic légitime, envisagez d'augmenter le nombre de ressources de mémoire de votre instance afin d'éviter tout plantage ou temps d'arrêt de la base de données.