Geekly articles weekly

Une façon d'obtenir le profil de charge de travail et l'historique d'attente dans PostgreSQL

Suite de l'article " Essayer de créer un analogue d'ASH pour PostgreSQL ".

L'article sera examiné et affiché sur des requêtes et des exemples spécifiques - quelles informations utiles peuvent être obtenues en utilisant l'historique de présentation pg_stat_activity.
Avertissement
En raison de la nouveauté du sujet et de la période de test incomplète, l'article peut contenir des erreurs. La critique et les commentaires sont fortement encouragés et attendus.

Entrer les données


Afficher l'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 );

Le tableau est rempli toutes les heures à l'aide de dblink vers la base de données cible. La colonne la plus intéressante et utile du tableau, bien sûr, est queryid .

Pg_stat_activity afficher l'historique


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 est une table history_pg_stat_activity partitionnée par horloge (pour plus de détails, voir pg_stat_statements + pg_stat_activity + loq_query = pg_ash? Et voici une tentative de création d'un analogue d'ASH pour PostgreSQL.)

Mentions légales


CLUSTER CPU TIME (SYSTEM + CLIENTS)


Demande
 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 aa.state = 'active' ) SELECT count(*) INTO cpu_total FROM t ;

Exemple
 CLUSTER CPU TIME (SYSTEM + CLIENTS ) : 28:37:46

TEMPS D'ATTENTE DU CLUSTER


Demande
 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' ) SELECT count(*) INTO cpu_total FROM t ;

Exemple
 CLUSTER WAITINGS TIME : 30:12:49

Total des valeurs de pg_stat_statements


Demande
  --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) 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 - Total Query Runtime


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

Exemple
 SQL DBTIME : 136:49:36

SQL CPU TIME temps du CPU consacré à l'exécution des requêtes


Demande
 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 d'attente total pour les requêtes


Demande
 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 d'implémentation 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

Nous passons à la section la plus intéressante

ATTENTE STATIQUE


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


Demande
 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 DES ATTENTES PAR TEMPS D'ATTENTE TOTAL POUR LES PROCESSUS SYSTÈME
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  wait_event |  la 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 DES CLIENTS


Demande
 SELECT wait_event_type , wait_event , get_clients_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
  + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  # |  wait_event_type |  wait_event |  durée |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  1 |  Lock |  transactionid |  08: 16: 47 |  6.05
 |  2 |  IO |  DataFileRead |  06: 13: 41 |  4,55
 |  3 |  Délai d'expiration |  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 |  Lock |  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'ATTENTE PAR TEMPS D'ATTENTE TOTAL, POUR LES PROCESSUS SYSTÈME


Demande
 SELECT wait_event_type , get_system_waiting_type_duration( wait_event_type , 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 ORDER BY 2 DESC

Exemple
  + ----- + ------------------------------ + ------------ --------
 |  # |  wait_event_type |  la durée
 + ----- + ------------------------------ + ------------ --------
 |  1 |  Activité |  53:08:45
 |  2 |  IO |  00:06:24
 |  3 |  LWLock |  00:03:02
 + ----- + ------------------------------ + ------------ --------

TYPES D'ATTENTE PAR TEMPS D'ATTENTE TOTAL, POUR LES PROCESSUS DES CLIENTS


Demande
 SELECT wait_event_type , get_clients_waiting_type_duration( wait_event_type , 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 ORDER BY 2 DESC

Exemple
  + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  durée |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Lock |  08: 18: 19 |  6.07
 |  2 |  IO |  06: 16: 01 |  4,58
 |  3 |  Délai d'expiration |  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ée des attentes, pour les processus système et les demandes individuelles.

ATTENTE DES PROCESSUS SYSTÈME


Demande
 SELECT backend_type , datname , wait_event_type , wait_event , get_backend_type_waiting_duration( backend_type , 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 backend_type , datname , wait_event_type , wait_event ORDER BY 5 DESC

Exemple
  + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  # |  backend_type |  dbname |  wait_event_type |  wait_event |  la durée
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  1 |  lanceur de réplication logique |  |  Activité |  LogicalLauncherMain |  10:43:28
 |  2 |  lanceur autovacuum |  |  Activité |  AutoVacuumMain |  10:42:49
 |  3 |  walwriter |  |  Activité |  WalWriterMain |  10:28:53
 |  4 |  pointeur de contrôle |  |  Activité |  CheckpointerMain |  10:23:50
 |  5 |  écrivain de fond |  |  Activité |  BgWriterMain |  09:11:59
 |  6 |  écrivain de fond |  |  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 autovacuum |  tdb1 |  LWLock |  buffer_mapping |  00:00:13
 |  14 |  écrivain de fond |  |  IO |  DataFileWrite |  00:00:12
 |  15 |  pointeur de contrôle |  |  IO |  DataFileWrite |  00:00:11
 |  16 |  walwriter |  |  LWLock |  WALWriteLock |  00:00:09
 |  17 |  pointeur de contrôle |  |  LWLock |  WALWriteLock |  00:00:06
 |  18 |  écrivain de fond |  |  LWLock |  WALWriteLock |  00:00:06
 |  19 |  walwriter |  |  IO |  WALInitWrite |  00:00:02
 |  20 |  travailleur autovacuum |  tdb1 |  LWLock |  WALWriteLock |  00:00:02
 |  21 |  walwriter |  |  IO |  WALInitSync |  00:00:02
 |  22 |  travailleur autovacuum |  tdb1 |  IO |  DataFileRead |  00:00:01
 |  23 |  pointeur de contrôle |  |  IO |  ControlFileSyncUpdate |  00:00:01
 |  24 |  écrivain de fond |  |  IO |  WALWrite |  00:00:01
 |  25 |  écrivain de fond |  |  IO |  DataFileFlush |  00:00:01
 |  26 |  pointeur de contrôle |  |  IO |  SLRUFlushSync |  00:00:01
 |  27 |  travailleur autovacuum |  tdb1 |  IO |  WALWrite |  00:00:01
 |  28 |  pointeur de contrôle |  |  IO |  DataFileSync |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------

ATTENTES POUR SQL - attentes pour les requêtes individuelles par queryid


Demande
 SELECT 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') ) 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 AND queryid IS NOT NULL GROUP BY queryid , datname , wait_event_type , wait_event ORDER BY 1 , 5 DESC

Exemple
  + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------
 |  # |  queryid |  dbname |  wait_event_type |  wait_event |  attente |  au total
 |  |  |  |  |  |  durée |  la durée
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------
 |  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 |
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------

CLIENT SQL STATICTICS - TOP requêtes


Cependant, les demandes de réception sont triviales et pour gagner de la place, elles ne sont pas données.

Des exemples
  + ------------------------------------------------- -----------------------------------
 |  CLIENT SQL ordonné par Elapsed Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  temps écoulé |  appels |  % dbtime |  % CPU |  % IO |  dbname |  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 ordonné par CPU Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  temps processeur |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  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 ordonné par temps d'attente des E / S utilisateur
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  temps io_wait |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  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 ordonné par des lectures de tampons partagés
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  tampons lit |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  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 ordonné par Disk Reads Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  temps de lecture |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  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 ordonné par Executions
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  appels |  lignes |  % dbtime | total_time |  % CPU |  % IO |  dbname |  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
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------

Résumé


En utilisant les demandes soumises et les rapports résultants, vous pouvez obtenir une image plus complète pour analyser et résoudre les problèmes de dégradation des performances pour les demandes individuelles et l'ensemble du cluster dans son ensemble.

Développement


Jusqu'à présent, les plans de développement sont les suivants:

  • Compléter les rapports avec un historique des verrous Les demandes sont en cours de test et seront soumises sous peu.
  • Utilisez l'extension TimescaleDB pour stocker l'historique de pg_stat_activity et pg_locks.
  • Préparez une solution batch sur github pour un déploiement de masse sur les bases de production.

À suivre ...

Source: https://habr.com/ru/post/fr467575/

More articles:


All Articles