PgFouine - профилирование в postgres
С ситуацией, когда СУБД “работает медленно”, сталкиваются многие разработчики. И это относится не только к высоконагруженным системам. Как обычно себя ведут разработчики? Начинают оптимизировать запросы, вслепую. В итоге эта работа приносит вовсе не ощутимый результат, либо ускоряются только отдельные части приложения, но проблемы остаются в других местах.
Для того, что-бы провести оптимизацию запросов качественно, необходимо использовать системы профилирования, которые могут явно показать проблемные места. С профилирования и нужно начинать оптимизацию. В этой статье мы рассмотрим пример системы профилирования запросов для СУБД Postgres, которая называется PgFouine.
Установка
PgFouine является достаточно известной утилитой, поэтому проверяйте пакеты своей ОС. Для любителей ubuntu новости хорошие:
sudo apt-get install pgfouine
Если в ваших пакетах не нашлось этой утилиты, не расстраивайтесь, качайте исходники тут. Pgfouine написана на PHP, поэтому можете не беспокоиться насчет зависимости от ОС.
Конфигурирование
Pgfouine работает с двумя типами логов: syslog и stderr. Давайте рассмотрим пример конфигурации для syslog:
Для начала необходимо проверить, установлена ли у Вас на сервере утилита syslogd, и если нет, то установить ее
Теперь необходимо в конфигурационном файле postgres (postgresqk.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’
Если Вы хотите логировать только те запросы, которые длятся более N секунд, поправте этот параметр:
log_min_duration_statement = N
Далее, необходимо поправить настройки syslogd для того, что-бы логи postgres записывались в отдельный файл:
LOCAL0.* -/var/log/pgsql
После этого, Вам необходимо будет перезапустить postgres, что-бы новые настройки вступили в силу. В файл /var/log/pgsql начнуть записываться запросы с временными характеристиками.
Внимание! Не проводите эксперименты на рабочей СУБД! Для того, что-бы собрать информацию для анализа, обычно хватает запуска СУБД в режиме логирования на несколько часов. Естественно, Вам необходимо включать логирование в непиковое время, т.к. на каждый запрос к базе данных будут расходоваться дополнительные ресурсы (хотя оверхед будет небольшим).
Построение отчетов
После накопления логов, скопируйте файл с логами на рабочий компьютер (не стоит запускать Pgfouine на сервере). После этого, можете запускать Pgfouine с указанием имени логфайла в качестве параметра:
pgfouine -file pgsql > report.html
Если будут ошибки, контекстная помощь pgfouine подскажет, где искать проблему. Если все хорошо, система начнет обрабатывать файл (не торопитесь, это достаточно длительная процедура). В итоге, будет сгенерирован файл с отчетами “report.html”. В нем Вы сможете увидеть несколько различных отчетов:
- Сумарная статистика запросов и времени выполения
- Самые медленные запросы
- Запросы, которые заняли больше всего времени (обратите внимание на них!)
- Наиболее частые запросы
Система генерирует отчеты на основе паттернов. Для каждого запроса можно посмотреть несколько примеров с реальными параметрами (нажав кнопку “show examples”).
Теперь, имея статистику того, как часто выполняются запросы, Вы поймете, какие из них и в какой последовательности нужно оптимизировать или переписывать. Причем можно заранее определить, что принесет оптимизация того или иного запроса в терминах времени исполнения.
Примеры отчетов
Система Pgfouine обладает большим количеством настроек, которые позволяют генерировать различные по типу содержимого отчеты. Примеры смотрите на официальном сайте:
Спасибо за статью, как раз актуально. Одно дополнение: “redirect_stderr = off” это для Postgres 8.2, для 8.3 нужно раскомментить “logging_collector = off”