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

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

В документе описывается настройка 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 включительно

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

plantuner

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

online_analyze

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

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

Источник

Записки IT специалиста

Технический блог специалистов ООО»Интерфейс»

• Главная
• Оптимизация производительности PostgreSQL для работы с 1С:Предприятие

Оптимизация производительности PostgreSQL для работы с 1С:Предприятие

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

Существуют разные рекомендации по оптимизации PostgreSQL для совместной работы с 1С, мы будем опираться на официальные рекомендации, изложенные на ИТС, также можно использовать онлайн-калькулятор для быстрого расчета некоторых параметров. Если данные калькулятора и рекомендации 1С будут расходиться — то предпочтение будет отдано рекомендациям 1С.

Для тестирования мы использовали систему:

• CPU — Core i5-4670 — 3.4/3.8 ГГц
• RAM — 32 ГБ DDR3
• Системный диск — SSD WD Green 120 ГБ
• Диск для данных — 2 х SSD Samsung 860 EVO 250 ГБ — RAID1
• СУБД — PostgresPro 11.6
• Платформа — 8.3.16.1148
• ОС — Debian 10 x64

Прежде всего выполним тестирование с параметрами по умолчанию:

Полученный результат — 22,32 по Гилеву высоким не назовешь, для субъективного контроля мы использовали конфигурацию Розница 2.2 с базой реального торгового предприятия объемом в 8 ГБ, в целом работу можно было признать удовлетворительной, но местами наблюдалась некоторая «задумчивость», особенно при открытии динамических списков.

Перейдем к оптимизации. Все изменения следует вносить в файл postgesql.conf, который располагается в Linuх для сборки от 1С по пути /etc/postgres/1x/main, а для сборки от PostgresPro в /var/lib/pgpro/1c-1x/data. В Windows данный файл располагается в каталоге данных, по умолчанию это C:\Program Files\PostgreSQL 1C\1х\data. Все параметры указаны в порядке их следования в конфигурационном файла.

Одним из основных параметров, используемых при расчетах, является объем оперативной памяти. При этом следует использовать то значение, которое вы готовы выделить серверу СУБД, за вычетом ОЗУ используемой ОС и другими службами, скажем, сервером 1С. В нашем случае будет использоваться значение в 24 ГБ.

Затем рассчитаем значения отдельных параметров с помощью калькулятора, для чего укажем ОС и версию Postgres, тип накопителя, количество доступной памяти и количество ядер процессора. В поле DB Type указываем Data Warehouses, количество соединений можем проигнорировать, так как вычисляемый результат будет значительно расходиться с рекомендациями 1С.

Теперь можно приступать к редактированию файла конфигурации. Многие значения в нем закомментированы и содержат значения по умолчанию, при изменении таких параметров данные строки следует раскомментировать.

Максимальное число соединений, 1С рекомендует указанные выше значения, мы установили 1000.

Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение — четверть доступного объема памяти, в нашем случае 6 ГБ.

Верхний лимит для временных таблиц в каждой сессии, рекомендуется фиксированное значение.

Указывает объем памяти, который может быть использован для запроса прежде, чем будут задействованы временные файлы на диске. Применяется для каждого соединения и каждой операции, поэтому итоговый объем используемой памяти может существенно превосходить указанное значение. Это один из тех параметров, вычисляемое значение которого калькулятором существенно отличается от рекомендаций 1С. Для объема памяти в 24 ГБ рекомендуемыми значениями будут 375 — 750 МБ, мы выбрали 512 МБ.

Объем памяти для обслуживающих задач (автовакуум, реиндексация и т.д.), указываем рекомендованный калькулятором объем, в нашем случае 2 ГБ.

Максимальное количество открытых файлов на один процесс, в сборке от PostgresPro для Linux это значение по умолчанию.

Параметры процесса фоновой записи, который отвечает за синхронизацию страниц в shared_buffers с диском.

Допустимое число одновременных операций ввода/вывода. Для жестких дисков указывается по количеству шпинделей, для массивов RAID5/6 следует исключить диски четности. Для SATA SSD это значение рекомендуется указывать равным 200, а для быстрых NVMe дисков его можно увеличить до 500-1000. При этом следует понимать, что высокие значения в сочетании с медленными дисками сделают обратный эффект, поэтому подходите к этой настройке грамотно.

Важно! Параметр effective_io_concurrency настраивается только в среде Linux, в Windows системах его значение должно быть равно нулю.

Настройки фоновых рабочих процессов, выбираются исходя из количества процессорных ядер, берем значения из калькулятора. Выше указаны настройки для четырехъядерного СРU.

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

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

Задает размер буферов журнала предзаписи (WAL, он же журнал транзакций), если оставить эту настройку без изменений, то сервер будет автоматически устанавливать это значение в 1/32 от shared_buffers, но не менее 64 КБ и не более размера одного сегмента WAL в 16 МБ.

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

Минимальный и максимальный размер файлов журнала предзаписи. Указываем значения из калькулятора, в нашем случае это 4 ГБ и 16 ГБ.

Скорость записи изменений на диск, рассчитывается как время между точками сохранения транзакций (чекпойнты) умноженное на данный показатель, позволяет растянуть процесс записи по времени и тем самым снизить одномоментную нагрузку на диски. В нашем случае использовано рекомендованное калькулятором максимальное значение 0,9.

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

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

Так как это относительные значения, но не имеет смысла устанавливать random_page_cost ниже seq_page_cost, однако при применении производительных SSD имеет смысл понизить стоимость обоих значений, чтобы повысить приоритет дисковых операций по отношению к процессорным.

Для производительных SSD можно использовать значения:

Но еще раз напомним, данные значения не являются панацеей и должны устанавливаться осмысленно, с реальным пониманием производительности дисковой подсистемы сервера, бездумное копирование настроек способно привести к обратному эффекту.

Определяет эффективный размер кеша, который может использоваться при одном запросе. Этот параметр не влияет на размер выделяемой памяти, не резервирует ее, а служит для ориентировочной оценки доступного размера кеша планировщиком запросов. Чем он выше, тем большая вероятность использования сканирования по индексу, а не последовательного сканирования. При расчете следует использовать выделенный серверу объем RAM, а не полный объем ОЗУ. В нашем случае это 18 ГБ.

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

Количество рабочих процессов автовакуума, рассчитывается по числу процессорных ядер, не менее 4, в нашем случае 4.

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

Отключает политику защиты на уровне строк, данная опция не используется платформой и ее отключение дает некоторое повышение производительности.

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

Данные опции специфичны для 1С и регулируют использование символа \ для экранирования.

Сохраним файл конфигурации и перезапустим PostgreSQL, в Linux это можно выполнить командами:

В Windows штатными средствами операционной системы, либо скриптами из поставки сборки PostgreSQL:

После чего снова выполним тестирование производительности, на этот раз мы получили следующий результат:

Как видим, достаточно несложные действия по оптимизации добавили серверу около 30% производительности, субъективные ощущения от работы с конфигурацией Розница также повысились, исчезло ощущение «задумчивости», повысилась отзывчивость системы.

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

Источник