Linx 18 ноя в 15:49

Оптимизация PostgreSQL: параметры для ускорения запросов

Средний

5 мин

11K

Блог компании LinxАдминистрирование баз данных*PostgreSQL*Облачные сервисы*

Туториал

+12

Комментарии 8

Akina 18 ноя в 16:07

effective_cache_size - 50-75% от объёма оперативной памяти сервера
shared_buffers - 25-40% от объёма оперативной памяти сервера

Во-первых, что в данном случае "оперативная память сервера"? PostgreSQL не в вакууме работает, под ним ОС, рядом, возможно, ещё какие-то сервисы и приложения, они тоже памяти хотят - их учитывать будем? Или на них наплевать, считаем от физической памяти, и пусть крутятся, как хотят...

Во-вторых, если размахнуться в обоих параметрах, то сумма превысит объём оперативной памяти сервера. Что, скорее всего, окончится фатально... а какой предел у этой суммы? и да, с учётом упомянутого в "во-первых"... А ещё - как определить, что мы таки хапнули лишнего?

Linx 18 ноя в 18:41

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

Да, при настройке параметров необходимо учитывать:

ОС: она использует часть оперативной памяти для своих нужд, включая файловый кэш.
Другие приложения: если сервер используется не только для PostgreSQL, но и для других сервисов.
Резерв: всегда стоит оставлять запас оперативной памяти для непредвиденных нагрузок или системных процессов.

Таким образом, расчёт значений shared_buffers и effective_cache_size лучше производить от доступной памяти, остающейся после учёта всех прочих сервисов и ОС.

Akina 19 ноя в 08:18

У вас тоже есть привычка отвечать исключительно на самый первый вопрос? ну я не гордый, повторю:

как определить, что мы таки хапнули лишнего?

Ну или в подробностях - как, по каким показателям или иным (каким?) признакам можно определить, что суммарный объём запрошенной настройками оперативной памяти превышает возможности системы по её выделению? Причём как признакам в ОС (и Windows, и Linux), так и в самом PostgreSQL.

И если такое превышение выявлено - как корректировать настройки. Что их надо уменьшать - очевидно, но какую настройку надо снижать в первую очередь, насколько и почему именно её? какую корреляцию между значениями (например, их соотношение или разность) выдерживать, и опять же почему?

Linx 21 ноя в 16:43

Чтобы понять, что PostgreSQL использует слишком много памяти ("хапнул лишнего"), обратите внимание на ключевые признаки и проведите диагностику.

Признаки проблемы

Свопинг (swap): Если система активно использует swap, это указывает на нехватку оперативной памяти, что может резко снизить производительность.

Замедление запросов: Запросы выполняются медленно, даже если CPU не загружен. Это может быть связано с чрезмерным выделением памяти под work_mem или большим количеством соединений.

Чрезмерное количество соединений:Если количество соединений близко к значению max_connections, сервер начинает расходовать больше ресурсов на их обслуживание.

Как диагностировать

На уровне ОС:

free -m: проверьте, сколько памяти доступно (available) и не растёт ли использование swap.

top/htop: найдите процессы PostgreSQL с высоким потреблением памяти.

Использование памяти:

Оцените настройки work_mem и нагрузку от соединений, особенно при большом числе активных запросов.

Что делать
Оптимизируйте запросы:

Добавьте индексы для ускорения фильтров.
Избегайте SELECT *, выбирайте только нужные колонки.
Разбейте сложные запросы на более простые.

Ограничьте соединения:

Проверьте необходимость всех активных соединений.
Используйте пул соединений (PgBouncer, Pgpool-II) для управления нагрузкой.
Установите разумное значение для max_connections (например, 100-200 вместо нескольких сотен).

Настройте параметры памяти:

Уменьшите work_mem для интенсивных запросов: SET work_mem = '16MB';
Убедитесь, что shared_buffers не превышает 25-40% доступной памяти.

Мониторинг:

Настройте системы мониторинга для отслеживания активности соединений и использования памяти (pg_stat_activity, node_memory_MemAvailable).
Настройте автоматические уведомления при достижении определённых порогов использования памяти или swap.