Geekly articles weekly

Una forma de obtener el perfil de carga de trabajo y el historial de espera en PostgreSQL

Continuación del artículo " Intentando crear un análogo de ASH para PostgreSQL ".

El artículo se considerará y se mostrará en consultas y ejemplos específicos: qué información útil se puede obtener utilizando el historial de presentación pg_stat_activity.
Advertencia
Debido a la novedad del tema y al período de prueba incompleto, el artículo puede contener errores. Las críticas y los comentarios son fuertemente alentados y esperados.

Datos de entrada


Ver historial 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 tabla se llena cada hora usando dblink a la base de datos de destino. La columna más interesante y útil de la tabla, por supuesto, es queryid .

Pg_stat_activity ver historial


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 tabla es una tabla history_pg_stat_activity particionada por reloj (para obtener más detalles, consulte pg_stat_statements + pg_stat_activity + loq_query = pg_ash? Y aquí hay un intento de crear un análogo de ASH para PostgreSQL).

Pie de imprenta


CLUSTER CPU TIME (SISTEMA + CLIENTES)


Solicitud
 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 ;

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

TIEMPO DE ESPERA DE CLUSTER


Solicitud
 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 ;

Ejemplo
 CLUSTER WAITINGS TIME : 30:12:49

Valores totales de pg_stat_statements


Solicitud
  --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 - Tiempo de ejecución total de consultas


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

Ejemplo
 SQL DBTIME : 136:49:36

Tiempo de tiempo de CPU SQL de la CPU empleada ejecutando consultas


Solicitud
 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 ;

Ejemplo
 SQL CPU TIME : 27:40:15

TIEMPO DE ESPERA DE SQL - Tiempo total de espera para consultas


Solicitud
 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 ;

Ejemplo
 SQL WAITINGS TIME : 30:04:09

Las siguientes consultas son triviales y para ahorrar espacio, se omiten los detalles de implementación:

Ejemplo
 | 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

Pasamos a la sección más interesante

ESTADÍSTICAS DE ESPERA


TOP 10 ESPERAS POR TIEMPO TOTAL DE ESPERA PARA PROCESOS DE CLIENTES


Solicitud
 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

Ejemplo
  + ------------------------------------------------- -----------------------------------
 El |  TOP 10 ESPERAS POR TIEMPO TOTAL DE ESPERA PARA PROCESOS DEL SISTEMA
 + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  # |  wait_event_type |  wait_event |  duración
 + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  1 |  Actividad |  LogicalLauncherMain |  10:43:28
 El |  2 |  Actividad |  AutoVacuumMain |  10:42:49
 El |  3 |  Actividad |  WalWriterMain |  10:28:53
 El |  4 |  Actividad |  CheckpointerMain |  10:23:50
 El |  5 |  Actividad |  BgWriterMain |  09:11:59
 El |  6 |  Actividad |  BgWriterHibernate |  01:37:46
 El |  7 |  IO |  BufFileWrite |  00:02:35
 El |  8 |  LWLock |  buffer_mapping |  00:01:54
 El |  9 |  IO |  DataFileRead |  00:01:23
 El |  10 |  IO |  WALWrite |  00:00:59
 + ----- + ------------------------------ + ------------ -------- + --------------------

TOP 10 ESPERAS POR TIEMPO TOTAL DE ESPERA PARA PROCESOS DE CLIENTES


Solicitud
 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

Ejemplo
  + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 El |  # |  wait_event_type |  wait_event |  duración |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 El |  1 |  Bloqueo |  transaccion |  08: 16: 47 |  6.05
 El |  2 |  IO |  DataFileRead |  06: 13: 41 |  4.55
 El |  3 |  Tiempo de espera |  PgSleep |  02: 53: 21 |  2.11
 El |  4 |  LWLock |  buffer_mapping |  00: 40: 42 |  0.5 0.5
 El |  5 |  LWLock |  buffer_io |  00: 17: 17 |  0,21
 El |  6 |  IO |  BufFileWrite |  00: 01: 34 |  0,02
 El |  7 |  Bloqueo |  tupla |  00: 01: 32 |  0,02
 El |  8 |  Cliente |  ClientRead |  00: 01: 19 |  0,02
 El |  9 |  IO |  BufFileRead |  00: 00: 37 |  0,01
 El |  10 |  LWLock |  buffer_content |  00: 00: 08 |  0 0
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------

TIPOS DE ESPERA POR TIEMPO TOTAL DE ESPERA, PARA PROCESOS DEL SISTEMA


Solicitud
 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

Ejemplo
  + ----- + ------------------------------ + ------------ --------
 El |  # |  wait_event_type |  duración
 + ----- + ------------------------------ + ------------ --------
 El |  1 |  Actividad |  53:08:45
 El |  2 |  IO |  00:06:24
 El |  3 |  LWLock |  00:03:02
 + ----- + ------------------------------ + ------------ --------

TIPOS DE ESPERA POR TIEMPO TOTAL DE ESPERA, PARA PROCESOS DE CLIENTES


Solicitud
 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

Ejemplo
  + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  # |  wait_event_type |  duración |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  1 |  Bloqueo |  08: 18: 19 |  6.07
 El |  2 |  IO |  06: 16: 01 |  4.58
 El |  3 |  Tiempo de espera |  02: 53: 21 |  2.11
 El |  4 |  LWLock |  00: 58: 12 |  0,71
 El |  5 |  Cliente |  00: 01: 19 |  0,02
 El |  6 |  IPC |  00: 00: 04 |  0 0
 + ----- + ------------------------------ + ------------ -------- + --------------------

Duración de las expectativas, para procesos del sistema y solicitudes individuales.

ESPERAS DE PROCESOS DEL SISTEMA


Solicitud
 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

Ejemplo
  + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 El |  # |  backend_type |  dbname |  wait_event_type |  wait_event |  duración
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 El |  1 |  lanzador de replicación lógica |  El |  Actividad |  LogicalLauncherMain |  10:43:28
 El |  2 |  lanzador de vacío automático |  El |  Actividad |  AutoVacuumMain |  10:42:49
 El |  3 |  walwriter |  El |  Actividad |  WalWriterMain |  10:28:53
 El |  4 |  puntero de control |  El |  Actividad |  CheckpointerMain |  10:23:50
 El |  5 |  escritor de fondo |  El |  Actividad |  BgWriterMain |  09:11:59
 El |  6 |  escritor de fondo |  El |  Actividad |  BgWriterHibernate |  01:37:46
 El |  7 |  trabajador paralelo |  tdb1 |  IO |  BufFileWrite |  00:02:35
 El |  8 |  trabajador paralelo |  tdb1 |  LWLock |  buffer_mapping |  00:01:41
 El |  9 |  trabajador paralelo |  tdb1 |  IO |  DataFileRead |  00:01:22
 El |  10 |  trabajador paralelo |  tdb1 |  IO |  BufFileRead |  00:00:59
 El |  11 |  walwriter |  El |  IO |  WALWrite |  00:00:57
 El |  12 |  trabajador paralelo |  tdb1 |  LWLock |  buffer_io |  00:00:47
 El |  13  trabajador de autovacuum |  tdb1 |  LWLock |  buffer_mapping |  00:00:13
 El |  14  escritor de fondo |  El |  IO |  DataFileWrite |  00:00:12
 El |  15 |  puntero de control |  El |  IO |  DataFileWrite |  00:00:11
 El |  16  walwriter |  El |  LWLock |  WALWriteLock |  00:00:09
 El |  17 |  puntero de control |  El |  LWLock |  WALWriteLock |  00:00:06
 El |  18  escritor de fondo |  El |  LWLock |  WALWriteLock |  00:00:06
 El |  19  walwriter |  El |  IO |  WALInitWrite |  00:00:02
 El |  20 |  trabajador de autovacuum |  tdb1 |  LWLock |  WALWriteLock |  00:00:02
 El |  21 |  walwriter |  El |  IO |  WALInitSync |  00:00:02
 El |  22 |  trabajador de autovacuum |  tdb1 |  IO |  DataFileRead |  00:00:01
 El |  23 |  puntero de control |  El |  IO |  ControlFileSyncUpdate |  00:00:01
 El |  24 |  escritor de fondo |  El |  IO |  WALWrite |  00:00:01
 El |  25  escritor de fondo |  El |  IO |  DataFileFlush |  00:00:01
 El |  26 |  puntero de control |  El |  IO |  SLRUFlushSync |  00:00:01
 El |  27  trabajador de autovacuum |  tdb1 |  IO |  WALWrite |  00:00:01
 El |  28 |  puntero de control |  El |  IO |  DataFileSync |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------

WAITINGS FOR SQL: expectativas de consultas individuales por queryid


Solicitud
 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

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

ESTADÍSTICAS DEL CLIENTE SQL: consultas principales


Sin embargo, las solicitudes para recibir de nuevo son triviales y no se dan para ahorrar espacio.

Ejemplos
  + ------------------------------------------------- -----------------------------------
 El |  CLIENTE SQL ordenado por tiempo transcurrido
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 El |  tiempo transcurrido |  llamadas |  % dbtime |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 El |  04: 14: 15 |  19  3.1 |  10,83 |  11,52 |  tdb1 |  389015618226997618
 El |  02: 45: 40 |  746  2,02 |  4,23 |  0,08 |  tdb1 |  1237430309438971376
 El |  02: 18: 21 |  749  1,69 |  3,39 |  0,1 |  tdb1 |  4710212362688288619
 El |  00: 46: 06 |  375  0,56 |  0,94 |  0,41 |  tdb1 |  28942442626229688
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 El |  CLIENTE SQL ordenado por tiempo de CPU
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  tiempo de la CPU |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  02: 59: 49 |  19  3.1 |  04: 14: 15 |  10,83 |  11,52 |  tdb1 |  389015618226997618
 El |  01: 10: 12 |  746  2,02 |  02: 45: 40 |  4,23 |  0,08 |  tdb1 |  1237430309438971376
 El |  00: 56: 15 |  749  1,69 |  02: 18: 21 |  3,39 |  0,1 |  tdb1 |  4710212362688288619
 El |  00: 15: 35 |  375  0,56 |  00: 46: 06 |  0,94 |  0,41 |  tdb1 |  28942442626229688
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por usuario E / S Tiempo de espera
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  io_wait time |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  03: 23: 10 |  19  3.1 |  04: 14: 15 |  10,83 |  11,52 |  tdb1 |  389015618226997618
 El |  00: 02: 54 |  375  0,56 |  00: 46: 06 |  0,94 |  0,41 |  tdb1 |  28942442626229688
 El |  00: 00: 27 |  746  2,02 |  02: 45: 40 |  4,23 |  0,08 |  tdb1 |  1237430309438971376
 El |  00: 00: 22 |  749  1,69 |  02: 18: 21 |  3,39 |  0,1 |  tdb1 |  4710212362688288619
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por lecturas de búferes compartidos
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  tampones lee |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  1056388566 |  19  3.1 |  04: 14: 15 |  10,83 |  11,52 |  tdb1 |  389015618226997618
 El |  11709251 |  375  0,56 |  00: 46: 06 |  0,94 |  0,41 |  tdb1 |  28942442626229688
 El |  3439004 |  746  2,02 |  02: 45: 40 |  4,23 |  0,08 |  tdb1 |  1237430309438971376
 El |  3373330 |  749  1,69 |  02: 18: 21 |  3,39 |  0,1 |  tdb1 |  4710212362688288619
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por Tiempo de lectura de disco
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  tiempo de lectura |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  02: 16: 30 |  19  3.1 |  04: 14: 15 |  10,83 |  11,52 |  tdb1 |  389015618226997618
 El |  00: 04: 50 |  375  0,56 |  00: 46: 06 |  0,94 |  0,41 |  tdb1 |  28942442626229688
 El |  00: 01: 10 |  749  1,69 |  02: 18: 21 |  3,39 |  0,1 |  tdb1 |  4710212362688288619
 El |  00: 00: 57 |  746  2,02 |  02: 45: 40 |  4,23 |  0,08 |  tdb1 |  1237430309438971376
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por Ejecuciones
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  llamadas |  filas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  749  749  1,69 |  02: 18: 21 |  3,39 |  0,1 |  tdb1 |  4710212362688288619
 El |  746  746  2,02 |  02: 45: 40 |  4,23 |  0,08 |  tdb1 |  1237430309438971376
 El |  375  0 |  0,56 |  00: 46: 06 |  0,94 |  0,41 |  tdb1 |  28942442626229688
 El |  19  19  3.1 |  04: 14: 15 |  10,83 |  11,52 |  tdb1 |  389015618226997618
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------

Resumen


Utilizando las solicitudes enviadas y los informes resultantes, puede obtener una imagen más completa para analizar y resolver los problemas de degradación del rendimiento de las solicitudes individuales y de todo el clúster en su conjunto.

Desarrollo


Hasta ahora, los planes de desarrollo son los siguientes:

  • Suplemento de informes con un historial de bloqueo. Las solicitudes se están probando y se enviarán en breve.
  • Use la extensión TimescaleDB para almacenar el historial de pg_stat_activity y pg_locks.
  • Prepare una solución por lotes en github para un despliegue masivo en bases de producción.

Continuará ...

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

More articles:


All Articles