Профилирование в PostgreSQL

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

Профилирование – главный метод, с него и нужно начинать оптимизацию. В случае с Postgres отличным инструментом для профилирования запросов станет pgFouine.

Установка

pgFouine достаточно популярна, поэтому часто доступна для установки из пакетов ОС. В случае с Ubuntu это делается так:

sudo apt-get install pgfouine
## Утилита написана на PHP, так что ее можно скомпилировать из исходников

Настройка

Система профилирования работает с двумя типами логов:syslog и stderr. Для конфигурирования pgFouine под syslog прежде проверьте, установлена ли утилита [http://www.syslog.org/ syslogd], и если она отсутствует, то установить.

После этого нужно отредактировать конфигурационный файл Postgres postgresql.conf:

log_destination = 'syslog'

redirect_stderr = off

silent_mode = on

syslog_facility = ‘LOCAL0’

syslog_ident = ‘postgres’

## Указание получателя лога с дополнительными опциями

Дополнительно можно задействовать логирование каждого запроса и запись времени исполнения:

log_min_duration_statement = 0

log_duration = on

log_statement = ‘none’

## Можно записывать только запросы, которые длятся больше определенного времени, нужно его указать в директиве log_min_duration_statement, в миллисекундах

После этого необходимо отредактировать параметры syslogd, чтобы логи записывались в отдельный файл:

LOCAL0.* -/var/log/pgsql/pgsql.log
## Также можно указать путь хранения лога без использования директивы syslog_facility в настройках Postgres

Чтобы новая настройка вступила в силу нужно перезапустить Postgres. После чего в файл /var/log/pgsql/pgsql.log будут записываться запросы с временными характеристиками.

Важно

Мы не рекомендуем профилировать и экспериментировать на рабочей СУБД. Для сбора информации хватит запуска СУБД в режиме логирования на 2-3 часа. Лучше включать в непиковое время, ведь на каждый запрос к БД будут расходоваться дополнительные ресурсы (хоть и небольшие).

Построение отчетов

После сбора необходимой информации и логов лучше скопировать файл с логами на рабочий компьютер, запуск pgFouine на производственном сервере нежелателен. После этого можно запускать pgFouine:

pgfouine -file pgsql.log > report.html
## Указание логфайла и файла отчета

Процедура обработки файла достаточно длительная. После нее будет сгенерирован файл с отчетами:

1. 1. Суммарная статистика запросов и времени выполнения;

1. 1. Самые медленные запросы;

1. 1. Запросы, которые заняли больше всего времени ( важная информация);

1. 1. Наиболее частые запросы;

Система генерирует отчеты на основе паттернов. И для каждого запроса доступны примеры с реальными параметрами (кнопка show examples).

С помощью этой статистики оцениваются запросы, которые чаще всего выполняются и что занимает наибольшее время. Так что можно будет приступить к оптимизации и настройке.

Примеры отчетов

pgFouine имеет множество настроек, при помощи которых можно генерировать различные по типу содержимого отчеты.

Пройдемся по стандартному отчету.

Здесь отображается общая статистика:

• количество запросов;
• общее время на их выполнение;
• пиковое количество
• типы запросов по количеству.

Далее размещена таблица с самыми медленными запросами:

А после нее идет таблица с запросами, которые заняли больше всего времени:

Здесь показано общее количество одинаковых запросов и время их выполнения с реальными примерами. Это, пожалуй, самая важная часть отчета. На основе этих тяжелых запросов можно начинать процесс оптимизации и ускорения работы СУБД.

Больше примеров и опций для генерации отчетов размещено на [http://pgfouine.projects.pgfoundry.org/reports.html официальном сайте].

Самое главное

Бездумная оптимизация веб-приложения не приводит к существенным улучшениям производительности СУБД. А при помощи профилирования можно определить проблемные места системы и понять, что принесет оптимизация того или иного запроса в контексте времени исполнения.