Geekly articles weekly

Tentando criar um analógico ASH para o PostgreSQL

Declaração do problema


Para otimizar as consultas do PostgreSQL, ele realmente requer a capacidade de analisar o histórico de atividades, em particular - expectativas, bloqueios, estatísticas da tabela.

Opções disponíveis


Ferramenta de análise de carga histórica ou "AWR for Postgres" : uma solução muito interessante, mas não há histórico de pg_stat_activity e pg_locks.

Extensão pgsentinel :
" Todas as informações acumuladas são armazenadas apenas na RAM, e a quantidade consumida de memória é regulada pelo número de últimos registros armazenados.

O campo queryid é adicionado - o mesmo queryid da extensão pg_stat_statements (é necessária uma instalação preliminar). "

Isso certamente ajudaria muito, mas o incômodo é o primeiro parágrafo: " Todas as informações acumuladas são armazenadas apenas na RAM ", ou seja, temos um impacto na base alvo. Além disso, não há histórico de bloqueio e estatísticas de tabela. I.e. de um modo geral, a solução está incompleta: “ Ainda não existe um pacote pronto para instalação. Propõe-se baixar as fontes e construir a biblioteca você mesmo. Primeiro, você precisa instalar o pacote “devel” no seu servidor e escrever o caminho para pg_config na variável PATH. "

Em geral - muita confusão e, no caso de bancos de dados de produção sérios, talvez não haja maneira de fazer algo com o servidor. Novamente, você precisa criar algo próprio.

Advertência
Devido à novidade do tópico e à incompletude do período de teste, o artigo é principalmente apenas para orientação, e não como um conjunto de resumos e resultados intermediários.
Material mais detalhado será preparado posteriormente, em partes.


Descrever os requisitos da solução


É necessário desenvolver uma ferramenta para armazenar:

Pg_stat_activity ver histórico
Histórico de bloqueio de sessão usando a visualização pg_locks

O requisito de decisão é minimizar o impacto no banco de dados de destino.

A ideia geral é que o agente de coleta de dados seja iniciado não no banco de dados de destino, mas no banco de dados de monitoramento como um serviço systemd. Sim, é possível perder alguns dados, mas isso não é crítico para os relatórios, mas não há impacto no banco de dados de destino da memória e do espaço em disco. E no caso de usar um pool de conexões, o impacto nos processos do usuário é mínimo.

Etapas de implementação


1. Mesas de serviço


Para armazenar tabelas, um esquema separado é usado para não complicar a análise das tabelas principais usadas.

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

Importante: o esquema não é criado no banco de dados de destino, mas no banco de dados de monitoramento.

Pg_stat_activity ver histórico


Use a tabela para armazenar as capturas instantâneas atuais da visualização 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 a inserção - sem índices ou restrições.

Para armazenar o histórico diretamente, uma tabela particionada é usada:

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

Como nesse caso não há requisitos para a velocidade de inserção, alguns índices foram criados para acelerar os relatórios.

Histórico de Bloqueio de Sessão


Para armazenar os instantâneos de bloqueio de sessão atuais, use a tabela:

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

Além disso, para acelerar a inserção - sem índices ou restrições.

Para armazenar o histórico diretamente, uma tabela particionada é usada:

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

Como nesse caso não há requisitos para a velocidade de inserção, alguns índices foram criados para acelerar os relatórios.

2. Preenchendo o histórico atual


Para capturar diretamente os instantâneos de uma visualização, é usado um script bash que executa a função 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

A função plpgsql dblink acessa visualizações no banco de dados de destino e insere linhas nas tabelas de serviço no banco de dados de monitoramento.

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 coletar capturas instantâneas da exibição, o serviço systemd é usado e dois 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


Atribua direitos a scripts:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Iniciamos o serviço:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

Assim, a história das performances é coletada na forma de cada segundo disparo. Obviamente, se tudo permanecer como está, as tabelas aumentarão muito rapidamente de tamanho e o trabalho mais ou menos produtivo se tornará impossível.

É necessário organizar o arquivamento de dados.

3. história do arquivo


Para arquivamento, são usadas tabelas particionadas *.

Novas seções são criadas a cada hora, enquanto os dados antigos das tabelas de histórico * são excluídos, portanto o tamanho das tabelas de histórico * não muda muito e a velocidade de inserção não diminui com o tempo.

A criação de novas seções é realizada pela função plpgsql activity_hist.archive_current_activity. O algoritmo de operação é muito simples (usando o exemplo da seção da tabela archive_pg_stat_activity).

Crie e preencha uma nova seção
 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 );

Criar í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 )' );

Excluir dados antigos da tabela history_pg_stat_activity
 DELETE FROM activity_hist.history_pg_stat_activity WHERE timepoint < partition_max_range;

Obviamente, periodicamente, as seções antigas são excluídas como desnecessárias.

Relatórios básicos


Na verdade, por que tudo isso está sendo feito. Para receber relatórios muito remotamente, lembra o Oracle AWR.
É importante adicionar que, para receber relatórios, é necessário construir um relacionamento entre as visualizações pg_stat_activity e pg_stat_statements. As tabelas são vinculadas adicionando a coluna 'queryid' às tabelas 'history_pg_stat_activity', 'archive_pg_stat_activity'. A maneira de adicionar um valor de coluna está além do escopo deste artigo e é descrita aqui - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TEMPO TOTAL DE CPU PARA CONSULTAS


Pedido:
 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 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 backend' E datname! = 'Postgres' E (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

Um exemplo:
 ------------------------------------------------------------------- | 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 +----+----------------------------------------+--------------------


Tempo total de espera para consultas


Pedido:
 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

Um exemplo:
 ------------------------------------------------------------------- | 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 +----+----------------------------------------+--------------------

ESPERANDO QUESTÕES


Pedidos:
 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

Um exemplo:
 ------------------------------------------------ | 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 +-----------------------------------------------

HISTÓRICO DE PROCESSOS BLOQUEADOS


Pedido:
 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

Um exemplo:
  -------------------------------------------------- -------------------------------------------------- ---------------------------------
 |  HISTÓRICO DE PROCESSOS BLOQUEADOS
 + ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
 |  # |  pid |  iniciado |  duração |  blocking_pids |  relação |  modo |  locktype
 + ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
 |  1 |  26224  2019-09-02 19: 32: 16 |  00: 01: 45 |  {26211} |  16541  AccessShareLock |  relação
 |  2  26390  2019-09-02 19: 34: 03 |  00: 00: 53 |  {26211} |  16541  AccessShareLock |  relação
 |  3  26391  2019-09-02 19: 34: 03 |  00: 00: 53 |  {26211} |  16541  AccessShareLock |  relação
 |  4  26531  2019-09-02 19: 35: 27 |  00: 00: 12 |  {26211} |  16541  AccessShareLock |  relação
 |  5  27284  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541  AccessShareLock |  relação
 |  6  27283  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541  AccessShareLock |  relação
 |  7  27286  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541  AccessShareLock |  relação
 |  8  27423  2019-09-02 19:45:24 |  00: 00: 12 |  {27394} |  16541  AccessShareLock |  relação
 |  9  27648  2019-09-02 19: 48: 06 |  00: 00: 20 |  {27647} |  16541  AccessShareLock |  relação
 |  10  27650  2019-09-02 19: 48: 06 |  00: 00: 20 |  {27647} |  16541  AccessShareLock |  relação
 |  11  27735  2019-09-02 19: 49: 08 |  00: 00: 06 |  {27650} |  16541  AccessExclusiveLock |  relação
 |  12  28380  2019-09-02 19: 56: 03 |  00: 01: 56 |  {28379} |  16541  AccessShareLock |  relação
 |  13  28379  2019-09-02 19: 56: 03 |  00: 00: 01 |  28377  16541  AccessExclusiveLock |  relação
 |  |  |  |  |  28376  |

HISTÓRICO DE PROCESSOS DE BLOQUEIO


Pedidos:
 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

Um exemplo:
  -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------
 HISTÓRICO DE PROCESSOS DE BLOQUEIO
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 |  # |  pid |  nome de usuário |  application_name |  datname |  iniciado |  duração |  estado |  consulta
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 |  1 |  26211  tuser |  psql |  tdb1  2019-09-02 19: 31: 54 |  00: 00: 04 |  ocioso |
 |  2  26211  tuser |  psql |  tdb1  2019-09-02 19: 31: 58 |  00: 00: 06 |  ocioso na transação |  começar;
 |  3  26211  tuser |  psql |  tdb1  2019-09-02 19: 32: 16 |  00: 01: 45 |  ocioso na transação |  tabela de bloqueio wafer_data;
 |  4  26211  tuser |  psql |  tdb1  2019-09-02 19: 35: 54 |  00: 01: 23 |  ocioso |  confirmar;
 |  5  26211  tuser |  psql |  tdb1  2019-09-02 19: 38: 46 |  00: 00: 02 |  ocioso na transação |  começar;
 |  6  26211  tuser |  psql |  tdb1  2019-09-02 19: 38: 54 |  00: 00: 08 |  ocioso na transação |  tabela de bloqueio wafer_data;
 |  7  26211  tuser |  psql |  tdb1  2019-09-02 19: 39: 08 |  00: 42: 42 |  ocioso |  confirmar;
 |  8  26211  tuser |  psql |  tdb1  2019-09-03 07: 12: 07 |  00: 00: 52 |  ativo |  selecione test_del ();


Desenvolvimento.


As consultas básicas mostradas e os relatórios recebidos já simplificam bastante a vida ao analisar incidentes de desempenho.
Com base em consultas básicas, você pode obter um relatório remotamente reminiscente do Oracle AWR.
Relatório de resumo de amostra
  + ------------------------------------------------- -----------------------------------
 |  RELATÓRIO CONSOLIDADO DE ATIVIDADES E DE ESPERA.  Data: 09/03/2019 14:08
 | ------------------------------------------------- -----------------------------------
 |  HOST: XXXX
 |  BEGIN_SNAPSHOT: 09/02/2019 14:08 END_SNAPSHOT: 09/03/2019 14:00
 | ------------------------------------------------- -----------------------------------
 |  TAMANHO ATUAL DAS BASES DE DADOS:
 |  DATABASE: monitor
 |  TAMANHO (MB): 1370,00
 | ------------------------------------------------- -----------------------------------
 |  CLUSTER TEMPO DE CPU: 19:44:22
 |  CLUSTER WAITINGS TIME: 78:49:16
 |
 |  SQL DBTIME: 65:53:09
 |  TEMPO DE CPU DO SQL: 19:05:21
 |  TEMPO DE ESPERA DO SQL: 21:50:46
 |  SQL IOTIME: 20:53:00
 |  SQL TEMPO DE LER: 20:52:55
 |  SQL WRITE TIME: 00:00:05
 |
 |  CHAMADAS SQL: 311293
 -------------------------------------------------- -----------
 |  BLOCOS COMPARTILHADOS DO SQL LEIA: 13351563334
 |  HITS DE BLOCOS COMPARTILHADOS DO SQL: 2775427045
 |  BLOCOS COMPARTILHADOS DO SQL HITS / LEITOS%: 20.79
 |  BLOCOS COMPARTILHADOS DO SQL DIRTED: 21105
 |  BLOCOS COMPARTILHADOS DO SQL ESCRITO: 3656
 |
 |  BLOCOS TEMPORÁRIOS DO SQL LEIA: 7464932
 |  BLOCOS TEMPORÁRIOS DO SQL ESCRITOS: 10176024
 -------------------------------------------------- -----------
 |
 |  ESTATÍSTICAS DE ESPERA
 |
 + ------------------------------------------------- -----------------------------------
 |  AS 10 MELHORES ESPERANÇAS POR TEMPO TOTAL DE ESPERA PARA PROCESSOS DO SISTEMA
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  wait_event |  duração
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Atividade |  LogicalLauncherMain |  11:21:01
 |  2  Atividade |  CheckpointerMain |  11:20:35
 |  3  Atividade |  AutoVacuumMain |  11:20:31
 |  4  Atividade |  WalWriterMain |  11:19:35
 |  5  Atividade |  BgWriterMain |  10:14:19
 |  6  Atividade |  BgWriterHibernate |  01:06:04
 |  7  Atividade |  WalSenderMain |  00:04:05
 |  8  Cliente  ClientWrite |  00:04:00
 |  9  IO  BufFileWrite |  00:02:45
 |  10  LWLock |  buffer_mapping |  00:02:14
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  AS 10 MELHORES ESPERANÇAS POR TEMPO TOTAL DE ESPERA PARA PROCESSOS DE CLIENTES
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  # |  wait_event_type |  wait_event |  duração |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  1 |  Bloqueio |  transactionid |  11: 55: 37 |  18,1
 |  2  IO  DataFileRead |  07: 19: 43 |  12/12
 |  3  Cliente  ClientRead |  00: 46: 54 |  1,19
 |  4  Bloqueio |  relação |  00: 40: 37 |  1.03
 |  5  LWLock |  buffer_mapping |  00: 31: 08 |  0,79
 |  6  LWLock |  buffer_io |  00: 22: 12 |  0,56
 |  7  Tempo limite |  PgSleep |  00: 10: 58 |  0,28
 |  8  Bloqueio |  tupla |  00: 01: 30 |  0,04
 |  9  IO  BufFileWrite |  00: 01: 16 |  0,03
 |  10  IO  BufFileRead |  00: 00: 37 |  0,02
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  TIPOS DE ESPERA POR TEMPO TOTAL DE ESPERA, PARA PROCESSOS DO SISTEMA
 + ----- + ------------------------------ + ------------ --------
 |  # |  wait_event_type |  duração
 + ----- + ------------------------------ + ------------ --------
 |  1 |  Atividade |  56:46:10
 |  2  IO  00:05:13
 |  3  Cliente  00:04:00
 |  4  LWLock |  00:03:07
 + ----- + ------------------------------ + ------------ --------
 |  TIPOS DE ESPERANÇA POR TEMPO TOTAL DE ESPERA, PARA PROCESSOS DE CLIENTES
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  duração |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Bloqueio |  12: 37: 44  19.17
 |  2  IO  07: 21: 40 |  11.17
 |  3  LWLock |  00: 53: 26 |  1,35
 |  4  Cliente  00: 46: 54 |  1,19
 |  5  Tempo limite |  00: 10: 58 |  0,28
 |  6  IPC  00: 00: 04 |  0 0
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  ESPERANDO PROCESSOS DE SISTEMA
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  # |  backend_type |  dbname |  wait_event_type |  wait_event |  duração
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  1 |  iniciador de replicação lógica |  |  Atividade |  LogicalLauncherMain |  11:21:01
 |  2  checkpointer |  |  Atividade |  CheckpointerMain |  11:20:35
 |  3  lançador de vácuo automático |  |  Atividade |  AutoVacuumMain |  11:20:31
 |  4  walwriter |  |  Atividade |  WalWriterMain |  11:19:35
 |  5  escritor de antecedentes |  |  Atividade |  BgWriterMain |  10:14:19
 |  6  escritor de antecedentes |  |  Atividade |  BgWriterHibernate |  01:06:04
 |  7  walsender |  |  Atividade |  WalSenderMain |  00:04:05
 |  8  walsender |  |  Cliente  ClientWrite |  00:04:00
 |  9  trabalhador paralelo |  tdb1  IO  BufFileWrite |  00:02:45
 |  10  trabalhador paralelo |  tdb1  LWLock |  buffer_mapping |  00:02:05
 |  11  trabalhador paralelo |  tdb1  IO  DataFileRead |  00:01:10
 |  12  trabalhador paralelo |  tdb1  IO  BufFileRead |  00:01:05
 |  13  trabalhador paralelo |  tdb1  LWLock |  buffer_io |  00:00:45
 |  14  trabalhador de autovacuum |  tdb1  LWLock |  buffer_mapping |  00:00:09
 |  15  walwriter |  |  IO  WALWrite |  00:00:08
 |  16  walwriter |  |  LWLock |  WALWriteLock |  00:00:04
 |  17  escritor de antecedentes |  |  LWLock |  WALWriteLock |  00:00:03
 |  18  escritor de antecedentes |  |  IO  WALWrite |  00:00:02
 |  19  escritor de antecedentes |  |  IO  DataFileWrite |  00:00:02
 |  20  checkpointer |  |  IO  ControlFileSyncUpdate |  00:00:01
 |  21  trabalhador de autovacuum |  tdb1  LWLock |  buffer_io |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  ESPERANDO PARA SQL
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |  # |  queryid |  dbname |  wait_event_type |  wait_event |  duração |  % dbtime
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |  1 |  389015618226997618 |  tdb1  Bloqueio |  transactionid |  09: 47: 43 |  14,87
 |  2  389015618226997618 |  tdb1  IO  DataFileRead |  05: 47: 07  8,78
 |  3  |  tdb1  Bloqueio |  transactionid |  02: 07: 54 |  3,24
 |  4  |  tdb1  IO  DataFileRead |  01: 30: 24 |  2,29
 |  5  8085340880788646241 |  tdb1  Cliente  ClientRead |  00: 40: 20 |  1.02
 |  6  389015618226997618 |  tdb1  LWLock |  buffer_mapping |  00: 20: 41 |  0,52
 |  7  389015618226997618 |  tdb1  LWLock |  buffer_io |  00: 17: 30 |  0,44
 |  8  2649515222348904837 |  tdb1  Tempo limite |  PgSleep |  00: 10: 58 |  0,28
 |  9  4710212362688288619 |  tdb1  Bloqueio |  relação |  00: 10: 44 |  0,27
 |  10  9150846928388977274 |  tdb1  Bloqueio |  relação |  00: 10: 24 |  0,26
 |  11  28942442626229688 |  tdb1  Bloqueio |  relação |  00: 07: 48 |  0,2
 |  12  1237430309438971376 |  tdb1  Bloqueio |  relação |  00: 07: 32 |  0,19
 |  13  |  tdb1  LWLock |  buffer_mapping |  00: 04: 32 |  0,11
 |  14  |  tdb1  LWLock |  buffer_io |  00: 04: 13 |  0,11
 |  15  |  tdb1  Cliente  ClientRead |  00: 03: 57 |  0,1
 |  16  4710212362688288619 |  tdb1  LWLock |  buffer_mapping |  00: 02: 26 |  0,06
 |  17  3167065002719415275 |  tdb1  Bloqueio |  relação |  00: 02: 20 |  0,06
 |  18  5731212217001535134 |  tdb1  Cliente  ClientRead |  00: 01: 53 |  0,05
 |  19  1237430309438971376 |  tdb1  LWLock |  buffer_mapping |  00: 01: 42 |  0,04
 |  20  389015618226997618 |  tdb1  Bloqueio |  tupla |  00: 01: 30 |  0,04
 |  21  8304755792398128062 |  tdb1  Bloqueio |  relação |  00: 01: 29 |  0,04
 |  22  28942442626229688 |  tdb1  IO  BufFileWrite |  00: 01: 16 |  0,03
 |  23  9150846928388977274 |  tdb1  IO  DataFileRead |  00: 01: 07 |  0,03
 |  24  28942442626229688 |  tdb1  LWLock |  buffer_mapping |  00: 01: 03 |  0,03
 |  25  9150846928388977274 |  tdb1  LWLock |  buffer_mapping |  00: 00: 44 |  0,02
 |  26  28942442626229688 |  tdb1  IO  BufFileRead |  00: 00: 37 |  0,02
 |  27  28942442626229688 |  tdb1  LWLock |  buffer_io |  00: 00: 25 |  0,01
 |  28  1237430309438971376 |  tdb1  IO  DataFileRead |  00: 00: 24 |  0,01
 |  29  28942442626229688 |  tdb1  IO  DataFileRead |  00: 00: 22 |  0,01
 |  30  5731212217001535134 |  tdb1  Bloqueio |  relação |  00: 00: 20 |  0,01
 |  31  4710212362688288619 |  tdb1  IO  DataFileRead |  00: 00: 19 |  0,01
 |  32  9150846928388977274 |  tdb1  Cliente  ClientRead |  00: 00: 09 |  0 0
 |  33  3422818749220588372 |  tdb1  Cliente  ClientRead |  00: 00: 08 |  0 0
 |  34  1237430309438971376 |  tdb1  Cliente  ClientRead |  00: 00: 06 |  0 0
 |  35  389015618226997618 |  tdb1  LWLock |  buffer_content |  00: 00: 05 |  0 0
 |  36  4710212362688288619 |  tdb1  Cliente  ClientRead |  00: 00: 05 |  0 0
 |  37  4710212362688288619 |  tdb1  LWLock |  buffer_io |  00: 00: 04 |  0 0
 |  38  28942442626229688 |  tdb1  Cliente  ClientRead |  00: 00: 04 |  0 0
 |  39  28942442626229688 |  tdb1  IPC  ParallelFinish |  00: 00: 03 |  0 0
 |  40  389015618226997618 |  tdb1  IO  DataFileWrite |  00: 00: 02 |  0 0
 |  41  -5730801771815999400 |  tdb1  Cliente  ClientRead |  00: 00: 02 |  0 0
 |  42  2404820632950544954 |  tdb1  Cliente  ClientRead |  00: 00: 02 |  0 0
 |  43  -6572922443698419129 |  tdb1  Cliente  ClientRead |  00: 00: 02 |  0 0
 |  44  8304755792398128062 |  tdb1  Cliente  ClientRead |  00: 00: 02 |  0 0
 |  45  -1473395109729441239 |  tdb1  Cliente  ClientRead |  00: 00: 02 |  0 0
 |  46  |  tdb1  LWLock |  buffer_content |  00: 00: 01 |  0 0
 |  47  180077086776069052 |  tdb1  Cliente  ClientRead |  00: 00: 01 |  0 0
 |  48  |  tdb1  IO  DataFileWrite |  00: 00: 01 |  0 0
 |  49  28942442626229688 |  tdb1  IPC  MessageQueueReceive |  00: 00: 01 |  0 0
 |  50  2369289265278398647 |  tdb1  Cliente  ClientRead |  00: 00: 01 |  0 0
 |  51  9150846928388977274 |  tdb1  IO  DataFileWrite |  00: 00: 01 |  0 0
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |
 |  ESTATÍSTICAS SQL DO CLIENTE
 |
 + ------------------------------------------------- -----------------------------------
 |  CLIENT SQL ordenado por tempo decorrido
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  tempo decorrido |  chamadas |  % dbtime |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  06: 43: 19  36  10,2  85 de setembro |  17,38 |  tdb1  389015618226997618
 |  02: 06: 53 |  715  3,21 |  0,85 |  0,06 |  tdb1  1237430309438971376
 |  01: 52: 07  720  2,84 |  1,19 |  0,08 |  tdb1  4710212362688288619
 |  00: 39: 03 |  357  0,99 |  1.02  0,33 |  tdb1  28942442626229688
 |  00: 22: 00 |  8  0,56 |  0,96 |  0  tdb1  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  CLIENT SQL ordenado pelo tempo da CPU
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  tempo da CPU |  chamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  16: 14: 38 |  36  10,2  06: 43: 19  85 de setembro |  17,38 |  tdb1  389015618226997618
 |  00: 13: 38 |  720  2,84 |  01: 52: 07  1,19 |  0,08 |  tdb1  4710212362688288619
 |  00: 11: 39 |  357  0,99 |  00: 39: 03 |  1.02  0,33 |  tdb1  28942442626229688
 |  00: 10: 58 |  8  0,56 |  00: 22: 00 |  0,96 |  0  tdb1  2649515222348904837
 |  00: 09: 44 |  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1  1237430309438971376
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordenado pelo tempo de espera de E / S do usuário
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  tempo de espera |  chamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  05: 47: 09 |  36  10,2  06: 43: 19  85 de setembro |  17,38 |  tdb1  389015618226997618
 |  00: 02: 15 |  357  0,99 |  00: 39: 03 |  1.02  0,33 |  tdb1  28942442626229688
 |  00: 00: 24 |  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1  1237430309438971376
 |  00: 00: 19 |  720  2,84 |  01: 52: 07  1,19 |  0,08 |  tdb1  4710212362688288619
 |  00: 00: 00 |  8  0,56 |  00: 22: 00 |  0,96 |  0  tdb1  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordenado por buffers compartilhados lê
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  buffers lê |  chamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  2562353244 |  36  10,2  06: 43: 19  85 de setembro |  17,38 |  tdb1  389015618226997618
 |  11041689 |  357  0,99 |  00: 39: 03 |  1.02  0,33 |  tdb1  28942442626229688
 |  3303551  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1  1237430309438971376
 |  3242892 |  720  2,84 |  01: 52: 07  1,19 |  0,08 |  tdb1  4710212362688288619
 |  0  8  0,56 |  00: 22: 00 |  0,96 |  0  tdb1  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordenado pelo tempo de leitura do disco
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  tempo de leitura |  chamadas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  03: 37: 46  36  10,2  06: 43: 19  85 de setembro |  17,38 |  tdb1  389015618226997618
 |  00: 04: 07 |  357  0,99 |  00: 39: 03 |  1.02  0,33 |  tdb1  28942442626229688
 |  00: 00: 59 |  720  2,84 |  01: 52: 07  1,19 |  0,08 |  tdb1  4710212362688288619
 |  00: 00: 42 |  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1  1237430309438971376
 |  00: 00: 00 |  8  0,56 |  00: 22: 00 |  0,96 |  0  tdb1  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordenado pelas execuções
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  chamadas |  linhas |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  720  720  2,84 |  01: 52: 07  1,19 |  0,08 |  tdb1  4710212362688288619
 |  715  715  3,21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1  1237430309438971376
 |  357  0  0,99 |  00: 39: 03 |  1.02  0,33 |  tdb1  28942442626229688
 |  36  36  10,2  06: 43: 19  85 de setembro |  17,38 |  tdb1  389015618226997618
 |  8  8  0,56 |  00: 22: 00 |  0,96 |  0  tdb1  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Lista completa de texto SQL
 ----------------------------------------------
 ...

Para ser continuado. O próximo passo é a formação do histórico de bloqueios (pg_stat_locks), uma descrição mais detalhada do processo de preenchimento de tabelas.

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

More articles:


All Articles