Шаг 7.2. Настройка PostgreSQL
Предоставление доступа
Для корректного сбора статистики для Алькир требуется создать на серверах СУБД отдельного пользователя с правами на чтение статистики. Пример команд для создания такого пользователя ниже (используется имя пользователя 'alkir'):
CREATE ROLE alkir WITH LOGIN ENCRYPTED PASSWORD '<секьюрный_пароль>';
GRANT pg_read_all_stats TO alkir;
Установка дополнительных пакетов
Необходимо на всех серверах PostgreSQL в зависимости от используемого дистрибутива ОС, а также дистрибутива СУБД, выполнить установку дополнительных пакетов plperl и contrib для PostgreSQL (приведён пример для 14й версии PostgreSQL - при необходимости замените '14' на свой вариант):
sudo yum install postgresql14-plperl postgresql14-contrib
sudo apt install postgresql-plperl-14 postgresql-contrib
sudo apt install postgrespro-1c-14-plperl postgrespro-1c-14-contrib
Дополнительные настройки
Необходимо установить расширения pg_wait_sampling и pg_stat_kcache. Способ установки зависит от версии PostgreSQL, с инструкциями по утсановке можно ознакомится по ссылкам: pg_wait_sampling, pg_stat_kcache. Активируем расширения: в конфиг postgresql.conf на сервере PostgreSQL в параметр shared_preload_libraries нужно добавить 'pg_stat_statements, pg_wait_sampling, auto_explain, pg_stat_kcache' (создать такой параметр, если его ещё нет). Если в нём уже есть значения, добавить к прочим значениям в нём pg_stat_statements, pg_wait_sampling, auto_explain, pg_stat_kcache через запятую. Дополнительно в конфиг файл postgresql.conf добавить настройки:
logging_collector='True'
og_directory = '/var/log/postgresql/' # каталог логов
log_destination='csvlog' # формат логов csv
log_filename = 'postgresql-%a_%H' # формат имени файла записи логов - %a сокращенное название дня недели, %H - час
log_min_duration_statement=100 # минимальная длительность, для которых в логи записывается план запроса в миллисекундах
log_rotation_age='1h' # время хранения логов
log_rotation_size=0 # отключен максимальный размер файла лога, чтобы postgres не генерировал новый файл логов в час
log_truncate_on_rotation='on' # необходимо чтобы перезаписывал файл логов, а не создавал новые
log_lock_waits='on' # Определяет, нужно ли фиксировать в журнале события, когда сеанс ожидает получения блокировки дольше, чем указано в deadlock_timeout
auto_explain.log_min_duration='5s' # минимальное время выполнения запроса для записи логов
auto_explain.log_analyze = 'on' # в протокол будет записываться вывод команды EXPLAIN ANALYZE, а не простой EXPLAIN
auto_explain.log_timing='off' # определяет, печатается ли информация о времени для каждого узла при регистрации плана выполнения, необходимо тестирование т.к. согласно документации: накладные расходы на многократное считывание системных часов могут значительно замедлять запросы в некоторых системах
pg_wait_sampling.profile_pid = 'false' # отключаем сбор данных в разрезе пид
pg_wait_sampling.profile_queries = 'false' # отключаем сбор данных в разрезе queryid
pg_stat_statements.max = 10000 # увеличиваем объем хранимых запросов
Для применения внесённых изменений потребуется перезапуск сервера PostgreSQL.
После перезапуска требуется активировать расширения 'pg_stat_statements' и 'plperlu' для каждой из баз, за которыми планируется наблюдение - в консоли Postgres от имени суперюзера или владельца базы нужно выполнить:
\с {имя_базы}
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pg_wait_sampling;
CREATE EXTENSION auto_explain;
CREATE EXTENSION pg_stat_kcache;
CREATE EXTENSION plperlu;
CREATE OR REPLACE FUNCTION get_pid_cpu_mem(int) returns table(pid INT,cpu_perc float,mem_perc float)
as
$$
my $ps = "ps aux";
my $awk = "awk '{if (\$2==".$_[0]."){print \$2\":\"\$3\":\"\$4}}'";
my $cmd = $ps."|".$awk;
$output = `$cmd 2>&1`;
@output = split(/[\n\r]+/,$output);
foreach $out (@output)
{
my @line = split(/:/,$out);
return_next{'pid' => $line[0],'cpu_perc' => $line[1], 'mem_perc' => $line[2]};
return undef;
}
return;
$$ language plperlu;
Это активирует расширения и создаст функцию для сбора статистики использования системных ресурсов.