PostgreSQL и 1С

PostgreSQL — это мощная, открытая (open-source) объектно-реляционная система управления базами данных (СУБД). Она известна своей надежностью, производительностью и рядом преимуществ, таких как

1. Бесплатность. PostgreSQL — это open-source СУБД, что позволяет значительно снизить затраты на лицензирование по сравнению с коммерческими СУБД, такими как Microsoft SQL Server
2. Кроссплатформенность. PostgreSQL работает на различных операционных системах (Windows, Linux, macOS), что позволяет гибко настраивать инфраструктуру
3. Масштабируемость. PostgreSQL поддерживает репликацию, партиционирование и другие функции, которые позволяют масштабировать базы данных 1С для работы с большими объемами данных.

Начиная с версии 1С: Предприятие 8.3.10, платформа 1С официально поддерживает PostgreSQL в качестве СУБД для хранения данных. Это позволяет использовать PostgreSQL как альтернативу другим базам данных, таким как Microsoft SQL Server

В прошлых статьях мы рассматривали настройку Microsoft SQL Server а так же самого сервера, его параметры BIOS

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

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

Мы настоятельно рекомендуем выбрать операционную систему из семейства Linux так как они имеют ряд преимуществ

Преимущества Linux:

• Высокая производительность и низкие накладные расходы.
• Гибкость в настройке и оптимизации.
• Широкая поддержка сообщества и документация.
• Бесплатность и открытый исходный код.

Допустим система у нас уже установлена и настроена. Для более лучшего взаимодействия с 1С, Мы предлагаем остановиться версии Postgres Pro

Postgres Pro — это коммерческая версия PostgreSQL, разработанная российской компанией Postgres Professional. Она основана на открытом исходном коде PostgreSQL, но включает дополнительные функции, оптимизации и инструменты, которые делают её более подходящей для корпоративных и высоконагруженных систем. Ниже приведены основные отличия Postgres Pro от стандартного PostgreSQL.

Postgres Pro предлагает ряд расширений и улучшений, которых нет в стандартном PostgreSQL:

• Улучшенная производительность:
  • Оптимизированные алгоритмы для работы с индексами, запросами и транзакциями.
  • Поддержка многомерных индексов (например, для геоданных).
  • Улучшенная работа с большими объемами данных (Big Data).
• Расширенные типы данных:
  • Добавлены специализированные типы данных для работы с JSON, XML и другими структурами.
• Поддержка распределенных баз данных:
  • Встроенные механизмы для работы с распределенными системами (например, шардирование и репликация).
• Улучшенная безопасность:
  • Дополнительные механизмы шифрования данных.
  • Расширенные возможности аудита и мониторинга.

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

Postgres Pro включает множество оптимизаций, которые могут быть полезны для высоконагруженных систем:

• Улучшенный планировщик запросов.
• Оптимизация работы с памятью и диском.
• Поддержка аппаратного ускорения (например, GPU)

Значения параметров работы сервера устанавливаются в конфигурационном файле postgresql.conf, расположенном обычно в директории данных кластера.

Давайте разберем следующие настройки и рекомендации более детально

Память

    shared_buffers = 1GB          # Рекомендуется 25% от оперативной памяти.
    work_mem = 64MB               # Память для операций сортировки и хеширования.
    maintenance_work_mem = 256MB  # Память для операций обслуживания.
    effective_cache_size = 3GB    # Рекомендуется 50-75% от оперативной памяти.
    temp_buffers = 256MB

shared_buffers

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

Подробнее. PostgreSQL не читает данные напрямую с диска и не пишет их сразу на диск. Данные загружаются в общий буфер сервера, находящийся в разделяемой памяти, серверные процессы читают и пишут блоки в этом буфере, а затем уже изменения сбрасываются на диск.Если процессу нужен доступ к таблице, то он сначала ищет нужные блоки в общем буфере. Если блоки присутствуют, то он может продолжать работу, если нет — делается системный вызов для их загрузки. Загружаться блоки могут как из файлового кэша ОС, так и с диска, и эта операция может оказаться весьма «дорогой». Если объём буфера недостаточен для хранения часто используемых рабочих данных, то они будут постоянно писаться и читаться из кэша ОС или с диска, что крайне отрицательно скажется на производительности. В то же время не следует устанавливать это значение слишком большим: это НЕ вся память, которая нужна для работы PostgreSQL, это только размер разделяемой между процессами PostgreSQL памяти, которая нужна для выполнения активных операций. Она должна занимать меньшую часть оперативной памяти вашего компьютера, так как PostgreSQL полагается на то, что операционная система кэширует файлы, и не старается дублировать эту работу. Кроме того, чем больше памяти будет отдано под буфер, тем меньше останется операционной системе и другим приложениям, что может привести к своппингу.

К сожалению, чтобы знать точное число shared_buffers, нужно учесть количество оперативной памяти компьютера, размер базы данных, число соединений и сложность запросов, так что лучше воспользуемся несколькими простыми правилами настройки. На выделенных серверах полезным объемом для shared_buffers будет значение 1/4 памяти в системе. Если у вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, вам доступен большой объем оперативной памяти или большее количество процессоров, то можно подымать это значение и мониторить результат, чтобы не привести к «деградации» (падению) производительности. Выделив слишком много памяти для базы данных, мы можем получить ухудшение производительности, поскольку PostgreSQL также использует кэш операционной системы (увеличениеданного параметра более 40% оперативной памяти может давать «нулевой» прирост производительности).Для тонкой настройки параметра установите для него большое значение и потестируйте базу при обычной нагрузке. Проверяйте использование разделяемой памяти при помощи ipcs или других утилит(например, free или vmstat). Рекомендуемое значение параметра будет примерно в 1,2 –2 раза больше, чем максимум использованной памяти. Обратите внимание,что память под буфер выделяется при запуске сервера, и её объём приработе не изменяется.На Windows, большие значения для shared_buffers не столь эффективны, как на Linux системах, и в результате лучшие результаты можно будет получить, если держать это значение относительно небольшое.

Внутрь буферного кеша позволяет заглянуть расширение pg_buffercache:

    CREATE EXTENSION pg_buffercache;
    SELECT * FROM buffercache('вашатаблица');

work_mem

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

maintenance_work_mem

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

effective_cache_size

оценивает объем памяти, доступной для кэширования диска операционной системой и самой базой данных shared_buffers. Этот параметр является просто указанием планировщика и не выделяет и не резервирует память.effective_cache_size = RAM - shared_buffers

Чаще всего сканирование индексов используется для более высоких значений; в противном случае будет использовано последовательное сканирование, если значение низкое. Рекомендуется установить effective_cache_size на уровне 50–75 % от общего объема ОЗУ компьютера. Увеличение параметра увеличивает склонность системы выбирать IndexScan планы. И это хорошо. В официальной документации рекомендуется немного другой подход - половина озу. Если система ведет себя крайне неадекватно, то это первый параметр который надо проверять. Например когда добавлять ОЗУ вируалке, его часто забывают корректировать.

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

Пример настроек в зависимости от количества Gb

Процессор

VACUUM и AUTOVACUUM — это важные механизмы в PostgreSQL, которые отвечают за обслуживание базы данных, освобождение места и поддержание производительности. Они помогают управлять "мертвыми" (устаревшими) строками и поддерживать эффективность работы базы данных. Давайте разберем их подробнее.

AUTOVACUUM — это автоматизированный процесс, который выполняет те же задачи, что и ручной VACUUM, но без вмешательства администратора. Он включается по умолчанию в PostgreSQL и работает в фоновом режиме.

Преимущества AUTOVACUUM:

• Автоматизация: Не требует ручного запуска.
• Поддержание производительности: Регулярно очищает "мертвые" строки, предотвращая снижение производительности.
• Адаптивность: Настраивается в зависимости от активности в базе данных.

Параметры:

    autovacuum = on
    autovacuum_max_workers = 4

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

При действующем по умолчанию значении -1 этот объём определяется значением maintenance_work_mem (со значением по умолчанию 64MB).

Учтите что слишком сильное увеличение числа рабочих ролей автоматической очистки приведет к увеличению потребления памяти и в зависимости от значения maintenance_work_mem может привести к снижению производительности. Каждый рабочий процесс автоматической очистки получает только (1/autovacuum_max_workers) от общего числа autovacuum_cost_limit, поэтому наличие большого количества рабочих ролей приводит к замедлению работы каждой из них. Тут необходимо искать баланс.

    autovacuum_vacuum_cost_limit = -1

Параметр работает в паре с предыдущим. Просто увеличение количество воркеров – не даст никакого эффекта, потому что PostgreSQL считает суммарную стоимость работы каждого воркера – autovacuum_vacuum_cost_limit (по умолчанию равна 400). Например, он прочитал страницу – это стоит один балл. Он удалил запись с этой странички, это стоит 5 баллов. И эта стоимость указана в настройках. Посмотрите текущее значение параметра таким образом:

    autovacuum_vacuum_cost_limit;

Откуда берутся баллы. Это условная стоимость затрачиваемых ресурсов. Компоненты затрат:

• параметр vacuum_cost_page_hit: затраты на чтение страницы, которая уже находится в общих буферах и не требует чтения с диска. По умолчанию задано значение 1.
• vacuum_cost_page_miss: затраты на загрузку страницы, которой нет в общих буферах. Значение по умолчанию — 10. Т.е. типа диск в 10 раз тяжелее чтения озу.
• vacuum_cost_page_dirty: затраты на запись на страницу при обнаружении на ней неиспользуемых кортежей. Значение по умолчанию: 20.

По умолчанию для autovacuum_vacuum_cost_limit установлено значение –1, то есть ограничение затрат на автоматическую очистку совпадает со значением параметра vacuum_cost_limit, которое по умолчанию равно 200. vacuum_cost_limit — это затраты на очистку вручную.

    autovacuum_vacuum_cost_delay = 20 mc

Посмотрите на ваше текущее значение параметра. Это можно сделать так:

    SELECT name, setting, unit, boot_val, reset_val FROM pg_settings WHERE name = 'autovacuum_vacuum_cost_delay'

Это время в мс означает паузу между итерациями, в каждой итерации все потоки автоваукуума останавливаются по достижению лимита autovacuum_vacuum_cost_limit , и затем ждут время, указанное в этом параметре. Слишком маленькое время может создать избыточную нагрузку. Некоторые предлагают ставить 20 миллисекунд , но можно отталкиваться и от текущей загруженности.Временно на период интенсивной записи в большие таблицы некоторые рекомендация дают от 50 до 200 мс. Для ещё большего временно го эффекта вы можете повысить vacuum_cost_page_hit , например до 6, и понизить vacuum_cost_limit до 100 (по умолчанию 400), учтите что при этом влияние VACUUM может уменьшится более чем на 80%, но его длительность увеличилась в трое;

    autovacuum_vacuum_scale_factor = 0.1 -> 0.01

Значение этого параметра может требовать редактирования по мере роста базы. Пока база маленькая, значение по умолчанию 10% отражает реальный объем изменяемых данных и не требует правок. Однако по мере роста базы, оперативная часть вводимых данных будет уменьшаться относительно объема архивных неизменяемых данных. Если у вас введено базу 100 месяцев работы, то можно перейти на 1% (0.01)

    autovacuum_analyze_scale_factor = 0.2 -> 0.05

Параметр выпадающий из этой статьи, но особенности обсчета показаны здесь. Значение по умолчанию 20% означает объем изменений в таблицах, после которых обновляется статистика таблиц. Как и предыдущий параметр, его значение оптимально уменьшать по мере роста данных. Если размер базы в сотнях гигабайт или тысячах пользователей есть советы пробовать ставить 0.05, но вы можете и более агрессивные маленькие значения ставить, если понимаете что делаете.

Указанные параметры можно задать так:

    ALTER SYSTEM SET autovacuum TO on;
    ALTER SYSTEM SET autovacuum_max_workers TO 4;
    ALTER SYSTEM SET autovacuum_vacuum_cost_limit TO -1;
    ALTER SYSTEM SET autovacuum_vacuum_cost_delay TO 20;
    ALTER SYSTEM SET autovacuum_vacuum_scale_factor TO 0.01;
    ALTER SYSTEM SET autovacuum_analyze_scale_factor TO 0.005;
    SELECT pg_reload_conf();

учтите что параметры будут писаться не в postgresql.conf, а в более приоритетный postgresql.auto.conf, поэтому сбрасывать если потребуется надо будет через ALTER SYSTEM RESET <параметр>; или ALTER SYSTEM RESET ALL;

Генерируемая нагрузка.

Если autovacuum_vacuum_cost_limit — 200, а autovacuum_vacuum_cost_delay составляет 2 миллисекунды, то допустим автоматическая очистка активируется 50 раз (50*20 мс = 1000 мс) каждую секунду. При каждой активации автоматическая очистка считывает 200 страниц. Это означает, что за одну секунду автоматическая очистка может обработать следующий объем:

~80 МБ/с [ (200 страниц/vacuum_cost_page_hit) * 50 * 8 КБ на страницу], если все страницы с неиспользуемыми кортежами находятся в общих буферах.

~8 МБ/с [ (200 страниц/vacuum_cost_page_miss) * 50 * 8 КБ на страницу], если все страницы с неиспользуемыми кортежами считываются с диска.

~4 МБ/с [ (200 страниц/vacuum_cost_page_dirty) * 50 * 8 КБ на страницу] — скорость записи автоматической очистки.

Ваш диск должен быть способен выдавать необходимые Mb/сек со случайной записью/чтения. Т.е просто тупо копировать настройки нельзя, надо учитывать скоростные реальные характеристики ваших дисков в реальном тесте, а не на сайте вендора.

Мониторинг процессов автоматической очистки и возможные проблемы

можно делать запросом:

    select count(1) from pg_stat_progress_vacuum;

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

Автоматическая очистка выполняется непрерывно.

Непрерывное выполнение автоматической очистки может повлиять на использование ЦП и операций ввода-вывода на сервере. Возможны следующие причины: maintenance_work_mem

Управляющая программа автоматической очистки использует параметр autovacuum_work_mem, для которого по умолчанию задано -1, то есть у autovacuum_work_mem будет то же значение, что и у параметра maintenance_work_mem. Обычно для autovacuum_work_mem установлено значение -1, а управляющая программа автоматической очистки использует maintenance_work_mem.

Если для параметра maintenance_work_mem установлено низкое значение, его можно увеличить. Как правило, рекомендуется выделить 50 МБ на maintenance_work_mem для каждого 1 ГБ ОЗУ. 

Большое количество баз данных

Автоматическая очистка пытается запустить рабочую роль в каждой базе данных каждые autovacuum_naptime сек.

Например, если на сервере имеется 60 баз данных, а для autovacuum_naptime задано 60 секунд, рабочая роль автоматической очистки запускается каждую секунду [autovacuum_naptime/число баз данных].

Рекомендуется увеличить значение autovacuum_naptime, если в кластере больше баз данных. В то же время процесс автоматической очистки можно сделать более агрессивным, увеличив значение параметра autovacuum_cost_limit и уменьшив значение параметра autovacuum_cost_delay, а также увеличив значение параметра autovacuum_max_workers с 3 (по умолчанию) до 4 или 5.

Ошибки, связанные с нехваткой памяти

Чрезмерно агрессивные значения maintenance_work_mem могут периодически вызывать ошибки нехватки памяти в системе. Прежде чем вносить изменения в параметр maintenance_work_mem, важно понимать доступный на сервере объем ОЗУ.

Если автоматическая очистка мешает работе, рекомендуется сделать следующее:

Увеличьте значение параметра autovacuum_vacuum_cost_delay и уменьшите значение параметра autovacuum_vacuum_cost_limit, если задано значение больше 200 (значение по умолчанию).Уменьшите число autovacuum_max_workers, если оно больше 3 (значение по умолчанию). 

Продолжительные транзакции

Длительные транзакции в системе не позволяют удалять неиспользуемые версии данных во время выполнения автоматической очистки. Они блокируют процесс очистки. Удаление длительных транзакций освобождает неиспользуемые версии данных для удаления при запуске автоматической очистки.Длительные транзакции можно обнаружить с помощью следующего запроса:

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query FROM pg_stat_activity WHERE state != 'idle' ORDER BY 2 DESC LIMIT 10;

Для очень больших таблиц вы можете выставить индивидуальные параметры для автоочистки и анализа:

    ALTER TABLE вашатаблица SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE вашатаблица SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE вашатаблица SET (autovacuum_vacuum_scale_factor =xx); 
    ALTER TABLE вашатаблица SET (autovacuum_vacuum_threshold = xx); 
    ALTER TABLE вашатаблица SET (autovacuum_vacuum_cost_delay = xx); 
    ALTER TABLE вашатаблица SET (autovacuum_vacuum_cost_limit = xx);

но помните про возможность удаления их некоторыми действиями платформы 1С в результате реструктуризации

Распараллеливание запросов

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

Как работает распараллеливание запросов?

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

Основные операции, которые могут быть распараллелены:

• Последовательное сканирование (Sequential Scan)
• Сканирование индекса (Index Scan)
• Соединения (Joins)
• Сортировка (Sort)
• Агрегация (Aggregation)

Минимальное ограничение количества рабочих процессов на распараллеливание исполнения запроса задает параметр max_parallel_workers_per_gather.

Это настройка указывает, сколько максимум в плане запроса будет потоков у оператора "сбора" (Gather), фрагмент узла плана запросов в качестве примера: Gather (cost=1000.00..217018.43 rows=1 width=97) Workers Planned: 2

Потом исполнитель запросов берет рабочие процессы из пула, ограниченного параметром max_parallel_workers - указывающим сколько всего может быть параллельных потоков.

Последнее ограничение на распараллеливание исполнения запроса — это max_worker_processes, то есть общее число фоновых процессов. Общее количество рабочих процессов может совпадать с количеством потоков под параллельную обработку, или быть больше, но не меньше. Дефолтовое значение 8. В максимальном значении = числу ядер процессор. Обратите внимание что при изменении настройки нужен рестарт службы PostgreSQL. Обратите внимание! Если субд может сильно распараллелить запрос, то как правило, обычно это означает недостаточную оптимизацию кода 1С.Если не удалось выделить рабочий процесс, обработка будет однопроцессной. max_parallel_maintenance_workers - задаёт максимальное число рабочих процессов, которые могут запускаться одной служебной командой.

Перейдем к примеру настроек в зависимости от количества ядер

Пример настроек в зависимости от количества ядер

    max_parallel_workers_per_gather  2;

• 2 это дефолтовое значение
• Значение 0 означает что не будет параллелизма, чем больше конкуренции за ресурcы, больше блокировок
• Чем выше загруженность процессора, тем меньше это значение. При одном пользователи значение будет максимальным
• Можно также пробовать при закрытии месяца, реструкторизации, загрузке dt в один поток 1с разрешать большие значения.
• При многопоточной загрузке dt 1С наоборот отключать ставя 0

    max_parallel_workers  4;

• 8 - дефолтовое значение
• рекомендуется подбирать настройку смотря на загруженность процессора, при загруженности меньше 30%
• пробуйте добавить пару ядер, перечитать настройки и оценить новую загруженность

    max_worker_processes  4;

• 8 - дефолтовое значение
• обычно совпадает c max_parallel_workers

    parallel_leader_participation  on;

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

    max_parallel_maintenance_workers 0;

Дисковая система

Выбор типа дисков

SSD (предпочтительно):

SSD обеспечивают высокую скорость операций ввода-вывода (IOPS) и низкую задержку, что критично для производительности PostgreSQL. Рекомендуется использовать SSD для всех компонентов: данных, индексов, журналов транзакций (WAL).

HDD:

Если бюджет ограничен, HDD можно использовать для хранения резервных копий или архивных данных. Для рабочих данных HDD не рекомендуются из-за низкой скорости операций ввода-вывода

RAID-массивы

RAID 10 (предпочтительно):

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

RAID 5:

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

RAID 0:

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

Файловая система

Linux (рекомендуется):

• Используйте файловую систему ext4 или XFS. XFS предпочтительнее для больших объемов данных.
• Настройте монтирование с опциями, повышающими производительность: noatime,data=writeback,barrier=0
  • noatime: Отключает обновление времени доступа к файлам, снижая нагрузку на диск.
  • data=writeback: Улучшает производительность записи.
  • barrier=0: Отключает барьеры записи (используйте с осторожностью, только если есть UPS).

Windows:

• Используйте NTFS с настройкой размера кластера 64 КБ для больших таблиц.

Разделение данных

Разделение табличного пространства (Tablespaces):

Разместите данные, индексы и журналы транзакций (WAL) на разных физических дисках для распределения нагрузки.

    CREATE TABLESPACE data LOCATION '/path/to/data';
    CREATE TABLESPACE index LOCATION '/path/to/index';
    CREATE TABLESPACE wal LOCATION '/path/to/wal';

Журнал транзакций (WAL)

WAL — это критически важный компонент для производительности и надежности. Разместите его на отдельном быстром SSD.

Дополнительные параметры влияющие на производительность

effective_io_concurrency = 2 (только для Linux систем с управлением буферизацией файлового ввода-вывода, осуществляемой в ядре, не применять для Windows)

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

Диски SSD и другие виды хранилища в памяти часто могут обрабатывать множество параллельных запросов, так что оптимальным числом может быть несколько сотен.

Пример допустимого числа параллельных операций ввода/вывода от количества дисков.

Константы стоимости дисков для оптимизатора

random_page_cost = 1.5-2.0 для RAID, 1.1-1.3 для SSD, 0.4 для NVMe - cтоимость чтения случайной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PgSQL к выбору планов с сканированием всей таблицы (PgSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). И это плохо.

seq_page_cost = 0.1 для NVMe дисков, последовательное чтение всегда быстрее случайного, определяет стоимость сканирования таблиц и индексов

Надежность против производительности

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

full_page_writes= on - гарантирует целостность записи в страницу. Сохранение образа всей страницы гарантирует, что страницу можно восстановить корректно, ценой увеличения объёма данных, которые будут записываться в WAL. (Так как воспроизведение WAL всегда начинается от контрольной точки, достаточно сделать это при первом изменении каждой страницы после контрольной точки. Таким образом, уменьшить затраты на запись полных страниц можно, увеличив интервалы контрольных точек checkpoint_timeout.)Отключение этого параметра ускоряет обычные операции, но может привести к неисправимому повреждению или незаметной порче данных после сбоя системы. Так как при этом возникают практически те же риски, что и при отключении fsync, хотя и в меньшей степени, отключать его следует только при тех же обстоятельствах, которые перечислялись в рекомендациях для вышеописанного параметра.

synchronous_commit = on - выключение (off) синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность.  Оно Включает/выключает синхронную запись в лог-файлы после каждой транзакции. Включение синхронной записи защищает от возможной потери данных. Но, накладывает ограничение на пропускную способность сервера. Вы можете отключить синхронную запись, если вам необходимо обеспечить более высокую производительность по количеству транзакций. А потенциально низкая возможность потери небольшого количества изменений при крахе системы не критична. Для отключения синхронной записи установите значение off в этом параметре.

Еще одним способом увеличения производительности работы PostgreSQL является перенос журнала транзакций (pg_xlog) на другой диск. Выделение для журнала транзакций отдельного дискового ресурса позволяет получить получить при этом существенный выигрыш в производительности 10%-12% для нагруженных OLTP систем. В Linux это делается с помощью создания символьной ссылки на новое положение каталога с журналом транзакций.

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

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

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

commit_delay = 1000 пауза (в микросекундах) перед собственно выполнением сохранения WAL

commit_siblings = 5 Минимальное число одновременно открытых транзакций, при котором будет добавляться задержка commit_delay. Групповой коммит нескольких транзакций. Имеет смысл включать, если темп транзакций превосходит 1000 TPS. Иначе эффекта не имеет.

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

checkpoint_completion_target = 0.5..0.9 Степень "размазывания" checkpoint'a. Скорость записи во время checkpoint'а регулируется так, что бы время checkpoint'а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_target.

min_wal_size = 512MB .. 4G  и max_wal_size = 2 * min_wal_size - минимальное и максимальный объем WAL файлов. Аналогично checkpoint_segments

    ALTER SYSTEM SET min_wal_size TO '512 MB'; --
    ALTER SYSTEM SET max_wal_size TO '1024 MB';--

Таблица зависимости от размера базы данных 

Влияние количества файлов, а не дисков

max_files_per_process = 1000 (default) Максимальное количество открытых файлов на один процесс PostreSQL. Один файл это как минимум либо индекс либо таблица, но таблица/может состоять из нескольких файлов. Если PostgreSQL упирается в этот лимит, он начинает открывать/закрывать файлы, что может сказываться на производительности. Диагностировать проблему под Linux можно с помощью команды lsof. Уменьшите данный параметр, если в процессе работы наблюдается сообщение «Too many open files».

Разнесение по разным дискам при высокой нагрузке на основной диск или большом количестве файлов в каталоге

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

Безопасность против производительности

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

Сеть

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

max_connections = 500..1000 Количество одновременных коннектов/сессийЕсли у Вас больше 100 пользователей, то лучше указать вручную значение для этого параметра по количеству пользователей

Типовая проблема в Windows

Ошибка СУБД: could not send data to server: No buffer space available (0x00002747/10055)

При использовании операционной системы Windows, максимальное стандартное число временных TCP-портов равно 5000. При попытке установить TCP-соединение через порты, номера которых превышают 5000, выдается сообщение об ошибке. Другими словами, надо увеличить количество доступных портов в реестре, где выберите Parameters (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters) и добавьте следующий параметр реестра  MaxUserPort с типом: DWORD и значением: 65534 (Допустимые значения: 5000-65534)

Блокировки

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

Диагностика блокировок Postgre вручную

Блокировки БД по пользователям

    select a.usename, count(l.pid) from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not(mode = ‘AccessShareLock’) group by a.usename;

Список текущих блокировок в postgres

    SELECT l.mode,d.datname,c.relname,l.granted,l.transactionid 
    FROM pg_locks AS l
    LEFT JOIN pg_database AS d ON l.database = d.oid
    LEFT JOIN pg_class AS c ON l.relation = c.oid;

Пользователи блокирующие конкретную таблицу

    select a.usename, t.relname, a.current_query, mode 
    from pg_locks l 
    inner join pg_stat_activity a on a.procpid = l.pid 
    inner join pg_stat_all_tables t on t.relid=l.relation where t.relname = ‘tablename’;

    select relation::regclass, mode, a.usename, granted, pid 
    from pg_locks l 
    inner join pg_stat_activity a on a.procpid = l.pid 
    where not mode = ‘AccessShareLock’ and relation is not null;

Запросы с эксклюзивными блокировками

    select a.usename, a.current_query, mode 
    from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid 
    where mode ilike ‘%exclusive%’;

Транзакции, ожидающие на блокировках Postgres

    SELECT blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process,
    blocked_activity.application_name AS blocked_application,
    blocking_activity.application_name AS blocking_application
    FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locksON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT 
    FROM blocked_locks.DATABASEAND blocking_locks.relation IS NOT DISTINCT 
    FROM blocked_locks.relationAND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED;

Неустановленные блокировки postgres

    SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

Настройки под платформу 1С

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

escape_string_warning = off Не выдавать предупреждение о использовании символа \ для экранирования shared_preload_libraries = 'plantuner' - несколько разделяемых библиотек, которые будут загружаться при запуске сервера, если указанная в нём библиотека не будет найдена, сервер не запустится, настройка параметра имеет больше значения для linux, хотя и windows её делать тоже стоит Модуль plantuner добавляет поддержку указаний для планировщика, позволяющих отключать или подключать определённые индексы при выполнении запроса. plantuner.fix_empty_table = 'on' - plantuner будет обнулять число страниц/кортежей в таблице, которая не содержит никаких блоков в файле

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

Варианты решения проблемы:

1. Увеличить количество записей, просматриваемых при сборе статистики по таблицам. Большие значения могут повысить время выполнения команды ANALYZE, но улучшат построение плана запроса:
   • Файл postgresql.conf — default_statistics_target = 1000 -10000.
2. Отключение оптимизатору возможности использования NESTED LOOP при выборе плана выполнения запроса в конфигурации PostgreSQL:
   • Файл postgresql.conf — enable_nestloop = off.
   • Отрицательным эффектом этого способа является возможное замедление некоторых запросов, поскольку при их выполении будут использоваться другие, более затратные, методы соединения (HASH JOIN).
3. Отключение оптимизатору возможности изменения порядка соединений таблиц в запросе:
   • Файл postgresql.conf — join_collapse_limit=1.
   • Следует использовать этот метод, если вы уверены в правильности порядка соединений таблиц в проблемном запросе.
4. Изменение параметров настройки оптимизатора:
   • Файл postgresql.conf:
     • seq_page_cost = 0.1
     • random_page_cost = 0.4
     • cpu_operator_cost = 0.00025
5. Использование версии PostgreSQL 9.1.2-1.1.C и выше, в которой реализован независимый от AUTOVACUUM сбор статистики, на основе информации об изменении данных в таблице. По умолчанию включен сбор статистики только для временных таблиц и во многих ситуациях этого достаточно. При возникновении проблем с производительностью выполнения регламентных операций, можно включить сбор статистики для всех или отдельных проблемных таблиц изменив значение параметра конфигурации PostgreSQL (файл postgresql.conf) online_analyze.table_type = "temporary" на online_analyze.table_type = "all".

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

Оптимизатор/Планировщик запросов

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

ВАЖНОЕ: Если вы не понимаете, что точно означает параметр и что конкретно изменится при его установке или изменении, то данный параметр не надо трогать и следует оставить его в значении по умолчанию.

Статистика

    default_statistics_target = 1000 -10000

Улучшение статистики оптимизатора/  Значение этого параметра по умолчанию — 100

Cтатистика собирается командой ANALYZE. Не путать с EXPLAIN ANALYZE (это другое).

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

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

За анализ отвечает параметр autovacuum_analyze_scale_factor отвечающий за процент данных, при изменнении которого вызывается новый анализ статистики таблицы.

Можно вручную вызывать обсчет запросом командой (парамерт skip_locked пропускает заблокированные данные), но сама команда ANALYZE запрашивает для целевой таблицы блокировку только на чтение, так что эта команда может выполняться параллельно с другими операциями с таблицей.:ANALYZE SKIP_LOCKEDв команде можно указать конкретную таблицу или даже колонку.Обязательно выполняйте обсчет статистики после загрузки данных из dt 1C.

Параметр default_statistics_target отвечает за значение ориентира статистики по умолчанию (дефолтовые 100 записей, не процентов). Чем больше установленное значение, тем больше времени требуется для выполнения ANALYZE, но тем выше может быть качество оценок планировщика.У себя посмотреть можете так:

    SHOW default_statistics_target

В больших таблицах ANALYZE не просматривает все строки, а обрабатывает только небольшую случайную выборку.Вы можете для крупных таблиц 1С вручную задать свою выборку «ALTER TABLE вашатаблица SET STATISTICS» в диапазоне 0..10000 , но платформа 1С в результате реструкторизации может это сбросить, так что учитывайте это.

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

    SELECT *FROM pg_statsWHERE tablename = 'ваша таблица';

Если на большой таблице обсчет выполняется долго, то о ходе процесса можно подробней узнать через представление pg_stat_progress_analyze примерно так:

    SELECT *FROM pg_stat_progress_analyze;

При вызове утилиты через строку /usr/bin/vacuumdb для обсчета передаются ключи--analyze или -aПример точечного обсчета статистики например возможно полезного при работ по оптимизации запроса:vacuumdb --analyze --table='вашатаблица(вашаколонка)' вашабаза

Регламенты.

Каждую ночь можно выполнять вот так:

    /usr/bin/vacuumdb -h 127.0.0.1 -p 5432 -U postgres -a -Z -j 4

анализ всех баз в 4 потока

Синхронное автообновление статистики online_analyze

Если в процессах автовакуума также выполнялось асинхронное обновление статистики, то в ряде случаев может потребоваться синхронное обновление статистики после операций INSERT, UPDATE, DELETE или SELECT INTO в целевых таблицах. Особо это актуально для пользователей 1С старых версий платформы (до 8.3.13).Также модуль online_analyze можно включить, если есть основания полагать, что фоновое обновление не дает нужного результата или оптимизатор часто ошибается в оценке количества строк.

Для тех кто сидит версий платформы (до 8.3.13) может существать проблема отсутствия статистики для временных таблиц (при условии что в них вставляются большое количество строк). Платформа 1С:Предприятие самостоятельно НЕ включала использование analyze после вставки во временную таблицу.

Включить временно в рамках сессии (например вы закрываете месяц в 1С, и до следующего раза вам синхронное обновление не нужно) можно командой:load 'online_analyze';либо на постоянной основе, для чего убедимся что уже не включен указанием в списке подгружаемых модулей (упоминание «online_analyze»:

    select setting from pg_settings where name=’shared_preload_libraries’

модуля в параметр shared_preload_librariesдля чего посмотрим где расположен файл конфигурации postgresql.conf экзепляра PostgreSQL командой:

    SHOW config_file;

в данном примере будет /var/lib/pgpro/1c-14/data/postgresql.conf , отредактируем файл:

    nano /var/lib/pgpro/1c-14/data/postgresql.conf

находим там shared_preload_libraries и корректируем если требуется

    shared_preload_libraries = ‘online_analyze’

сохраняем изменения ctrl o, и выходим ctrl x

но это только подключен, а чтобы он заработал надо задать параметр командой:

    ALTER SYSTEM SET online_analyze.enable TO on;

и перечитать настройки postgresql.conf:

    SELECT pg_reload_conf();

БЕЗ ВКЛЮЧЕНИЯ ПАРАМЕТРА online_analyze.enable НИЖЕ ПЕРЕЧИСЛЕННЫЕ НАСТРОЙКИ ПРОСТО НЕ БУДУТ РАБОТАТЬ!

Укажем что мы будем использовать синхронную статистику только для временных таблиц

    ALTER SYSTEM SET online_analyze.table_type TO 'temporary';

Возможные значения: Типы таблиц, для которых выполняется немедленный анализ: all (все), persistent (постоянные), temporary (временные), none (никакие). Но «all» — больше нагрузка, время запросов увеличится на время обсчета, более компромисный вариант temporary.

Остальные параметры, которые можете задать аналогичным образом:

online_analyze.threshold = 50 — Минимальное число изменений строк, после которого может начаться немедленный анализ (этот параметр подобен autovacuum_analyze_threshold).

online_analyze.scale_factor = 0.1 — Процент от размера таблицы, при котором начинается немедленный анализ (этот параметр подобен autovacuum_analyze_scale_factor).

online_analyze.min_interval = 10000 — Минимальный интервал времени между вызовами ANALYZE для отдельной таблицы (в миллисекундах).

online_analyze.local_tracking = off — Включает в online_analyze отслеживание временных таблиц в рамках обслуживающего процесса. Когда эта переменная отключена (off),

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

online_analyze.verbose = ‘off’ — Отключает подробные сообщения расширения

online_analyze.include_tables = «» -Список таблиц, исключаемых из немедленного анализа.

При использовании синхронного обновления вы должны понимать, что дополнительное время запросов на обсчет статистики будет меньше, чем текущее время проблемного запроса, иначе пользы не будет. В планах запросов «explain (analyze,buffers) вашзапрос» смортите чтобы Planning time не был на несколько порядков меньше Execution time.

Отключение соединений

    enable_nestloop=off, 
    enable_mergejoin=off 

(Изменение параметров оптимизатора)

• Включает или отключает использование планировщиком планов соединения с вложенными циклами
• Включает или отключает использование планов соединения слиянием.Например ошибки типа out of memory или в старых конфигурациях с полным соединением или расчетом себестоимости

Влияние на соединения

join_collapse_limit = 20 (8 default) - задаёт максимальное количество элементов в списке FROM, до достижения которого планировщик будет сносить в него явные конструкции JOIN (за исключением FULL JOIN). При меньших значениях сокращается время планирования, но план запроса может стать менее эффективным.

В случае join_collapse_limit=1 (отключение при понимании порядка соединений таблиц!) ,при значении, равном 1, предложения JOIN переставляться не будут, так что явно заданный в запросе порядок соединений определит фактический порядок, в котором будут соединяться отношения.

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

Распараллеливание запросов

Сам факт включения более одного потока для выполнения распараллевания не гарантирует что оно произойдет.Параметр parallel_setup_cost указывает стоимостный порог параллельной обработки, значение по умолчанию 1000. Т.е. слишком простые и недорогие запросы не выгодно параллелить, на сборе потоков больше штраф будет. Вы можете поднять значение если процессор перегружен.Параметр parallel_tuple_cost -стоимости перевода версии данных от одного потока к другому. Дефолтовое значение 0.1. Например на 4х и 8-ми сокетных серверах эта стоимость может быть выше, если процессы окажутся в разных нума-узлах.

Также нет смысла расспараллевать сканирование слишком маленькой таблицы. Порог задается параметром min_parallel_table_scan_size данных (по умолчанию 8MB), в таблице показана зависимость числа потоков от размера таблицы (x => log(x / min_parallel_table_scan_size) / log(3) + 1 worker):

Аналогичным образом для индексов работает параметр min_parallel_index_scan_size.Дефолтовое значение 512 килобайт, но при перечислении 64 страниц мы это значение превысим, будет также подключаться дополнительный рабочий процесс для параллельной обработки.

Параметром enable_parallel_append мы можем включить параллелизм для соединения двух запросов операцией «ОБЪЕДИНИТЬ» или «ОБЪЕДИНИТЬ ВСЕ». Если этот параметр отключен, конструкции такого вида на Postgres не будут параллелиться. По умолчанию, включен (установлен в ON).

enable_parallel_hash – параллельная сборка по хэшу. По умолчанию тоже включен (установлено значение ON). Есть параметр force_parallel_mode – сейчас всегда по умолчанию в OFF. Экспериментальный режим тотального распараллелиования.

Эвристический поиск вместо полного перебора отношений

geqo = on (default) - для запросов со множеством таблиц обычное планирование (перебор всех вариантов) может занять слишком много времени, в этом случае выгоднее потерять на качестве плана, но выполнить планирование быстро.

geqo_threshold = 8 (12 default) -задаёт минимальное число элементов во FROM, при котором сработает этот механизм

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

Источники:

gilev.ru

postgresql.org

postgrespro.ru