Настройка параметров PostgreSQL для оптимизации производительности

В PostgreSQL для обработки каждого соединения клиента создается отдельный процесс, который расходует оперативную память. Значения, установленные по умолчанию, обеспечивают работоспособность PostgreSQL на компьютерах с наименьшими системными требованиями. Администратору базы данных необходимо настроить PostgreSQL в соответствии с нагрузкой и ресурсами системы. Конфигурационные параметры PostgreSQL хранятся в файле postgresql.conf в каталоге с данными (PGDATA) по пути установки PostgreSQL.

Максимальное количество одновременных подключений к серверу базы данных устанавливается значением параметра max_connections (integer).

1. По умолчанию обычно задано значение 100 соединений. Узнать установленное значение можно с помощью команды:

   [имя базы данных]=# SHOW MAX_CONNECTIONS;

2. Расчитать максимальное количество подключений для системы Pilot можно по следующей формуле:

   [количество клиентских приложений]*5 + [количество узлов сервера]*5

   Например, если система состоит из 50 пользователей и 10 узлов сервера, то рекомендуемое значение параметра max_connections будет:

   50*5 + 10*5 = 300

3. Изменить значение параметра можно в настройках СУБД в конфигурационном файле или с помощью команды:

   ALTER SYSTEM SET max_connections = [количество соединений];

   Например:

   ALTER SYSTEM SET max_connections = 300;

4. Так как этот параметр можно задать только при запуске сервера, изменение значения max_connections требует перезапуска всех узлов кластера PostgreSQL.

Для ведомого сервера значение этого параметра должно быть больше или равно значению на ведущем. В противном случае на ведомом сервере не будут разрешены запросы.

Если требуется большое количество подключений можно воспользоваться пулером соединений pg_bouncer. Он может объединять соединения.

Недостаточно только настроить параметр max_connections. Процессы, вызываемые соединениями, расходуют оперативную память. В PostgreSQL используется двойная буферизация — данные хранятся в памяти дважды, сначала в буфере PostgreSQL, а затем в буфере ядра. Объём выделенной памяти, которая используется PostgreSQL для кеширования, устанавливается значением параметра shared_buffers (integer).

1. Значение может колебаться от 16 до 1073741823 блоков по 8 КБ, что составляет от 128 КБ до 8192 ГБ. По умолчанию обычно задано 128 МБ. Узнать установленное значение можно с помощью команды:

   [имя базы данных]=# SHOW SHARED_BUFFERS;

2. Расчитать необходимый размер буфера PostgreSQL для системы Pilot можно по следующей формуле:

   [Расчитанное значение параметра max_connections] * 10 МБ

   Например, если расчётное значение параметра max_connections получилось равным 300 подключений, то размер буфера должен быть не менее:

   300*10 = 3000 МБ

   3000*1024/8 = 384000 блока

3. Изменить значение параметра можно в настройках СУБД в конфигурационном файле или с помощью команды:

   ALTER SYSTEM SET shared_buffers = [количество блоков];

   Например:

   ALTER SYSTEM SET shared_buffers = 384000;

4. Так как этот параметр можно задать только при запуске сервера, изменение значения shared_buffers требует перезапуска всех узлов кластера PostgreSQL.

Рекомендуется следить, чтобы значение shared_buffers не превышало 24-40% от всего объёма ОЗУ для комфортной работы операционной системы и других приложений.