======Monitoring de la base postgres======
--- //[[f.raynaud@arles-linux.org|François Raynaud]] 2020/06/08 15:55//
Dans ce chapître, nous allons récupérer les statistiques d'activité de la base et les analyser
[[https://docs.postgresql.fr/8.2/monitoring-stats.html#monitoring-stats-views-table|source monitoring stat]]
====Processus====
Processus maître (475) et 6 processus fils.
ps auxww | grep ^postgres
postgres 475 0.0 2.7 214340 27700 S 11:36 0:02 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
postgres 504 0.0 1.6 214468 17048 Ss 11:36 0:00 postgres: 11/main: checkpointer
postgres 505 0.0 0.6 214480 6092 Ss 11:36 0:00 postgres: 11/main: background writer
postgres 506 0.0 0.9 214340 9912 Ss 11:36 0:01 postgres: 11/main: walwriter
postgres 507 0.0 0.7 214900 8032 Ss 11:36 0:01 postgres: 11/main: autovacuum launcher
postgres 508 0.0 0.5 69536 5324 Ss 11:36 0:02 postgres: 11/main: stats collector
postgres 509 0.0 0.6 214764 6972 Ss 11:36 0:00 postgres: 11/main: logical replication launcher
la récupération de statistiques (stats collector) ajoute un temps supplémentaire à l'exécution de la requête, le système peut être configuré pour récupérer ou non des informations.
activité du serveur postgres (1 ligne par processus serveur)
select pid,usename,datname from pg_stat_activity ;
pid | usename | datname
------+----------+-----------
507 | |
509 | postgres |
4207 | deb | openstock 1 processus par utilisateur
505 | |
504 | |
506 | |
select * from pg_stat_activity where datname='openstock' and usename='deb';
datid|16384
datname|openstock pid|4207 usesysid|18845 usename|deb application_name|psql
client_addr| client_hostname| client_port|-1
backend_start|2020-05-08 19:25:53.791812+02
xact_start|2020-05-08 19:37:12.941945+02
query_start|2020-05-08 19:37:12.941945+02
state_change|2020-05-08 19:37:12.94195+02
wait_event_type| wait_event| state|active
backend_xid| backend_xmin|679
query|select * from pg_stat_activity where datname='openstock' and usename='deb';
backend_type|client backend
==== BASE ====
pg_stat_database : 1 ligne par base de données
select * from pg_stat_database where datname ='openstock';
datid|16384
datname|openstock
numbackends|1
xact_commit|2392 * nombre de transactions validaes
xact_rollback|12 * nombre de transactions non validées
blks_read|810 blks_hit|157974 (nombre de lecture dans le tampon)
tup_returned|2214352 tup_fetched|30262
tup_inserted|29 tup_updated|3 tup_deleted|0 : nblignes insérées, modifiées supprimées
conflicts|0 ***
temp_files|0 temp_bytes|0
deadlocks|0 ***
blk_read_time|0 blk_write_time|0
stats_reset|2020-05-07 11:39:20.587674+02
==== TABLES ====
pg_stat_all_tables pg_stat_sys_tables pg_stat_user_tables
select * from pg_stat_user_tables where relname ='jpa';
relid|27036 schemaname|public relname|jpa
seq_scan|4 seq_tup_read|4 : parcours sequentiels et nb lignes récupérées
idx_scan| idx_tup_fetch| : parcours index et nombre de lignes récupérés
*** insertion/modification/suppression
n_tup_ins|1 n_tup_upd|0 n_tup_del|0 n_tup_hot_upd|0 n_live_tup|1 n_dead_tup|0
*** vacum et analyse manuel / vacum et analyse fait par démon
n_mod_since_analyze|1 last_vacuum| last_autovacuum| last_analyze| last_autoanalyze|
vacuum_count|0 autovacuum_count|0
analyze_count|0 autoanalyze_count|0
==== INDEX ====
pg_stat_all_indexes pg_stat_sys_indexes pg_stat_user_indexes
select * from pg_stat_user_indexes where relname='article';
relid|18236
indexrelid|18243
schemaname|openstock_association
relname|article indexrelname|article_pkey
idx_scan|0 idx_tup_read|0 idx_tup_fetch|0 : nb parcours index, nb entrée de l index renvoyées, nb lignes actives
====Les verrous ====
[[https://docs.postgresql.fr/8.2/view-pg-locks.html|source]]
La vue pg_locks fournit un accès aux informations concernant les verrous détenus par les transactions ouvertes sur le serveur de bases de données.
select * from pg_locks;
==== VACCUM et ANALYZE ====
[[https://docs.postgresql.fr/8.2/sql-vacuum.html|source]]
VACUUM — récupère l'espace inutilisé (lignes supprimées) et, optionnellement, analyse une base.
A faire sur les tables frequemment mises à jour.
vacuum verbose openstock_association.facture;
INFO: vacuuming "openstock_association.facture"
INFO: index "facture_pkey" now contains 2 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "facture": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 5 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_564276"
INFO: index "pg_toast_564276_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_564276": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
Query returned successfully with no result in 92 msec.
analyse verbose openstock_association.facture;
INFO: analyzing "openstock_association.facture"
INFO: "facture": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
Query returned successfully with no result in 34 msec.
VACUUM VERBOSE ANALYZE openstock_association.facture;
===Recommandations===
* planifier un VACUUM général sur toute la base une fois par jour, en dehors des horaires normaux de production
* VACUUM FULL est recommandé dans les cas où vous savez que vous avez supprimé la majorité des lignes dans une table
* TRUNCATE (au lieu de delete) supprime le contenu entier de la table immédiatement sans nécessiter un VACUUM ou VACUUM FULL pour réclamer l'espace disque maintenant inutilisé
==== autovacuum ====
autovacuum (boolean): Contrôle si le serveur doit démarrer le démon d'autovacuum.
(paramètre de postgresql.conf). track_counts doit être aussi activé (statistiques)
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.