Как оптимизировать работу PostgreSQL под оборудование в Linux

Вопрос:

Как оптимизировать работу СУБД PostgreSQL под оборудование в Linux?

Ответ:

Все параметры работы СУБД PostgreSQL хранятся в специальном файле, который располагается как правило в каталоге

/etc/postgresql/14/main/postgresql.conf

В данном файле указываются параметры оптимизации. Рекомендуется указывать собственные параметры оптимизации в конце файла.

Далее идет список параметров, рекомендуемые значения и комментарии

Параметр	Рекомендуемое значение	Комментарии
shared_buffers	RAM/4	Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение - четверть доступного объема памяти
temp_buffers	128MB	Верхний лимит для временных таблиц в каждой сессии, рекомендуется фиксированное значение. Альтернативный вариант расчета - 1/128 RAM
work_mem	(RAM - shared_buffers) / 32..256	Указывает объем памяти, который может быть использован для запроса прежде, чем будут задействованы временные файлы на диске. Применяется для каждого соединения и каждой операции, поэтому итоговый объем используемой памяти может существенно превосходить указанное значение. Рекомендуемое значение для  work_mem — (RAM - shared_buffers)/(16 x CPU cores)
effective_io_concurrency	2 для RAID, 200 для SSD, 500..1000 для NVMe	Допустимое число одновременных операций ввода/вывода. Для жестких дисков указывается по количеству шпинделей, для массивов RAID5/6 следует исключить диски четности. Для SATA SSD это значение рекомендуется указывать равным 200, а для быстрых NVMe дисков его можно увеличить до 500-1000
synchronous_commit	off	Позволяет серверу не ждать сохранения данных на диске, прежде чем сообщить клиенту об успешном завершении операции. Позволяет достаточно безопасно повысить производительность работы. В случае внезапного выключения питания могут быть потеряны несколько последних транзакций, но сама база останется в рабочем состоянии, также, как и при штатной отмене потерянных транзакций.
seq_page_cost	1.0	Стоимость последовательного чтения с диска, является относительным числом, вокруг которого определяются все остальные переменные стоимости, данное значение является значением по умолчанию.
random_page_cost	1.5..2.0 для RAID, 0.5..1.3 для SSD	Стоимость случайного чтения с диска, чем ниже это число, тем более вероятно использование сканирования по индексу, нежели полное считывание таблицы, однако не следует указывать слишком низких, не соответствующих реальной производительности дисковой подсистемы, значений, иначе вы можете получить обратный эффект, когда производительность упрется в медленный случайный доступ.
effective_cache_size	RAM - shared_buffers	Определяет эффективный размер кеша, который может использоваться при одном запросе. Этот параметр не влияет на размер выделяемой памяти, не резервирует ее, а служит для ориентировочной оценки доступного размера кеша планировщиком запросов. Чем он выше, тем большая вероятность использования сканирования по индексу, а не последовательного сканирования.
row_security	off	Отключает политику защиты на уровне строк, данная опция не используется платформой и ее отключение дает некоторое повышение производительности.
ssl	off	Отключает шифрование, если обеспечивается безопасность другими способами.
wal_sync_method	fdatasync	Способ синхронизации wal с диском.
cpu_operator_cost	0.0001	Стоимость операции CPU. Стандартное значение 0.0025. Можно уменьшать цифру при производительных CPU

Для применения изменений необходимо перезапустить службу используя команду

sudo service postgresql restart

Примечание:

Для версии PostgreSQL 14 рекомендуется выполнить перенос каталога со статистикой в RAM диск

Кейсы оптимальных конфигураций производительности PostgreSQL Linux

Заметки по изменению производительности

Стенд: AMD Ryzen 7 2700X, 32GB RAM, NVMe 500Gb, Green Linux 21.1, ядро 5.15.0-67, Postgresql 14

При включении shared_buffer = 8Gb: с 19,53 до 23,26 баллов Гилева

При включении synchronous_commit = off: с 23.26 до 21.01 баллов Гилева

При включении seq_page_cost и random_page_cost для NVMe: с 21.01 до 20.58 баллов Гилева

При отключении synchronous_commit = off и включенных остальных параметрах: с 20.58 до 23.58

При включении effective_io_concurrency = 500: без изменений

При включении effective_cache_size = 16GB: без изменений

При включении work_mem = 512MB: с 23.58 до 23.47 (в рамках погрешности)

При включении row_security, escape_string_warning, standard_conforming_string: с 23.47 до 22.83

При изменении work_mem = 128MB: с 22.83 до 23.81

При изменении seq_page_cost = 0.5 и random_page_cost = 0.5: с 23.81 до 24.04

При добавлении temp_buffers = 128MB: с 24.04 до 23.58

При отключении temp_buffers (стандартное значение 8MB): с 23.58 до 24.04

При включении temp_buffers = 24MB: с 24.04 до 23.04

При включении ssl = off и temp_buffers = 128MB: с 23.58 до 23.47 (в рамках погрешности)

При включении temp_buffers = 1024MB с 23.47 до 20.75

При включении wal_sync_method = fdatasync: с 20.75 до 24.39

При изменении effective_io_concurrency = 200: с 24.39 до 24.04

При изменении effective_io_concurrency = 1000: с 24.04 до 24.39

При отключении escape_string_warning, standard_conforming_string: без изменений

При отключении row_security: с 24.39 до 24.04

При включении row_security = off и synchronous_commit = off: с 24.04 до 21/01

Итого имеют влияния:

1) shared_buffer - крайне высокое

2) work_mem - среднее

3) random_page_cost - среднее

4) wal_sync_method - высокое

5) row_security - низкое

Отрицательное влияние:

1) temp_buffers (для однопоточных операций)

2) synchronous_commit (для однопоточных операций) (для NVMe)