— 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

source monitoring stat

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

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

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

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 

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;

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;

• 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 (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.