Привет! Меня зовут Геннадий, я Oracle и PostgreSQL DBA в компании Uzum. По пути еще иногда занимаюсь NoSQL, люблю автоматизировать информацию из Ansible и визуализировать нужные мне метрики в Grafana. Хочу поделиться дашбордом для Grafana, который отображает историю активных сессий PG с их текущим SQL, отсортированных по группам ожиданий. Он помогает мне искать проблемы производительности PG-баз, и может пригодиться тем, кто администрирует Postgres.
Дашборд выглядит так:
DBA, которые работают с Oracle, наверняка вспомнили Oracle Enterprise Manager и его визуализацию Active Sessions. Выглядит это так (с небольшими изменениями от версии к версии самого Oracle EM):
Что-то такое мне захотелось сделать и для Postgres. Конечно, подобные решения есть, в основном, в платных системах мониторинга, а вот в бесплатных достаточно удобного и информативного я не нашел. Какие-то варианты описывались на Хабре: раз, два. Спасибо авторам, решения не плохи, просто немного устарели: в одном нет графического интерфейса, а в другом он старый однопользовательский толстый клиент. А мне хотелось получить современный интерфейс именно в Grafana. Ок, берем лучшее из уже имеющегося, заодно прикрутим partitioning к таблице истории и автоматическое удаление старых данных (чтобы наша таблица истории не превратилась в терабайтного монстра, с которым сложно справиться). По версиям — это Postgres 15.3 и Grafana v11.2. Поехали!
Делаем минимальным набором инструментов самого Postgres — из дополнительных расширений нам понадобится только pg_cron, но его необязательно собирать из исходников, есть и готовые пакеты под популярные дистрибутивы.
Итак, общая идея следующая: мы будем раз в 10 секунд сбрасывать данные по активным (и еще idle in transaction) сессиям из pg_stat_activity в нашу таблицу истории — pg_stat_activity_history, которая по сути та же pg_stat_activity, но с дополнительным полем sample_time (время создания снимка). Таким образом мы получим историю сессий. А потом будем запрашивать из нее в Grafana, группируя по типам ожиданий для построения временных графиков, и, в другом случае, по запросам для отображения статистики по ним снизу.
Табличка истории у нас будет партиционированной, это позволит эффективно читать и, главное, удалять уже ненужную историю с drop partition без лишней WAL-генерации и загрузки autovacuum. Поверьте, если у вас достаточно нагруженная БД, то истории может быть десяток гигабайтов в день, и если диски у вас не резиновые, это вам очень пригодится.
Начнем с создания партиционированной таблицы для хранения истории и автоматического создания и удаления партиций в ней (для этого можно использовать pg_partman, но так как у нас pg_cron и так везде используется, и де-факто стандарт — обходимся им). Итак, пишем код создания и удаления старых партиций для заданий, которые потом запланируем в pg_cron:
Код
CREATE SCHEMA adm AUTHORIZATION postgres;
CREATE OR REPLACE FUNCTION adm.create_day_partition(t_name character varying, s_name character varying)
RETURNS void AS
$$
DECLARE
sql_query TEXT;
P_NAME VARCHAR(255);
N INTEGER := 0;
t_owner VARCHAR(255);
begin
select t.tableowner into t_owner from pg_tables t where t.tablename = t_name and t.schemaname = s_name ;
while N <= 10
loop
P_NAME := T_NAME|| replace(cast(CURRENT_DATE + N as varchar ),'-','_');
IF NOT EXISTS (select 1 as f1 from pg_tables t
where t.tablename = P_NAME and t.schemaname = S_NAME ) then
sql_query := 'CREATE TABLE ' || S_NAME || '.' || P_NAME || ' PARTITION OF ' || S_NAME || '.' || T_NAME || ' FOR VALUES FROM ('''
|| CURRENT_DATE + N || ' 00:00:00'') TO (''' || CURRENT_DATE + N + 1 || ' 00:00:00'')' ;
EXECUTE sql_query ;
sql_query := 'ALTER TABLE ' || S_NAME || '.' || P_NAME || ' OWNER TO ' || t_owner;
EXECUTE sql_query ;
END IF;
N := N+1 ;
end loop;
-- commit;
END $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION adm.drop_old_day_partition ( T_NAME VARCHAR(240) -- table name
,S_NAME VARCHAR(100) -- schema name
,days_ago integer -- delete partitions older than N days
)
RETURNS void AS
$$
DECLARE
v_parent_rec RECORD;
begin
FOR v_parent_rec IN (
select q.* , 'DROP TABLE ' || q.part_name as sql_query
from (
SELECT cast( inhrelid::regclass as varchar) AS part_name
,to_date ( replace( cast( inhrelid::regclass as varchar)
,case when ( S_NAME = 'public' or S_NAME = '' ) then ''
else S_NAME || '.'
end ||
T_NAME,'') ,'yyyy_mm_dd') as dt
FROM pg_catalog.pg_inherits i
WHERE inhparent = ( S_NAME || '.' || T_NAME )::regclass ) q
where q.dt < current_date - days_ago )
LOOP
EXECUTE v_parent_rec.sql_query ;
END LOOP;
END $$ LANGUAGE plpgsql;
Функция создает по 10 партиций за раз, на следующие 10 дней. Если какие-то уже созданы, то функция просто создаст недостающие. Можно создать хоть на год вперед, просто укажите дни в строке while N <= 10.
Теперь создадим таблицу хранения истории и ее партиции:
Код
CREATE TABLE adm.pg_stat_activity_history (
sample_time timestamptz ,
datid oid NULL,
pid int4 NULL,
leader_pid int4 NULL,
usesysid oid NULL,
application_name text NULL,
client_addr inet NULL,
client_hostname text NULL,
client_port int4 NULL,
backend_start timestamptz NULL,
xact_start timestamptz NULL,
state_change timestamptz NULL,
wait_event_type text NULL,
wait_event text NULL,
state text NULL,
backend_xid xid NULL,
backend_xmin xid NULL,
query_id int8 NULL,
query text null,
query_start timestamptz NULL,
duration numeric NULL
) PARTITION BY RANGE (sample_time);
CREATE INDEX pg_stat_act_h_idx_stime ON adm.pg_stat_activity_history USING btree (sample_time);
INSERT INTO cron.job (schedule,command,nodename,nodeport,"database",username,active,jobname) VALUES
('20 0 * * *'
,'select adm.create_day_partition(''pg_stat_activity_history'', ''adm''); commit ;'
,'',5432
,'postgres','postgres',true,'part_awr_hist');
INSERT INTO cron.job (schedule,command,nodename,nodeport,"database",username,active,jobname) VALUES
('30 0 * * *'
,'select adm.drop_old_day_partition(''pg_stat_activity_history'', ''adm'', 14); commit ;'
,'',5432
,'postgres','postgres',true,'del_part_awr_hist');
select adm.create_day_partition('pg_stat_activity_history', 'adm');
CREATE OR REPLACE FUNCTION adm.pg_stat_activity_snapshot()
RETURNS void AS
$$
DECLARE
start_ts timestamp := (select clock_timestamp());
ldiff numeric := 0;
BEGIN
WHILE ldiff < 60
LOOP
insert
into adm.pg_stat_activity_history
select
clock_timestamp() as sample_time,
datid ,
pid ,
leader_pid ,
usesysid ,
application_name ,
client_addr inet ,
client_hostname ,
client_port ,
backend_start ,
xact_start ,
state_change ,
wait_event_type ,
wait_event ,
state ,
backend_xid ,
backend_xmin ,
query_id ,
query ,
query_start ,
1000 * extract(EPOCH from (clock_timestamp()-query_start)) as duration -- milliseconds (1/1000sec)
from pg_stat_activity
where state <> 'idle' and usename <> 'replicator'
and pid != pg_backend_pid();
perform pg_stat_clear_snapshot();
perform pg_sleep(10);
ldiff := EXTRACT (EPOCH FROM (clock_timestamp() - start_ts));
END LOOP;
END $$ LANGUAGE plpgsql;
INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username) values
('* * * * *', 'SELECT adm.pg_stat_activity_snapshot();', '', 5432, 'postgres', 'postgres');
COMMIT; Не забывайте, что pg_cron запускает процесс локально и ему нужно авторизоваться, то есть требуется разрешение на trust connect в pg_hba. Итак, история собирается, партиции автоматически добавляются и удаляются. В принципе, это уже немало: табличная история наиболее полезна, когда вы будете раскапывать и анализировать конкретный проблемный момент. Но чтобы легко находить такие моменты, нам и нужна Grafana.
Весь экспериментальный путь настройки дашборда выкладывать смысла нет, мой финальный вариант выглядит так:
Каждый график отображает количество сессий с соответствующим типом ожидания, при этом они нарастающие. Точка отсчета каждого графика не 0, а значение предыдущего, то есть в пике мы получаем суммарное количество активных сессий.
Колонка avg_activity в нижней таблице — это среднее количество активных сессий с конкретным запросом в выбранный промежуток времени. Чтобы объяснить, как рассчитывается значение, приведу пример: если у вас была одна активная сессия продолжительностью полчаса, то при выбранном промежутке в час avg_activity будет 0,5; или если у вас было 10 активных сессий в течение 6 минут в диапазоне одного часа, то avg_activity будет 1.
Просадки и всплески сразу хорошо видны. Правда, из графика мы видим только сами запросы, их время и их группы ожиданий. А если нам нужны подробности кто и откуда, то идем уже непосредственно в БД, в pg_stat_activity_history, благо нужные данные у нас уже есть.
Оптимизируем запросы, исправим долгие транзакции, и снова у нас «штиль». Ну, почти ?:
В верхнем выпадающем списке можно фильтровать активные сессии по отдельным wait types, внизу данные по SQL-запросам по этим ожиданиям фильтруются сразу по выбранным типам ожиданий:
Примечание: когда вы фильтруете, выбирая конкретные цвета линий — «типов ожиданий» — в списке справа от графиков, то данные по SQL-запросам снизу не фильтруются. Это Grafana так работает, не перезапрашивает список SQL.
Ну и главное: файл для импорта дашборда можно взять здесь (там и все приведенные выше скрипты).
Не забудьте добавить в Grafana в качестве datasource свою Postgres-БД с таблицей pg_stat_activity_history и указать его при импорте дашборда.
Вот и всё. Теперь для анализа проблем не сложно окинуть взглядом «сверху» активности в вашей БД в Grafana и раскопать подробности в конкретный момент времени в таблице истории сессий pg_stat_activity_history.
Успешного вам troubleshooting!