Повышение производительности PostgreSQL после настройки параметров

Многие думают, что изменение параметров в postgresql.conf — это реальный путь к успеху. Однако это не всегда так. Конечно, чаще всего хорошие параметры конфигурации базы данных очень полезны. Тем не менее, во многих случаях реальные проблемы будут возникать из-за странного запроса, скрытого глубоко в некоторой логике приложения. Даже вполне вероятно, что запросы, вызывающие реальные проблемы, не являются теми, на которые вы обратили внимание. Возникает естественный вопрос: как мы можем отследить эти запросы и выяснить, что на самом деле происходит? Мой любимый инструмент для этого — pg_stat_statements, который всегда должен быть включен по моему мнению, если вы используете PostgreSQL 9.2 или выше (пожалуйста, не используйте его в более старых версиях).
 

Включение pg_stat_statements

Чтобы включить pg_stat_statements на вашем сервере, измените следующую строку в postgresql.conf и перезапустите PostgreSQL:

shared_preload_libraries = ‘pg_stat_statements’

После загрузки этого модуля на сервер PostgreSQL автоматически начнет собирать информацию. Хорошо то, что накладные расходы модуля действительно очень низкие (накладные расходы в основном просто «шум»).

Затем выполните следующую команду для создания представления, необходимого для доступа к данным:

CREATE EXTENSION pg_stat_statements;

Расширение создаст представление с именем pg_stat_statements и сделает данные легко доступными.
 

Обнаружение медленных запросов в PostgreSQL

Самый простой способ найти наиболее интересные запросы — отсортировать вывод pg_stat_statements по total_time:

	
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
или
SELECT d.datname, t.query, t.calls, t.total_time, t.min_time, t.max_time FROM pg_stat_statements as t
inner join pg_database as d on d.oid = t.dbid
ORDER BY t.total_time DESC;

Прелесть здесь в том, что тип запроса, который наиболее трудоемкий, естественно будет отображаться в верхней части списка. Лучший способ — пройтись от первого до, скажем, 10-го запроса и посмотреть, что там происходит.

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

Для сброса информации можно выполнить команду

SELECT pg_stat_statements_reset()