Métriques système PostgreSQL

Imagine que ton serveur PostgreSQL, c’est un resto, et nous, on est les inspecteurs. Pour que le resto tourne bien, on doit savoir combien d’ingrédients (CPU, mémoire, disque) sont utilisés, à quelle fréquence ils manquent, et qui consomme tout ça. Si on rate ce point, notre "resto" peut soit prendre trop de commandes et ne pas suivre, soit carrément planter en plein service. Voilà pourquoi piger les métriques système, c’est vital.

Métriques clés à monitorer pour PostgreSQL :

1. CPU : indique combien de temps processeur est utilisé pour exécuter les requêtes.
2. Mémoire (RAM) : montre comment PostgreSQL utilise la RAM, y compris le cache des requêtes.
3. Espace disque : sûrement la plus prévisible : tu ne peux pas stocker plus de données que ce que le disque permet.

Notre but — apprendre à checker et interpréter les métriques système de PostgreSQL pour éviter les soucis de perf et de manque de ressources.

Monitoring de l’utilisation CPU

Le processeur, c’est le cœur de ton serveur. PostgreSQL peut pomper du CPU pour exécuter des requêtes complexes, mais aussi pour les tâches de fond comme l’auto-analyse et l’auto-nettoyage de la base (autovacuum). Si ta base ressemble à un gros gourmand qui "bouffe" le CPU, il est temps d’agir.

1. Utilisation des outils système.
   Pour commencer, regarde combien de temps CPU PostgreSQL consomme au niveau système. Sous Linux, tu peux utiliser les commandes top ou htop.

Repère le process PostgreSQL (en général il contient le nom de ta base). Par exemple, postgres: postgres [your_query].

Mate la colonne %CPU. Si elle est toujours "dans le rouge", c’est pas bon signe.

2. Analyse de la charge CPU depuis PostgreSQL.

PostgreSQL fournit des vues intégrées pour monitorer la charge. La plus utile ici — pg_stat_activity, qui montre les requêtes actives.

Exemple de requête :

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Qu’est-ce qui compte ici ?

• state = 'active' va afficher seulement les requêtes en cours d’exécution.
• duration montre depuis combien de temps les requêtes chargent le CPU.

Conseil pratique :

Si tu vois une requête longue qui ne devrait pas prendre autant de temps, vérifie quels index sont utilisés. Tu peux aussi killer le process problématique avec pg_terminate_backend.

Exemple de kill :

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' AND duration > interval '10 minutes';

Monitoring de l’utilisation mémoire

La mémoire, c’est la deuxième ressource la plus importante pour PostgreSQL, surtout pour le cache des données. PostgreSQL utilise à fond la RAM (via les paramètres work_mem et shared_buffers) pour accélérer les opérations.

1. Paramètres principaux de PostgreSQL pour la mémoire :

• shared_buffers : C’est le bloc mémoire principal alloué à PostgreSQL. En général, ça prend 25-40% de toute la RAM du serveur.
• work_mem : Mémoire pour les opérations de tri et de hash dans une requête. Plus la valeur est grande, plus d’opérations temporaires peuvent se faire en RAM (et pas sur disque).

2. Vérifier les paramètres mémoire actuels.

Pour voir quels réglages mémoire sont actifs pour PostgreSQL, lance :

SHOW shared_buffers;
SHOW work_mem;

Exemple de sortie :

1GB
4MB

Ça veut dire que le serveur a 1 Go de RAM pour shared_buffers et 4 Mo pour chaque opération de tri/hash.

3. Monitoring mémoire avec pg_stat_activity

Tu peux checker combien de mémoire est utilisée par les connexions en cours. Voilà une requête pratique :

SELECT pid, usename, state, backend_start, pg_size_pretty(pg_backend_memory_contexts_size(pid)) AS memory_used
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY memory_used DESC;

Cette requête montre combien de RAM est utilisée par chaque connexion active.

Conseil : si une connexion bouffe trop de mémoire, regarde si la requête fait des tris ou des agrégations qui pourraient être optimisés.

Monitoring de l’espace disque

Le disque, c’est la dernière réserve de ressources pour PostgreSQL. Même si t’as assez de RAM et de CPU, PostgreSQL a besoin d’espace disque pour stocker les données, les logs de transactions (WAL) et les fichiers temporaires.

1. Vérifier la taille de la base.

On commence simple : déterminer la taille de la base.

SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

À quoi sert cette requête ?

Elle affiche la taille totale de la base courante dans un format lisible (MB, GB).

2. Vérifier la taille des tables et index.

Pour repérer les "poids lourds" dans la base, utilise :

SELECT relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Exemple de sortie :

3. Monitoring des WAL (logs de transactions).

Si ta base est active, les logs de transactions peuvent grossir vite. Pour checker leur taille :

SELECT pg_size_pretty(pg_xlog_location_diff(pg_current_wal_lsn(), '0/0')) AS wal_size;

Conclusion

Maintenant t’as les outils et les bases pour surveiller les métriques système de PostgreSQL :

1. Utilise htop ou pg_stat_activity pour monitorer la charge CPU.
2. Règle les paramètres shared_buffers et work_mem pour une utilisation mémoire optimale.
3. Vérifie régulièrement la taille de la base, des tables et des index pour éviter de manquer d’espace disque.

Ces skills te serviront pour éviter les plantages surprises et garder ton serveur PostgreSQL au top. Pense à ton serveur comme à un resto : des ressources bien gérées, et ton produit sera le préféré des clients (utilisateurs).