Анализ статистики для мониторинга PostgreSQL

Введение

Для эффективной работы системы важно отслеживать основные показатели производительности её базы данных. В PostgreSQL существует готовая подсистема, которая осуществляет сбор информации о работе сервера БД, а также предоставляет к ней доступ в виде готовых представлений и функций. Регулярный анализ собранной статистики может помочь администратору и разработчику найти наиболее уязвимые места при ежедневном эксплуатировании базы данных. В этой статье я расскажу об основных статистиках, которые накапливаются в PostgreSQL, в качестве примеров я приведу SQL-запросы на выборку основных показателей работы базы данных, а также затрону встроенные инструменты визуализации статистики в современных GUI для PostgreSQL.

Базовые статистики для анализа работы БД

В PostgreSQL сборщик статистики предоставляет доступ к накопленным данным через предопределённые представления. Основные из них показаны ниже на схеме:

• pg_stat_activity – предоставление информации о текущей активности серверного процесса, включая его состояние и текущий запрос;
• pg_stat_database – представление статистических данных по каждой базе данных на сервере;
• pg_stat_all_indexes – представление статистических данных по каждому индексу;
• pg_stat_all_tables – представление статистических данных по каждой таблице;
• pg_stat_wal – представление статистических данных о работе журнала предзаписи (Write-Ahead Logging) в кластере;
• pg_stat_user_functions – представление статистических данных о выполнении функций.

На основе данных из представлений пользователь может анализировать полезные показатели, характеризующие работу базы данных.

Примеры запросов для анализа основных показателей БД

Нагрузка на базу данных

Для понимания нагрузки на базу данных хорошо знать общий объём транзакций за определенный период времени. Для получения этих данных можно использовать следующий запрос:

SELECT 
   datname, 
   xact_commit + xact_rollback , 
   stats_reset
FROM pg_stat_database;

Здесь сумма xact_commit и количество xact_rollback – суммарное количество транзакций за период с момента сброса статистических данных stats_reset.

Распределение серверных процессов по состояниям

Для мониторинга клиентских подключений можно использовать представление pg_stat_activity, которое отображает информацию по работе серверных процессов. Каждый серверный процесс может находиться в следующих состояниях:

• active - выполнение запроса;
• idle - ожидание новой команды от клиента;
• idle in transaction - серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос;
• idle in transaction (aborted) - серверный процесс находится внутри транзакции, но один из операторов в транзакции вызывал ошибку;
• fastpath function call - выполнение fast-path функции;
• disabled – у серверного процесса отключён параметр track_activities. 

Получить общее количество соединений по состояниям позволяет следующая группировка:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

Длительность текущих активных транзакций и запросов

Длительность текущих активных транзакций и запросов можно проанализировать, выполнив запрос:

SELECT 
   datname, 
   usename, 
   now() - xact_start AS TransactionDuration,
   now() - query_start as QueryDuration
FROM pg_stat_activity
WHERE state = 'active';

Определение наиболее нагруженных таблиц

Статистику обращений к таблицам базы данных предоставляет pg_stat_all_tables. Представление позволяет оценить, например, общий объём insert, update, delate операций к таблице. Определить наиболее часто используемые таблицы в БД можно с помощью запроса:

SELECT 
   relname, 
   n_tup_upd+n_tup_ins+n_tup_del AS operationsAmount
FROM pg_stat_all_tables
ORDER BY operationsAmount DESC;

Отношение сканирований по индексам к последовательным сканированиям

Для анализа эффективности чтения данных в конкретной таблице можно получить соотношение запросов, выполненных с использованием индексов к количеству запросов, читающих данные путём последовательного сканирования таблиц. Отсортированный список таблиц по данному соотношению вернёт следующий запрос:

SELECT 
   relname, 
   seq_scan, 
   idx_scan, 
   idx_scan/seq_scan as IndexStat
FROM pg_stat_all_tables
WHERE seq_scan <> 0
ORDER BY IndexStat DESC;

Отслеживание устаревших индексов

Полную информацию по созданным в базе данных индексам содержит представление pg_stat_all_indexes. Устаревшие индексы можно обнаружить с помощью запроса:

SELECT 
   indexrelname, 
   relname, 
   idx_tup_read/idx_tup_fetch as stats
FROM pg_stat_all_indexes
WHERE idx_tup_fetch <> 0
ORDER BY stats DESC;

Здесь idx_tup_read/idx_tup_fetch – это отношение записей из индекса, возвращённых в запросах по этому индексу, к общему числу записей, для которых пришлось обращаться к родительским таблицам. Если этот коэффициент меньше единицы, значит много данных читается в обход индекса, поэтому его необходимо обновить.

Мониторинг в GUI-клиентах

Для администраторов баз данных и программистам важно быстро определять производительность СУБД, анализировать проблемы с дисковым пространством, контролировать количество подключений и т.д. Поэтому современные GUI-клиенты предлагают собственные панели мониторинга для СУБД. Наиболее развитые системы мониторинга существуют в следующих графических приложениях:

В DBeaver используется функция Dashboard, с помощью которой можно настроить пользовательские панели из соответствующих виджетов для мониторинга. По умолчанию в DBeaver поставляются виджеты для нескольких предопределенных информационных панелей мониторинга. Создать информационную панель можно из набора готовых шаблонов виджетов:

• Server sessions - показывает активные/неактивные сеансы сервера;
• Transactions per second – отображает количество транзакций в секунду;
• Block IO – отображает количество блокировок при операциях ввода/вывода в секунду.

Предопределённые виджеты изменять в DBeaver нельзя, однако можно создать новый виджет путём копирования шаблона. В диалоговом окне можно настроить новые параметры шаблона и реализовать свой запрос для получения данных виджета:

Аналогичные шаблоны также реализованы и в клиенте pgAdmin, однако дополнительно в нём присутствуют следующие виджеты:

• Tuples in – отображает количество записей, вставленных, обновленных и удаленных на сервере или в базе данных;
• Tupels out – отображает количество записей, полученных и возвращенных с сервера или базы данных.

Также в pgAdmin на вкладке "Статистика" отображается готовая сводная статистика для выбранного элемента из дерева БД.

• PID – ИД серверного процесса;
• User – имя пользователя;
• Database – название базы данных;
• Backends – число текущих клиентских подключений к БД;
• Backend start – время старта серверного процесса;
• Xact Committe

Для пользователя доступны для отслеживания следующие показатели:d – число зафиксированных транзакций за последнюю неделю;

• Xact Rolled Back – число отменённых транзакций за последнюю неделю;
• Blocks Read – число блоков, считанных с диска за последнюю неделю;
• Blocks Hit – число блоков, считанных из оперативной памяти за последнюю неделю;
• Tuples Returned – количество, возвращённых записей за последнюю неделю;
• Tuples Fetched – количество записей, выбранных за последнюю неделю;
• Tuples Inserted – количество вставленных записей в базе данных за последнюю неделю;
• Tuples Updated – количество обновлённых записей в базе данных за последнюю неделю;
• Tuples Deleted – количество удалённых записей в базе данных за последнюю неделю;
• Last statistics – дата и время последнего сброса накопленных статистик в базе данных.

Заключение

В PostgreSQL сборщик статистики предоставляет полноценную информацию о работе сервера, которую можно регулярно анализировать. Также дополнительно включив модуль pg_stat_statements, СУБД будет отслеживать статистику планирования и выполнения сервером всех операторов SQL. Отдельно стоит выделить базовые возможности GUI-клиентов по мониторингу сервера базы данных. Используя базовые представления статистики PostgreSQL, они предоставляют пользователю готовую информацию в виде дашбордов, что позволяет динамически отслеживать работу сервера и выявлять ошибки.