Lenteur des performances de la base de données

Les symptômes typiques de ce problème sont les suivants :

• Utilisation élevée du disque IO (entrée/sortie) ou de la mémoire
• Requêtes lentes

La cause la plus courante est l'examen des requêtes lentes. Suivez les étapes suivantes pour déterminer si c'est le cas :

1. Répertorier les requêtes longues :

   select age(now(),query_start), pid, client_addr, state, substring(query,0,80) from pg_stat_activity where state != 'idle' order by 1 desc;
   

2. Valider le blocage des requêtes :

   select pg_blocking_pids(pid), pid AS blocked_pid, state, query, age(now(),query_start) AS duration from pg_stat_activity where array_length(pg_blocking_pids(pid),1) > 0 order by 1,2;
   

3. Vérifiez le plan d'exécution de la requête :

   Explain <query>;
   

4. Vérifiez si des statistiques de base de données sont manquantes. Utilisez la requête suivante pour valider : Action > Exécuter l'analyse Vous pouvez également exécuter la commande suivante :

   Analyze <tablename>  select relname, n_live_tup, n_dead_tup, last_vacuum,last_autovacuum,last_analyze,last_autoanalyze, analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='public';
   

5. Vérifiez si l'index est absent du tableau. PostgreSQL fournit des méthodes d'indexation telles que B-Tree, hash, GiST,SP-GiST, GIN et BRIN. Utilisez l'une de ces méthodes pour créer l'index.

6. Vérifiez si la table est trop volumineuse. Exécutez la requête suivante pour valider. Action > Lancer l'aspirateur

   Vous pouvez également utiliser l'instruction suivante :

   SELECT
   relname AS table_name,
   pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
   pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24))) * 100 / pg_relation_size(c.oid)
   END
   ) AS bloat_percentage, pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24)))
   END
   )AS bloat_size
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r' AND n.nspname = 'public' -- Adjust schema if needed
   ORDER BY bloat_size DESC NULLS LAST;
   

7. Vérifiez si la base de données ou les objets ont été créés avec pg_dump ou pg_restore. Utilisez Action > Mettre à jour les statistiques pour actualiser.

8. Assurez-vous qu'une quantité suffisante work_mem est allouée si la requête effectue une opération de tri.

9. Utilisez pg_stat les instructions pour obtenir les informations suivantes :

   • temps d'exécution moyen et nombre d'appels

   • Pourcentage d'utilisation du processeur par les requêtes

   • pourcentage d'utilisation de la mémoire par les requêtes

pg_stat_activity, pg_stat_bgwriter, et pg_buffercache sont des outils importants dans PostgreSQL pour surveiller et comprendre les performances des bases de données.

Surveillance du déploiement et surveillance de la charge de la base de données

Databases for PostgreSQL offrent une intégration avec le service IBM Cloud Monitoring pour la surveillance de l'utilisation des ressources sur votre déploiement. Utilisez ce service pour surveiller votre déploiement (disque et mémoire) et la charge de votre base de données.

Utilisez Cloud Databases les tableaux de bord pour définir des alertes sur les seuils d'utilisation du processeur, de la mémoire et des IOPS du disque. La plupart des mesures disponibles, comme l'utilisation du disque et les IOPS, sont utiles pour vous aider à configurer l 'autoscaling sur votre déploiement. La mise à l'échelle automatique n'est pas activée par défaut, vous devez donc la configurer manuellement.

L'observation des tendances d'utilisation et la configuration de la mise à l'échelle automatique pour répondre à ces tendances peuvent aider à résoudre les problèmes de performance avant que vos bases de données ne deviennent instables en raison de l'épuisement des ressources. Par exemple, des changements dans les E/S disque ou des défaillances du processeur ou de la mémoire entraînant des problèmes de performances.

IOPS de disque

Le nombre d'opérations d'entrée/sortie par seconde (IOPS) est limité par le type et la taille du volume de stockage. Les volumes de stockage pour Databases for PostgreSQL les déploiements sont provisionnés sur des volumes Block Storage Endurance dans la couche 10 IOPS par Go.

Si votre charge opérationnelle sature ou dépasse la limite d'IOPS, les requêtes et les opérations de la base de données sont retardées jusqu'à ce que le sous-système de stockage puisse rattraper son retard. Des périodes prolongées de forte charge peuvent empêcher votre déploiement de traiter les requêtes et le rendre indisponible. Vous pouvez augmenter le nombre d'IOPS disponibles pour votre déploiement en augmentant la taille des disques. Par exemple, pour 10 IOPS par niveau de Go, vous pouvez augmenter les IOPS en augmentant la taille du volume.

Même une utilisation prolongée du disque à 40-50 % peut avoir un impact négatif significatif sur les performances de la base de données. Allouez au moins 100 Go de disque (1 000 IOPS) pour les environnements de production. IOPS = 10 × Go alloués (par exemple, 100 Go = 1 000 IOPS)

Utilisation de la mémoire

La mémoire est le moyen le plus rapide et le plus efficace pour accéder aux données et les traiter. Pour cette raison, une base de données fonctionne presque toujours plus rapidement en utilisant la mémoire plutôt qu'en lisant les données sur le disque. Il existe d'autres indicateurs à prendre en compte, tels que les accès au cache, les blocs lus et les blocs consultés. Le simple fait de constater une utilisation de la mémoire à 100 % n'est pas en soi une source d'inquiétude. Si la mémoire est épuisée et que les pages d'échange sont utilisées, les performances peuvent se dégrader considérablement.

Vous pouvez définir la quantité de mémoire dédiée au pool de tampons partagés de la base de données en ajustant shared_buffers dans votre configuration Databases for PostgreSQL. La valeur maximale recommandée est de 25 % de la mémoire totale du déploiement. L'allocation d'une trop grande quantité de mémoire au pool de mémoire tampon partagée peut priver le système de mémoire pour d'autres usages, entraver les performances, voire désactiver la base de données.

Surveillance de la charge de la base de données

Vous disposez de deux options pour vérifier la charge de la base de données :

{

source logs|filter message.attr.durationMillis>=1000

}

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT username, database, queryid, query_preview, calls, total_exec_time, pct_exec_time, ROUND(SUM(pct_exec_time) OVER (ORDER BY total_exec_time DESC)) AS cum_pct_exec_time, avg_exec_time FROM ( SELECT pu.usename AS username, pd.datname AS database, pss.queryid, LEFT(pss.query, 50) AS query_preview, pss.calls, ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time, ROUND((100.0 * pss.total_exec_time / SUM(pss.total_exec_time) OVER ())::numeric, 2) AS pct_exec_time, ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time FROM pg_stat_statements pss JOIN pg_user pu ON pss.userid = pu.usesysid JOIN pg_database pd ON pss.dbid = pd.oid ) AS subquery ORDER BY total_exec_time DESC LIMIT 25;

SELECT
   username,
   database,
   queryid,
   query_preview,
   calls,
   pct_calls,
   ROUND(SUM(pct_calls) OVER (ORDER BY calls DESC)) AS cum_pct_calls,
   total_exec_time,
   avg_exec_time
FROM (
   SELECT
      pu.usename AS username,
      pd.datname AS database,
      pss.queryid,
      LEFT(pss.query, 50) AS query_preview,
      pss.calls,
      ROUND(100.0 * pss.calls / SUM(pss.calls) OVER (), 2) AS pct_calls,
      ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time,
      ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time
   FROM
      pg_stat_statements pss
      JOIN pg_user pu ON pss.userid = pu.usesysid
      JOIN pg_database pd ON pss.dbid = pd.oid
) AS subquery
ORDER BY
   calls DESC
LIMIT 25;

SELECT query, calls, total_exec_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Autres mesures que vous pouvez prendre pour améliorer les performances

Vous pouvez également envisager les mesures suivantes pour résoudre les problèmes de performances :

• Optimiser les requêtes lentes

  • Exécutez EXPLAIN (ANALYZE, BUFFERS) sur les requêtes lentes. Cela montre comment la base de données exécute la requête et où elle est lente. Exemple :

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM student WHERE student_id = 12345;
  

  • Recherchez :

    • Requêtes qui utilisent beaucoup de mémoire ou d'espace disque

    • Ajoutez IBM Cloud des ressources selon les besoins

    • Augmentez la taille du disque/de la mémoire pour obtenir un IOPS et une mémoire plus élevés

• Les index manquants ou inefficaces sont une cause fréquente de lenteur des requêtes. Utilisez EXPLAIN pour identifier les analyses séquentielles et envisagez d'ajouter des index.

• Exécutez VACUUM pour faciliter l'analyse de l'état de santé de la base de données.

• Envisagez d'utiliser la mise en commun des connexions pour gérer un plus grand nombre de connexions. Databases for PostgreSQL fixe à 115 le nombre maximal de connexions à votre base de données Databases for PostgreSQL. 15 connexions sont réservées au superutilisateur pour maintenir l'état et l'intégrité de votre base de données, et 100 connexions sont disponibles pour vous et vos applications. Lorsque la limite de connexion est atteinte, toute tentative d'établissement d'une nouvelle connexion entraîne une erreur. Pour éviter de surcharger votre déploiement avec des connexions, utilisez le regroupement de connexions ou mettez à niveau votre déploiement et augmentez le nombre limite de connexions. Pour plus d'informations, consultez la section Gestion du pool PostgreSQL de connexions.

• Vérifiez si des recettes exécutent des sauvegardes et des téléchargements groupés de données, par exemple : Les sauvegardes automatiques sont effectuées quotidiennement et conservées selon un calendrier de conservation simple de 30 jours. Si une sauvegarde est bloquée, vous pouvez consulter la section Sauvegardes disponibles et identifier la sauvegarde bloquée dans la page Cloud UI de l'instance de base de données.

• Vérifiez vos IBM Cloud notifications pour connaître les éventuelles opérations de maintenance. Par exemple, l'application de correctifs à une base de données.

• Si vous pensez qu'il s'agit d'un problème lié à la plateforme, tel qu'une maintenance, contactez IBM le service d'assistance en indiquant le numéro CRN de la base de données.