Как оптимизировать работу 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)