Оптимизация PostgreSQL для 1С

Для оптимизации PostgreSLQ нужно отредактировать некоторые параметры его работы. Редактировать можно основной конфиг (postgresql.conf), который для 14 версии находится в этом каталоге — /etc/postgresql/14/main/. Хотя в некоторых случаях конфиг может быть и в другом месте.

Но мне кажется, лучше создать новый конфиг в каталоге /etc/postgresql/14/main/conf.d/. Этот конфиг можете назвать как угодно, но расширение файла должно быть .conf, например my-pg.conf. В этом случае основной конфиг не будет изменяться, но параметры указанные в файле my-pg.conf будут приоритетнее. И в случае чего, вы можете просто очистить my-pg.conf и вернуть настройки по умолчанию.

В этой статье будет показана оптимизация PostgreSQL для его работы с базами 1С. Я постараюсь не просто написать параметры и их значения, а объяснить, для чего я правлю тот или иной параметр.

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

Здесь мы настроим потребление памяти сервером PostgreSQL. Основной особенностью его работы с памятью является то, что вся работа с базами и таблицами ведётся в специальном буфере (shared_buffers). Для этого PostgreSQL при старте выделяет себе shared_buffers. Но таблички загружаются в shared_buffers не просто так, они туда попадают только если они требуются для работы. То есть нужна для работы какая-та табличка, PostgreSQL ищет её в shared_buffers, если не найдет то начинает искать её на диске.

Всякие временные таблички, которые любит создавать 1С хранятся в другой области памяти — temp_buffers. А раз 1С их любит создавать, то нужно и под это выделить побольше памяти.

Процессы, которые работают с данными, например выполняют какие-то запросы, потребляют память под названием work_mem. При этом, такие процессы, запрашивая данные из shared_buffers, а обрабатывают их в work_mem.

Но есть в PostgreSQL и служебные процессы, которые для своих операций выделяют себе maintenance_work_mem.

shared_buffers

Для начала нам нужно выделить память для хранения данных (баз и их табличек). Это делается с помощью параметра shared_buffers.

Я полагаю что нет смысла задавать shared_buffers намного больше чем размер всех баз, но некоторый запас всё же стоит оставить. Но это только, если у вас много физической памяти и вы можете сделать такой запас. Во многих статьях пишут, что нужно установить этот параметр в 15% — 40% от всей доступной памяти.

Как вариант, задайте этот параметр побольше (40%), затем пусть база поработает. После чего, с помощью команды free, посмотрите сколько сейчас занято памяти в shared. Если занято намного меньше, чем вы задали в параметре, то можете уменьшить значение этого параметра. Поставьте значение в 1,5 — 2 раза больше, чем сейчас занимает shared.

Представим, что у вас доступно 30GB памяти, а базы будут занимать 5GB. Тогда под shared_buffers можно выделить 8GB (shared_buffers = 8192MB). Хоть 40% от 30GB — это 12GB, но так как базы данных у нас занимают всего 5GB, то я выделяю чуть меньше.

А если базы данных вырастут до 20GB, а количество памяти останется 30GB, тогда уж можно выделить 12GB. При этом все базы данных в память у нас уже не поместятся. Но самые часто используемые данные будут находится в оперативной памяти.

temp_buffers

Временные буферы нужны для временных объектов, в основном для временных таблиц. Этот параметр задаёт лимит для временных буферов в каждой сессии. Так как 1С любит создавать временные таблицы, рекомендуется увеличить значение этого параметра, например до 256: temp_buffers = 256MB.

work_mem

Максимальный лимит памяти, который выделяется для обработки запросов. То есть, каждый запрос сможет получить этот объём памяти. Для 1С, мне кажется, должно хватить 512MB (если столько есть физически на каждое соединение). То есть: work_mem = 512MB.

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

# select sum(temp_files) as temp_files, pg_size_pretty(sum(temp_bytes)) as temp_size from pg_stat_database;
 temp_files | temp_size
------------+-----------
          0 | 0 bytes
(1 строка)

Если временных файлов нет, значит всё хорошо.

Есть формула по которой можно вычислить максимальное значение для work_mem: (вся память — shared_bufer) / максимальное_количество_соединений.

С помощью такого запроса можно узнать количество соединений в данный момент:

SELECT COUNT(*) as connections,
       backend_type
FROM pg_stat_activity
where state = 'active' OR state = 'idle'
GROUP BY backend_type
ORDER BY connections DESC;

Представим ситуацию, у вас 30GB всего памяти, на shared_bufer выделили 8GB. Вычислили, что соединений в процессе работы максимально было 15. Значит высчитываем (30 — 8) / 15 = 1,46GB. Это довольно много, поэтому можем уменьшить и указать — work_mem = 512MB.

Затем понаблюдаем, не растёт ли количество временных файлов. Если не растёт, то всё хорошо. А если растёт, то можно немного увеличить значение, но не делать его больше чем 1,46GB.

maintenance_work_mem

В PostgreSQL одни процессы выполняют запросы клиентов, а другие обслуживают базу данных. Например, выполняют VACUUM и ANALYZE. Параметр maintenance_work_mem как раз задаёт максимальный объём памяти для операций обслуживания.

Так как служебные операции обычно не выполняются параллельно, то можем указать здесь немного больше чем в work_mem. Например, в два раза больше — maintenance_work_mem = 1024MB.

Эффективность планирования

Когда мы пишем запрос на SQL, мы говорим что хотим получить, а не как это сделать. За то «как это сделать» отвечает планировщик основываясь на статистике. Статистика включает в себя: сколько у нас таблиц, сколько в них строчек, сколько они занимают страниц, как распределены данные в столбцах и тому подобное. Планировщик решает с каких таблиц начать выполнение запроса, какие условия и в каком порядке применять и так далее. Планировщик подготавливает план выполнения.

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

effective_cache_size

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

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

Считается что нужно выделять от 50% до 75% доступной памяти. Допустим у вас на сервере 30GB, тогда делаем — effective_cache_size = 15GB.

random_page_cost

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

Seek Time — время позиционирования на нужную дорожку. На SSD дисках нет механического позиционирования, поэтому Seek Time маленькое.

Сам сервер Postgres не знает, какая у нас дисковая подсистема. И с помощью этого параметра мы ему об этом сообщаем. Установите random_page_cost:

4.0 — для HDD;
1.5-2.0 — для RAID из HDD;
1.1 — 1.3 — для SSD;
0.1 — для NVMe.

Например — random_page_cost = 1.2.

Автоматический VACUUM

В PostgreSQL, когда данные изменяются (UPDATE), или удаляются (DELETE), это не приводит к изменению старых версий изменяемых или удаляемых строк. Версия строки не должна удаляться до тех пор, пока она остаётся потенциально видимой для других транзакций. Однако, в конце концов, устаревшая или удалённая версия строки оказывается не нужна ни одной из транзакций. В этот момент такие строки можно удалить физически с помощью команды VACUUM.

autovacuum

А чтобы удаление старых и не нужных строк происходило автоматически, нужно включить — autovacuum = on.

autovacuum_max_workers

Если включен autovacuum, то процессов автоматической очистки может быть запущено не больше чем указано в этом параметре, например autovacuum_max_workers = 4.

Многие советуют устанавливать значение этого параметра не меньше 4. Но если у вас много ядер, то можете указать значение, рассчитанное по формуле «количество ядер / 4«.

autovacuum_naptime

Этот параметр задаёт задержку между запусками процессов автоочистки для отдельной базы данных. Он связан с параметром autovacuum_max_workers.

Объясню как этот работает. Допустим у вас 4 базы данных. И вы установили autovacuum_max_workers = 4 и autovacuum_naptime = 20s. Процесс автоматической очистки будет работать следующем образом. Просыпаются 4 процесса автоматической очистки и одновременно обрабатывают 4 базы. Через 20 секунд это повторяется.

Или другой пример. У вас 20 баз данных, а настройки такие же. Просыпаются 4 процесса автоматической очистки и чистят 4 базы. Через 20 секунд просыпаются ещё 4 процесса автоматической очистки и чистятся другие 4 базы. И так далее.

То есть, если у вас много баз данных, и позволяет количество ядер, то лучше увеличить значение autovacuum_max_workers.

Если задать слишком большое значение autovacuum_naptime, то таблицы не будут успевать очищаться. Это приведёт к распуханию базы данных. А если задать слишком маленькое значение, то базы данных будут слишком часто проверятся на необходимость в чистке.

Для 1С рекомендуют установить значение в 20 секунд: autovacuum_naptime = 20s.

Определить раздутые таблицы в базе поможет этот скрипт. Он перечисляет раздутые таблицы и оценивает объемы раздувания. Вы можете вручную очистить эти таблицы с помощью запросов: VACUUM ANALYZE <имя таблицы>; или VACUUM FULL <имя таблицы>;.

Процесс фоновой записи

В PostgreSQL есть специальный процесс фоновой записи. Этот процесс отвечает за сброс грязных буферов из shared_buffer на диск. Когда количество shared_buffer уже недостаточно, данный процесс записывает изменённые страницы на диск, освобождая память. Это снижает вероятность того, что процессы, выполняющие запросы пользователей, не смогут найти чистые буферы и им придётся сбрасывать грязные буферы самостоятельно.

bgwriter_delay

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

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

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

По умолчанию этот параметр установлен в 200ms, рекомендуется уменьшить это значение, например до: bgwriter_delay = 20ms.

bgwriter_lru_multiplier

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

Рекомендуется указать значение 4.0 — bgwriter_lru_multiplier = 4.0. При этом число сбрасываемых буферов ограничено следующем параметром.

bgwriter_lru_maxpages

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

Рекомендуется установить 400 — bgwriter_lru_maxpages = 400.

Надёжность и скорость работы

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

full_page_writes

Здесь нужно разобраться как PostgreSQL сбрасывает странички на диск и восстанавливает базу в случае сбоя. SQL сервер изменяя каждую страничку данных, предварительно пишет о запланированном изменении в журнале предварительной записи (WAL). И периодически выполняет контрольные точки (checkpoint), то есть сбрасывает изменённые странички на диск.

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

Если параметр full_page_writes включен, то при каждой контрольной точке, записываются изменённые странички (8KB) целиком в журнал wall. Вот на эти странички (гарантированно исправные) и будет полагаться PostgreSQL при востановлении.

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

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

Кстати, на файловой системе ZFS также рекомендуют этот параметр выключать — ссылка. Это связано с тем, что ZFS всегда записывает полные блоки и вы можете отключить запись полных страниц в PostgreSQL. Ещё, про этот параметр можете почитать здесь.

Я бы рекомендовал задать параметр full_page_writes = off, настроить репликацию кластера на резервный сервер и периодически делать резервные копии.

synchronous_commit

Определяет, после завершения какого уровня обработки WAL сервер будет сообщать об успешном выполнении операции.

Если параметр включен (on) — происходит ожидании локального сброса WAL на диск и только после этого операция фиксируется. А если параметр выключен (off) — возрастает производительность сервера, при этом гарантирована целостность базы. Но вы можете потерять несколько последних транзакций при сбое.

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

Этот параметр может принимать и другие значения, но я их не буду рассматривать.

ssl

Сервер PostgreSQL можно запустить с включённым механизмом SSL. Но если сервер баз данных защищен другими способами, то можно выключить шифрование, уменьшив нагрузку на процессор. Чтобы выключить механизм SSL установите: ssl = off.

fsync

Если этот параметр включен, сервер старается добиться, чтобы изменения были записаны на диск физически, выполняя системные вызовы fsync() или другими подобными методами.

Хотя отключение fsync даёт выигрыш в скорости, это может привести к неисправимой порче данных в случае сбоя системы.

Во многих случаях отключение synchronous_commit может дать больший выигрыш в скорости, чем отключение fsync, при этом не добавляя риски повреждения данных. Поэтому не рекомендуется выключать этот параметр: fsync = on.

Нагрузка на диск

checkpoint_timeout

Чтобы изменения в базе данных не потерялись, Postgres периодически сбрасывает изменённые данные на диск. Такой сброс называется контрольной точкой (checkpoint).

Такой сброс по умолчанию выполняется раз в 5 минут, я думаю не следует менять это значение — checkpoint_timeout = 5min.

checkpoint_completion_target

Этот параметр задает долю от общего времени между контрольными точками. Например, контрольные точки происходят раз в 5 минут. А для параметра checkpoint_completion_target мы установим значение 0,9. Это будет означать что контрольные точки будут происходить раз в 5 минут, а сам процесс сброса данных на диск будет происходить 5*0,9 = 4,5 минуты. Остальные 30 секунд останутся для запаса, например для завершения контрольной точки.

Значение этого параметра не рекомендуется изменять. Если задать его слишком маленьким, то в момент контрольной точки будет слишком сильная нагрузка на диск. А если указать 1, то контрольные точки не будут успевать завершаться. А значение 0,9 позволяет сделать нагрузку на диск более плавной и оставляет некоторое время на завершение контрольной точки — checkpoint_completion_target = 0,9.

effective_io_concurrency

Параметр задаёт допустимое число параллельных операций ввода/вывода. Чем больше это число, тем больше операций ввода/вывода будет пытаться выполнить параллельно PostgreSQL в отдельном сеансе. Допустимые значения лежат в интервале от 1 до 1000, а нулевое значение отключает асинхронные запросы ввода/вывода.

Для RAID массивов, можно увеличить на количество дисков. Для RAID5 следует исключить 1 диск. Если диск не нагружен, то можно увеличить. Для SSD диска можно указать несколько сотен, например: effective_io_concurrency = 200.

Предупреждение! В Windows системах этот параметр должен равняться нулю.

Выделение памяти под WAL

Журнал предварительной записи (WAL), в основном нужен для надёжности и способности восстановить базы в случае сбоя. Но он занимает место на диске. Здесь я рассмотрю параметры для настойки размера WAL.

min_wal_size

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

Рекомендуется увеличить значение этого параметра, например — min_wal_size = 1G. Вообще выделите столько места, сколько не жалко.

А чтобы узнать сколько сейчас занимает WAL, выполните:

$ du -sh /var/lib/postgresql/14/main/pg_wal/
2,1G    /var/lib/postgresql/14/main/pg_wal/

max_wal_size

Максимальный размер, до которого может вырастать WAL. Если мы приближаемся к этому порогу, то будет выполнена дополнительная контрольная точка и старые (не нужные) записи из WAL будут удалены. Размер WAL может превышать max_wal_size при особых обстоятельствах, например при большой нагрузке.

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

Можно выставить — max_wal_size = 4G.

Также можете установить параметр checkpoint_warning = 3min. Тогда, если контрольные точки будут случаться чаще 3 минут, то вы увидите записи об этом в журнале с рекомендацией увеличить max_wal_size.

wal_keep_size

Задаёт минимальный объём прошлых сегментов журнала WAL, который будет сохраняться, чтобы ведомый сервер мог выбрать их при потоковой репликации. Если ведомый сервер, подключённый к передающему, отстаёт больше чем на wal_keep_size то репликация может сломаться.

Если у вас используется репликация на ведомый сервер, то задайте этот параметр немного меньше чем max_wal_size, например wal_keep_size = 3G.

Специфичные настройки для 1С

row_security

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

Для 1С можно отключить этот параметр, так как политика защиты строк здесь не используется: row_security = off.

standard_conforming_strings

Этот параметр определяет, будет ли обратная косая черта в обычных строковых константах восприниматься буквально, как того требует стандарт SQL.

Для 1С нужно разрешить использовать символ «\» для экранирования, поэтому указываем: standard_conforming_strings = off.

escape_string_warning

Предыдущим параметром мы разрешили использовать символ «\» для экранирования. А с помощью этого параметра нужно выключить предупреждения об использовании этого символа: escape_string_warning = off.

Различные ограничения

max_files_per_process

Задаёт максимальное число файлов, которые могут быть одновременно открыты каждым серверным подпроцессом. По умолчанию 1 процесс может открыть 1000 файлов. Можно это значение не изменять: max_files_per_process = 1000.

max_locks_per_transaction

Этот параметр управляет средним числом блокировок объектов, выделяемым для каждой транзакции. Значение по умолчанию — 64. Но его можно увеличить, если запросы обращаются ко множеству различных таблиц в одной транзакции.

Для 1С это значение нужно увеличивать, хотя бы до 256: max_locks_per_transaction = 256.

max_connections

Определяет максимальное число одновременных подключений к серверу.

Для 1С можно задать от 500 до 1000, например max_connections = 500.

Логирование

Здесь будут рассмотрены параметры логирования, которые можно изменить для уменьшения логирования и уменьшения нагрузки на сервер. Но совсем логирование всё таки лучше не выключать.

log_min_messages

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

log_min_messages = log

log_min_error_statement

Этим параметром определяется, если SQL-оператор завершается ошибкой, то он будет, или не будет записан в лог.

Здесь уровень можно оставить по умолчанию — ERROR. В этом случае вы будите видеть ошибки, которые привели к прерыванию сессий (PANIC, FATAL), если различные служебные процессы завершились с ошибками (LOG), и ошибки текущих команд (ERROR).

log_min_error_statement = error

log_min_duration_statement

Записывает в журнал продолжительность выполнения всех команд, время работы которых не меньше указанного. Например, при значении 250ms в журнал сервера будут записаны все команды, выполняющиеся 250 миллисекунд и дольше. При нулевом значении записывается продолжительность выполнения всех команд. Со значением -1 запись полностью отключается.

Можем указать log_min_duration_statement = 10000, тогда в лог будут сохраняться запросы, которые выполнялись более 10 секунд.

Ссылки на используемую информацию

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

А если хотите разобраться как работает PostgreSQL, можете почитать этот цикл статей.

Сводка

Имя статьи