Технологические вопросы крупных внедрений
02.04.2021

Настройки PostgreSQL для работы с 1С:Предприятием. Часть 2

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

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

Рекомендуемые значения индивидуальны и зависят от системы и нагрузки на нее.

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

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

Обозначения

Параметры работы сервера PostgreSQL

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

Копировать в буфер обмена

pg_stat_temp = ' '

Рекомендуется изменять значение по умолчанию пути к директории pg_stat_temp так, чтобы она находилась отдельно от директории кластера. Причина в интенсивном изменении файлов в этой директории, что создает значительную нагрузку на дисковую подсистему. Директорию рекомендуется размещать в RAM-диске (для Windows) или tmpfs (для linux).

Параметры клиентских сеансов

Копировать в буфер обмена

temp_tablespaces = 'NAME_OF_TABLESPACE'

Задает директорию расположения для временных таблиц и индексов. Помещение временных таблиц на отдельные (быстрые) диски может увеличить производительность. Предварительно необходимо создать пространство командой CREATE TABLESPACE. Если характеристики дисков отличаются от основных дисков, то следует в команде CREATE TABLESPACE указать соответствующий random_page_cost. См. https://www.postgresql.org/docs/10/sql-createtablespace.html.

Копировать в буфер обмена

row_security = off >= 9.5

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

Параметры подключений

Копировать в буфер обмена

ssl = off

Выключение шифрования, которое может приводить к увеличению загрузки CPU.

Потребление оперативной памяти

Копировать в буфер обмена

shared_buffers = RAM/4

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

Копировать в буфер обмена

temp_buffers = 256MB

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

Копировать в буфер обмена

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

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

select sum(temp_files) as temp_files, pg_size_pretty(sum(temp_bytes)) as temp_size from pg_stat_database;

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

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

В случае выявления существенной фрагментации памяти процессов PostgreSQL в Linux, имеет смысл воспользоваться переменной окружения (её нужно установить в файле/etc/systemd/system/postgresql-10.service):

Копировать в буфер обмена

Environment = MALLOC_MMAP_THRESHOLD_= 8192

Настройки WAL

Копировать в буфер обмена

fsync = on

Сброс буферов на диск (выполнение PostgerSQL системных вызовов fsync()). Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания.

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

Копировать в буфер обмена

synchronous_commit = off

Выключение синхронной записи в WAL момент коммита транзакции. Создает риск потери последних нескольких транзакций (в течении 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.

Копировать в буфер обмена

commit_delay = 1000
commit_siblings = 5

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

Фоновая запись на диск

Копировать в буфер обмена

bgwriter_delay = 20ms

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

Копировать в буфер обмена

bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400

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

Настройки выполнения очистки (автовакуума)

Копировать в буфер обмена

autovacuum = on

Включение автовакуума.

Внимание! Не выключайте автовакуум, это приведет к росту размеров базы и серьезной деградации производительности.

Копировать в буфер обмена

autovacuum_max_workers =" CPU "cores/4..2 но не меньше 4

Количество процессов автовакуума. Общее правило - чем больше запросов на запись выполняется в системе (такие системы называются OLTP), тем больше процессов.

Копировать в буфер обмена

autovacuum_naptime = 20s

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

Использование ресурсов ядра

Копировать в буфер обмена

max_files_per_process = 8000

Значение по умолчанию – 8000, его не нужно уменьшать. Оно может быть увеличено в зависимости от характера нагрузки (максимальное значение зависит от операционной системы). Один файл - это как минимум либо индекс либо таблица, но таблица/может состоять из нескольких файлов. Если PostgreSQL «упирается» в этот лимит, он начинает открывать/закрывать файлы, что может сказываться на производительности. Диагностировать проблему под Linux можно с помощью команды lsof.

Настройки планировщика запросов

Копировать в буфер обмена

effective_cache_size =" RAM - "shared_buffers

Оценка планировщика запроса о размере дискового кеша, доступного для одного запроса. Это представление влияет на оценку стоимости использования индекса. Чем выше это значение, тем больше вероятность, что оптимизатором будет выбираться сканирование по индексу (Index Scan), чем ниже, тем более вероятно, что будет выбрано последовательное сканирование (Seq Scan).

Копировать в буфер обмена

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

Стоимость чтения рандомной страницы, на которую будет опираться оптимизатор (по-умолчанию 4). Практическое значение параметра должно зависеть от «seek time» дисковой системы: чем он меньше, тем меньше должно быть значение random_page_cost (но не менее 1.0) . Излишне большое значение параметра увеличивает склонность PostgreSQL к выбору планов с сканированием всей таблицы (PostgreSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). Оценка стоимости последовательного чтения делается, в свою очередь, с учетом параметра seq_page_cost, который равен по умолчанию 1.

Копировать в буфер обмена

from_collapse_limit= 20

Задаёт максимальное число элементов в списке FROM, до которого планировщик будет объединять вложенные запросы с внешним запросом. При меньших значениях сокращается время планирования, но план запроса может стать менее эффективным.

Копировать в буфер обмена

join_collapse_limit = 20

Задаёт максимальное количество элементов в списке FROM, до достижения которого планировщик будет сносить в него явные конструкции JOIN (за исключением FULL JOIN). При меньших значениях сокращается время планирования, но план запроса может стать менее эффективным.

Копировать в буфер обмена

geqo = on

GEQO - генетический оптимизатор запросов PоstgreSQL, который осуществляет планирование запросов, применяя эвристический поиск вместо полного перебора отношений. Он позволяет сократить время планирования для сложных запросов с большим числом соединений, потому не рекомендуется его отключать. Однако надо учитывать, что полученный им план может оказаться менее эффективным и, как следствие, увеличится время выполнения запроса. Управлять его включением более тонко помогает следующий параметр:

Копировать в буфер обмена

geqo_threshold = 12

Задаёт минимальное число элементов во FROM, при котором для планирования запроса будет привлечён генетический оптимизатор. Для более простых запросов лучше использовать обычный планировщик, для запросов со множеством таблиц обычное планирование может занять слишком много времени, в этом случае выгоднее потерять на качестве плана, но выполнить планирование быстро.

Асинхронное поведение

Копировать в буфер обмена

effective_io_concurrency = 2

Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Допустимый диапазон от 1 до 1000. Значение по умолчанию равно 1, где это поддерживается, в остальных системах - 0.

Для одиночного диска можно условно поставить 1, для RAID - 2 или больше.

Сейчас эта оценка влияет только на выбор bitmap heap scan.


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

Копировать в буфер обмена

standard_conforming_strings = off

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

Копировать в буфер обмена

escape_string_warning = off

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

Копировать в буфер обмена

max_locks_per_transaction = 150…256

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

Копировать в буфер обмена

max_connections = 500..1000

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

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

Копировать в буфер обмена

online_analyze.enable = off

В общем случае мы не рекомендуем использовать синхронное автообновление статистики, однако его можно включить, если есть основания полагать, что фоновое обновление не дает нужного результата / оптимизатор часто ошибается в оценке количества строк.

Все остальные параметры имеют смысл, только если online_analyze.enable = on.

Копировать в буфер обмена

online_analyze.table_type = 'temporary'

Включает синхронное автообновление статистики на временных таблицах.

Копировать в буфер обмена

online_analyze.verbose = 'off'

Выполнение инструкции ANALYZE без опции VERBOSE.

Копировать в буфер обмена

online_analyze.threshold = 50

Минимальное количество записей, предшествующее обновлению статистики.

Копировать в буфер обмена

online_analyze.scale_factor = 0.1

«Доля» в величине таблицы, начиная с которой будет происходить автообновление.

Копировать в буфер обмена

online_analyze.local_tracking = on

Отслеживание изменений в рамках соединения (для локальных временных таблиц).

Копировать в буфер обмена

online_analyze.min_interval = 10000

Минимальный интервал обновления для одной таблицы.