Linux postgresql 1c оптимизация

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Степень «размазывания» 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.

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

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

bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400

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

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

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

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

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

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

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

Значение по умолчанию – 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, до которого планировщик будет объединять вложенные запросы с внешним запросом. При меньших значениях сокращается время планирования, но план запроса может стать менее эффективным.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник