Geekly articles weekly

Intentando crear un análogo ASH para PostgreSQL

Declaración del problema.


Para optimizar las consultas de PostgreSQL, realmente requiere la capacidad de analizar el historial de actividades, en particular: expectativas, bloqueos, estadísticas de tabla.

Opciones disponibles


Herramienta de análisis de carga histórica o "AWR for Postgres" : una solución muy interesante, pero no hay historial de pg_stat_activity y pg_locks.

Extensión de Pgsentinel :
" Toda la información acumulada se almacena solo en la RAM, y la cantidad de memoria consumida está regulada por el número de los últimos registros almacenados.

Se agrega el campo queryid: el mismo queryid de la extensión pg_stat_statements (se requiere una instalación preliminar). "

Esto ciertamente ayudaría mucho, pero la molestia es el primer párrafo, " Toda la información acumulada se almacena solo en la RAM ", es decir. Tenemos un impacto en la base objetivo. Además, no hay historial de bloqueo ni estadísticas de tabla. Es decir En términos generales, la solución está incompleta: “ Todavía no hay un paquete listo para instalar. Se propone descargar las fuentes y construir la biblioteca usted mismo. Primero, necesita instalar el paquete "devel" para su servidor y escribir la ruta a pg_config en la variable PATH. "

En general, mucho alboroto y, en el caso de bases de datos de producción serias, tal vez no haya forma de hacer algo con el servidor. Una vez más, necesitas crear algo propio.

Advertencia
Debido a la novedad del tema y lo incompleto del período de prueba, el artículo es principalmente orientativo, más bien como un conjunto de resúmenes y resultados intermedios.
Más adelante se preparará material más detallado, en partes.


Esquema de los requisitos de la solución


Es necesario desarrollar una herramienta para almacenar:

Pg_stat_activity ver historial
Historial de bloqueo de sesión usando la vista pg_locks

El requisito de decisión es minimizar el impacto en la base de datos de destino.

La idea general es que el agente de recopilación de datos no se inicia en la base de datos de destino, sino en la base de datos de monitoreo como un servicio systemd. Sí, es posible cierta pérdida de datos, pero esto no es crítico para la presentación de informes, pero no hay impacto en la base de datos de destino de la memoria y el espacio en disco. Y en el caso de utilizar un grupo de conexiones, el impacto en los procesos del usuario es mínimo.

Etapas de implementación


1. Mesas de servicio


Para almacenar tablas, se utiliza un esquema separado para no complicar el análisis de las tablas principales utilizadas.

DROP SCHEMA IF EXISTS activity_hist ; CREATE SCHEMA activity_hist AUTHORIZATION monitor ;

Importante: el esquema no se crea en la base de datos de destino, sino en la base de datos de monitoreo.

Pg_stat_activity ver historial


Use la tabla para almacenar las instantáneas actuales de la vista pg_stat_activity

activity_hist.history_pg_stat_activity:
 --ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity; CREATE TABLE activity_hist.history_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 with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text , queryid bigint );

Para acelerar la inserción, no hay índices ni restricciones.

Para almacenar el historial directamente, se usa una tabla particionada:

activity_hist.archive_pg_stat_activity:
 DROP TABLE IF EXISTS activity_hist.archive_pg_stat_activity; CREATE TABLE activity_hist.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 with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text , queryid bigint ) PARTITION BY RANGE (timepoint);

Dado que en este caso no hay requisitos para la velocidad de inserción, se han creado algunos índices para acelerar los informes.

Historial de bloqueo de sesión


Para almacenar las instantáneas de bloqueo de sesión actuales, use la tabla:

activity_hist.history_locking:
 --ACTIVITY_HIST.HISTORY_LOCKING DROP TABLE IF EXISTS activity_hist.history_locking; CREATE TABLE activity_hist.history_locking ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean );

Además, para acelerar la inserción, no hay índices ni restricciones.

Para almacenar el historial directamente, se usa una tabla particionada:

activity_hist.archive_locking:
 DROP TABLE IF EXISTS activity_hist.archive_locking; CREATE TABLE activity_hist.archive_locking ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean ) PARTITION BY RANGE (timepoint);

Dado que en este caso no hay requisitos para la velocidad de inserción, se han creado algunos índices para acelerar los informes.

2. Llenando el historial actual


Para capturar instantáneas directamente de una vista, se usa un script bash que ejecuta la función plpgsql.

get_current_activity.sh
 #!/bin/bash ######################################################### #get_current_activity.sh ERROR_FILE='/home/demon/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S) host=$1 s_name=$2 s_pass=$3 psql -A -t -q -v ON_ERROR_STOP=1 -c "SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )" >/dev/null 2>$ERROR_FILE line_count=`cat $ERROR_FILE | wc -l` if [[ $line_count != '0' ]]; then rm -f /home/demon/*.err >/dev/null 2>/dev/null cp $ERROR_FILE $ERROR_FILE'.err' >/dev/null 2>/dev/null fi rm $ERROR_FILE >/dev/null 2>/dev/null exit 0

La función dblink plpgsql accede a las vistas en la base de datos de destino e inserta filas en las tablas de servicio en la base de datos de monitoreo.

get_current_activity.sql
 CREATE OR REPLACE FUNCTION activity_hist.get_current_activity( current_host text , current_s_name text , current_s_pass text ) RETURNS BOOLEAN AS $$ DECLARE database_rec record; dblink_str text ; BEGIN EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||current_host||' port=5432 dbname=postgres'|| ' user='||current_s_name||' password='||current_s_pass|| ' '')'; -------------------------------------------------------------------- --GET pg_stat_activity stats INSERT INTO activity_hist.history_pg_stat_activity ( SELECT * FROM dblink('LINK1', 'SELECT now() , datid , datname , pid , usesysid , usename , application_name , client_addr , client_hostname , client_port , backend_start , xact_start , query_start , state_change , wait_event_type , wait_event , state , backend_xid , backend_xmin , query , backend_type FROM pg_stat_activity ') AS t ( 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 with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text ) ); --------------------------------------- --ACTIVITY_HIST.HISTORY_LOCKING INSERT INTO activity_hist.history_locking ( SELECT * FROM dblink('LINK1', 'SELECT now() , lock.locktype, lock.relation, lock.mode, lock.transactionid as tid, lock.virtualtransaction as vtid, lock.pid, pg_blocking_pids(lock.pid), lock.granted FROM pg_catalog.pg_locks lock LEFT JOIN pg_catalog.pg_database db ON db.oid = lock.database WHERE NOT lock.pid = pg_backend_pid() ') AS t ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean ) ); PERFORM dblink_disconnect('LINK1'); RETURN TRUE ; END $$ LANGUAGE plpgsql;

Para recopilar instantáneas de la vista, se utiliza el servicio systemd y dos scripts:

pg_current_activity.service
 # /etc/systemd/system/pg_current_activity.service [Unit] Description=Collect history of pg_stat_activity , pg_locks Wants=pg_current_activity.timer [Service] Type=forking StartLimitIntervalSec=0 ExecStart=/home/postgres/pgutils/demon/get_current_activity.sh XXXX postgres postgres [Install] WantedBy=multi-user.target

pg_current_activity.timer
 # /etc/systemd/system/pg_current_activity.timer [Unit] Description=Run pg_current_activity.sh every 1 second Requires=pg_current_activity.service [Timer] Unit=pg_current_activity.service OnCalendar=*:*:0/1 AccuracySec=1 [Install] WantedBy=timers.target


Asignar derechos a los scripts:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Iniciamos el servicio:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

Por lo tanto, la historia de las actuaciones se recopila en forma de tomas cada segundo. Por supuesto, si todo se deja como está, las tablas aumentarán rápidamente de tamaño y el trabajo más o menos productivo será imposible.

Es necesario organizar el archivo de datos.

3. Historia de archivo


Para archivar, se usan tablas particionadas *.

Se crean nuevas secciones cada hora, mientras que los datos antiguos de las tablas de historial * se eliminan, por lo que el tamaño de las tablas de historial * no cambia mucho y la velocidad de inserción no se degrada con el tiempo.

La creación de nuevas secciones se realiza mediante la función plpgsql activity_hist.archive_current_activity. El algoritmo de operación es muy simple (usando el ejemplo de la sección para la tabla archive_pg_stat_activity).

Crea y completa una nueva sección
 EXECUTE format( 'CREATE TABLE ' || partition_name || ' PARTITION OF activity_hist.archive_pg_stat_activity FOR VALUES FROM ( %L ) TO ( %L ) ' , to_char(date_trunc('year', partition_min_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_min_range ),'MM')||'-'|| to_char(date_trunc('day', partition_min_range ),'DD')||' '|| to_char(date_trunc('hour', partition_min_range ),'HH24')||':00', to_char(date_trunc('year', partition_max_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_max_range ),'MM')||'-'|| to_char(date_trunc('day', partition_max_range ),'DD')||' '|| to_char(date_trunc('hour', partition_max_range ),'HH24')||':00' ); INSERT INTO activity_hist.archive_pg_stat_activity ( SELECT * FROM activity_hist.history_pg_stat_activity WHERE timepoint BETWEEN partition_min_range AND partition_max_range );

Crear índices
 EXECUTE format ( 'CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint )' ); EXECUTE format ('CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint , queryid )' );

Eliminar datos antiguos de la tabla history_pg_stat_activity
 DELETE FROM activity_hist.history_pg_stat_activity WHERE timepoint < partition_max_range;

Por supuesto, periódicamente, las secciones antiguas se eliminan como innecesarias.

Informes básicos


En realidad, ¿por qué se está haciendo todo esto? Para recibir informes de forma muy remota, más o menos una reminiscencia de Oracle AWR.
Es importante agregar que para recibir informes es necesario construir una relación entre las vistas pg_stat_activity y pg_stat_statements. Las tablas se vinculan agregando la columna 'queryid' a las tablas 'history_pg_stat_activity', 'archive_pg_stat_activity'. La forma de agregar un valor de columna está más allá del alcance de este artículo y se describe aquí: pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TIEMPO TOTAL DE CPU PARA CONSULTAS


Solicitud:
 WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC intervalo de ' WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC * Intervalo ' WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC backend' Y datname! = 'Postgres' Y (aa.wait_event_type IS NULL) ANDaa.state = WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC

Un ejemplo:
 ------------------------------------------------------------------- | TOTAL CPU TIME FOR QUERIES : 07:47:36 +----+----------------------------------------+-------------------- | #| queryid| duration +----+----------------------------------------+-------------------- | 1| 389015618226997618| 04:28:58 | 2| | 01:07:29 | 3| 1237430309438971376| 00:59:38 | 4| 4710212362688288619| 00:50:48 | 5| 28942442626229688| 00:15:50 | 6| 9150846928388977274| 00:04:46 | 7| -6572922443698419129| 00:00:06 | 8| | 00:00:01 +----+----------------------------------------+--------------------


TIEMPO DE ESPERA TOTAL PARA CONSULTAS


Solicitud:
 WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type IS NOT NULL ) GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type IS NOT NULL ) GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC

Un ejemplo:
 ------------------------------------------------------------------- | TOTAL WAITINGS TIME FOR QUERIES : 21:55:04 +----+----------------------------------------+-------------------- | #| queryid| duration +----+----------------------------------------+-------------------- | 1| 389015618226997618| 16:19:05 | 2| | 03:47:04 | 3| 8085340880788646241| 00:40:20 | 4| 4710212362688288619| 00:13:35 | 5| 9150846928388977274| 00:12:25 | 6| 28942442626229688| 00:11:32 | 7| 1237430309438971376| 00:09:45 | 8| 2649515222348904837| 00:09:37 | 9| | 00:03:45 | 10| 3167065002719415275| 00:02:20 | 11| 5731212217001535134| 00:02:13 | 12| 8304755792398128062| 00:01:31 | 13| 2649515222348904837| 00:00:59 | 14| 2649515222348904837| 00:00:22 | 15| | 00:00:12 | 16| 3422818749220588372| 00:00:08 | 17| -5730801771815999400| 00:00:03 | 18| -1473395109729441239| 00:00:02 | 19| 2404820632950544954| 00:00:02 | 20| -6572922443698419129| 00:00:02 | 21| 2369289265278398647| 00:00:01 | 22| 180077086776069052| 00:00:01 +----+----------------------------------------+--------------------

ESPERAS DE CONSULTAS


Solicitudes:
 WITH hist AS ( SELECT aa.wait_event_type , aa.wait_event 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 datname != 'postgres' AND aa.wait_event IS NOT NULL GROUP BY aa.wait_event_type , aa.wait_event UNION SELECT ha.wait_event_type , ha.wait_event FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ha.wait_event IS NOT NULL GROUP BY ha.wait_event_type , ha.wait_event ) SELECT wait_event_type , wait_event FROM hist GROUP BY wait_event_type , wait_event ORDER BY 1 ASC,2 ASC ---------------------------------------------------------------------- WITH hist AS ( SELECT aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid , count(*) * interval '1 second' 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 datname != 'postgres' AND ( aa.wait_event_type = waitings_stat_rec.wait_event_type AND aa.wait_event = waitings_stat_rec.wait_event ) GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha 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 datname != 'postgres' AND ( ha.wait_event_type = waitings_stat_rec.wait_event_type AND ha.wait_event = waitings_stat_rec.wait_event ) GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC

Un ejemplo:
 ------------------------------------------------ | WAITINGS FOR QUERIES +----------------------------------------------- | wait_event_type = Client| | wait_event = ClientRead| | Total time = 00:46:56| ------------------------------------------------ | #| queryid| duration +-----+--------------------+-------------------- | 1| 8085340880788646241| 00:40:20 | 2| | 00:03:45 | 3| 5731212217001535134| 00:01:53 | 4| | 00:00:12 | 5| 9150846928388977274| 00:00:09 | 6| 3422818749220588372| 00:00:08 | 7| 1237430309438971376| 00:00:06 | 8| 28942442626229688| 00:00:05 | 9| 4710212362688288619| 00:00:05 | 10|-5730801771815999400| 00:00:03 | 11| 8304755792398128062| 00:00:02 | 12|-6572922443698419129| 00:00:02 | 13|-1473395109729441239| 00:00:02 | 14| 2404820632950544954| 00:00:02 | 15| 180077086776069052| 00:00:01 | 16| 2369289265278398647| 00:00:01 +----------------------------------------------- | wait_event_type = IO| | wait_event = BufFileRead| | Total time = 00:00:38| ------------------------------------------------ | #| queryid| duration +-----+--------------------+-------------------- | 1| 28942442626229688| 00:00:38 +-----------------------------------------------

HISTORIA DE PROCESOS BLOQUEADOS


Solicitud:
 SELECT MIN(date_trunc('second',timepoint)) AS started , count(*) * interval '1 second' as duration , pid , blocking_pids , relation , mode , locktype FROM activity_hist.archive_locking al 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 NOT granted AND locktype = 'relation' GROUP BY pid , blocking_pids , relation , mode , locktype UNION SELECT MIN(date_trunc('second',timepoint)) AS started , count(*) * interval '1 second' as duration , pid , blocking_pids , relation , mode , locktype FROM activity_hist.history_locking 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 NOT granted AND locktype = 'relation' GROUP BY pid , blocking_pids , relation , mode , locktype ORDER BY 1

Un ejemplo:
  -------------------------------------------------- -------------------------------------------------- ---------------------------------
 El |  HISTORIA DE PROCESOS BLOQUEADOS
 + ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
 El |  # |  pid |  comenzó |  duración |  Blocking_pids |  relación |  modo |  tipo de bloqueo
 + ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
 El |  1 |  26224 |  2019-09-02 19: 32: 16 |  00: 01: 45 |  {26211} |  16541 |  AccessShareLock |  relación
 El |  2 |  26390 |  2019-09-02 19: 34: 03 |  00: 00: 53 |  {26211} |  16541 |  AccessShareLock |  relación
 El |  3 |  26391 |  2019-09-02 19: 34: 03 |  00: 00: 53 |  {26211} |  16541 |  AccessShareLock |  relación
 El |  4 |  26531 |  2019-09-02 19: 35: 27 |  00: 00: 12 |  {26211} |  16541 |  AccessShareLock |  relación
 El |  5 |  27284 |  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541 |  AccessShareLock |  relación
 El |  6 |  27283 |  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541 |  AccessShareLock |  relación
 El |  7 |  27286 |  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541 |  AccessShareLock |  relación
 El |  8 |  27423 |  2019-09-02 19:45:24 |  00: 00: 12 |  {27394} |  16541 |  AccessShareLock |  relación
 El |  9 |  27648 |  2019-09-02 19: 48: 06 |  00: 00: 20 |  {27647} |  16541 |  AccessShareLock |  relación
 El |  10 |  27650 |  2019-09-02 19: 48: 06 |  00: 00: 20 |  {27647} |  16541 |  AccessShareLock |  relación
 El |  11 |  27735 |  2019-09-02 19: 49: 08 |  00: 00: 06 |  {27650} |  16541 |  AccessExclusiveLock |  relación
 El |  12 |  28380 |  2019-09-02 19: 56: 03 |  00: 01: 56 |  {28379} |  16541 |  AccessShareLock |  relación
 El |  13  28379 |  2019-09-02 19: 56: 03 |  00: 00: 01 |  28377 |  16541 |  AccessExclusiveLock |  relación
 El |  El |  El |  El |  El |  28376 |  El |

HISTORIA DE PROCESOS DE BLOQUEO


Solicitudes:
 SELECT blocking_pids FROM activity_hist.archive_locking al 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 NOT granted AND locktype = 'relation' GROUP BY blocking_pids UNION SELECT blocking_pids FROM activity_hist.history_locking 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 NOT granted AND locktype = 'relation' GROUP BY blocking_pids ORDER BY 1 --------------------------------------------------------------- SELECT pid , usename , application_name , datname , MIN(date_trunc('second',timepoint)) as started , count(*) * interval '1 second' as duration , state , query FROM activity_hist.archive_pg_stat_activity WHERE pid= current_pid AND timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') GROUP BY pid , usename , application_name , datname , state_change, state , query UNION SELECT pid , usename , application_name , datname , MIN(date_trunc('second',timepoint)) as started , count(*) * interval '1 second' as duration , state , query FROM activity_hist.history_pg_stat_activity_for_reports WHERE pid= current_pid AND timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') GROUP BY pid , usename , application_name , datname , state_change, state , query ORDER BY 5 , 1

Un ejemplo:
  -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------
 HISTORIA DE PROCESOS DE BLOQUEO
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 El |  # |  pid |  usename |  nombre_aplicación |  datname |  comenzó |  duración |  estado |  consulta
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 El |  1 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 31: 54 |  00: 00: 04 |  inactivo |
 El |  2 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 31: 58 |  00: 00: 06 |  inactivo en la transacción |  comenzar
 El |  3 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 32: 16 |  00: 01: 45 |  inactivo en la transacción |  tabla de bloqueo wafer_data;
 El |  4 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 35: 54 |  00: 01: 23 |  inactivo |  cometer
 El |  5 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 38: 46 |  00: 00: 02 |  inactivo en la transacción |  comenzar
 El |  6 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 38: 54 |  00: 00: 08 |  inactivo en la transacción |  tabla de bloqueo wafer_data;
 El |  7 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 39: 08 |  00: 42: 42 |  inactivo |  cometer
 El |  8 |  26211 |  tuser |  psql |  tdb1 |  2019-09-03 07: 12: 07 |  00: 00: 52 |  activo |  seleccione test_del ();


Desarrollo.


Las consultas básicas mostradas y los informes recibidos ya simplifican enormemente la vida al analizar incidentes de rendimiento.
Basado en consultas básicas, puede obtener un informe de forma remota que recuerda aproximadamente a Oracle AWR.
Informe resumido de muestra
  + ------------------------------------------------- -----------------------------------
 El |  INFORME CONSOLIDADO DE ACTIVIDAD Y ESPERAS.  FECHA: 09/03/2019 14:08
 | ------------------------------------------------- -----------------------------------
 El |  Anfitrión: XXXX
 El |  BEGIN_SNAPSHOT: 09/02/2019 14:08 END_SNAPSHOT: 09/03/2019 14:00
 | ------------------------------------------------- -----------------------------------
 El |  TAMAÑO ACTUAL DE BASES DE DATOS:
 El |  BASE DE DATOS: monitor
 El |  TAMAÑO (MB): 1370.00
 | ------------------------------------------------- -----------------------------------
 El |  CLUSTER CPU TIME: 19:44:22
 El |  TIEMPO DE ESPERA DEL GRUPO: 78:49:16
 El |
 El |  SQL DBTIME: 65:53:09
 El |  TIEMPO DE CPU SQL: 19:05:21
 El |  TIEMPO DE ESPERA DE SQL: 21:50:46
 El |  SQL IOTIME: 20:53:00
 El |  TIEMPO DE LECTURA DE SQL: 20:52:55
 El |  TIEMPO DE ESCRITURA SQL: 00:00:05
 El |
 El |  LLAMADAS DE SQL: 311293
 -------------------------------------------------- -----------
 El |  LECTURAS DE BLOQUES COMPARTIDOS EN SQL: 13351563334
 El |  HITS DE BLOQUES COMPARTIDOS EN SQL: 2775427045
 El |  BLOQUES COMPARTIDOS SQL HITS / READS%: 20.79
 El |  BLOQUES COMPARTIDOS EN SQL DIRIGIDOS: 21105
 El |  BLOQUES COMPARTIDOS POR ESCRITO: 3656
 El |
 El |  LECTURAS DE BLOQUES TEMPORALES DE SQL: 7464932
 El |  BLOQUES TEMPORALES DE SQL ESCRITOS: 10176024
 -------------------------------------------------- -----------
 El |
 El |  ESTADÍSTICAS DE ESPERA
 El |
 + ------------------------------------------------- -----------------------------------
 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 |  11:21:01
 El |  2 |  Actividad |  CheckpointerMain |  11:20:35
 El |  3 |  Actividad |  AutoVacuumMain |  11:20:31
 El |  4 |  Actividad |  WalWriterMain |  11:19:35
 El |  5 |  Actividad |  BgWriterMain |  10:14:19
 El |  6 |  Actividad |  BgWriterHibernate |  01:06:04
 El |  7 |  Actividad |  WalSenderMain |  00:04:05
 El |  8 |  Cliente |  ClientWrite |  00:04:00
 El |  9 |  IO |  BufFileWrite |  00:02:45
 El |  10 |  LWLock |  buffer_mapping |  00:02:14
 + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  TOP 10 ESPERAS POR TIEMPO TOTAL DE ESPERA PARA PROCESOS DE CLIENTES
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 El |  # |  wait_event_type |  wait_event |  duración |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 El |  1 |  Bloqueo |  transaccion |  11: 55: 37 |  18,1
 El |  2 |  IO |  DataFileRead |  07: 19: 43 |  12/12
 El |  3 |  Cliente |  ClientRead |  00: 46: 54 |  1.19
 El |  4 |  Bloqueo |  relación |  00: 40: 37 |  1.03
 El |  5 |  LWLock |  buffer_mapping |  00: 31: 08 |  0,79
 El |  6 |  LWLock |  buffer_io |  00: 22: 12 |  0,56
 El |  7 |  Tiempo de espera |  PgSleep |  00: 10: 58 |  0.28
 El |  8 |  Bloqueo |  tupla |  00: 01: 30 |  0,04
 El |  9 |  IO |  BufFileWrite |  00: 01: 16 |  0,03
 El |  10 |  IO |  BufFileRead |  00: 00: 37 |  0,02
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 El |  TIPOS DE ESPERA POR TIEMPO TOTAL DE ESPERA, PARA PROCESOS DEL SISTEMA
 + ----- + ------------------------------ + ------------ --------
 El |  # |  wait_event_type |  duración
 + ----- + ------------------------------ + ------------ --------
 El |  1 |  Actividad |  56:46:10
 El |  2 |  IO |  00:05:13
 El |  3 |  Cliente |  00:04:00
 El |  4 |  LWLock |  00:03:07
 + ----- + ------------------------------ + ------------ --------
 El |  TIPOS DE ESPERA POR TIEMPO TOTAL DE ESPERA, PARA PROCESOS DE CLIENTES
 + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  # |  wait_event_type |  duración |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  1 |  Bloqueo |  12: 37: 44 |  19,17
 El |  2 |  IO |  07: 21: 40 |  11,17
 El |  3 |  LWLock |  00: 53: 26 |  1,35
 El |  4 |  Cliente |  00: 46: 54 |  1.19
 El |  5 |  Tiempo de espera |  00: 10: 58 |  0.28
 El |  6 |  IPC |  00: 00: 04 |  0 0
 + ----- + ------------------------------ + ------------ -------- + --------------------
 El |  ESPERAS DE PROCESOS DEL SISTEMA
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 El |  # |  backend_type |  dbname |  wait_event_type |  wait_event |  duración
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 El |  1 |  lanzador de replicación lógica |  El |  Actividad |  LogicalLauncherMain |  11:21:01
 El |  2 |  puntero de control |  El |  Actividad |  CheckpointerMain |  11:20:35
 El |  3 |  lanzador de vacío automático |  El |  Actividad |  AutoVacuumMain |  11:20:31
 El |  4 |  walwriter |  El |  Actividad |  WalWriterMain |  11:19:35
 El |  5 |  escritor de fondo |  El |  Actividad |  BgWriterMain |  10:14:19
 El |  6 |  escritor de fondo |  El |  Actividad |  BgWriterHibernate |  01:06:04
 El |  7 |  walsender |  El |  Actividad |  WalSenderMain |  00:04:05
 El |  8 |  walsender |  El |  Cliente |  ClientWrite |  00:04:00
 El |  9 |  trabajador paralelo |  tdb1 |  IO |  BufFileWrite |  00:02:45
 El |  10 |  trabajador paralelo |  tdb1 |  LWLock |  buffer_mapping |  00:02:05
 El |  11 |  trabajador paralelo |  tdb1 |  IO |  DataFileRead |  00:01:10
 El |  12 |  trabajador paralelo |  tdb1 |  IO |  BufFileRead |  00:01:05
 El |  13  trabajador paralelo |  tdb1 |  LWLock |  buffer_io |  00:00:45
 El |  14  trabajador de autovacuum |  tdb1 |  LWLock |  buffer_mapping |  00:00:09
 El |  15 |  walwriter |  El |  IO |  WALWrite |  00:00:08
 El |  16  walwriter |  El |  LWLock |  WALWriteLock |  00:00:04
 El |  17 |  escritor de fondo |  El |  LWLock |  WALWriteLock |  00:00:03
 El |  18  escritor de fondo |  El |  IO |  WALWrite |  00:00:02
 El |  19  escritor de fondo |  El |  IO |  DataFileWrite |  00:00:02
 El |  20 |  puntero de control |  El |  IO |  ControlFileSyncUpdate |  00:00:01
 El |  21 |  trabajador de autovacuum |  tdb1 |  LWLock |  buffer_io |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 El |  ESPERAS DE SQL
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 El |  # |  queryid |  dbname |  wait_event_type |  wait_event |  duración |  % dbtime
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 El |  1 |  389015618226997618 |  tdb1 |  Bloqueo |  transaccion |  09: 47: 43 |  14,87
 El |  2 |  389015618226997618 |  tdb1 |  IO |  DataFileRead |  05: 47: 07 |  8.78
 El |  3 |  El |  tdb1 |  Bloqueo |  transaccion |  02: 07: 54 |  3.24
 El |  4 |  El |  tdb1 |  IO |  DataFileRead |  01: 30: 24 |  2,29
 El |  5 |  8085340880788646241 |  tdb1 |  Cliente |  ClientRead |  00: 40: 20 |  1.02
 El |  6 |  389015618226997618 |  tdb1 |  LWLock |  buffer_mapping |  00: 20: 41 |  0,52
 El |  7 |  389015618226997618 |  tdb1 |  LWLock |  buffer_io |  00: 17: 30 |  0,44
 El |  8 |  2649515222348904837 |  tdb1 |  Tiempo de espera |  PgSleep |  00: 10: 58 |  0.28
 El |  9 |  4710212362688288619 |  tdb1 |  Bloqueo |  relación |  00: 10: 44 |  0.27
 El |  10 |  9150846928388977274 |  tdb1 |  Bloqueo |  relación |  00: 10: 24 |  0,26
 El |  11 |  28942442626229688 |  tdb1 |  Bloqueo |  relación |  00: 07: 48 |  0.2 0.2
 El |  12 |  1237430309438971376 |  tdb1 |  Bloqueo |  relación |  00: 07: 32 |  0,19
 El |  13  El |  tdb1 |  LWLock |  buffer_mapping |  00: 04: 32 |  0,11
 El |  14  El |  tdb1 |  LWLock |  buffer_io |  00: 04: 13 |  0,11
 El |  15 |  El |  tdb1 |  Cliente |  ClientRead |  00: 03: 57 |  0.1
 El |  16  4710212362688288619 |  tdb1 |  LWLock |  buffer_mapping |  00: 02: 26 |  0,06
 El |  17 |  3167065002719415275 |  tdb1 |  Bloqueo |  relación |  00: 02: 20 |  0,06
 El |  18  5731212217001535134 |  tdb1 |  Cliente |  ClientRead |  00: 01: 53 |  0,05
 El |  19  1237430309438971376 |  tdb1 |  LWLock |  buffer_mapping |  00: 01: 42 |  0,04
 El |  20 |  389015618226997618 |  tdb1 |  Bloqueo |  tupla |  00: 01: 30 |  0,04
 El |  21 |  8304755792398128062 |  tdb1 |  Bloqueo |  relación |  00: 01: 29 |  0,04
 El |  22 |  28942442626229688 |  tdb1 |  IO |  BufFileWrite |  00: 01: 16 |  0,03
 El |  23 |  9150846928388977274 |  tdb1 |  IO |  DataFileRead |  00: 01: 07 |  0,03
 El |  24 |  28942442626229688 |  tdb1 |  LWLock |  buffer_mapping |  00: 01: 03 |  0,03
 El |  25  9150846928388977274 |  tdb1 |  LWLock |  buffer_mapping |  00: 00: 44 |  0,02
 El |  26 |  28942442626229688 |  tdb1 |  IO |  BufFileRead |  00: 00: 37 |  0,02
 El |  27  28942442626229688 |  tdb1 |  LWLock |  buffer_io |  00: 00: 25 |  0,01
 El |  28 |  1237430309438971376 |  tdb1 |  IO |  DataFileRead |  00: 00: 24 |  0,01
 El |  29 |  28942442626229688 |  tdb1 |  IO |  DataFileRead |  00: 00: 22 |  0,01
 El |  30  5731212217001535134 |  tdb1 |  Bloqueo |  relación |  00: 00: 20 |  0,01
 El |  31  4710212362688288619 |  tdb1 |  IO |  DataFileRead |  00: 00: 19 |  0,01
 El |  32  9150846928388977274 |  tdb1 |  Cliente |  ClientRead |  00: 00: 09 |  0 0
 El |  33  3422818749220588372 |  tdb1 |  Cliente |  ClientRead |  00: 00: 08 |  0 0
 El |  34  1237430309438971376 |  tdb1 |  Cliente |  ClientRead |  00: 00: 06 |  0 0
 El |  35  389015618226997618 |  tdb1 |  LWLock |  buffer_content |  00: 00: 05 |  0 0
 El |  36  4710212362688288619 |  tdb1 |  Cliente |  ClientRead |  00: 00: 05 |  0 0
 El |  37 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_io |  00: 00: 04 |  0 0
 El |  38  28942442626229688 |  tdb1 |  Cliente |  ClientRead |  00: 00: 04 |  0 0
 El |  39  28942442626229688 |  tdb1 |  IPC |  ParaleloAcabado |  00: 00: 03 |  0 0
 El |  40 |  389015618226997618 |  tdb1 |  IO |  DataFileWrite |  00: 00: 02 |  0 0
 El |  41 |  -5730801771815999400 |  tdb1 |  Cliente |  ClientRead |  00: 00: 02 |  0 0
 El |  42 |  2404820632950544954 |  tdb1 |  Cliente |  ClientRead |  00: 00: 02 |  0 0
 El |  43  -6572922443698419129 |  tdb1 |  Cliente |  ClientRead |  00: 00: 02 |  0 0
 El |  44  8304755792398128062 |  tdb1 |  Cliente |  ClientRead |  00: 00: 02 |  0 0
 El |  45 |  -1473395109729441239 |  tdb1 |  Cliente |  ClientRead |  00: 00: 02 |  0 0
 El |  46 |  El |  tdb1 |  LWLock |  buffer_content |  00: 00: 01 |  0 0
 El |  47  180077086776069052 |  tdb1 |  Cliente |  ClientRead |  00: 00: 01 |  0 0
 El |  48  El |  tdb1 |  IO |  DataFileWrite |  00: 00: 01 |  0 0
 El |  49  28942442626229688 |  tdb1 |  IPC |  MessageQueueReceive |  00: 00: 01 |  0 0
 El |  50  2369289265278398647 |  tdb1 |  Cliente |  ClientRead |  00: 00: 01 |  0 0
 El |  51  9150846928388977274 |  tdb1 |  IO |  DataFileWrite |  00: 00: 01 |  0 0
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 El |
 El |  ESTADÍSTICAS DEL CLIENTE SQL
 El |
 + ------------------------------------------------- -----------------------------------
 El |  CLIENTE SQL ordenado por tiempo transcurrido
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 El |  tiempo transcurrido |  llamadas |  % dbtime |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 El |  06: 43: 19 |  36  10,2 |  Septiembre 85 |  17,38 |  tdb1 |  389015618226997618
 El |  02: 06: 53 |  715  3,21 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 El |  01: 52: 07 |  720  2,84 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 El |  00: 39: 03 |  357  0,99 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 El |  00: 22: 00 |  8 |  0,56 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 El |  CLIENTE SQL ordenado por tiempo de CPU
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  tiempo de la CPU |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  16: 14: 38 |  36  10,2 |  06: 43: 19 |  Septiembre 85 |  17,38 |  tdb1 |  389015618226997618
 El |  00: 13: 38 |  720  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 El |  00: 11: 39 |  357  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 El |  00: 10: 58 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 El |  00: 09: 44 |  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por usuario E / S Tiempo de espera
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  io_wait time |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  05: 47: 09 |  36  10,2 |  06: 43: 19 |  Septiembre 85 |  17,38 |  tdb1 |  389015618226997618
 El |  00: 02: 15 |  357  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 El |  00: 00: 24 |  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 El |  00: 00: 19 |  720  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 El |  00: 00: 00 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por lecturas de búferes compartidos
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  tampones lee |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  2562353244 |  36  10,2 |  06: 43: 19 |  Septiembre 85 |  17,38 |  tdb1 |  389015618226997618
 El |  11041689 |  357  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 El |  3303551 |  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 El |  3242892 |  720  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 El |  0 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por Tiempo de lectura de disco
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  tiempo de lectura |  llamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  03: 37: 46 |  36  10,2 |  06: 43: 19 |  Septiembre 85 |  17,38 |  tdb1 |  389015618226997618
 El |  00: 04: 07 |  357  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 El |  00: 00: 59 |  720  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 El |  00: 00: 42 |  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 El |  00: 00: 00 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  CLIENTE SQL ordenado por Ejecuciones
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  llamadas |  filas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  720  720  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 El |  715  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 El |  357  0 |  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 El |  36  36  10,2 |  06: 43: 19 |  Septiembre 85 |  17,38 |  tdb1 |  389015618226997618
 El |  8 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 El |  Lista completa de texto SQL
 ----------------------------------------------
 ...

Continuará El siguiente paso es la formación del historial de bloqueo (pg_stat_locks), una descripción más detallada del proceso de llenado de tablas.

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

More articles:


All Articles