Une utilisation élevée du processeur affecte les performances de votre instance. Toute activité effectuée sur l'instance utilise le processeur. Par conséquent, en cas de notification d'utilisation élevée du processeur, vous devez d'abord identifier la cause du problème : requêtes mal écrites, transactions de longue durée, autre activité de base de données, etc.
Ce document décrit les différents moyens d'identifier les goulots d'étranglement sur une instance et de résoudre les problèmes d'utilisation du processeur dans cette instance.
Identifier les goulots d'étranglement du processeur
Utiliser Insights sur les requêtes pour identifier les requêtes qui consomment beaucoup de ressources processeur
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.
Utiliser l'extension pg_proctab
Utilisez l'extension pg_proctab conjointement avec l'utilitaire pg_top pour obtenir les résultats du système d'exploitation contenant les informations d'utilisation du processeur par processus.
Utiliser des requêtes
Identifier les connexions actives par état
Chaque connexion active à la base de données nécessite une certaine quantité de ressources processeur. Par conséquent, si l'instance présente un grand nombre de connexions, l'utilisation cumulée peut être élevée. Utilisez la requête suivante pour obtenir les informations sur le nombre de connexions par état.
SELECT
state,
usename,
count(1)
FROM
pg_stat_activity
WHERE
pid <> pg_backend_pid()
group by
state,
usename
order by
1;
La sortie ressemble à ceci :
state | usename | count
---------------------+---------------+-------
active | ltest | 318
active | sbtest | 95
active | | 2
idle | cloudsqladmin | 2
idle in transaction | ltest | 32
idle in transaction | sbtest | 5
| cloudsqladmin | 3
| | 4
(8 rows)
Si le nombre de connexions actives est élevé, vérifiez les requêtes de longue durée ou attendez les événements qui bloquent l'exécution des requêtes.
Si le nombre de connexions inactives est élevé, exécutez la requête suivante pour mettre fin aux connexions après avoir obtenu les approbations nécessaires.
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
usename = 'sbtest'
and pid <> pg_backend_pid()
and state in ('idle');
Vous pouvez également interrompre les connexions individuellement avec pg_terminate_backend à l'aide de la requête suivante :
SELECT pg_terminate_backend (<pid>);
Vous pouvez obtenir le PID auprès de pg_stat_activity.
Identifier les connexions de longue durée
Voici un exemple de requête qui renvoie des requêtes de longue durée. Dans ce cas, vous pouvez identifier les requêtes actives depuis plus de 5 minutes.
SELECT
pid,
query_start,
xact_start,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM
pg_stat_activity
WHERE
(
now() - pg_stat_activity.query_start
) > interval '5 minutes' order by 4 desc;
Examiner le plan d'exécution pour identifier les requêtes mal écrites
Utilisez le plan d'exécution pour examiner une requête mal écrite et la réécrire, si nécessaire. Vous pouvez éventuellement envisager d'annuler la requête de longue durée à l'aide de la commande suivante avec les approbations nécessaires.
SELECT pg_cancel_backend(<pid>);
Surveiller l'activité VACUUM
L'activité AUTOVACUUM pour effacer les tuples morts est une opération nécessitant une utilisation intensive du processeur. Si votre instance utilise PostgreSQL version 11 ou ultérieure, utilisez la requête suivante pour vérifier si une activité AUTOVACUUM ou VACUUM est en cours.
SELECT
relid :: regclass,
pid,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count,
max_dead_tuples,
num_dead_tuples
FROM
pg_stat_progress_vacuum;
Vérifiez si une instance VACUUM est en cours dans une instance à l'aide de la requête suivante :
SELECT
pid,
datname,
usename,
query
FROM
pg_stat_activity
WHERE
query like '%vacuum%';
Vous pouvez également optimiser et dépanner les opérations VACUUM dans PostgreSQL.
Ajouter l'extension pg_stat_statements
Configurez l'extension pg_stat_statements pour obtenir des informations de dictionnaire améliorées sur l'activité de l'instance.
Points de contrôle fréquents
Les points de contrôle fréquents dégradent les performances. Envisagez d'ajuster l'option checkpoint_timeout si le journal d'alerte PostgreSQL signale l'avertissement checkpoint occurring too frequently.
Recueillir des statistiques
Assurez-vous que le planificateur de requêtes dispose des statistiques les plus récentes sur les tables afin de choisir le meilleur plan pour les requêtes. L'opération ANALYZE collecte des statistiques sur le contenu des tables de la base de données et stocke les résultats dans le catalogue système pg_statistic. Le planificateur de requêtes utilise ensuite ces statistiques pour déterminer les plans d'exécution les plus efficaces pour les requêtes. Le processus AUTOVACUUM analyse automatiquement les tables de façon périodique. Par conséquent, exécutez la commande suivante pour vérifier si toutes les tables ont été analysées et que les dernières métadonnées sont disponibles pour le planificateur.
SELECT
relname,
last_autovacuum,
last_autoanalyze
FROM
pg_stat_user_tables;
Paramètres système inappropriés
Il existe d'autres facteurs et paramètres d'option qui influencent les performances de votre requête. Exécutez la requête suivante pour vérifier les événements d'attente et le type d'attente afin d'obtenir des informations sur les performances des autres paramètres système.
SELECT
datname,
usename,
(
case when usename is not null then state else query end
) AS what,
wait_event_type,
wait_event,
backend_type,
count(*)
FROM
pg_stat_activity
GROUP BY
1,
2,
3,
4,
5,
6
ORDER BY
1,
2,
3,
4 nulls first,
5,
6;
Le résultat ressemble à ceci :
.. | .. | what | wait_event_type | wait_event | .. | count
-..--+-..-+----------------+-----------------+----------------------+-..----+------
..
.. | .. | active | IO | CommitWaitFlush | .. | 750
.. | .. | idle | IO | CommitWaitFlush | .. | 360
.. | .. | active | LWLock | BufferMapping | .. | 191
Surveiller les analyses séquentielles
Les analyses séquentielles fréquentes effectuées sur des tables de plus de quelques dizaines de lignes indiquent généralement un index manquant. Lorsque les analyses affectent des milliers, voire des centaines de milliers de lignes, elles peuvent entraîner une utilisation excessive du processeur.
Les analyses séquentielles fréquentes portant sur des tables comportant des centaines de milliers de lignes peuvent entraîner une utilisation excessive du processeur. Évitez les analyses séquentielles sur ces tables en créant les index nécessaires.
Exécutez la requête suivante pour vérifier le nombre de lancements d'analyses séquentielles sur une table.
SELECT
relname,
idx_scan,
seq_scan,
n_live_tup
FROM
pg_stat_user_tables
WHERE
seq_scan > 0
ORDER BY
n_live_tup desc;
Enfin, si le processeur est toujours élevé et que vous estimez que ces requêtes sont du trafic légitime, envisagez d'augmenter les ressources processeur de votre instance pour éviter tout plantage ou temps d'arrêt de la base de données.