Информация о материале: Автор: Артемьев Евгений Викторович; Категория: SQL; Опубликовано: 29 декабря 2019; Просмотров: 1693

Настройки 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 включительно

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

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.

источник: https://its.1c.ru/db/metod8dev#content:5866:hdoc