В моем блоге уже был цикл статей с конспектом по MySQL. Эта одна из публикаций цикла конспектов по PostgreSQL. Материал этих публикаций не особо структурирован и представляет собой просто мои заметки – конспектировал что-то новое и то, что я посчитал интересным и важным. Эта публикация посвящена тому, как выполняется мониторинг и диагностика PostgreSQL.
Список литературы
При подготовке этого конспекта я использовал следующие материалы:
1. Книга PostgreSQL 14 Administration Cookbook.
2. Курс DBA1: Администрирование PostgreSQL 13. Базовый курс.
Мониторинг и диагностика PostgreSQL
Проверить – подключен ли пользователь к серверу. Например, подключен ли пользователь “postgres” к базе данных и вывести имя базы данных. Запрос:
SELECT app FROM pg_stat_activity WHERE usename = 'postgres';
Более расширенный запрос на отображение всех активных клиентов:
SELECT datname, usename, client_addr, client_port,
application_name FROM pg_stat_activity
WHERE backend_type = 'client backend';
Посмотреть все активные запущенные запросы:
SELECT datname, usename, state, query
FROM pg_stat_activity
WHERE state = 'active'
AND backend_type = 'client backend';
Самый долгий запрос, который выполняется на текущий момент:
SELECT
current_timestamp - query_start AS runtime,
datname, usename, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY 1 DESC;
Запросы, которые длились более минуты:
SELECT
current_timestamp - query_start AS runtime,
datname, usename, query
FROM pg_stat_activity
WHERE state = 'active'
AND current_timestamp - query_start > '1 min'
ORDER BY 1 DESC;
Посмотреть прогресс создания резервной копии данных (для версии 14+):
SELECT pid, phase,
100.0*((backup_streamed*1.0)/backup_total) AS "progress%"
FROM pg_stat_progress_basebackup;Просмотр активных ожидающих запросов (запросы, которые заблокированы и чего-то ждут):
SELECT datname, usename, wait_event_type, wait_event,
backend_type, query
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL
AND wait_event_type NOT IN ('Activity', 'Client');
Посмотреть кем заблокирован запрос:
SELECT datname, usename, wait_event_type, wait_event, pg_
blocking_pids(pid) AS blocked_by, backend_type, query
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL
AND wait_event_type NOT IN ('Activity', 'Client');Посмотреть блокирующий PID:
pg_blocking_pids(pid)Завершить блокирующий процесс:
pg_cancel_backend(pid)Принудительно завершить блокирующий процесс:
pg_terminate_backend(pid)Просмотр статистики по таблице:
select * from pg_stat_all_tables where relid='t'::regclass \gx
Журнал WAL
Информация по текущим журналам сервера PostgreSQL:
SELECT * FROM pg_ls_waldir() ORDER BY name;
Диагностика медленных запросов
Диагностика медленных запросов – это довольно не тривиальный процесс. По нему можно написать целые книги.
Лучше всего начать с простых шагов – сначала попробовать обновить статистику для проблемных таблиц:
analyze table_name;
Неплохо было бы убедиться, что запрос не возвращает больше данных, чем нужно приложению. Нет смысла возвращать все поля, если приложению нужно какое-то конкретное поле.
Еще один из вариантов – оценить следующие моменты:
- Не мешает ли запросу множество других параллельных запросов?
- Может быть большая нагрузка на сервер? Не является ли ЦП, оперативная память, сеть или дисковая подсистема слабым местом?
Если первый запрос выполняется медленно, но второй и последующие запросы выполняются значительно быстрее, то это свидетельствует о том, что серверу не хватает оперативной памяти. Можно попробовать увеличить параметры work_mem и (или) shared_buffers.
Можно еще попробовать оценить план выполнения запроса на предмет использования индекса или других потенциальных “зацепок”. Стоит учитывать, что планировщик сам определяет оптимальный план выполнения запроса. Даже при наличии индекса он не всегда может быть использован. Например, в запросе ниже последовательных обход таблицы оказывается быстрее и индекс не используется:
EXPLAIN SELECT * FROM t;
EXPLAIN (analyze)
SELECT * FROM t;
EXPLAIN (analyze, buffers, costs off, timing off)
SELECT * FROM t;
pg_stat_statements
Расширение pg_stat_statements позволяет отслеживать статистику выполнения запросов к базе данных. Например, количество запросов, общее время выполнения, количество возвращаемых строк и дополнительную информацию по части использования оперативной памяти и подсистемы ввода-вывода.
Чтобы включить расширение необходимо выполнить следующие шаги:
1. Добавить в конфигурационный файл postgresql.conf строчку:
shared_preload_libraries = 'pg_stat_statements'
2. Перезагрузить сервис.
sudo systemctl restart postgresql3. Расширение включается для каждой базы данных в отдельности. Включить расширение в базе данных можно следующим запросом:
CREATE EXTENSION pg_stat_statements;
Теперь для этой базы данных можно будет выполнять запросы для получения статистики.
Например, запрос для получения 10-ти наиболее частых запросов:
SELECT query FROM pg_stat_statements ORDER BY calls DESC LIMIT
10;
Запросы с наиболее долгим среднем временем выполнения:
SELECT query, total_time/calls AS avg, calls
FROM pg_stat_statements ORDER BY 2 DESC;
Ссылка на документацию по pg_stat_statement.
Эмуляция нагрузки
Для генерирования синтетическо йнагрузки можно использовать утилиту pgbenck:
sudo -u postgres pgbench -i app
Первым шагом создается необходимая серия объектов в базе данных.
Теперь можно запустить тест:
sudo -u postgres pgbench -T 20 app
В параметре -T передается время в секундах, в течении которого необходимо выполнять синтетический тест.
После завершения синтетического тестирования можно посмотреть сводную статистику по базе данных:
SELECT * FROM pg_stat_database WHERE datname = 'app' \gx
Другие связанные статьи
PostgreSQL. Часть 1. Информационные запросы PostgreSQL
PostgreSQL. Часть 2. Нюансы настройки сервера PostgreSQL
PostgreSQL. Часть 3. Управление сервером PostgreSQL
PostgreSQL. Часть 4. Безопасность в PostgreSQL
PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL
PostgreSQL. Часть 7. Производительность и конкурентный доступ PostgreSQL
PostgreSQL. Часть 8. Резервное копирование и восстановление PostgreSQL