ProHoster > Blog > Administration > Une des méthodes pour obtenir le profil de charge de travail et l'historique des attentes dans PostgreSQL

Une des méthodes pour obtenir le profil de charge de travail et l'historique des attentes dans PostgreSQL

Suite de l'article «Tentative de création d'un équivalent d'ASH pour PostgreSQL «.

Cet article examinera et illustrera, Ă  l'aide de requĂȘtes et d'exemples concrets, quelles informations utiles peuvent ĂȘtre obtenues grĂące Ă  la vue pg_stat_activity.

Avertissement.
En raison de la nouveauté du sujet et de l'achÚvement du processus de test, l'article peut contenir des erreurs. Les critiques et commentaires sont les bienvenus et attendus.

Données d'entrée

Vue d'historique pg_stat_statements

pg_stat_history

CREATE TABLE pg_stat_history ( 
id SERIAL, 
snapshot_timestamp  timestamp without time zone, 
database_id integer,
dbid oid,
userid oid,
queryid bigint,
query text,
calls bigint, 
total_time double precision, 
min_time double precision, 
max_time double precision, 
mean_time double precision,
stddev_time double precision,
rows bigint,
shared_blks_hit bigint, 
shared_blks_read bigint, 
shared_blks_dirtied bigint, 
shared_blks_written bigint, 
local_blks_hit bigint, 
local_blks_read bigint,
local_blks_dirtied bigint, 
local_blks_written bigint, 
temp_blks_read bigint,
temp_blks_written bigint,
blk_read_time double precision, 
blk_write_time double precision, 
baseline_id integer );

La table est remplie chaque heure en utilisant dblink vers la base de données cible. La colonne la plus intéressante et utile de la table est bien sûr queryid.

Vue d'historique pg_stat_activity

archive_pg_stat_activity

CREATE TABLE archive_pg_stat_activity
(
  timepoint timestamp without time zone,
  datid             oid, 
  datname           name,
  pid               integer,
  usesysid          oid,
  usename           name,
  application_name  text,
  client_addr       inet,
  client_hostname   text,
  client_port       integer,
  backend_start     timestamp without time zone,
  xact_start        timestamp without time zone,
  query_start       timestamp without time zone,
  state_change      timestamp without time zone,
  wait_event_type   text,                     
  wait_event        text,                   
  state             text,                  
  backend_xid       xid,                 
  backend_xmin      xid,                
  query             text,               
  backend_type      text,
  queryid           bigint
);

La table reprĂ©sente une table partitionnĂ©e par heure de history_pg_stat_activity (En savoir plus ici — pg_stat_statements + pg_stat_activity + loq_query = pg_ash ? et ici — Tentative de crĂ©er un Ă©quivalent ASH pour PostgreSQL.)

Sortie

TEMPS CPU TOTAL (SYSTÈME + CLIENTS )

RequĂȘte

AVEC 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 heure') AND pg_stat_history_end+(current_hour_diff * interval '1 heure')  AND 
			( aa.wait_event_type IS NULL  ) AND
			aa.state = 'active'
 )
 SELECT count(*) 
 INTO cpu_total
 FROM t ;

Exemple

TEMPS CPU TOTAL (SYSTÈME + CLIENTS ) : 28:37:46

TEMPS D'ATTENTE TOTAL

RequĂȘte

AVEC 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 heure') AND pg_stat_history_end+(current_hour_diff * interval '1 heure')  AND 
			( aa.wait_event_type IS NOT NULL  ) AND
			aa.state = 'active'
 )
 SELECT count(*) 
 INTO cpu_total
 FROM t ;

Exemple

TEMPS D'ATTENTE TOTAL : 30:12:49

Valeurs totales pg_stat_statements

RequĂȘte

 --TOTAL pg_stat
  SELECT 
    SUM(calls) AS calls, SUM(total_time) AS total_time, SUM(rows) AS rows ,
	SUM(shared_blks_hit) AS shared_blks_hit, SUM(shared_blks_read) AS shared_blks_read ,
	SUM(shared_blks_dirtied) AS shared_blks_dirtied, SUM(shared_blks_written) AS shared_blks_written , 
    SUM(local_blks_hit) AS local_blks_hit , SUM(local_blks_read) AS local_blks_read , 
	SUM(local_blks_dirtied) AS local_blks_dirtied , SUM(local_blks_written) AS local_blks_written,
	SUM(temp_blks_read) AS temp_blks_read, SUM(temp_blks_written) AS temp_blks_written , 
	SUM(blk_read_time) AS blk_read_time , SUM(blk_write_time) AS blk_write_time
  INTO 
    pg_total_stat_history_rec
  FROM 
    pg_stat_history
  WHERE 
 	snapshot_timestamp BETWEEN pg_stat_history_begin AND pg_stat_history_end AND 
	queryid IS NULL;

SQL DBTIME — temps d'exĂ©cution total des requĂȘtes

RequĂȘte

dbtime_total = interval '1 millisecond' * pg_total_stat_history_rec.total_time ;

Exemple

SQL DBTIME : 136:49:36

SQL CPU TIME — temps CPU consacrĂ© Ă  l'exĂ©cution des requĂȘtes

RequĂȘte

WITH 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
			( aa.wait_event_type IS NULL ) AND
			backend_type = 'client backend' AND 
			aa.state = 'active'
 )
 SELECT count(*) 
 INTO cpu_total
 FROM t ;

Exemple

SQL CPU TIME : 27:40:15

SQL WAITINGS TIME — temps total d'attente pour les requĂȘtes

RequĂȘte

WITH 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
			( aa.wait_event_type IS NOT NULL ) AND
			aa.state = 'active' AND 
		backend_type = 'client backend'
 )
 SELECT count(*) 
 INTO waiting_total
 FROM t ;

Exemple

SQL WAITINGS TIME : 30:04:09

Les requĂȘtes suivantes sont triviales et pour Ă©conomiser de l'espace, les dĂ©tails de mise en Ɠuvre sont omis :

Exemple

| SQL IOTIME : 19:44:50
| SQL READ TIME : 19:44:32
| SQL WRITE TIME : 00:00:17
|
| SQL CALLS : 12188248
-------------------------------------------------------------
| SQL SHARED BLOCKS READS : 7997039120
| SQL SHARED BLOCKS HITS : 8868286092
| SQL SHARED BLOCKS HITS/READS % : 110.89
| SQL SHARED BLOCKS DIRTED : 419945
| SQL SHARED BLOCKS WRITTEN : 19857
|
| SQL TEMPORARY BLOCKS READS : 7836169
| SQL TEMPORARY BLOCKS WRITTEN : 10683938

Passons au chapitre le plus intéressant

STATISTIQUES D'ATTENTE

TOP 10 DES ATTENTES PAR TEMPS D'ATTENTE TOTAL POUR LES PROCESSUS CLIENTS

RequĂȘte

SELECT 
  wait_event_type , wait_event ,
  get_system_waiting_duration( wait_event_type , wait_event ,pg_stat_history_begin+(current_hour_diff * interval '1 hour') ,pg_stat_history_end+(current_hour_diff * interval '1 hour') ) AS duration 
FROM
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL 
GROUP BY 
  wait_event_type, wait_event
ORDER BY 3 DESC
LIMIT 10

Exemple

+------------------------------------------------------------------------------------
| TOP 10 ATTENTES PAR TEMPS TOTAL D'ATTENTE POUR LES PROCESSUS SYSTÈMES
+-----+------------------------------+--------------------+--------------------
|    #|               type_d'attente|          événement_d'attente|            durée
+-----+------------------------------+--------------------+--------------------
|    1|                      Activité| LogicalLauncherMain|            10:43:28
|    2|                      Activité|      AutoVacuumMain|            10:42:49
|    3|                      Activité|       WalWriterMain|            10:28:53
|    4|                      Activité|    CheckpointerMain|            10:23:50
|    5|                      Activité|        BgWriterMain|            09:11:59
|    6|                      Activité|   BgWriterHibernate|            01:37:46
|    7|                            IO|        BufFileWrite|            00:02:35
|    8|                        LWLock|      buffer_mapping|            00:01:54
|    9|                            IO|        DataFileRead|            00:01:23
|   10|                            IO|            WALWrite|            00:00:59
+-----+------------------------------+--------------------+--------------------

TOP 10 DES ATTENTES PAR TEMPS D'ATTENTE TOTAL POUR LES PROCESSUS CLIENTS

RequĂȘte

SÉLECTIONNER 
  type_d'attente , événement_d'attente ,
  get_clients_waiting_duration( type_d'attente , événement_d'attente , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as durée
DE 
  activity_hist.archive_pg_stat_activity aa
OÙ 
  timepoint ENTRE pg_stat_history_begin+(current_hour_diff * interval '1 hour') ET pg_stat_history_end+(current_hour_diff * interval '1 hour') ET backend_type = 'client backend' ET type_d'attente EST NOT NULL 
GROUP BY type_d'attente, événement_d'attente
ORDRE PAR 3 DESC
LIMIT 10

Exemple

+-----+------------------------------+--------------------+--------------------+----------
|    #|               type_d'attente|          événement_d'attente|            durée|  % temps_db
+-----+------------------------------+--------------------+--------------------+----------
|    1|                          Verrou|       transactionid|            08:16:47|      6.05
|    2|                            IO|        DataFileRead|            06:13:41|      4.55
|    3|                       Timeout|             PgSleep|            02:53:21|      2.11
|    4|                        LWLock|      buffer_mapping|            00:40:42|       0.5
|    5|                        LWLock|           buffer_io|            00:17:17|      0.21
|    6|                            IO|        BufFileWrite|            00:01:34|      0.02
|    7|                          Verrou|               tuple|            00:01:32|      0.02
|    8|                        Client|          ClientRead|            00:01:19|      0.02
|    9|                            IO|         BufFileRead|            00:00:37|      0.01
|   10|                        LWLock|      buffer_content|            00:00:08|         0
+-----+------------------------------+--------------------+--------------------+----------

TYPES D'ATTENTES PAR TEMPS TOTAL D'ATTENTE, POUR LES PROCESSUS SYSTÈMES

RequĂȘte

SÉLECTIONNER 
  type_d'attente ,
  get_system_waiting_type_duration( type_d'attente , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as durée
DE
  activity_hist.archive_pg_stat_activity aa
OÙ 
  timepoint ENTRE pg_stat_history_begin+(current_hour_diff * interval '1 hour') ET pg_stat_history_end+(current_hour_diff * interval '1 hour') ET  backend_type != 'client backend' ET type_d'attente EST NOT NULL 
GROUP BY type_d'attente
ORDRE PAR 2 DESC

Exemple

+-----+------------------------------+--------------------
|    #|               type_d_attente|            durée
+-----+------------------------------+--------------------
|    1|                      Activité|            53:08:45
|    2|                            I/O|            00:06:24
|    3|                        LWLock|            00:03:02
+-----+------------------------------+--------------------

TYPES D'ATTENTE PAR TEMPS D'ATTENTE TOTAL, POUR LES PROCESSES CLIENT

RequĂȘte

SELECT 
  type_d_attente ,
  get_clients_waiting_type_duration( type_d_attente , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as durée
FROM
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND type_d_attente IS NOT NULL 
GROUP BY type_d_attente
ORDER BY 2 DESC

Exemple

+-----+------------------------------+--------------------+--------------------
|    #|               type_d_attente|            durée|            % dbtime
+-----+------------------------------+--------------------+--------------------
|    1|                          Verrou|            08:18:19|                6.07
|    2|                            I/O|            06:16:01|                4.58
|    3|                       DĂ©lai|            02:53:21|                2.11
|    4|                        LWLock|            00:58:12|                0.71
|    5|                        Client|            00:01:19|                0.02
|    6|                           IPC|            00:00:04|                   0
+-----+------------------------------+--------------------+--------------------

DurĂ©es d'attente, pour les processus systĂšme et les requĂȘtes individuelles.

ATTENTES POUR LES PROCESSES SYSTÈMES

RequĂȘte

SELECT 
  type_backend , datname , type_d_attente , évÚnement_d_attente , get_backend_type_waiting_duration( type_backend , type_d_attente , évÚnement_d_attente , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as durée 
FROM 
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND type_backend != 'client backend' AND type_d_attente IS NOT NULL 
GROUP BY type_backend , datname , type_d_attente , Ă©vĂšnement_d'attente
ORDER BY 5 DESC

Exemple

+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    #|                 backend_type|    dbname|     wait_event_type|            wait_event|            duration
+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    1| lancement de réplication logique|          |            Activité|   LogicalLauncherMain|            10:43:28
|    2|          lancement d'autovacuum|          |            Activité|        AutoVacuumMain|            10:42:49
|    3|                    walwriter|          |            Activité|         WalWriterMain|            10:28:53
|    4|                 vérificateur|          |            Activité|      CheckpointerMain|            10:23:50
|    5|            écrivain d'arriÚre-plan|          |            Activité|          BgWriterMain|            09:11:59
|    6|            écrivain d'arriÚre-plan|          |            Activité|     BgWriterHibernate|            01:37:46
|    7|              travailleur parallĂšle|      tdb1|                  IO|          BufFileWrite|            00:02:35
|    8|              travailleur parallĂšle|      tdb1|              LWLock|        buffer_mapping|            00:01:41
|    9|              travailleur parallĂšle|      tdb1|                  IO|          DataFileRead|            00:01:22
|   10|              travailleur parallĂšle|      tdb1|                  IO|           BufFileRead|            00:00:59
|   11|                    walwriter|          |                  IO|              WALWrite|            00:00:57
|   12|              travailleur parallĂšle|      tdb1|              LWLock|             buffer_io|            00:00:47
|   13|            travailleur d'autovacuum|      tdb1|              LWLock|        buffer_mapping|            00:00:13
|   14|            Ă©crivain d'arriĂšre-plan|          |                  IO|         DataFileWrite|            00:00:12
|   15|                 vérificateur|          |                  IO|         DataFileWrite|            00:00:11
|   16|                    walwriter|          |              LWLock|          WALWriteLock|            00:00:09
|   17|                 vérificateur|          |              LWLock|          WALWriteLock|            00:00:06
|   18|            Ă©crivain d'arriĂšre-plan|          |              LWLock|          WALWriteLock|            00:00:06
|   19|                    walwriter|          |                  IO|          WALInitWrite|            00:00:02
|   20|            travailleur d'autovacuum|      tdb1|              LWLock|          WALWriteLock|            00:00:02
|   21|                    walwriter|          |                  IO|           WALInitSync|            00:00:02
|   22|            travailleur d'autovacuum|      tdb1|                  IO|          DataFileRead|            00:00:01
|   23|                 vérificateur|          |                  IO| ControlFileSyncUpdate|            00:00:01
|   24|            Ă©crivain d'arriĂšre-plan|          |                  IO|              WALWrite|            00:00:01
|   25|            Ă©crivain d'arriĂšre-plan|          |                  IO|         DataFileFlush|            00:00:01
|   26|                 vérificateur|          |                  IO|         SLRUFlushSync|            00:00:01
|   27|            travailleur d'autovacuum|      tdb1|                  IO|              WALWrite|            00:00:01
|   28|                 vérificateur|          |                  IO|          DataFileSync|            00:00:01
+-----+-----------------------------+----------+--------------------+----------------------+--------------------

ATTENTES POUR SQL — attentes pour des requĂȘtes spĂ©cifiques par queryid

RequĂȘte

SÉLECTIONNER 
queryid, datname, wait_event_type, wait_event, get_query_waiting_duration( queryid, wait_event_type, wait_event, pg_stat_history_begin+(current_hour_diff * interval '1 hour'), pg_stat_history_end+(current_hour_diff * interval '1 hour') ) comme durée 
FROM 
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint ENTRE pg_stat_history_begin+(current_hour_diff * interval '1 hour') ET pg_stat_history_end+(current_hour_diff * interval '1 hour') ET backend_type = 'client backend' ET wait_event_type EST NON NULL ET queryid EST NON NULL 
GROUPE PAR queryid, datname, wait_event_type, wait_event
ORDER BY 1, 5 DESC

Exemple

+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
|    #|                  queryid|    dbname|     wait_event_type|          wait_event|            waitings|               total
|     |                         |          |                    |                    |            duration|            duration
+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
|    1|     -8247416849404883188|      tdb1|              Client|          ClientRead|            00:00:02|
|    2|     -6572922443698419129|      tdb1|              Client|          ClientRead|            00:00:05|
|    3|     -6572922443698419129|      tdb1|                  IO|        DataFileRead|            00:00:01|
|    4|     -5917408132400665328|      tdb1|              Client|          ClientRead|            00:00:04|
|    5|     -4091009262735781873|      tdb1|              Client|          ClientRead|            00:00:03|
|    6|     -1473395109729441239|      tdb1|              Client|          ClientRead|            00:00:01|
|    7|        28942442626229688|      tdb1|                  IO|        BufFileWrite|            00:01:34|            00:46:06
|    8|        28942442626229688|      tdb1|              LWLock|      buffer_mapping|            00:01:05|            00:46:06
|    9|        28942442626229688|      tdb1|                  IO|        DataFileRead|            00:00:44|            00:46:06
|   10|        28942442626229688|      tdb1|                  IO|         BufFileRead|            00:00:37|            00:46:06
|   11|        28942442626229688|      tdb1|              LWLock|           buffer_io|            00:00:35|            00:46:06
|   12|        28942442626229688|      tdb1|              Client|          ClientRead|            00:00:05|            00:46:06
|   13|        28942442626229688|      tdb1|                 IPC| MessageQueueReceive|            00:00:03|            00:46:06
|   14|        28942442626229688|      tdb1|                 IPC|    BgWorkerShutdown|            00:00:01|            00:46:06
|   15|       389015618226997618|      tdb1|                Lock|       transactionid|            03:55:09|            04:14:15
|   16|       389015618226997618|      tdb1|                  IO|        DataFileRead|            03:23:09|            04:14:15
|   17|       389015618226997618|      tdb1|              LWLock|      buffer_mapping|            00:12:09|            04:14:15
|   18|       389015618226997618|      tdb1|              LWLock|           buffer_io|            00:10:18|            04:14:15
|   19|       389015618226997618|      tdb1|                Lock|               tuple|            00:00:35|            04:14:15
|   20|       389015618226997618|      tdb1|              LWLock|        WALWriteLock|            00:00:02|            04:14:15
|   21|       389015618226997618|      tdb1|                  IO|       DataFileWrite|            00:00:01|            04:14:15
|   22|       389015618226997618|      tdb1|              LWLock|        SyncScanLock|            00:00:01|            04:14:15
|   23|       389015618226997618|      tdb1|              Client|          ClientRead|            00:00:01|            04:14:15
|   24|       734234407411547467|      tdb1|              Client|          ClientRead|            00:00:11|
|   25|       734234407411547467|      tdb1|              LWLock|      buffer_mapping|            00:00:05|
|   26|       734234407411547467|      tdb1|                  IO|        DataFileRead|            00:00:02|
|   27|      1237430309438971376|      tdb1|              LWLock|      buffer_mapping|            00:02:18|            02:45:40
|   28|      1237430309438971376|      tdb1|                  IO|        DataFileRead|            00:00:27|            02:45:40
|   29|      1237430309438971376|      tdb1|              Client|          ClientRead|            00:00:02|            02:45:40
|   30|      2404820632950544954|      tdb1|              Client|          ClientRead|            00:00:01|
|   31|      2515308626622579467|      tdb1|              Client|          ClientRead|            00:00:02|
|   32|      4710212362688288619|      tdb1|              LWLock|      buffer_mapping|            00:03:08|            02:18:21
|   33|      4710212362688288619|      tdb1|                  IO|        DataFileRead|            00:00:22|            02:18:21
|   34|      4710212362688288619|      tdb1|              Client|          ClientRead|            00:00:06|            02:18:21
|   35|      4710212362688288619|      tdb1|              LWLock|           buffer_io|            00:00:02|            02:18:21
|   36|      9150846928388977274|      tdb1|                  IO|        DataFileRead|            00:01:19|
|   37|      9150846928388977274|      tdb1|              LWLock|      buffer_mapping|            00:00:34|
|   38|      9150846928388977274|      tdb1|              Client|          ClientRead|            00:00:10|
|   39|      9150846928388977274|      tdb1|              LWLock|           buffer_io|            00:00:01|
+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------

STATISTIQUES SQL CLIENT — TOP des requĂȘtes

Les requĂȘtes pour obtenir Ă  nouveau, sont triviales et pour Ă©conomiser de l'espace, ne sont pas indiquĂ©es.

Exemples

+------------------------------------------------------------------------------------
| CLIENT SQL classĂ© par Temps ÉcoulĂ©
+--------------------+----------+----------+----------+----------+----------+--------------------
|        temps écoulé|     appels|  % dbtime|     % CPU|      % IO|    nom_base|             queryid
+--------------------+----------+----------+----------+----------+----------+--------------------
|            04:14:15|        19|       3.1|     10.83|     11.52|      tdb1|  389015618226997618
|            02:45:40|       746|      2.02|      4.23|      0.08|      tdb1| 1237430309438971376
|            02:18:21|       749|      1.69|      3.39|       0.1|      tdb1| 4710212362688288619
|            00:46:06|       375|      0.56|      0.94|      0.41|      tdb1|   28942442626229688
+--------------------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL classé par Temps CPU
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            temps cpu|     appels|  % dbtime|temps_total|     % CPU|      % IO|    nom_base|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            02:59:49|        19|       3.1|  04:14:15|     10.83|     11.52|      tdb1|  389015618226997618
|            01:10:12|       746|      2.02|  02:45:40|      4.23|      0.08|      tdb1| 1237430309438971376
|            00:56:15|       749|      1.69|  02:18:21|      3.39|       0.1|      tdb1| 4710212362688288619
|            00:15:35|       375|      0.56|  00:46:06|      0.94|      0.41|      tdb1|   28942442626229688
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL classé par Temps d'Attente I/O Utilisateur
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|        temps_io_wait|     appels|  % dbtime|temps_total|     % CPU|      % IO|    nom_base|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            03:23:10|        19|       3.1|  04:14:15|     10.83|     11.52|      tdb1|  389015618226997618
|            00:02:54|       375|      0.56|  00:46:06|      0.94|      0.41|      tdb1|   28942442626229688
|            00:00:27|       746|      2.02|  02:45:40|      4.23|      0.08|      tdb1| 1237430309438971376
|            00:00:22|       749|      1.69|  02:18:21|      3.39|       0.1|      tdb1| 4710212362688288619
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL classé par Lectures des Buffers Partagés
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|       lectures buffers|     appels|  % dbtime|temps_total|     % CPU|      % IO|    nom_base|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|          1056388566|        19|       3.1|  04:14:15|     10.83|     11.52|      tdb1|  389015618226997618
|            11709251|       375|      0.56|  00:46:06|      0.94|      0.41|      tdb1|   28942442626229688
|             3439004|       746|      2.02|  02:45:40|      4.23|      0.08|      tdb1| 1237430309438971376
|             3373330|       749|      1.69|  02:18:21|      3.39|       0.1|      tdb1| 4710212362688288619
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL classé par Temps de Lectures de Disque
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|           temps lecture|     appels|  % dbtime|temps_total|     % CPU|      % IO|    nom_base|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            02:16:30|        19|       3.1|  04:14:15|     10.83|     11.52|      tdb1|  389015618226997618
|            00:04:50|       375|      0.56|  00:46:06|      0.94|      0.41|      tdb1|   28942442626229688
|            00:01:10|       749|      1.69|  02:18:21|      3.39|       0.1|      tdb1| 4710212362688288619
|            00:00:57|       746|      2.02|  02:45:40|      4.23|      0.08|      tdb1| 1237430309438971376
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL classé par Exécutions
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|               appels|      lignes|  % dbtime|temps_total|     % CPU|      % IO|    nom_base|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|                 749|       749|      1.69|  02:18:21|      3.39|       0.1|      tdb1| 4710212362688288619
|                 746|       746|      2.02|  02:45:40|      4.23|      0.08|      tdb1| 1237430309438971376
|                 375|         0|      0.56|  00:46:06|      0.94|      0.41|      tdb1|   28942442626229688
|                  19|        19|       3.1|  04:14:15|     10.83|     11.52|      tdb1|  389015618226997618
+--------------------+----------+----------+----------+----------+----------+----------+--------------------

Conclusion

En utilisant les requĂȘtes prĂ©sentĂ©es et les rapports obtenus, vous pouvez obtenir une vision plus complĂšte pour analyser et rĂ©soudre les problĂšmes de dĂ©gradation des performances, tant pour les requĂȘtes individuelles que pour l'ensemble du cluster.

DĂ©veloppement

Les projets de développement suivants sont en cours :

  • ComplĂ©ter les rapports avec l'historique des verrouillages. Les requĂȘtes sont en cours de test et seront prĂ©sentĂ©es prochainement.
  • Utiliser l'extension TimescaleDB pour stocker l'historique de pg_stat_activity et pg_locks.
  • PrĂ©parer une solution en lot sur GitHub pour un dĂ©ploiement massif sur les bases de donnĂ©es de production.

À suivre...

Source : habr.com

Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS đŸ”„ Acheter un hĂ©bergement fiable pour les sites avec protection DDoS, serveurs VPS VDS | ProHoster