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

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

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

Установка

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

sudo apt-get install pgfouine

# Утилита написана на PHP, так что ее можно скомпилировать из исходников

Настройка

Система профилирования работает с двумя типами логов:syslog и stderr. Для конфигурирования pgFouine под syslog прежде проверьте, установлена ли утилита 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. Суммарная статистика запросов и времени выполнения;
  2. Самые медленные запросы;
  3. Запросы, которые заняли больше всего времени ( важная информация);
  4. Наиболее частые запросы;

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

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

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

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

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

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

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

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

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

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

Больше примеров и опций для генерации отчетов размещено на официальном сайте.

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

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

материалы по теме

  • Тюнинг базы Postgres
  • Подпишитесь на Хайлоад с помощью Google аккаунта
    или закройте эту хрень