Uma tentativa de criar um análogo ASH para PostgreSQL
Formulação do problema
Para otimizar as consultas do PostgreSQL, é muito necessária a capacidade de analisar o histórico de atividades, em particular, esperas, bloqueios e estatísticas da tabela.
extensão pgssentinel :
«Todas as informações acumuladas são armazenadas apenas na RAM, e a quantidade de memória consumida é regulada pelo número dos últimos registros armazenados.
O campo queryid é adicionado - o mesmo queryid da extensão pg_stat_statements (pré-instalação necessária).«
Isso, claro, ajudaria muito, mas o mais problemático é o primeiro ponto.”Todas as informações acumuladas são armazenadas apenas na RAM ”, ou seja há um impacto na base alvo. Além disso, não há histórico de bloqueios e estatísticas de tabela. Aqueles. a solução é geralmente incompleta: “Ainda não existe um pacote pronto para instalação. Sugere-se baixar os fontes e montar você mesmo a biblioteca. Primeiro você precisa instalar o pacote “devel” para o seu servidor e definir o caminho para pg_config na variável PATH.".
Em geral, há muito barulho e, no caso de bancos de dados de produção sérios, pode não ser possível fazer nada com o servidor. Precisamos inventar algo nosso novamente.
Atenção.
Devido ao volume bastante grande e ao período de testes incompleto, o artigo tem caráter principalmente informativo, mas sim como um conjunto de teses e resultados intermediários.
Material mais detalhado será preparado posteriormente, em partes
Rascunho dos requisitos para a solução
É necessário desenvolver uma ferramenta que permita armazenar:
pg_stat_activity visualizar histórico Histórico de bloqueio de sessão usando a visualização pg_locks
Requisito de solução–minimizar o impacto no banco de dados de destino.
Ideia geral– o agente de coleta de dados não é iniciado no banco de dados de destino, mas no banco de dados de monitoramento como um serviço systemd. Sim, é possível alguma perda de dados, mas isso não é crítico para os relatórios, mas não há impacto no banco de dados de destino em termos de memória e espaço em disco. E no caso de usar um pool de conexões, o impacto nos processos do usuário é mínimo.
Estágios de implementação
1. Tabelas de serviço
Um esquema separado é utilizado para armazenar tabelas, para não complicar a análise das principais tabelas utilizadas.
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 visualizar histórico
Uma tabela é usada para armazenar instantâneos atuais da visualização pg_stat_activity
atividade_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 em si, uma tabela particionada é usada:
atividade_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 neste caso não há requisitos de velocidade de inserção, alguns índices foram criados para agilizar a criação de relatórios.
Histórico de bloqueio de sessão
Uma tabela é usada para armazenar instantâneos atuais de bloqueios de sessão:
atividade_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 agilizar a inserção, não há índices ou restrições.
Para armazenar o histórico em si, uma tabela particionada é usada:
atividade_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 neste caso não há requisitos de velocidade de inserção, alguns índices foram criados para agilizar a criação de relatórios.
2.Preenchendo o histórico atual
Para coletar diretamente os instantâneos de visualização, é usado um script bash que executa a função plpgsql.
plpgsql A função dblink acessa visualizações no banco de dados de destino e insere linhas em 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 instantâneos de visualização, o serviço systemd e dois scripts são usados:
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 10.124.70.40 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
Iniciamos o serviço:
# systemctl daemon-reload
# systemctl start pg_current_activity.service
Assim, o histórico de visualizações é coletado na forma de instantâneos segundo a segundo. É claro que, se tudo ficar como está, as mesas aumentarão rapidamente de tamanho e o trabalho mais ou menos produtivo se tornará impossível.
É necessário organizar o arquivamento de dados.
3. Arquivando histórico
Para arquivamento, são usados arquivos de tabelas particionadas*.
Novas partições são criadas a cada hora, enquanto os dados antigos são removidos das tabelas de histórico*, 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 trabalho é 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
);
Criando í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 )'
);
Removendo dados antigos da tabela history_pg_stat_activity
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
É claro que, de tempos em tempos, seções antigas são excluídas por serem desnecessárias.
Relatórios básicos
Na verdade, por que tudo isso está sendo feito? Para obter relatórios que lembram vagamente o AWR da Oracle.
É importante acrescentar que para receber relatórios, você precisa construir uma conexão entre as visualizações pg_stat_activity e pg_stat_statements. As tabelas são vinculadas adicionando uma coluna 'queryid' às tabelas 'history_pg_stat_activity', 'archive_pg_stat_activity'. O método de adicionar um valor de coluna está além do escopo deste artigo e é descrito aqui - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .
TEMPO TOTAL DE CPU PARA CONSULTAS
Solicitar :
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
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
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
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
Exemplo:
-------------------------------------------------- -------------------------------------------------- --------------------------------- | HISTÓRICO DE PROCESSOS BLOQUEADOS +-----+----------+-----+-------- --+------------ --------+-----------------------+----- --------------- +------------------- | #| pid| começou| duração| bloqueio_pids| relação | modo| tipo de bloqueio +----------+----------+-----+---------- +------------ -----------+-----------+------- -------------+----- -------------- | 1| 26224| 2019/09/02 19:32:16| 00:01:45| {26211}| 16541| AcessoShareLock| relação | 2| 26390| 2019/09/02 19:34:03| 00:00:53| {26211}| 16541| AcessoShareLock| relação | 3| 26391| 2019/09/02 19:34:03| 00:00:53| {26211}| 16541| AcessoShareLock| relação | 4| 26531| 2019/09/02 19:35:27| 00:00:12| {26211}| 16541| AcessoShareLock| relação | 5| 27284| 2019/09/02 19:44:02| 00:00:19| {27276}| 16541| AcessoShareLock| relação | 6| 27283| 2019/09/02 19:44:02| 00:00:19| {27276}| 16541| AcessoShareLock| relação | 7| 27286| 2019/09/02 19:44:02| 00:00:19| {27276}| 16541| AcessoShareLock| relação | 8| 27423| 2019/09/02 19:45:24| 00:00:12| {27394}| 16541| AcessoShareLock| relação | 9| 27648| 2019/09/02 19:48:06| 00:00:20| {27647}| 16541| AcessoShareLock| relação | 10| 27650| 2019/09/02 19:48:06| 00:00:20| {27647}| 16541| AcessoShareLock| relação | 11| 27735| 2019/09/02 19:49:08| 00:00:06| {27650}| 16541| AcessoExclusivoLock| relação | 12| 28380| 2019/09/02 19:56:03| 00:01:56| {28379}| 16541| AcessoShareLock| relação | 13| 28379| 2019/09/02 19:56:03| 00:00:01| 28377| 16541| AcessoExclusivoLock| relação | | | | | 28376| |
HISTÓRICO DE PROCESSOS DE BLOQUEIO
Solicitações de:
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
As consultas básicas mostradas e os relatórios resultantes já facilitam muito a vida na hora de analisar incidentes de desempenho.
Com base em consultas básicas, você pode obter um relatório que lembra vagamente o AWR da Oracle. Exemplo de relatório resumido
+---------------------------------------------------------------- ----------------------------------- | RELATÓRIO CONSOLIDADO DE ATIVIDADES E ESPERAS.
Continua. O próximo passo é a criação de um histórico de bloqueios (pg_stat_locks), uma descrição mais detalhada do processo de preenchimento de tabelas.