pg_stat_activity хранение истории запросов PostgreSQL

View pg_stat_activity позволяет посмотреть текущие запросы в PostgreSQL. Настроим автоматический сбор и хранение этой информации.

https://habr.com/ru/post/413411/
https://github.com/dbacvetkov/PASH-Viewer/wiki/How-to-create-pg_stat_activity-historical-table

Создаем таблицу pg_stat_activity_history для исторических данных:

--create table for historical data PG10+
create table pg_stat_activity_history as 
SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, backend_type, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration
from pg_stat_activity where 1=2;

--create table for historical data PG9.6
create table pg_stat_activity_history as 
SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration
from pg_stat_activity where 1=2;

create index pg_stat_activity_history_idx on pg_stat_activity_history(sample_time);

Создаем функцию pg_stat_activity_snapshot() для сбора данных каждую секунду в течение 1 минуты:

--create function, which will take 60 snapshots every 1 minute PG10+
CREATE OR REPLACE FUNCTION pg_stat_activity_snapshot()
RETURNS void AS
$$
DECLARE
start_ts timestamp := (select clock_timestamp());
ldiff numeric := 0;
BEGIN
	WHILE ldiff < 60
	LOOP
		insert into pg_stat_activity_history SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, backend_type, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration from pg_stat_activity where state='active' and pid != pg_backend_pid();
		perform pg_stat_clear_snapshot();
		perform pg_sleep(1);
		ldiff := EXTRACT (EPOCH FROM (clock_timestamp() - start_ts));
	END LOOP;
END
$$ LANGUAGE plpgsql;

--create function, which will take 60 snapshots every 1 minute PG9.6
CREATE OR REPLACE FUNCTION pg_stat_activity_snapshot()
RETURNS void AS
$$
DECLARE
start_ts timestamp := (select clock_timestamp());
ldiff numeric := 0;
BEGIN
	WHILE ldiff < 60
	LOOP
		insert into pg_stat_activity_history SELECT clock_timestamp() as sample_time, datname, pid, usesysid, usename, application_name, client_hostname, client_addr, wait_event_type, wait_event, query, query_start, 1000 * EXTRACT(EPOCH FROM (clock_timestamp()-query_start)) as duration from pg_stat_activity where state='active' and pid != pg_backend_pid();
		perform pg_stat_clear_snapshot();
		perform pg_sleep(1);
		ldiff := EXTRACT (EPOCH FROM (clock_timestamp() - start_ts));
	END LOOP;
END
$$ LANGUAGE plpgsql;

Т.к. на получение и запись информации в функции pg_stat_activity_snapshot() уходит некоторое время, а cron запускает задание каждую минуту, предыдущее выполнение функции может запоздать (для этого идет проверка в задании cron выполняется ли еще предыдущая итерации функции) и в итоге cron не запустит следующую итерацию и чуть меньше минуты будет простой.

Для того чтобы это исключить, можно выставить ldiff не < 60, а <59. Так же стоит подумать имеет ли смысл собрать данные каждую секунду, возможно достаточно каждые, например, 3 (< 57) или 5 (< 55) секунд. Тогда надо поменять число меньше которого ldiff и количество секунд в pg_sleep().

Параметры зависят от нагрузки на конкретную систему. Подобрать можно отследив выполнение функции. Так же можно в команду вызова в cron добавить логирование отмен запусков в файл с указанием даты и времени.

Создаем задание в системном cron с проверкой — если задание уже запущено — не запускать новое. Это на случай если предыдущая итерация функции еще не успела завершить свою работу, или кто-то запустил ее руками:

#store pg_stat_activity from view to table with check than task is not running now
* * * * * if ps -ef | grep -v grep | grep pg_stat_activity_snapshot; then echo "runing"; else /usr/bin/psql -d postgres -c  "select pg_stat_activity_snapshot();"; fi > /dev/null

Реализация очистки данных из таблицы может быть разная. Можно руками, можно автоматизировать. Можно выполнять удаление,можно truncate. Можно использовать pg_pathman, но это слишком заморочено. Быстрее всего truncate, но так есть небольшая вероятность очистить данные которые могут понадобиться.

0

Добавить комментарий

Ваш e-mail не будет опубликован.