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

Вопрос:

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

Ответ:

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

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

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

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

Параметр	Рекомендуемое значение	Комментарии
shared_buffers	RAM/4	Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение - четверть доступного объема памяти
temp_buffers	256MB	Верхний лимит для временных таблиц в каждой сессии, рекомендуется фиксированное значение. Альтернативный вариант расчета - 1/128 RAM
work_mem	RAM/32..64	Указывает объем памяти, который может быть использован для запроса прежде, чем будут задействованы временные файлы на диске. Применяется для каждого соединения и каждой операции, поэтому итоговый объем используемой памяти может существенно превосходить указанное значение.
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, 1.1..1.3 для SSD	Стоимость случайного чтения с диска, чем ниже это число, тем более вероятно использование сканирования по индексу, нежели полное считывание таблицы, однако не следует указывать слишком низких, не соответствующих реальной производительности дисковой подсистемы, значений, иначе вы можете получить обратный эффект, когда производительность упрется в медленный случайный доступ.
effective_cache_size	RAM - shared_buffers	Определяет эффективный размер кеша, который может использоваться при одном запросе. Этот параметр не влияет на размер выделяемой памяти, не резервирует ее, а служит для ориентировочной оценки доступного размера кеша планировщиком запросов. Чем он выше, тем большая вероятность использования сканирования по индексу, а не последовательного сканирования.
row_security	off	Отключает политику защиты на уровне строк, данная опция не используется платформой и ее отключение дает некоторое повышение производительности.
escape_string_warning	off	Специфичная опция под 1С
standard_conforming_strings	off	Специфичная опция под 1С
ssl	off	Отключаешь шифрование, если обеспечивается безопасность другими способами.
wal_sync_method	fdatasync	Способ синхронизации wal с диском.

Для производительных SSD можно использовать значения:

seq_page_cost = 0.5

random_page_cost = 0.5

А для NVme:

seq_page_cost = 0.1

random_page_cost = 0.1

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

sudo service postgresql restart

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

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

При включении 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 до

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

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

2) work_mem - среднее

3) random_page_cost - среднее

4) wal_sync_method - высокое

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

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

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