Привет! Меня зовут Геннадий, я 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!