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, но так есть небольшая вероятность очистить данные которые могут понадобиться.

Например, самый простой вариант раз в неделю чистить старые данные. Оставляем данные в таблице за 7 последних суток (эти запросы нужно поместить в pg_cron или системный cron):

delete from pg_stat_activity_history where sample_time < current_timestamp - '7 days'::interval;
reindex index pg_stat_activity_history_idx;
vacuum full pg_stat_activity_history;

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

select count(*), wait_event_type, wait_event, round((count(*)::numeric/(select count(*) from pg_stat_activity_history)::numeric)*100, 2) as percent from pg_stat_activity_history group by wait_event_type, wait_event order by count(*) desc;

Пример результата:

 count  | wait_event_type |     wait_event     | percent
--------+-----------------+--------------------+---------
 325338 |                 |                    |   90.35
  28617 | LWLockNamed     | WALWriteLock       |    7.95
   3102 | Lock            | transactionid      |    0.86
   1835 | Lock            | tuple              |    0.51
    726 | LWLockTranche   | buffer_io          |    0.20
    314 | LWLockTranche   | lock_manager       |    0.09
    104 | LWLockNamed     | ProcArrayLock      |    0.03
     49 | LWLockTranche   | pg_stat_statements |    0.01
     10 | LWLockTranche   | buffer_mapping     |    0.00
      2 | LWLockTranche   | clog               |    0.00
      2 | LWLockTranche   | buffer_content     |    0.00
      2 | Lock            | relation           |    0.00
      1 | LWLockNamed     | CLogControlLock    |    0.00
(13 rows)

Получение списка запросов выполнявшихся более 10 секунд за указанный промежуток времени (с указанием бд, времени выполнения, времени начала выполнения запроса, событиях ожиданий):

select datname, query, query_start, wait_event_type, wait_event, max(duration) from pg_stat_activity_history
where query_start between '2019-04-23 10:00:00' and '2019-04-24 10:00:00'
group by datname, query, query_start, wait_event_type, wait_event
having max(duration) > 10000 order by max(duration) desc;
0

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

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