Как оптимизировать работу PostgreSQL под оборудование в Linux
Вопрос:
Как оптимизировать работу СУБД PostgreSQL под оборудование в Linux?
Ответ:
Все параметры работы СУБД PostgreSQL хранятся в специальном файле, который располагается как правило в каталоге
/etc/postgresql/14/main/postgresql.conf
В данном файле указываются параметры оптимизации. Рекомендуется указывать собственные параметры оптимизации в конце файла.
Далее идет список параметров, рекомендуемые значения и комментарии
| Параметр | Рекомендуемое
значение |
Комментарии |
|---|---|---|
| shared_buffers | RAM/4 | Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение - четверть доступного объема памяти |
| temp_buffers | 256MB | Верхний лимит для временных таблиц в каждой сессии, рекомендуется фиксированное значение. |
| 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С |
Для производительных 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 баллов Гилева