С ситуацией, когда СУБД “работает медленно”, сталкиваются многие разработчики. И это относится не только к высоконагруженным системам. Как обычно себя ведут разработчики? Начинают оптимизировать запросы, вслепую. В итоге эта работа приносит вовсе не ощутимый результат, либо ускоряются только отдельные части приложения, но проблемы остаются в других местах.

Для того, что-бы провести оптимизацию запросов качественно, необходимо использовать системы профилирования, которые могут явно показать проблемные места. С профилирования и нужно начинать оптимизацию. В этой статье мы рассмотрим пример системы профилирования запросов для СУБД 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”. В нем Вы сможете увидеть несколько различных отчетов:

1. Сумарная статистика запросов и времени выполения
2. Самые медленные запросы
3. Запросы, которые заняли больше всего времени (обратите внимание на них!)
4. Наиболее частые запросы

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

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

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

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

Статьи по теме

• PgFouine
• Настройка и оптимизация параметров postgresql
• XHprof - профилирование PHP от Facebook