pgsentinel настройка (хранение истории активных сессий PostgreSQL)

Компиляция и добавление pgsentinel в PostgreSQL

Расширение pgsentinel предоставляет возможность собирать и просматривать историю активных сессий в PostgreSQL.

https://habr.com/ru/post/416909/
https://github.com/pgsentinel/pgsentinel

Для успешной компиляции библиотеки, необходимо чтобы на сервере был установлен пакет postgresql11-devel (для каждой версии PostgreSQL естественно будет свой пакет). Так же потребуются пакеты gcc, пакет openssl-devel (начиная с 10-й версии PostgreSQL) и пакеты llvm и clang (начиная с 11-й версии PostgreSQL). О том, каких пакетов не хватает в конкретном случае, можно узнать из сообщений об ошибках при компиляции.

Создадим директорию под папки с версиями pgsentinel:

mkdir /var/lib/pgsql/11/ext

Далее копируем туда папку с релизами pgsentinel (с любой доступной версией,на данный момент это beta, релиза нет, копируем репозиторий. В репозитории нас интересует диреткория src).
Заходим в директорию pgsentinel и выполняем там команду (для PG_CONFIG указать путь к нужной версии PostgreSQL):

make PG_CONFIG=/usr/pgsql-11/bin/pg_config USE_PGXS=1 install

Если все необходимые пакеты в системе есть, то компиляция пройдет успешно, в директории релиза появятся все нужные файлы (pgsentinel.so и sql-файлы + pgsentinel.control) и они будут автоматически скопированы в директории /usr/pgsql-11/lib/ и /usr/pgsql-11/share/extension/. Если файлов там все же нет, сами копируем их руками в указанные директории.
Для получения полной информации, нам так же требуется установка extension pg_stat_statements (входит в пакет contrib).
Редактируем конфиг postgresql.conf:

# добавляем расширения
shared_preload_libraries = 'pg_stat_statements,pgsentinel'
# Изменяем максимальный размер логируемого запроса (необязательно)
track_activity_query_size = 2048
# Указываем pg_stat_statements что надо записывать все запросы
pg_stat_statements.track = all
# количество удерживаемых pgsentinel в памяти последних записей (тут зависит от количества запросов в бд и наличия свободной ram)
pgsentinel_ash.max_entries = 10000

Перезагружаем PostgreSQL и в бд postgres создаем оба расширения:

sudo systemctl restart postgresql-11
create extension pg_stat_statements;
create extension pgsentinel;

Настройка pgsentinel

После создания расширения, pgsentinel предоставляет собранные данные в виде view pg_active_session_history, данные хранит в оперативной памяти и запоминает лишь количество записей указанных в параметре pgsentinel_ash.max_entries (по умолчанию 1000).
Для для постоянного хранения данных создаем таблицу pg_active_session_history_store:

CREATE TABLE public.pg_active_session_history_store (
	ash_time timestamp with time zone not null, 
	datid oid,
	datname text,
	pid integer,                  
	usesysid oid,
	usename text,
	application_name text,
	client_addr text,
	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,
	top_level_query text,
	query text,
	cmdtype text,
	queryid bigint,
	backend_type text,
	blockers integer,
	blockerpid integer,
	blocker_state text
);

CREATE INDEX pg_active_session_history_store_idx ON pg_active_session_history_store USING btree (ash_time);

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

CREATE OR REPLACE FUNCTION public.pg_active_session_history_store_data()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
ash_time_max timestamp with time zone DEFAULT NULL;
BEGIN
	ash_time_max := (select max(ash_time) as ash_time_max from pg_active_session_history_store);
	
	IF ash_time_max IS NULL THEN
		insert into pg_active_session_history_store
		select * from pg_active_session_history;
	ELSE
		insert into pg_active_session_history_store
		select * from pg_active_session_history
		where pg_active_session_history.ash_time > ash_time_max;
	END IF;	
END;
$function$
;

Функция при пустой таблице pg_active_session_history_store запишет в нее все данные из представления pg_active_session_history, если же данные в таблице уже есть, то допишет в нее свежие из view.
Создаем задание в cron (лучше использовать расширение pg_cron) для вызова функции сохранения истории каждую минуту:

#store pgsentinel data from view to table
* * * * * /usr/bin/psql -d postgres -c  "select pg_active_session_history_store_data();" > /dev/null

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

--pgsentinel selects

--Get the average number of Active Sessions:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(count(*)/samples,2) as "AAS"
 from ash
 group by samples;
 
------
--Get the average CPU usage:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(count(*)/samples,2) as "AAS",wait_event_type
 from ash
 where wait_event_type='CPU'
 group by samples, wait_event_type
 order by 1 desc;

------
--Get the top N applications:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(100 * count(*)/sum(count(*)) over(), 0) as "%", round(count(*)/samples,2) as "AAS", application_name
 from ash
 group by application_name, samples
 order by 1 desc fetch first 10 rows only;

------
--Get the top N client addresses per database:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(100 * count(*)/sum(count(*)) over(), 0) as "%", round(count(*)/samples,2) as "AAS", datname, client_addr
 from ash
 group by datname,client_addr,samples
 order by 1 desc fetch first 10 rows only;
 
------
--Get the Average number of Active Sessions per database:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(100 * count(*)/sum(count(*)) over(), 0) as "%", round(count(*)/samples,2) as "AAS", datname
 from ash
 group by datname,samples
 order by 1 desc;

------
--Get the wait event type reparation per database:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(100 * count(*)/sum(count(*)) over(), 0) as "%", round(count(*)/samples,2) as "AAS", datname, wait_event_type
 from ash
 group by samples,datname,wait_event_type
 order by 1 desc;
 
------
--Get the wait event reparation per database:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(100 * count(*)/sum(count(*)) over(), 0) as "%", round(count(*)/samples,2) as "AAS", datname, wait_event_type, wait_event
 from ash
 group by samples,datname,wait_event_type,wait_event
 order by 1 desc;

------
--Get the top N queryid on CPU:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(100 * count(*)/sum(count(*)) over(), 0) as "%", round(count(*)/samples,2) as "AAS", backend_type, queryid, pg_stat_statements.query
 from ash left outer join pg_stat_statements using(queryid)
 where wait_event='CPU'
 group by samples,queryid,pg_stat_statements.query,backend_type
 order by 1 desc fetch first 10 rows only;

------
--Get the top N queryid waiting on IO:

with ash as (
	select *, ceil(extract(epoch from max(ash_time)over()-min(ash_time)over()))::numeric samples
	from pg_active_session_history_store where ash_time>=current_timestamp - interval '10 minutes'
) select round(100 * count(*)/sum(count(*)) over(), 0) as "%", round(count(*)/samples,2) as "AAS", backend_type, queryid, wait_event_type
 from ash
 where wait_event_type='IO'
 group by backend_type,queryid,samples,wait_event_type
 order by 1 desc fetch first 10 rows only;
 
------
--Get the wait chain of blocking session (if any):

WITH RECURSIVE search_wait_chain(ash_time, pid, blockerpid, wait_event_type, wait_event, level, path)
AS (
	SELECT ash_time, pid, blockerpid, wait_event_type, wait_event, 1 AS level,
	'pid:'||pid||' ('||wait_event_type||' : '||wait_event||') ->'||'pid:'||blockerpid as path
	FROM pg_active_session_history_store WHERE blockers > 0
  union ALL
	SELECT p.ash_time, p.pid, p.blockerpid, p.wait_event_type, p.wait_event, swc.level + 1 AS level,
	'pid:'||p.pid||' ('||p.wait_event_type||' : '||p.wait_event||') ->'||swc.path AS path
	FROM pg_active_session_history_store p, search_wait_chain swc
	WHERE p.blockerpid = swc.pid and p.ash_time = swc.ash_time and p.blockers > 0
)
select round(100 * count(*) / cnt)||'%' as "% of total wait", count(*) as seconds, path as wait_chain from (
		SELECT pid, wait_event, path, sum(count) over() as cnt from (
			select ash_time, level, pid, wait_event, path, count(*) as count, max(level) over(partition by ash_time, pid) as max_level
			from search_wait_chain where level > 0 group by ash_time, level, pid, wait_event, path
			) as all_wait_chain
			where level=max_level
) as wait_chain
group by path, cnt
order by count(*) desc;

Эти запросы корректируются под конкретную ситуацию, например, если заменить ash_time>=current_timestamp — interval ’10 minutes’ на конструкцию c between, то можно выставить временной промежуток, информация из которого интересует.
Например, добавим указание временного отрезка, в котором хотим посмотреть наличие блокировок (самый последний запрос), добавив условие ash_time between ‘2019-04-10 09:00:00’ and ‘2019-04-10 11:00:00’:

WITH RECURSIVE search_wait_chain(ash_time, pid, blockerpid, wait_event_type, wait_event, level, path)
AS (
	SELECT ash_time, pid, blockerpid, wait_event_type, wait_event, 1 AS level,
	'pid:'||pid||' ('||wait_event_type||' : '||wait_event||') ->'||'pid:'||blockerpid as path
	FROM pg_active_session_history_store WHERE blockers > 0
  union ALL
	SELECT p.ash_time, p.pid, p.blockerpid, p.wait_event_type, p.wait_event, swc.level + 1 AS level,
	'pid:'||p.pid||' ('||p.wait_event_type||' : '||p.wait_event||') ->'||swc.path AS path
	FROM pg_active_session_history_store p, search_wait_chain swc
	WHERE p.blockerpid = swc.pid and p.ash_time = swc.ash_time and p.blockers > 0
)
select round(100 * count(*) / cnt)||'%' as "% of total wait", count(*) as seconds, path as wait_chain from (
		SELECT pid, wait_event, path, sum(count) over() as cnt from (
			select ash_time, level, pid, wait_event, path, count(*) as count, max(level) over(partition by ash_time, pid) as max_level
			from search_wait_chain where level > 0 group by ash_time, level, pid, wait_event, path
			) as all_wait_chain
			where level=max_level and ash_time between '2019-04-10 09:00:00' and '2019-04-10 11:00:00'
) as wait_chain
group by path, cnt
order by count(*) desc;
1+

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

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