Postgresql в связке с 1с

12.05.2018

Общие положения

В документе описывается настройка PostgreSQL версий 9.2-9.4 на максимальную производительность для платформы 1С. Предполагается, что сервер, используемый для PostgreSQL является достаточно производительным и имеет приблизительно:

• 4 — 512  Gb RAM
• 2 — 256 CPU cores
• RAID 0-1 или SSD

Данный документ подразумевает хотя бы поверхностное знакомство с архитектурой PgSQL. Приведенные в документе параметры являются приблизительными и стартовыми для тонкой настройки.

Настройки сервера для PostgreSQL

• Рекомендуется отключать HyperThreading. Для программ типа систем управления базами данных от него скорее вред чем польза.
• Также рекомендуется отключать Energy Saving, поскольку в противном случае могут непредсказуемо вырастать задержки ответов БД.
• Надо запретить своппинг разделяемой памяти SYSV/posix (FreeBSD: kern.ipc.shm_use_phys=1)

Обозначения

• RAM — объем оперативной памяти сервера. Если сервер используется не только для PostgreSQL, то надо уменьшить эту величину на объем занятой памяти.
• NCores — суммарное число ядер на всех CPU сервера
• max_connections — максимальное число коннектов (или сессий) к PgSQL. Задается в конфигурационном файле.
• WAL — Write Ahead Log, опережающий лог действий с таблицами и индексами. Основная задача — целостность и отказоустойчивость  базы данных при одновременном росте производительности.
• checkpoint — точка восстановления база данных. Все WAL данные, записанные до checkpoint становятся не нужны.
• X..Y — диапазон значений от X до Y включительно

Параметры производительности

shared_buffers = RAM/4

Количество памяти, выделенной PgSQL для совместного кеша страниц. Эта память разделяется между всеми процессами PgSQL.

temp_buffers = 256MB

Максимальное количество страниц для временных таблиц. Т.е. это верхний лимит размера временных таблиц в каждой сессии.

work_mem = RAM/32..64 или 32MB..128MB

Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически, максимально потребная память равна max_connections * work_mem, на практике такого не встречается потому что большая  часть сессий почти всегда висит в ожидании. Это рекомендательное значение используется оптимайзером: он пытается предугадать размер необходимой памяти для запроса, и, если это значение больше work_mem, то указывает экзекьютору сразу создать временную таблицу. work_mem не является в полном смысле лимитом: оптимайзер может и промахнуться, и запрос займёт больше памяти, возможно в разы. Это значение можно уменьшать, следя за количеством создаваемых временных файлов: 

select maintenance_work_mem = RAM/16..32 или work_mem * 4 или 256MB..4GB

Лимит памяти для обслуживающих задач, например вакуум, автовакуум или создания индексов.

effective_cache_size = RAM — shared_buffers

Оценка размера кеша файловой системы. Увеличение параметра увеличивает склонность системы выбирать IndexScan планы. И это хорошо.

effective_io_concurrency = 2

Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Для одиночного диска = 1, для RAID — 2 или больше.

random_page_cost = 1.5-2.0 для RAID, 1.1-1.3 для SSD

Стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PgSQL к выбору планов с сканированием всей таблицы (PgSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). И это плохо.

autovacuum = on

Включение автовакуума. Не выключайте его!

autovacuum_max_workers = NCores/4..2 но не меньше 4

Количество процессов автовакуума. Общее правило — чем больше write-запросов, тем больше процессов. На read-only базе данных достаточно одного процесса.

autovacuum_naptime = 20s

Время сна процесса автовакуума. Слишком большая величина будет приводить к тому, что таблицы не будут успевать вакуумиться и, как следствие, вырастет bloat и размер таблиц и индексов. Малая величина приведет к бесполезному нагреванию.

bgwriter_delay = 20ms

Время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на  checkpoint процесс и процессы, обслуживающие сессии (backend). Малое значение приведет к полной загрузке одного из ядер.

bgwriter_lru_multiplier = 4.0bgwriter_lru_maxpages = 400

Параметры, управляющие интенсивностью записи фонового процесса записи. За один цикл bgwriter записывает не больше, чем было записано в прошлый цикл, умноженное на bgwriter_lru_multiplier, но не больше чемbgwriter_lru_maxpages.

synchronous_commit = off

Выключение синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность.

checkpoint_segments = 32..256 < 9.5

Максимальное количество сегментов WAL между checkpoint. Слишком частые checkpoint  приводят к значительной нагрузке на дисковую подсистему по записи. Каждый сегмент имеет размер 16MB.

checkpoint_completion_target = 0.5..0.9

Степень «размазывания» checkpoint’a. Скорость записи во время checkpoint’а регулируется так, что бы время checkpoint’а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_target.

min_wal_size = 512MB .. 4G         > =9.5
max_wal_size = 2 * min_wal_size    > =9.5

Минимальное и максимальный объем WAL файлов. Аналогично checkpoint_segments.

ssl = off

Выключение шифрования. Для защищенных ЦОД-ов шифрование бессмысленно, но приводит к увеличению загрузки CPU.

fsync = on

Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания. Внимание: если RAID имеет кеш и находиться в режиме write-back, проверьте наличие и функциональность батарейки кеша RAID контроллера! Иначе данные записанные в кеш RAID могут быть потеряны при выключении питания, и, как следствие, PgSQL не гарантирует целостность данных.

commit_delay = 1000commit_siblings = 5

Групповой коммит нескольких транзакций. Имеет смысл включать, если темп транзакций превосходит 1000 TPS. Иначе эффекта не имеет.

temp_tablespaces = ‘NAME_OF_TABLESPACE’

Дисковое пространство для временных таблиц/индексов. Помещение временных таблиц/индексов на отдельные диски может увеличить производительность. Предварительно надо создать tablespace командой CREATE TABLESPACE. Если характеристики дисков отличаются от основных дисков, то следует в команде указать соответствующий random_page_cost. См. статью.

row_security = off               >= 9.5

Отключение контроля разрешения уровня записи.

max_files_per_process = 1000 (default)

Максимальное количество открытых файлов на один процесс PostreSQL. Один файл это как минимум либо индекс либо таблица, но таблица/может состоять из нескольких файлов. Если PostgreSQL упирается в этот лимит, он начинает открывать/закрывать файлы, что может сказываться на производительности. Диагностировать проблему под Linux можно с помощью команды lsof.

Параметры для платформы 1С:Предприятия

standard_conforming_strings = off

Разрешить использовать символ для экранирования.

escape_string_warning = off

Не выдавать предупреждение о использовании символа для экранирования.

max_locks_per_transaction = 256

Максимальное число блокировок индексов/таблиц в одной транзакции.

max_connections = 500..1000

Количество одновременных коннектов/сессий.

Параметры для PgBadger

Приводится согласно документации

При большой нагрузке может влиять на производительность по причине большого потока записи на диск. Лучше вынести на отдельный шпиндель.

log_min_duration_statement = 0
log_line_prefix = ‘%t [%p]: [%l-1] ‘ или ‘%t [%p]: [%l-1] user=%u,db=%d,client=%h ‘
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
lc_messages=’C’
log_duration = on
log_statement = all
log_destination = stderr

Примечание. Здесь пока никак не рассматриваются вопросы ротации логов и использования самого PgBadger’a.

Параметры дополнительных модулей

plantuner

Исходники git clone 

Документация

plantuner.fix_empty_table = ‘on’

Исправляет чрезмерную пессимистичность оптимизатора посгтреса на пустых, недавно созданных таблицах.

online_analyze

Исходники git clone

Документация

online_analyze.table_type = ‘temporary’

Автоматически анализировать временные таблицы при их изменении. Фоновый analyze может заметно отставать, и, как результат, планер ошибается.

online_analyze.verbose = ‘off’

Отключение излишней болтливости автоматического analyze.