Vous n'êtes pas identifié(e).
Bonjour tout le monde,
Je travaille sous CenOS 6.4 avec un moteur PostgreSQL 9.3.2.
J'ai accès à des graphs centreon de surveillance des I/O sur mon serveur, mais je cherche le moyen de calculer le ratio des lectures/écritures de ma base de données.
Quelqu'un peut-il aiguiller mes recherches, svp ?
D'avance merci.
Hors ligne
Bonjour,
Vous souhaitez avoir un aperçu des lectures écritures au niveau système de fichier ou au niveau de la base de données?
Je pose la question car vous semblez mélanger les deux, des lectures sur la bdd peuvent taper dans le cache de postgres ce qui n'entraine pas de lecture sur le disque. De même que les écritures peuvent être confondues si vous n'avez pas séparé les journaux de transactions, une première écriture dans les journaux, puis en fonction de l'activité un checkpoint de vos journaux vers la base.
Je connais deux outils que je trouve assez sympa pour les lectures/ecritures : atop et dstat. Il y a même un démon atop qui permet de conserver un historique des statistiques.
Adrien Nayrat
DBA PostgreSQL Freelance
Hors ligne
Bonjour Anayrat,
Au niveau système, c'est bon, j'ai des graph centreon avec les lectures/écritures sur les différents disques (OS+moteur+logs sur disque A, data sur disque B, journaux sur disque C).
Je surveille déjà :
Taille BD : SELECT pg_database_size('ma_base');
Taille Table + Indexes + autres Relations rattachés : SELECT pg_total_relation_size('ma_table');
Le ratio cache hit/miss sur la BD (lecture en cache si bien compris) : SELECT ROUND((blks_hit::FLOAT/(blks_read+blks_hit+1)*100)::NUMERIC,2) FROM pg_stat_database WHERE datname='ma_base';
Je voudrai également surveiller les lectures/écritures côté base de données. Si j'ai bien compris : l'écriture des journaux de transactions, l'écriture en base et l'écriture des logs.
Merci pour votre aide.
Dernière modification par mortimer.pw (09/10/2014 07:23:47)
Hors ligne
Côté lecture et écriture, tout dépend sur vous parlez en nombre de blocs ou de lignes.
Pour ce qui concerne les lignes, allez voir la vue pg_stat_all_tables. Pour les blocs, vous n'aurez que les écritures. Le mieux dans ce cas est la vue pg_stat_bgwriter.
Guillaume.
Hors ligne
Bonjour,
Je prend également des photos de la table pg_stat_bgwriter, toutes les 6h, du lundi au samedi :
Checkpoint requis Checkpoint Serveur Checkpoint Client Volume moyen écrit (kB/Check) Volume écriture (kB/s) Volume lecture (kB/s)
0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h
20140526 0 0 0 0 71 66 70 53 28 33 29 46 0718 4403 4145 1176 01,721 11,000 10,068 03,751 07,143 002,880 019,000 00,381
20140527 0 0 0 0 78 68 70 52 21 31 29 47 2752 6096 4205 1130 05,992 15,000 10,179 03,663 01,653 003,997 016,000 00,440
20140528 0 0 0 0 77 68 65 47 22 31 34 52 2775 4375 3273 0937 06,144 11,000 08,498 03,386 01,342 007,080 000,650 00,404
20140529 0 0 0 0 76 49 43 45 23 50 56 54 2209 1373 1076 0845 04,910 04,704 04,212 03,174 00,938 000,083 000,076 00,055
20140530 0 0 0 0 92 62 61 48 07 37 38 51 0666 2925 2659 0998 01,228 07,981 07,428 03,493 00,135 000,845 000,518 00,459
20140531 0 0 0 0 76 57 46 00 23 42 53 00 1824 1958 1232 0000 04,073 05,876 04,559 00,000 00,784 000,226 000,166 00,000
Checkpoint requis Checkpoint Serveur Checkpoint Client Volume moyen écrit (kB/Check) Volume écriture (kB/s) Volume lecture (kB/s)
0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h 0h 6h 12h 18h
20141027 0 0 0 0 76 59 58 38 23 40 41 61 1250 6760 5603 1497 02,778 19,000 16,000 06,627 09,109 190,000 051,000 00,558
20141028 0 0 0 0 74 62 61 38 25 37 38 61 4872 8120 5973 1541 11,000 22,000 16,000 06,778 03,704 005,870 284,000 01,836
20141029 0 0 0 0 80 59 61 35 19 40 38 64 5711 6088 7803 1284 12,000 17,000 21,000 06,255 64,000 025,000 407,000 02,927
20141030 0 0 0 0 82 61 59 46 16 38 40 53 6221 6474 6379 2781 12,000 18,000 18,000 10,227 75,000 325,000 885,000 33,000
20141031 0 0 0 0 76 60 61 47 23 39 37 52 3946 6435 6621 2502 08,785 18,000 18,000 08,993 69,000 584,000 675,000 04,796
20141101 0 0 0 0 80 75 28 33 19 24 71 66 4374 8990 1140 0991 09,319 20,000 06,798 05,060 83,000 000,810 000,177 00,109
Je vois une augmentation du volume des écritures, mais surtout des lectures.
En même temps, comme il s'agit d'une base "centrale", dans laquelle de nouveaux sites sont venus s'intégrer (donc le nombre des connexions a augmenté, le volume des traitements et le volume des tables également) et que nous n'avons pas de remonté d'informations sur des lenteurs ou blocages, je ne suis pas trop inquiet.
Les volumes de lecture/écriture sont ils cohérents ?
Hors ligne
> Les volumes de lecture/écriture sont ils cohérents ?
Cohérents avec quoi ?
Guillaume.
Hors ligne
Bonjour Guillaume,
Désolé pour ma question un peu inadaptée.
En fait nous avions plusieurs bases de données (une par site) et nous avons évolué vers une architecture centralisée (une seule machine plus puissante, plus de mémoire, disques plus gros, OS mieux utilisé, séparation des data et logs sur différents disques, ...) et nous rapatrions progressivement les données et traitements des différents sites.
J'essaye de mettre en place les outils qui me permettront d'anticiper d'éventuels problèmes.
J'ai des infos au niveau système grâce à une surveillance Centreon.
Le ratio Cache hit/miss de ma BD reste au-dessus de 96%.
Le volume des données est bien géré.
J'ai mis en place la prise de "photos" de la table pg_stat_bgwriter, mais j'ai du mal à en interpréter les résultats.
Je me demande si les "Checkpoints clients" ne sont pas trop nombreux ?
Le volume des écritures a augmenté, je pense, avec l'augmentation de l'activité de la base.
Le volume des lectures a lui augmenté de façon plus significative.
Est-ce à dire que toutes les infos ne tiennent plus en mémoire ?
La machine dispose de 32Go et le cache n'est qu'à 24Go.
Le ratio global ne devrait-il pas baisser ?
Plus globalement, sur quels critères dois-je me baser pour lever une alerte ?
Hors ligne
Je me demande si les "Checkpoints clients" ne sont pas trop nombreux ?
Encore faudrait-il qu'on sache ce qu'est un "checkpoint client" ?
Est-ce à dire que toutes les infos ne tiennent plus en mémoire ?
Avec un ratio hit/miss de 96%, si.
La machine dispose de 32Go et le cache n'est qu'à 24Go.
Cache linux ?
Plus globalement, sur quels critères dois-je me baser pour lever une alerte ?
Tout dépend de ce que vous cherchez à faire. Désolé de ne jamais répondre directement mais c'est assez difficile sur ce genre d'exercices.
Ce que je veux dire, c'est qu'un ratio à 96% est très bon. Mais un ratio moindre n'est pas forcément un problème.
Le vrai truc important, c'est de s'assurer que ce sont bien le checkpointer et le writer qui font les écritures. Autrement dit, les processus postgres (ce qu'on appelle aussi les backends) ne doivent jamais en faire. Et pour donner une formule, buffers_checkpoint+buffers_clean doivent être très fortement supérieurs à buffers_backend. Et si ce dernier pouvait rester aussi bas que possible, ce serait une bonne chose.
Guillaume.
Hors ligne
Bonjour Guillaume,
C'est vrai qu'il manque quelques infos, désolé.
Alors, 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" :
Toutes les 6 heures, je fais la "photo" : INSERT INTO pg_stat_bgwriter_snapshot (SELECT current_timestamp,* FROM pg_stat_bgwriter);
Ensuite, le dimanche, je fais la requête suivante pour extraire les infos de ma table de collecte :
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 checkpoint_requis,
100 * buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) AS checkpoint_serveur,
100 * buffers_backend / (buffers_checkpoint + buffers_clean + buffers_backend) AS checkpoint_client,
pg_size_pretty(buffers_checkpoint * block_size / (checkpoints_timed + checkpoints_req)) AS volume_moyen_ecrit,
pg_size_pretty(cast(block_size * (buffers_checkpoint + buffers_clean + buffers_backend) / extract(epoch FROM elapsed) AS int8)) AS volume_ecrit,
pg_size_pretty(cast(block_size * (buffers_alloc) / extract(epoch FROM elapsed) AS int8)) AS volume_lecture
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;
Les buffers_checkpoint sont donc bien en majorité supérieur aux buffers_backend, cependant ces derniers augmentent.
Dans quelle mesure faut-il s'en inquiéter ?
Concernant le cache, oui c'est bien le cache Linux qui est à 24Go.
Merci encore pour votre analyse.
Hors ligne
Donc, pas de checkpoint requis, ce qui est plutôt bien (on veut plutôt des checkpoints à intervalle régulier). Le nombre de blocs écrits par le checkpointer et le writer (ce que vous appelez checkpoint serveur) est plus important que le nombre de blocs écrits par les processus postgres (checkpoint "client"). Ce qui est bien. Il n'empêche que le ratio est plutôt inquiétant. Ça peut être dû à l'utilisation de peu de connexions, à un cache trop petit ou à des checkpoints peu fréquents.
Quelle est la taille du cache ?
Tracez les checkpoints (log_checkpoints à on) et récupérer les traces en question. pgBadger est capable de faire de jolis rapports dessus. Ça sera plus intéressant à priori.
Guillaume.
Hors ligne
Bonjour Guillaume,
Ci-dessous les paramètres du postgresql.conf :
max_connections = 300
shared_buffers = 8192MB
work_mem = 10MB
maintenance_work_mem = 4096MB
checkpoint_segments = 64
checkpoint_timeout = 10min
effective_cache_size = 21840MB
32Go de RAM sur la machine.
En moyenne, 130 à 140 utilisateurs connectés.
Le log_checkpoints est bien à ON, j'ai bien des logs du style :
2014-10-27 08:10:28 CET [2613]: [33187-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint starting: time
2014-10-27 08:11:16 CET [2613]: [33188-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint complete: wrote 476 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=47.524 s, sync=0.009 s, total=47.536 s; sync files=113, longest=0.000 s, average=0.000 s
2014-10-27 08:20:28 CET [2613]: [33189-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint starting: time
2014-10-27 08:21:38 CET [2613]: [33190-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint complete: wrote 696 buffers (0.1%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=69.572 s, sync=0.011 s, total=69.587 s; sync files=72, longest=0.001 s, average=0.000 s
2014-10-27 08:30:28 CET [2613]: [33191-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint starting: time
2014-10-27 08:31:37 CET [2613]: [33192-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint complete: wrote 690 buffers (0.1%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=69.071 s, sync=0.008 s, total=69.082 s; sync files=83, longest=0.000 s, average=0.000 s
2014-10-27 08:40:28 CET [2613]: [33193-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint starting: time
2014-10-27 08:42:12 CET [2613]: [33194-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint complete: wrote 1041 buffers (0.1%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=104.147 s, sync=0.009 s, total=104.158 s; sync files=95, longest=0.000 s, average=0.000 s
Par contre, je n'ai rien dans "l'onglet" Checkpoints, d'une sortie HTML de PgBadger 5, exécuté de la façon suivante :
pgbadger -p '%t [%p]: [%l-1] user=%u %h %s ' -u postgres -o /home/postgres/pgbadger-5.0/HTML/postgres_20141027.html /home/postgres/pgbadger-5.0/LOGS/3-*.log
Hors ligne
Il n'y a pratiquement aucune écriture des checkpoints. Entre 500 et 1000 buffers, ça veut dire entre 4 et 8 Mo à écrire. Toutes les dix minutes. Rien du tout 
La durée d'écriture est plus étonnante mais comme le checkpointer va essayer de diluer ces écritures, ça n'est pas forcément un soucis.
Concernant pgBadger, le log_line_prefix que vous lui indiquez ne correspond pas aux traces que vous avez copié ici.
Guillaume.
Hors ligne
Bonjour Guillaume,
Voici la variable log_line_prefix de mon fichier postgresql.conf :
log_line_prefix = '%t [%p]: [%l-1] user=%u %h %s '
Vous avez raison, ci-dessous 2 lignes de traces :
2014-10-27 10:02:53 CET [2613]: [33210-1] user= 2014-07-04 03:04:54 CEST LOG: checkpoint complete: wrote 1444 buffers (0.1%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=144.695 s, sync=0.008 s, total=144.707 s; sync files=94, longest=0.000 s, average=0.000 s
2014-10-27 10:59:59 CET [1472]: [131358-1] user=serveurgeo 10.36.4.12 2014-10-16 00:02:13 CEST LOG: duration: 0.061 ms bind S_2: INSERT INTO geo.QUANTITE (code_cgu_site,id_perso,id_ligne,id_plan,id_fonction,id_operation,of_sec,id_document,jour,heure,nb_page,quantite,qta,qtb,qtc,qtd) values($1,$2,$3,$4,$5,$6,$7,$8,to_date($9,'yyyymmdd'),$10,$11,$12,$13,$14,$15,$16)
Sur la ligne de trace du checkpoint, il manque les %u (Utilisateur) et %h (adresse IP), mais je ne sais pas pourquoi ?
Même chose avec les analyze et vacuum (je n'ai rien dans l'onglet "vacuum" d'un html pgbadger) :
2014-10-27 18:17:20 CET [30436]: [2-1] user= 2014-10-27 18:17:19 CET LOG: automatic analyze of table "geo.geo.pointage_test" system usage: CPU 0.00s/0.47u sec elapsed 0.53 sec
J'ai l'impression que je n'ai pas les infos sur les traces générées par le serveur lui-même.
Hors ligne
Vous n'avez pas d'utilisateur et d'adresse IP pour les processus en tâche de fond de PostgreSQL. Par exemple, le checkpointer n'est pas connecté à une base.
Guillaume.
Hors ligne
Bonjour Guillaume,
J'ai relancé PgBadger comme cela :
pgbadger -p '%t [%p]: [%l-1] user= %s ' -o /home/postgres/pgbadger-5.0/test.html /home/postgres/pgbadger-5.0/LOGS/1-08.log
J'ai à présent les graphs des checkpoints, pas de warnings.
En le faisant sur plusieurs journées, je vois que les pics sont principalement sur l'export journalier et un traitement en particulier, qui s'exécute entre 5h et 6h, qui fait beaucoup de Insert/Update sur 3-4 tables.
Quelles informations vouliez-vous voir en me demandant de "Tracez les checkpoints" ?
Dernière modification par mortimer.pw (07/11/2014 10:48:03)
Hors ligne
Moi rien. Mais vous, ça vous donne des infos sur la quantité d'écriture, sur l'utilisation des journaux (combien créés, supprimés, recyclés). Ce sont des informations importantes pour savoir s'il y a beaucoup d'écritures ou non.
Guillaume.
Hors ligne
Bonjour Guillaume,
Ok. Pas de warning.
Que du recyclage, une moyenne de 3 à 4 fichiers par heure.
Quelques pics d'écriture, dont un "Checkpoint Peak" de 48027 buffers (quelle est la taille d'un buffer ?), qui correspondent à un traitement journalier qui fait beaucoup d'INSERT/UPDATE.
Dans une précédente réponse, vous me disiez "Le nombre de blocs écrits par le checkpointer et le writer est plus important que le nombre de blocs écrits par les processus postgres. Ce qui est bien. Il n'empêche que le ratio est plutôt inquiétant. Ça peut être dû à l'utilisation de peu de connexions, à un cache trop petit ou à des checkpoints peu fréquents.", comment identifier la cause ? pas de warning donc les checkpoints peu fréquents sont écartés ?
Dernière modification par mortimer.pw (13/11/2014 11:44:03)
Hors ligne
quelle est la taille d'un buffer ?
8Ko par défaut.
comment identifier la cause ? pas de warning donc les checkpoints peu fréquents sont écartés ?
Pour le cache, il faut regarder le hit ratio. S'il est bon, c'est que le cache est suffisant. S'il est mauvais... bin malheureusement, ça ne veut pas forcément dire que le cache insuffisant. Ce serait trop simple aussi
Pour le "peu de connexions", vous devriez savoir si vous avez 5 connexions qui se battent en duel ou 500. Ça devrait être simple à savoir
Quant aux checkpoints peu fréquents, faut regarder dans les traces tous les combien ils démarrent et à cause de quoi.
Guillaume.
Hors ligne
Bonjour Guillaume,
Désolé de reprendre ce post un peu tardivement, mais d'autres priorités :-(
Donc mon hit ratio reste à plus de 96%.
Concernant le nombre de connexions :
20 utilisateurs qui font en moyenne par jour (7h-17h) : 150000 insert, 25000 update, très peu de select
75 utilisateurs qui font en moyenne par jour (7h-17h) : 40000 select, 50000 appels à des procédures stockées, 40 insert, 15 update
1 utilisateur qui fait en moyenne chaque jour à 5h : 90000 select, 150000 insert, 150000 update
10 utilisateurs en moyenne par jour qui font un peu de select/insert/update/delete en cours de journée
20 utilisateurs en moyenne par jour qui font quelques "gros select" au cours de la journée
Pour les checkpoints, toutes les 10min (checkpoint_timeout), aucun warning, moins d'une dizaine de fichiers recyclés à l'heure.
Reste que les buffers_backend ne sont pas "très fortement supérieurs" aux buffers_checkpoint et buffers_clean. C'est la 1ère chose qui m'inquiête.
La 2ème chose qui m'inquiête ce n'est pas trop le volume des écritures, qui a certes augmenté mais comme nous avons intégré les données et traitements de différents sites dans cette base "centralisée" je pense que c'est normal. Par contre le volume des lectures a très fortement augmenté. Cela veut bien dire que davantage de données doivent être lu sur disque et pas en mémoire ? le hit ratio ne devrait-il pas se dégrader ?
Hors ligne
Reste que les buffers_backend ne sont pas "très fortement supérieurs" aux buffers_checkpoint et buffers_clean. C'est la 1ère chose qui m'inquiête.
Vu qu'un seul utilisateur, qui utilise à priori une seule connexion, fait la majorité des écritures, ce résultat n'est pas étonnant.
La 2ème chose qui m'inquiête ce n'est pas trop le volume des écritures, qui a certes augmenté mais comme nous avons intégré les données et traitements de différents sites dans cette base "centralisée" je pense que c'est normal. Par contre le volume des lectures a très fortement augmenté. Cela veut bien dire que davantage de données doivent être lu sur disque et pas en mémoire ? le hit ratio ne devrait-il pas se dégrader ?
Si ce sont toujours les mêmes données, le hit ratio peut très bien ne pas diminuer.
Guillaume.
Hors ligne
Bonjour Guillaume,
Pour le 1er point, je ne comprend pas, il y a également 20 utilisateurs qui font 150000 insert. Pouvez-vous m'expliquer ?
Pour le 2ème point, nous avons :
Quelques tables avec des "données statiques" (moins de 400Mo pour l'ensemble des tables avec leurs indexes),
Deux tables avec des "données dynamiques" (tables avec horodatage purgées cycliquement sur une période de 3 mois, volume 1,5Go tables+indexes). Les 20 utilisateurs qui font les 150000 inserts le font dans ces tables.
une table qui grossit au fil du temps (12Go aujourd'hui table+indexes). L'utilisateur qui fait les 150000 inserts le fait sur cette table et les 150000 update sur les 2 tables précédentes.
Donc se ne sont pas toujours les mêmes données.
Hors ligne
> Pour le 1er point, je ne comprend pas, il y a également 20 utilisateurs qui font 150000 insert. Pouvez-vous m'expliquer ?
Mauvaise lecture de ma part. Désolé.
> Pour le 2ème point
Ça ne veut pas dire que le volume en lui-même soit si conséquent. En tout cas, si PostgreSQL vous dit 96% de lecture en cache, c'est que c'est bien ce qu'il fait.
Guillaume.
Hors ligne
Bonjour Guillaume,
Mais alors le fait que les buffers_backend ne soient pas "très fortement supérieurs" aux buffers_checkpoint et buffers_clean est-il inquiétant ?
Je ne comprend pas pourquoi le hit ratio reste si haut si de plus en plus de lectures se font sur disque ?
Désolé d'être insistant.
Hors ligne
Bonjour à tous,
Meilleurs vœux pour cette nouvelle année qui commence.
Je me permets un dernier up afin d'obtenir de l'aide pour régler ce problème.
D'avance merci.
Hors ligne
Mais alors le fait que les buffers_backend ne soient pas "très fortement supérieurs" aux buffers_checkpoint et buffers_clean est-il inquiétant ?
Si, c'est inquiétant. J'avoue que j'ai du mal à voir ce qu'il se passe. Pourriez-vous compiler et installer l'outil sur https://github.com/gleu/pgstats et utiliser l'appli pgstat pour en savoir plus sur les écritures ? Merci.
Je ne comprend pas pourquoi le hit ratio reste si haut si de plus en plus de lectures se font sur disque ?
Même chose, utiliser pgstat avec pour la statistique database (pgstat -s database).
Guillaume.
Hors ligne
