PGHero — дашборд для мониторинга БД PostgeSQL

Всем привет. Сегодня я бы хотел поделиться рецептом установки утилиты PGHero с подключением нескольких баз данных. PGHero — это простенькая утилита, написанная на Ruby, с минималистичным дашбордом для мониторинга производительности БД PostgreSQL.

Что может показать нам PGHero:

• статистику по запросам: количество вызовов, среднее и суммарное время выполнения (с возможностью хранения истории);

• активные в данный момент запросы;

• информацию о таблицах: занимаемое на диске место, даты последних запусков VACUUM и ANALYSE;

• информацию об индексах: занимаемое на диске место, наличие дублируемых/неиспользуемых индексов. Также может порекомендовать добавить индекс при наличии сложных запросов с Seq Scan;

• статистику по открытым подключениям к БД;

• вывод основных настроек БД, влияющих на производительность (shared_buffers, work_mem, maintenance_work_mem и т.д.)

Одна из очень удобных возможностей утилиты — просмотр динамики среднего времени выполнения запросов (на основе статистики стандартного расширения PostgreSQL — pg_stat_statements).

Выглядит это в интерфейсе PGHero вот так:

Настройка баз данных

Следующие шаги нужно проделать для каждой БД, которую мы собираемся подключать к PGHero. 

Запросы нужно выполнять под суперпользователем.

1. Устанавливаем расширение pg_stat_statements (если еще не установлено):

Откройте файл postgresql.conf в текстовом редакторе и измените строку shared_preload_libraries:

shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track_utility = false

Перезапускаем сервер PostgreSQL:

sudo service postgresql restart

Создаем расширение и сбрасываем статистику:

create extension pg_stat_statements;  select pg_stat_statements_reset();

2. Создаем в БД отдельного пользователя для PGHero (чтобы не давать утилите полные права над базой). 

В следующем запросе заменяем эти значения в угловых скобках на свои:

<pghero_password> — пароль для пользователя pghero;

<db_name> — имя вашей БД;

<migrations_user> — имя основной роли с доступом к текущей БД.

CREATE SCHEMA pghero;  -- view queries CREATE OR REPLACE FUNCTION pghero.pg_stat_activity() RETURNS SETOF pg_stat_activity AS $$   SELECT * FROM pg_catalog.pg_stat_activity; $$ LANGUAGE sql VOLATILE SECURITY DEFINER;  CREATE VIEW pghero.pg_stat_activity AS SELECT * FROM pghero.pg_stat_activity();  -- kill queries CREATE OR REPLACE FUNCTION pghero.pg_terminate_backend(pid int) RETURNS boolean AS $$   SELECT * FROM pg_catalog.pg_terminate_backend(pid); $$ LANGUAGE sql VOLATILE SECURITY DEFINER;  -- query stats CREATE OR REPLACE FUNCTION pghero.pg_stat_statements() RETURNS SETOF pg_stat_statements AS $$   SELECT * FROM public.pg_stat_statements; $$ LANGUAGE sql VOLATILE SECURITY DEFINER;  CREATE VIEW pghero.pg_stat_statements AS SELECT * FROM pghero.pg_stat_statements();  -- query stats reset CREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset() RETURNS void AS $$   SELECT public.pg_stat_statements_reset(); $$ LANGUAGE sql VOLATILE SECURITY DEFINER;  -- improved query stats reset for Postgres 12+ - delete for earlier versions CREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset(userid oid, dbid oid, queryid bigint) RETURNS void AS $$   SELECT public.pg_stat_statements_reset(userid, dbid, queryid); $$ LANGUAGE sql VOLATILE SECURITY DEFINER;  -- suggested indexes CREATE OR REPLACE FUNCTION pghero.pg_stats() RETURNS TABLE(schemaname name, tablename name, attname name, null_frac real, avg_width integer, n_distinct real) AS $$   SELECT schemaname, tablename, attname, null_frac, avg_width, n_distinct FROM pg_catalog.pg_stats; $$ LANGUAGE sql VOLATILE SECURITY DEFINER;  CREATE VIEW pghero.pg_stats AS SELECT * FROM pghero.pg_stats();  -- create user CREATE ROLE pghero WITH LOGIN ENCRYPTED PASSWORD '<pghero_password>'; GRANT CONNECT ON DATABASE <db_name> TO pghero; ALTER ROLE pghero SET search_path = pghero, pg_catalog, public; GRANT USAGE ON SCHEMA pghero TO pghero; GRANT SELECT ON ALL TABLES IN SCHEMA pghero TO pghero;  -- grant permissions for current sequences GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO pghero;  -- grant permissions for future sequences ALTER DEFAULT PRIVILEGES FOR ROLE <migrations_user> IN SCHEMA public GRANT SELECT ON SEQUENCES TO pghero;

Установка и запуск PGHero

Допустим, у нас есть три таблицы: db_one, db_two и db_three. Мы хотим по всем трем отображать статистику в PGHero (вместе с историей запросов и размеров таблиц). Важный момент: для хранения истории запросов и размеров таблиц нужно завести в одной из баз данных отдельные таблицы, где будет храниться эта статистика.

CREATE TABLE "pghero_query_stats" (   "id" bigserial primary key,   "database" text,   "user" text,   "query" text,   "query_hash" bigint,   "total_time" float,   "calls" bigint,   "captured_at" timestamp ); CREATE INDEX ON "pghero_query_stats" ("database", "captured_at");  CREATE TABLE "pghero_space_stats" (   "id" bigserial primary key,   "database" text,   "schema" text,   "relation" text,   "size" bigint,   "captured_at" timestamp ); CREATE INDEX ON "pghero_space_stats" ("database", "captured_at");

Мы будем хранить эти таблицы в БД db_one (хотя можно завести отдельную базу для этой статистики). Далее создаем на сервере файл конфигурации pghero.yml со следующим содержимым (подставляем актуальные настройки):

# Конфигурационные урлы для наших БД databases:   db_one:     url: postgres://pghero:secret_pass@mydomain.ru:53001/db_one   db_two:     url: postgres://pghero:secret_pass@mydomain.ru:53001/db_two     capture_query_stats: db_one   db_three:     url: postgres://pghero:secret_pass@mydomain.ru:53001/db_three     capture_query_stats: db_one  # Минимальная длительность запросов (в секундах), которые будут считаться долгими long_running_query_sec: 60  # Минимальная длительность запросов (в миллисекундах), которые будут считаться медленными slow_query_ms: 250  # Минимальное кол-во вызовов запросов, которые будут считаться медленными slow_query_calls: 100  # Минимальное количество соединений для показа предупреждения total_connections_threshold: 100  # Таймаут для explain-запросов explain_timeout_sec: 10  # Нормализация запросов (замена значений запроса нумерованными параметрами) filter_data: true  # Basic авторизация username: pghero password: secret_pass  # Таймзона time_zone: "Europe/Moscow"

Переходим к установке. Документация предлагает нам несколько способов:

1. Docker-контейнер;

2. отдельная служба на Linux;

3. gem-пакет Ruby;

Мы будем использовать первый способ — запуск в виде Docker-контейнера. Для этого в папке с файлом конфигурации pghero.yml нужно добавить Docker-файл с таким содержимым:

docker build -t mypghero . docker run -ti -p 12345:8080 mypghero

Теперь собираем образ на основе Docker-файла и запускаем контейнер на нужном порту:

docker build -t mypghero . docker run -ti -p 12345:8080 mypghero

Теперь дашборд должен быть доступен по адресу http://123.45.67.89/12345. Не забывайте про basic-авторизацию, логин и пароль мы указывали в pghero.yml.

Запуск cron-jobs для сохранения истории

Последний этап: нужно настроить автозапуск по крону скриптов для сохранения в БД истории по запросам (capture_query_stats) и размерам таблиц (capture_space_stats).

Документация рекомендует запускать capture_query_stats раз в 5 минут, а capture_space_stats — раз в сутки (но тут нужно решать по ситуации). Запускаем в командной строке crontab -e и добавляем строки для запуска скриптов:

*/5 * * * *     /usr/bin/docker run --rm my-pghero bin/rake pghero:capture_query_stats 15 2 * * *     /usr/bin/docker run --rm my-pghero bin/rake pghero:capture_space_stats

Вот и всё. Спасибо за внимание.

Демо-версию утилиты можно посмотреть здесь. Исходный код и документация.