Технологические вопросы крупных внедрений
11.07.2023
В статье описывается настройка PostgreSQL версий 9.6 и выше на максимальную производительность для работы с Платформой 1С:Предприятие. Предполагается, что сервер СУБД 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
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.
standard_conforming_strings = off
Разрешить использовать символ \ для экранирования.
Копировать в буфер обменаescape_string_warning = off
Не выдавать предупреждение о использовании символа \ для экранирования.
Копировать в буфер обменаmax_locks_per_transaction = 512…1000
Максимальное число блокировок индексов/таблиц в одной транзакции. Для высоконагруженных систем с большими конфигурациями с большим числом таблиц рекомендуется устанавливать значение больше, например, 1000.
Копировать в буфер обменаmax_connections = 500..2000
Количество одновременных соединений. Для высоконагруженных систем с большим числом одновременных пользователей рекомендуется устанавливать значение больше, например, 2000.
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
Минимальный интервал обновления для одной таблицы.