[Résolu] Monitoring de base de données

mortimer.pw · 12/05/2014 09:48:07

Bonjour tout le monde,

Je travaille sous CenOS 5.4 avec un moteur PostgreSQL 9.3.2.

La charge machine (nombre de process, utilisation CPU, RAM, I/O) est sous surveillance Centreon.

J'essaye de mettre en place un monitoring de la base.

Je surveille la taille de la base
SELECT PG_DATABASE_SIZE;

Je surveille le Ratio Cache Hit/miss
SELECT ROUND((blks_hit::FLOAT/(blks_read+blks_hit+1)*100)::NUMERIC,2) FROM pg_stat_database WHERE datname='MA_BASE'

Je surveille le nombre d'enregistrements, la taille, le Ratio Cache Hit/miss par Table.
Je surveille la taille, le Ratio Cache Hit/miss par Indexe.

Je surveille les appels aux Fonctions
SELECT funcname,calls,total_time,self_time FROM pg_stat_user_functions;

Je surveille l'activité par Utilisateur avec PgBadger :
Le nombre des connexions
Le volume des requêtes Select/Insert/Update/Delete/Others
Les verrous
Les requêtes les plus lentes
Les requêtes les plus exécutées

Je surveille également les transactions validées et annulées, le nombre global de lignes renvoyées, récupérées, insérées, mise à jour et supprimées
SELECT xact_commit,xact_rollback,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted FROM pg_stat_database WHERE datname='MA_BASE'
Quelle est la différence entre TUP_RETURNED et TUP_FETCHED ? Est-ce les requêtes simples et par exemples les curseurs ?
Ces compteurs ne font qu'augmenter. Peut-on faire un PG_STAT_RESET(), par exemple chaque dimanche, pour avoir une vue à la semaine ?
Le PG_STAT_RESET() peut-il avoir des effets négatifs sur l'optimiseur de requêtes ou autre ?

Quels Ratios peut-on mettre en place pour les Lectures/Ecritures ?

Y-a t'il d'autres points importants à surveiller ?

D'avance merci pour vos réponses.

Dernière modification par mortimer.pw (23/05/2014 07:24:47)

gleu · 12/05/2014 16:57:27

Le plus important, à savoir qui fait les écritures. Tout se trouve dans la vue pg_stat_bgwriter.

mortimer.pw · 14/05/2014 12:25:56

Bonjour Guillaume,
J'ai repris la technique suivante, extraite du livre "Base de données PostgreSQL - Gestion des performances", Chapitre 11 "Activité et statistiques de la base de données", paragraphe "Sauvegarder des images de pg_stat_bgwriter" :
Création d'une table : CREATE TABLE pg_stat_bgwriter_snapshot AS SELECT current_timestamp,* FROM pg_stat_bgwriter;
Insertion d'une image dans la table par crontab toutes les 5 minutes : INSERT INTO pg_stat_bgwriter_snapshot (SELECT current_timestamp,* FROM pg_stat_bgwriter);
J'essaye d'utiliser la requête suivante qui exploite les informations de la table :
SELECT
cast(date_trunc(‘minute’,start) AS timestamp) AS start,
date_trunc(‘second’,elapsed) AS elapsed,
date_trunc(‘second’,elapsed / (checkpoints_timed + checkpoints_req)) AS avg_checkpoint_interval,
(100 * checkpoints_req) / (checkpoints_timed + checkpoints_req) AS checkpoints_req_pct,
100 * buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) AS checkpoint_write_pct,
100 * buffers_backend / (buffers_checkpoint + buffers_clean + buffers_backend) AS backend_write_pct,
pg_size_pretty(buffers_checkpoint * block_size / (checkpoints_timed + checkpoints_req)) AS avg_checkpoint_write,
pg_size_pretty(cast(block_size * (buffers_checkpoint + buffers_clean + buffers_backend) / extract(epoch FROM elapsed) AS int8)) AS written_per_sec,
pg_size_pretty(cast(block_size * (buffers_alloc) / extract(epoch FROM elapsed) AS int8)) AS alloc_per_sec
FROM
(
SELECT
one.now AS start,
two.now - one.now AS elapsed,
two.checkpoints_timed - one.checkpoints_timed AS checkpoints_timed,
two.checkpoints_req - one.checkpoints_req AS checkpoints_req,
two.buffers_checkpoint - one.buffers_checkpoint AS buffers_checkpoint,
two.buffers_clean - one.buffers_clean AS buffers_clean,
two.maxwritten_clean - one.maxwritten_clean AS maxwritten_clean,
two.buffers_backend - one.buffers_backend AS buffers_backend,
two.buffers_alloc - one.buffers_alloc AS buffers_alloc,
(SELECT cast(current_setting(‘block_size’) AS integer)) AS block_size
FROM pg_stat_bgwriter_snapshot one
INNER JOIN pg_stat_bgwriter_snapshot two ON two.now > one.now
) bgwriter_diff
WHERE (checkpoints_timed + checkpoints_req) > 0;
Je n'arrive pas à l'adapter pour qu'elle traite l'ensemble des enregistrements de la table et non pas uniquement deux enregistrements.
En fait chaque ligne est comparée avec L'ENSEMBLE des suivantes et non pas uniquement avec LA suivante.
Pourriez-vous m'apporter votre aide ?
D'avance merci.

gleu · 14/05/2014 22:26:15

Si vous voulez faire des requêtes qui traitent les données d'une ligne et de la suivante, il vous faut écrire une requête avec des fonctions de fenêtrage comme lag(). Vous trouverez plus de détails sur http://docs.postgresql.fr/9.3/tutorial-window.html, http://docs.postgresql.fr/9.3/sql-expre … -functions, et http://docs.postgresql.fr/9.3/queries-t … ies-window

gleu · 14/05/2014 22:26:37

J'oubliais, il faut au minimum une 8.4 pour utiliser les fonctions de fenêtrage.

mortimer.pw · 15/05/2014 11:54:04

Bonjour Guillaume,
Merci pour les fonctions de fenêtrage (un nouvel outil bien sympa).
J'ai donc modifier ma requête :
select cast(date_trunc('second',now) as timestamp) as jour,
date_trunc('second',elapsed) as elapsed,
date_trunc('second',elapsed/(checkpoints_timed+checkpoints_req)) as avg_checkpoint_interval,
(100 * checkpoints_req) / (checkpoints_timed + checkpoints_req) AS checkpoints_req_pct,
100 * buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) AS checkpoint_write_pct,
100 * buffers_backend / (buffers_checkpoint + buffers_clean + buffers_backend) AS backend_write_pct,
pg_size_pretty(buffers_checkpoint * block_size / (checkpoints_timed + checkpoints_req)) AS avg_checkpoint_write,
pg_size_pretty(cast(block_size * (buffers_checkpoint + buffers_clean + buffers_backend) / extract(epoch FROM elapsed) AS int8)) AS written_per_sec,
pg_size_pretty(cast(block_size * (buffers_alloc) / extract(epoch FROM elapsed) AS int8)) AS alloc_per_sec
from
(select now,
((lead(now,1) over(order by now))-now) as elapsed,
((lead(checkpoints_timed,1) over(order by now))-checkpoints_timed) as checkpoints_timed,
((lead(checkpoints_req,1) over(order by now))-checkpoints_req) as checkpoints_req,
((lead(buffers_checkpoint,1) over(order by now))-buffers_checkpoint) as buffers_checkpoint,
((lead(buffers_clean,1) over(order by now))-buffers_clean) as buffers_clean,
((lead(maxwritten_clean,1) over(order by now))-maxwritten_clean) as maxwritten_clean,
((lead(buffers_backend,1) over(order by now))-buffers_backend) as buffers_backend,
((lead(buffers_alloc,1) over(order by now))-buffers_alloc) as buffers_alloc,
(select cast(current_setting('block_size') as integer)) as block_size
from pg_stat_bgwriter_snapshot
order by now) as bgwriter_diff
where (checkpoints_timed+checkpoints_req)>0;
J'obtiens les résultats suivants pour les 2 dernières heures :
jour elapsed avg_checkpoint_interval checkpoints_req_pct checkpoint_write_pct backend_write_pct avg_checkpoint_write written_per_sec alloc_per_sec
2014-05-15 10:10:01 00:10:00 00:10:00 0 60 39 3472 kB 9829 bytes 2212 bytes
2014-05-15 10:20:01 00:10:00 00:10:00 0 65 34 6416 kB 16 kB 3618 bytes
2014-05-15 10:30:01 00:10:00 00:10:00 0 73 26 8424 kB 19 kB 2252 bytes
2014-05-15 10:40:01 00:10:00 00:10:00 0 67 32 5400 kB 13 kB 942 bytes
2014-05-15 10:50:01 00:10:00 00:10:00 0 59 40 3880 kB 11 kB 778 bytes
2014-05-15 11:00:02 00:09:59 00:09:59 0 93 6 3792 kB 6946 bytes 711 bytes
2014-05-15 11:10:01 00:10:00 00:10:00 0 61 38 3808 kB 10 kB 505 bytes
2014-05-15 11:20:01 00:10:00 00:10:00 0 77 22 4032 kB 8873 bytes 614 bytes
2014-05-15 11:30:01 00:10:00 00:10:00 0 65 34 3408 kB 8914 bytes 491 bytes
J'ai également fait cette requête pour avoir le ratio de lecture en cache :
select round((((lead(sum_read,1) over(order by now))-sum_read)*8192)/1024/1024,2) as read_Mo,round((((lead(sum_hit,1) over(order by now))-sum_hit)*8192)/1024/1024,2) as cache_Mo
from pg_stat_database_snapshot
where datname='geo'
and to_char(now,'YYYYMMDD')='20140515' and to_char(now,'HH24:MI:SS')>'100000';
J'obtiens les chiffres suivants :
read_mo cache_mo
0.39 5410.89
0.51 14342.68
2.83 22846.39
1.29 13710.18
0.54 7395.71
0.45 8036.46
0.40 12417.09
0.30 7482.95
0.35 13399.13
0.28 6752.86
Les paramètres de mon postgresql.conf sont :
max_connections = 300
shared_buffers = 8192MB
work_mem = 10MB
maintenance_work_mem = 4096MB
checkpoint_segments = 64
checkpoint_timeout = 10min
effective_cache_size = 21840MB
Pouvez-vous me donner votre avis sur le comportement de ma base, svp ?
Encore merci pour votre aide.

gleu · 15/05/2014 21:50:48

Concernant les stats provenant de pg_stat_bgwriter (et en gardant en tête qu'on analyse 1 minute 30 secondes, ce qui sous-entend quand même qu'on analyse sur une minuscule fenêtre de tir qui ne veut certainement rien dire), les checkpoints écrivent en gros 5 Mo toutes les dix secondes. Autant dire rien. Plus gênant, les backends écrivent entre 25 et 40% des écritures. C'est beaucoup. Si le système était chargé, je pense que les requêtes seraient assez fortement ralenties.

Pour les stats provenant de pg_stat_database, j'espère ne pas me montrer en supposant que sum_read et sum_hit sont la somme des colonnes blks_read et blks_hit de toutes les bases. Dans ce cas, l'utilisation du cache est très forte. C'est parfait.

mortimer.pw · 16/05/2014 08:44:59

Bonjour Guillaume,
La surveillance de pg_stat_bgwriter porte sur 1h30, entre 10h10 et 11h30, avec une photo toutes les dix minutes.
Pouvez-vous vérifier cela, svp ?
Pour les sum_read et sum_hit, ils sont le résultat de :
Création de la table : CREATE TABLE pg_stat_database_snapshot AS SELECT current_timestamp,blks_read,blks_hit FROM pg_stat_database where datname='geo';
Prise d'une photo toutes les 10 minutes.
Résultat de la requête :
select round((((lead(blks_read,1) over(order by now))-blks_read)*8192)/1024/1024,2) as read_Mo,round((((lead(blks_hit,1) over(order by now))-blks_hit)*8192)/1024/1024,2) as cache_Mo
from pg_stat_database_snapshot;
Merci pour votre analyse.

gleu · 16/05/2014 22:06:16

Ah oui, pardon. J'ai confondu heure et minute, une paille Désolé Pour en revenir à l'analyse, cela sous-entend qu'il n'y a pratiquement pas d'écriture dans la base. Que les backends écrivent une partie de ce rien n'est pas grave en soit.

Concernant les snapshots sur pg_stat_database, c'est donc sur une seule base. Ça ne change pas l'analyse en soit, le cache est toujours fortement utilisé.

mortimer.pw · 19/05/2014 07:36:02

Ok, merci Guillaume pour l'analyse.
Comme il n'y a pas de grosse activité, je pense faire des snapshots toutes les 6 heures.
Pour que les compteurs soient un peu plus parlant, je voudrais faire un reset des compteurs le dimanche, pour avoir des chiffres à la semaine.
Le PG_STAT_RESET() peut-il avoir des effets négatifs sur l'optimiseur de requêtes ou autre ?
Est-ce une bonne pratique ?

gleu · 19/05/2014 15:52:41

pg_stat_reset () concerne les statistiques d'activité. Ces statistiques ne concernent en rien l'optimiseur de requêtes. Donc pas de soucis de ce côté.

Quant au côté bonne pratique, c'est affaire de goût

mortimer.pw · 21/05/2014 07:48:48

Bonjour Guillaume,
Ok, merci encore pour votre aide.

mortimer.pw · 21/05/2014 13:14:37

Rebonjour,
Désolé, j'avais mis le post en [résolu] un peu vite.
Le PG_STAT_RESET va remettre les compteurs à zéro, par exemple dans la table PG_STAT_USER_TABLES. Le VACUUM ne se base t'il pas sur les compteurs n_live_tup et n_dead_tup pour ses déclenchements ?
Le PG_STAT_RESET réinitialise t'il également les compteurs du BGWRITER ?

gleu · 21/05/2014 20:00:21

Le PG_STAT_RESET va remettre les compteurs à zéro, par exemple dans la table PG_STAT_USER_TABLES. Le VACUUM ne se base t'il pas sur les compteurs n_live_tup et n_dead_tup pour ses déclenchements ?

L'autovacuum se base dessus, oui (mais rien à voir avec l'optimiseur de requêtes).

Le PG_STAT_RESET réinitialise t'il également les compteurs du BGWRITER ?

Non, il ne réinitialise que les statistiques de la base sur laquelle il est exécuté.

mortimer.pw · 22/05/2014 07:54:17

Bonjour Guillaume,
Donc pas une bonne idée de réinitialiser les statistiques le dimanche. Sinon, les tables pour lesquelles il y aurait des modifications (Insert/update/Delete) pourraient ne jamais être vacuumé ?
Mais dans ce cas, comment avoir des statistiques "parlantes", par exemple à la semaine (nombre de transactions, nombre d'insert/update/delete, parcours séquentiels ou parcours d'index, ......) ?

gleu · 22/05/2014 21:53:16

Personnellement, je ne vois pas l'intérêt de réinitialiser les statistiques. Certains le font et ont de bons arguments pour mais je trouve ça trop compliqué. Vous devez installer un outil qui va récupérer les statistiques de manière périodique (tous les jours, toutes les heures, toutes les cinq minutes, suivant la statistique) et qui vous les présentera de façon intéressante ou parlante. pgcluu (http://pgcluu.darold.net/) en est un bon exemple.

mortimer.pw · 23/05/2014 07:25:32

Bonjour Guillaume,
Bon d'accord, je ne vais pas réinitialiser les stats et regarder cet outil.
Merci encore.

#1 12/05/2014 09:48:07

[Résolu] Monitoring de base de données

#2 12/05/2014 16:57:27

Re : [Résolu] Monitoring de base de données

#3 14/05/2014 12:25:56

Re : [Résolu] Monitoring de base de données

#4 14/05/2014 22:26:15

Re : [Résolu] Monitoring de base de données

#5 14/05/2014 22:26:37

Re : [Résolu] Monitoring de base de données

#6 15/05/2014 11:54:04

Re : [Résolu] Monitoring de base de données

#7 15/05/2014 21:50:48

Re : [Résolu] Monitoring de base de données

#8 16/05/2014 08:44:59

Re : [Résolu] Monitoring de base de données

#9 16/05/2014 22:06:16

Re : [Résolu] Monitoring de base de données

#10 19/05/2014 07:36:02

Re : [Résolu] Monitoring de base de données

#11 19/05/2014 15:52:41

Re : [Résolu] Monitoring de base de données

#12 21/05/2014 07:48:48

Re : [Résolu] Monitoring de base de données

#13 21/05/2014 13:14:37

Re : [Résolu] Monitoring de base de données

#14 21/05/2014 20:00:21

Re : [Résolu] Monitoring de base de données

#15 22/05/2014 07:54:17

Re : [Résolu] Monitoring de base de données

#16 22/05/2014 21:53:16

Re : [Résolu] Monitoring de base de données

#17 23/05/2014 07:25:32

Re : [Résolu] Monitoring de base de données

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux