Настройка PostgreSQL для работы в связке с 1С 8.х на платформе Windows Server 2012, объём БД более 200 Гб

PostgreeSQL может работать с большими базами данных, не уступая по скорости и надёжности MSSQL. Всё зависит от настроек этой СУБД в файле postgresql.conf . Единственный минус — скорость разворачивания большой БД через dt-шник (10 часов) и скорость создания копии БД (5 часов c помощью pgdump при работающих пользователях, но ПЛЮС — всего 1 час выгрузкой в  dt-шник из конфигуратора при неработающих пользователях).

У нас тяжёлая конфигурация 1С КА 1.1.108.2, 50 одновременно работающих пользователей. Более 1 млн. проводок при закрытии месяца. Время закрытия месяца сравнимо с MSSQL и составляет в среднем 2 часа. Время отмены закрытия месяца — всего 10 минут! Ликвидированы зависания PostgreSQL. Всё за счет настроек файла postgesql.conf.

# Оптимальные настройки для сервера PostgreeSQL сборки postgresql-9.4.2-1.1C с тяжелыми базами данных и тяжёлыми запросами (подробное описание)
# Процессоры: Intel (R) Xeon (R) E5620 2.4 GHz (2 процессора по 8 ядер каждый) х64
# ОЗУ: 48 ГБ 1068MHz
# Дисковый массив уровня RAID 10: 4 диска по 800 ГБ, сумарный объем дискового массива 1600 ГБ
# ОС: Windows Server 2012 R2 Standard х64
# База данных: 1С КА текущий объем 215 ГБ, суммарный объём всех планируемых баз данных 1500 ГБ
# Сервер 1С 8.3.8.2137 х64 запущен на этой же машине.

# Текущее распределение памяти в ОС смотрим в Диспетчере задач закладка Производительность-> Память:
# Используется:  Доступно:   Выделено:   КЭШИРОВАНО: Выгружаемый Пул: Невыгружаемый пул:
#     15,2 ГБ           30,7 ГБ       15,6/54,9 ГБ          30,5 ГБ               484 МБ             85,3 МБ

# Итак, необходимые настройки postgresql.conf 

effective_cache_size = 30GB # чуть меньше 2/3 от доступного объема памяти (=текущий размер кэша ОС см. КЭШИРОВАНО=30,5 ГБ в Диспетчере задач)

#PostgreSQL в своих планах запросов опирается на кэширование файлов, осуществляемое операционной системой. Этот параметр соответствует максимальному размеру объекта, который может поместиться в системный кэш. Это значение используется только для оценки. effective_cache_size можно установить в 1/2 — 2/3 от объёма имеющейся в наличии оперативной памяти, если вся она отдана в распоряжение PostgreSQL.
#Начиная с версии PostreSQL 8.2 изменена работа оптимизатора запросов. Теперь она существенно зависит от размера выделенной PostreSQL оперативной памяти.

#При использовании сборки PostgreSQL-9.4.2-1.1C при работе с 1С:Предприятием 8 рекомендуется увеличить значение параметра effective_cache_size в конфиргурационном файле postgresql.conf до предела.
# В нашем данном случае предельный размер определяется Кэшем ОС = 30,5 Гб после запуска службы сервера PostgreSQL. Значение параметра корректируем по мере уменьшения кэша ОС в процессе работы системы, а также уменьшение может потребоваться, если мы увеличим параметры, изменяющие распределение памяти ,например, shared_buffers.

shared_buffers = 6GB # 1/8 RAM или больше (но не более 1/4). В нашем случае сложные запросы, тяжелые транзакции, 48 ГБ ОЗУ, но такого объема пока хватает (можно как в рекомендации: от 1/8 RAM = 6GB до 1/4 RAM = 12GB). При большом количестве строк данных записываемых в одной транзакции объем буфера должен быть увеличен, иначе PostgreSQL вылетит по ошибке. К примеру, для 1С поводом увеличить буфер является запись свыше 5000-6000 строк в табличную часть одного документа с помощью обработки и последующая запись или проведение этого документа.

#На выделенных серверах полезным объемом будет значение от 8 МБ до 2 ГБ. Объем может быть выше, если у вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, вам доступен большой объем оперативной памяти или большее количество процессоров. И, конечно же, не забываем об остальных приложениях. Выделив слишком много памяти для базы данных, мы можем получить ухудшение производительности.
# Объём совместно используемой памяти, выделяемой PostgreSQL для кэширования данных, определяется числом страниц (shared_buffers) по 8 килобайт каждая. Следует учитывать, что операционная система сама кеширует данные, поэтому нет необходимости отводить под кэш всю наличную оперативную память. Размер shared_buffers зависит от многих факторов, для начала можно принять следующие значения:
#8–16 Мб  – Обычный настольный компьютер с 512 Мб и небольшой базой данных
#80–160 Мб – Небольшой > сервер, предназначенный для обслуживания базы данных с объёмом оперативной памяти 1 Гб и базой данных около 10 Гб.
#400 Мб – Сервер с несколькими процессорами, с объёмом памяти в 8 Гб и базой данных занимающей свыше 100 Гб обслуживающий несколько сотен активных соединений одновременно.
#Примеры:
#Laptop, Celeron processor, 384 МБ RAM, база данных 25 МБ: 12 МБ
#Athlon server, 1 ГБ RAM, база данных поддержки принятия решений 10 ГБ: 200 МБ
#Quad PIII server, 4 ГБ RAM, 40 ГБ, 150 соединений, «тяжелые» транзакции: 1 ГБ
#Quad Xeon server, 8 ГБ RAM, 200 ГБ, 300 соединений, «тяжелые» транзакции: 2 ГБ
#Intel Xeon server, 48 ГБ RAM, 800 ГБ, 100 соединений, «тяжелые» транзакции: 6 ГБ
#Если объём буфера недостаточен для хранения часто используемых рабочих данных, то они будут постоянно писаться и читаться из кэша ОС или с диска, что крайне отрицательно скажется на производительности.
#В то же время не следует устанавливать это значение слишком большим: это НЕ вся память, которая нужна для работы PostgreSQL, это только размер разделяемой между процессами PostgreSQL памяти, которая нужна для выполнения активных операций. Она должна занимать меньшую часть оперативной памяти вашего компьютера, так как PostgreSQL полагается на то, что операционная система кэширует файлы, и не старается дублировать эту работу. Кроме того, чем больше памяти будет отдано под буфер, тем меньше останется операционной системе и другим приложениям, что может привести к своппингу.
#К сожалению, чтобы знать точное число shared_buffers, нужно учесть количество оперативной памяти компьютера, размер базы данных, число соединений и сложность запросов, так что лучше воспользуемся несколькими простыми правилами настройки.
#Для тонкой настройки параметра установите для него большое значение и потестируйте базу при обычной нагрузке. Проверяйте использование разделяемой памяти при помощи ipcs или других утилит(например, free или vmstat). Рекомендуемое значение параметра будет примерно в 1,2 -2 раза больше, чем максимум использованной памяти. Обратите внимание, что память под буфер выделятся при запуске сервера, и её объём при работе не изменяется. Учтите также, что настройки ядра операционной системы могут не дать вам выделить большой объём памяти.

max_connections = 100 # максимальное число клиентских подключений, которые могут подсоединяться к базе данных одновременно (взято с запасом)

# не может быть бесконечным. Каждое подсоединение порождает ещё один процесс postmaster, что, естественно, требует ресурсов. Средней «паршивости» современный однопроцессорный компьютер со стандартным наполнении без особых проблем может обслуживать 100-200 соединений, но, например, 600 активных соединений будут уже явной проблемой. Любая попытка подсоединиться сверх указанного лимита приведёт к отказу от обслуживания. Плохо написанная программа в цикле открывающая, но не закрывающая за собой соединения, легко создаст проблему. Если число клиентов жёстко ограничено, то имеет смысл уменьшить этот параметр до минимально возможного значения.

maintenance_work_mem = 2024MB # увеличить до рекомендуемого размера не позволяет сам сервер PostgreSQL, при увеличении служба сервера не стартует!!! (рекомендуемый размер 1/4 RAM = 12GB)

# Эта память используется для выполнения операций по сбору статистики (ANALYZE), сборке мусора (VACUUM), создания индексов (CREATE INDEX) и для добавления внешних ключей (FOREGIN KEY). Размер выделяемой под эти операции памяти должен быть сравним с физическим размером самого большого индекса на диске. Как и в случае work_mem эта переменная может быть установлена прямо во время выполнения запроса.

work_mem = 2024MB # увеличить до рекомендуемого размера не позволяет сам сервер PostgreSQL, при увеличении служба сервера не стартует!!! (рекомендуемый размер 1/20 RAM = 2,4GB)

# Под каждый запрос можно выделить личный ограниченный объём памяти для работы. Этот объём может использоваться для сортировки, объединения и других подобных операций. При превышении этого объёма сервер начинает использовать временные файлы на диске, что может существенно замедлить скорость обработки запросов. Предел для work_mem можно вычислить, разделив объём доступной памяти (физическая память минус объём занятый под другие программы и под совместно используемые страницы shared_buffers) на максимальное число одновременно используемых активных соединений. При необходимости, например, выполнения очень объёмных операций, допустимый лимит можно изменять прямо во время выполнения запроса. Поэтому нет нужды изначально задавать теоретический предел.

temp_buffers = 2024MB # увеличить до рекомендуемого размера не позволяет сам сервер PostgreSQL, при увеличении служба сервера не стартует!!! (рекомендуемый размер 1/20 RAM = 2,4GB)

# Буфер под временные объекты, в основном для временных таблиц.

wal_sync_method = open_datasync # наилучший по тесту для Windows (для Линукс = fdatasync)

# На производительность PostgreSQL оказывает существенное влияние производительность дисковой системы. В конфигурационном файле postgresql.conf есть несколько параметров, значения которых могут оказать существенное влияние на производительность:
#fsync
#По умолчанию, параметр fsync включен. Это означает, что при выполнении операции COMMIT данные сразу переписываются из кеша операционной системы на диск, тем самым гарантируется консистентность при возможном аппаратном сбое. Обратной стороной этого является снижение производительности операций записи на диск, поскольку при этом не используются возможности отложенной записи данных операционной системы.
#Отрицательное влияние включенного fsync можно уменьшить, отключив его, положившись на надежность вашего оборудования, или правильно подобрав параметр wal_sync_method — метод, который используется для принудительной записи данных на диск.
#Возможные значения:
#open_datasync – запись данных методом open() с параметром O_DSYNC,
#fdatasync – вызов метода fdatasync() после каждого commit,
#fsync_writethrough – вызывать fsync() после каждого commit игнорирую паралельные процессы,
#fsync – вызов fsync() после каждого commit,
#open_sync – запись данных методом open() с параметром O_SYNC.
#Не все методы доступны на определенных платформах. Выбор метода зависит от операционной системы под управлением, которой работает PostgreSQL.
#В состав PostgreSQL входит утилита pg_test_fsync, с помощью которой можно определить оптимальное значение параметра wal_sync_method.
#Она выполняет серию дисковых тестов с использованием различных методов синхронизации. В результате этого теста получаются оценки производительности #дисковой системы, по которым можно определить оптимальный метод синхронизации для данной опереционной системы

checkpoint_segments = 24 #можно до 32 — подбирается экспериментально

#Журнал транзакций PostgreSQL работает следующим образом: все изменения в файлах данных (в которых находятся таблицы и индексы) производятся только после того, как они были занесены в журнал транзакций, при этом записи в журнале должны быть гарантированно записаны на диск.
#В этом случае нет необходимости сбрасывать на диск изменения данных при каждом успешном завершении транзакции: в случае сбоя БД может быть восстановлена по записям в журнале. Таким образом, данные из буферов сбрасываются на диск при проходе контрольной точки: либо при заполнении нескольких (параметр checkpoint_segments, по умолчанию 3) сегментов журнала транзакций, либо через определённый интервал времени (параметр checkpoint_timeout, измеряется в секундах, по умолчанию 300).
#Изменение этих параметров прямо не повлияет на скорость чтения, но может принести большую пользу, если данные в базе активно изменяются.
#1 Уменьшение количества контрольных точек: checkpoint_segments
#Если в базу заносятся большие объёмы данных, то контрольные точки могут происходить слишком часто2. При этом производительность упадёт из-за постоянного сбрасывания на диск данных из буфера.
#Для увеличения интервала между контрольными точками нужно увеличить количество сегментов журнала транзакций (checkpoint_segments). Данный параметр определяет количество сегментов (каждый по 16 МБ) лога транзакций между контрольными точками. Этот параметр не имеет особого значения для базы данных, предназначенной преимущественно для чтения, но для баз данных со множеством транзакций увеличение этого параметра может оказаться жизненно необходимым. В зависимости от объема данных установите этот параметр в диапазоне от 12 до 256 сегментов и, если в логе появляются предупреждения (warning) о том, что контрольные точки происходят слишком часто, постепенно увеличивайте его. Место, требуемое на диске, вычисляется по формуле (checkpoint_segments * 2 + 1) * 16 МБ, так что убедитесь, что у вас достаточно свободного места. Например, если вы выставите значение 32, вам потребуется больше 1 ГБ дискового пространства.
#Следует также отметить, что чем больше интервал между контрольными точками, тем дольше будут восстанавливаться данные по журналу транзакций после сбоя.

checkpoint_completion_target = 0.9 # рекомендуемое максимальное значение

# Чтобы избежать "завала" большим количеством системных операций дискового ввода/вывода из-за взрывного количества операций записи страниц, запись заполненных буферов во время контрольной точки "размазывается" на определённый период времени. Этот период управляется параметром checkpoint_completion_target, который задаётся как часть интервала контрольной точки. Количество данных ввода/вывода согласуется так, чтобы контрольная точка завершалась, когда данная часть checkpoint_segments сегментов WAL будет израсходована с момента старта контрольной точки или когда заданная часть checkpoint_timeout секунд истечёт, смотря какое событие из вышеуказанных наступит быстрее. С значением 0.5, заданным по умолчанию, PostgreSQL может ожидать завершения каждой контрольной точки примерно половину времени перед стартом следующей контрольной точки. На системах, которые очень близки к максимальному потоку данных ввода/вывода во время обычного функционирования, вы возможно захотите увеличить checkpoint_completion_target, чтобы снизить загрузку по вводу/выводу, возникающую из-за контрольных точек. Недостаток такого подхода состоит в том, что пролонгированные контрольные точки влияют на время восстановления, потому что при восстановлении нужно будет использовать большее количество сегментов WAL. Хотя значение checkpoint_completion_target может быть установлено столь высоким как 1.0, лучше оставить его поменьше (по крайней мере, предположительно, меньше 0.9), так как контрольные точки включают некоторые другие операции, помимо записи заполненных буферов. Установка значения 1.0 вполне вероятно приведёт к тому, что контрольные точки не будут завершаться вовремя, что приведёт к потере производительности из-за неожиданного изменения количества необходимых сегментов WAL.

default_statistics_target = 300 # количество записей, просматриваемых при сборе статистики по таблицам. По умолчанию — 100, 1С рекомендует от 1000 до 10000 при зависании запросов к БД

#Этот параметр задаёт объём статистики, собираемой командой ANALYZE. Увеличение параметра заставит эту команду работать дольше, но может позволить оптимизатору строить более быстрые планы запросов, используя полученные дополнительные данные. Объём статистики для конкретного поля может быть задан командой ALTER TABLE …SET STATISTICS.

constraint_exclusion = partition # включаем партиционирование таблиц только для партиционированных таблиц

#Начиная с 8.4 версии PostgreSQL «constraint_exclusion» может быть «on», «off» и «partition». По умолчанию (и рекомендуется) ставить «constraint_exclusion» не «on», и не «off», а «partition», который будет проверять «CHECK» только на партиционированых таблицах.

#ну и 1С-специфические настройки:
escape_string_warning = off #чтобы лог не засорялся соответствующими предупреждениями по
standard_conforming_strings = off # отключение как спецсимвола

#Следующие ниже настройки используются, если база начала подвисать:

join_collapse_limit = 6 # по умолчанию 8 . Внимание!!! Для 1С не стоит устанавливать значение этого параметра равным 1, как в рекомендациях фирмы 1С. Иначе сложные запросы с большим количеством соединений и источников данных станут надолго зависать. Примером для КА являются: документ Инвентаризационная опись основных средств, Отчет по временным разницам и т.п., поскольку данные отчеты используют множество соединений с таблицами регистров сведений.

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

seq_page_cost = 0.1 # стоимость последовательного чтения страниц
#Задаёт приблизительную стоимость чтения одной страницы с диска, которое выполняется в серии последовательных чтений. Значение по умолчанию равно 1.0. Это значение можно переопределить для таблиц и индексов в определённом табличном пространстве, установив одноимённый параметр табличного пространства (см. ALTER TABLESPACE).

random_page_cost = 0.4 # стоимость случайного чтения страниц

#Задаёт приблизительную стоимость чтения одной произвольной страницы с диска. Значение по умолчанию равно 4.0. Это значение можно переопределить для таблиц и индексов в определённом табличном пространстве, установив одноимённый параметр табличного пространства (см. ALTER TABLESPACE).

cpu_operator_cost = 0.00025 # стоимость одного оператора запроса
#Задаёт приблизительную стоимость обработки оператора или функции при выполнении запроса. Значение по умолчанию — 0.0025

max_locks_per_transaction = 248  # 64 по умолчанию. Пришлось увеличить этот параметр одновременно с shared_buffers из-за записи более 5000 строк  в табличной части одного документа 1С. При заданном по умолчанию параметре PostgreSQL вылетала по ошибке.

#Общая таблица блокировок отслеживает блокировки для max_locks_per_transaction * (max_connections + max_prepared_transactions) объектов (например, таблиц); таким образом, в любой момент времени может быть заблокировано не больше этого числа различных объектов. Этот параметр управляет средним числом блокировок объектов, выделяемым для каждой транзакции; отдельные транзакции могут заблокировать и больше объектов, если все они умещаются в таблице блокировок. Заметьте, что это не число строк, которое может быть заблокировано; их количество не ограничено. Значение по умолчанию, 64, как показала практика, вполне приемлемо, но может возникнуть потребность его увеличить, если запросы обращаются ко множеству различных таблиц в одной транзакции, как например, запрос к родительской таблице со многими потомками. Этот параметр можно задать только при запуске сервера.

Остальные настройки пока по умолчанию.  Об их влиянии на работу 1С буду писать по мере появления свободного времени. Также будет обновляться файл postgresql.conf c оптимальными настройками начального уровня для работы на мощных серверах с большими объемами баз данных. Надеюсь, что это сэкономит время программистам и деньги предприятиям, внедряющим 1С, ибо использование MSSQL дороговато 😉

Статья в ближайшее время будет доработана. А пока выкладываю настройки для версии  выкладываю файл postgresql.conf для версии PostgreSQL 9.6 и выше для объема ОЗУ 48 Гб. Пояснения дам чуть позже. Пока на это времени нет.

Источники: 

Решение проблемы с зависанием PostgreSQL 

Документация на русском по всем версиям PostgreSQL