Postgresql 1c настройка производительности

Обновлено: 06.07.2024

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

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

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

Настраиваемые параметры PostgreSQL

PostgreSQL использует свой собственный буфер, а также использует буферизованный IO ядра. Это означает, что данные хранятся в памяти дважды, сначала в буфере PostgreSQL, а затем в буфере ядра. В отличие от других баз данных, PostgreSQL не обеспечивает прямой ввод-вывод. Это называется двойной буферизацией. Буфер PostgreSQL называется shared_buffer, который является наиболее эффективным настраиваемым параметром для большинства операционных систем. Этот параметр устанавливает, сколько выделенной памяти будет использоваться PostgreSQL для кеширования.

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

Рекомендуемое значение составляет 25% от общего объема оперативной памяти компьютера. Вам следует попробовать некоторые более низкие и более высокие значения, потому что в некоторых случаях можно получить хорошую производительность с настройкой более 25%. Но реальная конфигурация зависит от вашей машины и рабочего набора данных. Если ваш рабочий набор данных может легко поместиться в вашу оперативную память, вы можете увеличить значение shared_buffer, чтобы оно содержало всю вашу базу данных и чтобы весь рабочий набор данных мог находиться в кеше. Тем не менее, вы, очевидно, не хотите резервировать всю оперативную память для PostgreSQL.

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

Проверка значения shared_buffer

Примечание: Будьте осторожны, так как некоторые ядра не поддерживают большее значение, особенно в Windows.

wal_buffers

PostgreSQL сначала записывает записи в WAL (журнал предзаписи) в буферы, а затем эти буферы сбрасываются на диск. Размер буфера по умолчанию, определенный wal_buffers, составляет 16 МБ. Но если у вас много одновременных подключений, то более высокое значение может повысить производительность.

effective_cache_size

effective_cache_size предоставляет оценку памяти, доступной для кэширования диска. Это всего лишь ориентир, а не точный объем выделенной памяти или кеша. Он не выделяет фактическую память, но сообщает оптимизатору объем кеша, доступный в ядре. Если значение этого параметра установлено слишком низким, планировщик запросов может принять решение не использовать некоторые индексы, даже если они будут полезны. Поэтому установка большого значения всегда имеет смысл.

work_mem

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

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

Первоначальный узел сортировки запроса оценивается в 514431,86. Стоимость — это произвольная вычисляемая единица. Для приведенного выше запроса у нас work_mem всего 2 МБ. В целях тестирования давайте увеличим это значение до 256 МБ и посмотрим, повлияет ли это на стоимость.

Стоимость запроса снижена с 514431,86 до 360617,36, то есть уменьшилась на 30%.

maintenance_work_mem

maintenance_work_mem — это параметр памяти, используемый для задач обслуживания. Значение по умолчанию составляет 64 МБ. Установка большого значения помогает в таких задачах, как VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY и ALTER TABLE.

Время создания индекса составляет 170091,371 мс, если для параметра maintenance_work_mem установлено значение только 10 МБ, но оно уменьшается до 111274,903 мс, когда мы увеличиваем значение параметра maintenance_work_mem до 256 МБ.

synchronous_commit

checkpoint_timeout, checkpoint_completion_target

PostgreSQL записывает изменения в WAL. Процесс контрольной точки сбрасывает данные в файлы. Это действие выполняется, когда возникает контрольная точка (CHECKPOINT). Это дорогостоящая операция и может вызвать огромное количество операций IO. Весь этот процесс включает в себя дорогостоящие операции чтения/записи на диск. Пользователи могут всегда запустить задание контрольной точки (CHECKPOINT), когда это необходимо, или автоматизировать запуск с помощью параметров checkpoint_timeout и checkpoint_completion_target.

Параметр checkpoint_timeout используется для установки времени между контрольными точками WAL. Установка слишком низкого значения уменьшает время восстановления после сбоя, поскольку на диск записывается больше данных, но это также снижает производительность, поскольку каждая контрольная точка в конечном итоге потребляет ценные системные ресурсы. checkpoint_completion_target — это доля времени между контрольными точками для завершения контрольной точки. Высокая частота контрольных точек может повлиять на производительность. Для плавного выполнения задания контрольной точки, checkpoint_timeout должен иметь низкое значение. В противном случае ОС будет накапливать все грязные страницы до тех пор, пока соотношение не будет соблюдено, а затем производить большой сброс.

Заключение

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

Настройки PostgreSQL для работы с 1С:Предприятием. Часть 2

В статье описывается настройка PostgreSQL версий 9.6 и выше на максимальную производительность для работы с Платформой 1С:Предприятие. Предполагается, что сервер СУБД PostgreSQL является достаточно производительным и имеет не менее:

8 - 512 Gb RAM
4 - 256 CPU cores
RAID 0-1 или SSD

Рекомендуемые значения индивидуальны и зависят от системы и нагрузки на нее.

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

Настройки сервера для PostgreSQL

Рекомендуется изменять значение по умолчанию пути к директории pg_stat_temp так, чтобы она находилась отдельно от директории кластера. Причина в интенсивном изменении файлов в этой директории, что создает значительную нагрузку на дисковую подсистему. Директорию рекомендуется размещать в RAM-диске (для Windows) или tmpfs (для linux).

Параметры клиентских сеансов

row_security = off >= 9.5

Отключение контроля на уровне записей.

Параметры подключений

Выключение шифрования, которое может приводить к увеличению загрузки CPU.

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

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

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

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

Настройки WAL

Сброс буферов на диск (выполнение PostgerSQL системных вызовов fsync()). Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания.

Внимание: если RAID имеет кэш и находиться в режиме write-back, проверьте наличие и функциональность батарейки кэша RAID контроллера! Иначе данные, записанные в кэш RAID, могут быть потеряны при выключении питания, и, как следствие, PostgreSQL не гарантирует целостность данных.

Выключение синхронной записи в WAL момент коммита транзакции. Создает риск потери последних нескольких транзакций (в течении 0.5-1" секунды), но гарантирует целостность базы данных. Может значительно увеличить производительность.

checkpoint_segments = 32..256 < 9.5

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

Степень "размазывания" 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.

Фоновая запись на диск

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

bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400

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

Настройки выполнения очистки (автовакуума)

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

autovacuum_max_workers =" CPU "cores/4..2 но не меньше 4

Количество процессов автовакуума. Общее правило - чем больше запросов на запись выполняется в системе (такие системы называются OLTP), тем больше процессов.

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

Использование ресурсов ядра

Значение по умолчанию – 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, до которого планировщик будет объединять вложенные запросы с внешним запросом. При меньших значениях сокращается время планирования, но план запроса может стать менее эффективным.

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

GEQO - генетический оптимизатор запросов PоstgreSQL, который осуществляет планирование запросов, применяя эвристический поиск вместо полного перебора отношений. Он позволяет сократить время планирования для сложных запросов с большим числом соединений, потому не рекомендуется его отключать. Однако надо учитывать, что полученный им план может оказаться менее эффективным и, как следствие, увеличится время выполнения запроса. Управлять его включением более тонко помогает следующий параметр:

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

Асинхронное поведение

Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Допустимый диапазон от 1 до 1000. Значение по умолчанию равно 1, где это поддерживается, в остальных системах - 0.

Для одиночного диска можно условно поставить 1, для RAID - 2 или больше.

Сейчас эта оценка влияет только на выбор bitmap heap scan.

Параметры для платформы 1С:Предприятия

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

Не выдавать предупреждение о использовании символа \ для экранирования.

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

Количество одновременных соединений.

Параметры дополнительных модулей

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

Все остальные параметры имеют смысл, только если online_analyze.enable = on.

Включает синхронное автообновление статистики на временных таблицах.

Выполнение инструкции ANALYZE без опции VERBOSE.

Минимальное количество записей, предшествующее обновлению статистики.

«Доля» в величине таблицы, начиная с которой будет происходить автообновление.

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

Оптимизация производительности PostgreSQL для работы с 1С:Предприятие

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

Существуют разные рекомендации по оптимизации PostgreSQL для совместной работы с 1С, мы будем опираться на официальные рекомендации, изложенные на ИТС, также можно использовать онлайн-калькулятор для быстрого расчета некоторых параметров. Если данные калькулятора и рекомендации 1С будут расходиться - то предпочтение будет отдано рекомендациям 1С.

Для тестирования мы использовали систему:

CPU - Core i5-4670 - 3.4/3.8 ГГц
RAM - 32 ГБ DDR3
Системный диск - SSD WD Green 120 ГБ
Диск для данных - 2 х SSD Samsung 860 EVO 250 ГБ - RAID1
СУБД - PostgresPro 11.6
Платформа - 8.3.16.1148
ОС - Debian 10 x64

Прежде всего выполним тестирование с параметрами по умолчанию:

Полученный результат - 22,32 по Гилеву высоким не назовешь, для субъективного контроля мы использовали конфигурацию Розница 2.2 с базой реального торгового предприятия объемом в 8 ГБ, в целом работу можно было признать удовлетворительной, но местами наблюдалась некоторая "задумчивость", особенно при открытии динамических списков.

Перейдем к оптимизации. Все изменения следует вносить в файл postgesql.conf, который располагается в Linuх для сборки от 1С по пути /etc/postgres/1x/main, а для сборки от PostgresPro в /var/lib/pgpro/1c-1x/data. В Windows данный файл располагается в каталоге данных, по умолчанию это C:\Program Files\PostgreSQL 1C\1х\data. Все параметры указаны в порядке их следования в конфигурационном файла.

Одним из основных параметров, используемых при расчетах, является объем оперативной памяти. При этом следует использовать то значение, которое вы готовы выделить серверу СУБД, за вычетом ОЗУ используемой ОС и другими службами, скажем, сервером 1С. В нашем случае будет использоваться значение в 24 ГБ.

Затем рассчитаем значения отдельных параметров с помощью калькулятора, для чего укажем ОС и версию Postgres, тип накопителя, количество доступной памяти и количество ядер процессора. В поле DB Type указываем Data Warehouses, количество соединений можем проигнорировать, так как вычисляемый результат будет значительно расходиться с рекомендациями 1С.

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

Максимальное число соединений, 1С рекомендует указанные выше значения, мы установили 1000.

Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение - четверть доступного объема памяти, в нашем случае 6 ГБ.

Верхний лимит для временных таблиц в каждой сессии, рекомендуется фиксированное значение.

Указывает объем памяти, который может быть использован для запроса прежде, чем будут задействованы временные файлы на диске. Применяется для каждого соединения и каждой операции, поэтому итоговый объем используемой памяти может существенно превосходить указанное значение. Это один из тех параметров, вычисляемое значение которого калькулятором существенно отличается от рекомендаций 1С. Для объема памяти в 24 ГБ рекомендуемыми значениями будут 375 - 750 МБ, мы выбрали 512 МБ.

Объем памяти для обслуживающих задач (автовакуум, реиндексация и т.д.), указываем рекомендованный калькулятором объем, в нашем случае 2 ГБ.

Максимальное количество открытых файлов на один процесс, в сборке от PostgresPro для Linux это значение по умолчанию.

Параметры процесса фоновой записи, который отвечает за синхронизацию страниц в shared_buffers с диском.

Допустимое число одновременных операций ввода/вывода. Для жестких дисков указывается по количеству шпинделей, для массивов RAID5/6 следует исключить диски четности. Для SATA SSD это значение рекомендуется указывать равным 200, а для быстрых NVMe дисков его можно увеличить до 500-1000. При этом следует понимать, что высокие значения в сочетании с медленными дисками сделают обратный эффект, поэтому подходите к этой настройке грамотно.

Важно! Параметр effective_io_concurrency настраивается только в среде Linux, в Windows системах его значение должно быть равно нулю.

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

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

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

Задает размер буферов журнала предзаписи (WAL, он же журнал транзакций), если оставить эту настройку без изменений, то сервер будет автоматически устанавливать это значение в 1/32 от shared_buffers, но не менее 64 КБ и не более размера одного сегмента WAL в 16 МБ.

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

Минимальный и максимальный размер файлов журнала предзаписи. Указываем значения из калькулятора, в нашем случае это 4 ГБ и 16 ГБ.

Скорость записи изменений на диск, рассчитывается как время между точками сохранения транзакций (чекпойнты) умноженное на данный показатель, позволяет растянуть процесс записи по времени и тем самым снизить одномоментную нагрузку на диски. В нашем случае использовано рекомендованное калькулятором максимальное значение 0,9.

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

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

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

Для производительных SSD можно использовать значения:

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

Определяет эффективный размер кеша, который может использоваться при одном запросе. Этот параметр не влияет на размер выделяемой памяти, не резервирует ее, а служит для ориентировочной оценки доступного размера кеша планировщиком запросов. Чем он выше, тем большая вероятность использования сканирования по индексу, а не последовательного сканирования. При расчете следует использовать выделенный серверу объем RAM, а не полный объем ОЗУ. В нашем случае это 18 ГБ.

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

Количество рабочих процессов автовакуума, рассчитывается по числу процессорных ядер, не менее 4, в нашем случае 4.

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

Отключает политику защиты на уровне строк, данная опция не используется платформой и ее отключение дает некоторое повышение производительности.

Максимальное количество блокировок в одной транзакции, рекомендация от 1С.

Данные опции специфичны для 1С и регулируют использование символа \ для экранирования.

Сохраним файл конфигурации и перезапустим PostgreSQL, в Linux это можно выполнить командами:

В Windows штатными средствами операционной системы, либо скриптами из поставки сборки PostgreSQL:

После чего снова выполним тестирование производительности, на этот раз мы получили следующий результат:

Как видим, достаточно несложные действия по оптимизации добавили серверу около 30% производительности, субъективные ощущения от работы с конфигурацией Розница также повысились, исчезло ощущение "задумчивости", повысилась отзывчивость системы.

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

Некоторое время назад я настраивал работу 1С предприятия с базой данных postgresql. Во время тестирования столкнулся с проблемой медленной работы некоторых запросов. Хочу поделиться полезной информацией, которая позволит разобраться в таких ситуациях и попытаться ускорить работу и избавиться от узких мест в базе.

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

Данная статья является частью единого цикла статьей про сервер Debian.

Введение

Сервер postgresql настроен по предыдущей статье - Установка и настройка postgresql на debian 8 для работы с 1С. Основные моменты по ускорению работы базы там приведены. Они существенно увеличивают производительность по сравнению с настройками по-умолчанию. В большинстве случаев этого бывает достаточно. Если нет - то у вас уже не типичный случай и надо разбираться более детально.

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

Параллельное выполнение запросов на нескольких ядрах в postgresql

Есть несколько параметров, которые как раз отвечают за параллельную обработку запросов:

Их необходимо подбирать под свое количество ядер. В данном случае настройки представлены для 16-ти ядерной системы. Далее необходимо применить скрипт на базе 1С, который позволит оптимизатору постгреса использовать параллельную обработку тех запросов 1С где участвуют текстовые поля (большинство запросов), путём изменения определений функций. Текст скрипта очень длинный, поэтому не привожу его здесь, чтобы не нагружать статью. Качаем его с сайта - postgre.sql.

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

Подключаемся к серверу с postgresql по ssh. Заходим под юзером postgres:

Переходим в домашний каталог пользователя:

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

Если будете копировать готовый файл, убедитесь, что у пользователя postgres есть доступ к этому файлу.

Подключаемся к серверу бд:

Подключаемся к нужной базе данных:

Выполняем sql запрос из файла:

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

Логирование sql запросов в postgresql

Для того, чтобы разобраться, что же конкретно у нас тормозит, надо посмотреть на сами запросы. Для этого нам нужно включить логирование запросов к базе данных. Запросов будет очень много, нам не нужны все подряд. Сделаем ограничение на логирование только тех запросов, которые выполняются дольше, чем 3 секунды. Для этого рисуем следующие параметры в конфиге БД:

И добавляем описание канала для логов LOCAL0 в конфиг rsyslog в файле /etc/rsyslog.conf, в самый конец:

Если оставить настройки rsyslog в таком виде, то лог запросов будет писаться не только в файл /var/log/postgresql/sql.log, но и в messages, и в syslog. Я не люблю спамить в системные логи, поэтому отключим запись sql логов туда. Добавляем в описание этих лог файлов значение LOCAL0.none. Должно получиться примерно так:

Перезапускаем postgresql и rsyslog:

Идем в базу 1С и вызываем свой запрос, который тормозит. Если его выполнение занимает больше, чем 3 секунды, вы увидите текст запроса в лог файле. Можете подольше попользоваться базой, чтобы собрать список запросов для анализа. Запросы 1С настолько громоздкие, что даже просто скопировать их из лога и обработать непростая задача. Воспользуемся для этого специальной программой.

Включение логирования запросов замедляет работу системы. Я рекомендую заниматься диагностикой либо в нерабочее время, либо на тестовой базе и сервере, если есть такая возможность. Для применения настроек базы данных, необходимо перезапускать ее. Это может доставить проблем, если с другими базами сервера кто-то работает. Примите это к сведению.

Анализ запросов postgresql с помощью pgFouine

Устанавливаем pgFouine в debian:

Это старая программа, но для наших целей сойдет. Пользоваться ей очень просто. Я не вдавался в подробности настройки и не смотрел возможные параметры. Мне было достаточно сделать вот так:

Забираем файл report.html к себе на компьютер и открываем в браузере. У меня получилось примерно так:

Дальше вы можете разбираться со своими запросами, в зависимости от ваших знаний и возможностей. Я не знал, что делать дальше, для решения своей проблемы. Попытался построить карту запроса с помощью EXPLAIN ANALYZE, но не получилось. Запрос использует какие-то временные таблицы, так что просто скопировать и повторить его не получалось. Выходила ошибка, что какой-то таблицы не существует.

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

Заключение

На текущий момент моя проблема не решена, но стало понятно, в каком направлении двигаться и что делать. В принципе, я изначально, когда стал заниматься этой задачей, предполагал, что проблема именно на стороне 1С из-за сложного запроса и отсутствии оптимизации работы 1С именно с postgresql. Я это понял, потому что с mssql таких тормозов никогда наблюдал на базах такого размера. В данном случае объем базы всего 10 гб, она не очень большая. 15 секунд лопатить запрос на такой базе можно только, если этот запрос ужасен. На деле все так и оказалось.

В процессе разбора ситуации приобрел определенный опыт, который постарался зафиксировать в этой статье. Думаю, он пригодится в будущем, как мне, так и другим пользователям. В интернете не нашел хороших статей по анализу производительности постгрес. Пришлось все собирать по крохам в разных статьях, но больше на форумах. С учетом стоимости лицензии mssql, замена ее на postgresql выглядит весьма обоснованной, так что тема актуальна.

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

Напоминаю, что данная статья является частью единого цикла статьей про сервер Debian.

Тюнинг PostgreSQL 9.4.2-1.1C для 1С:Предприятия 8.3: рельаный опыт настройки

28 сентября 2016 ВК Tw Fb

Как только размер файловой базы данных 1С:Предприятие одного из наших клиентов достиг размера в 32Гб (да, 32Гб), в следствии чего всё постепенно начало тормозить, а потом и встало намертво, наши клиенты попросили нас решить эту проблемы. SSD Enterprise класса ненадолго подсластил пилюлю, но через некоторое время всё вернулось в исходную точку. Ну что ж, тут и к бабке не ходи – переходим на SQL версию БД.

Поскольку мы ярые пользователи Windows, доступно нам только два варианта СУБД – это MSSql и PostgreSQL. Первый хорош до безумия, но стоимость не порадовала. А ещё больше не порадовала новость о дополнительных лицензиях 1С для работы с MSSQL. Поэтому PostgreSQL.

Подробная инструкция с видео доступна здесь. В этой статье мы пройдёмся по ключевым моментам.

Не забываем про резервное копирование баз данных 1С!

ОС Windows Server 2008R2,
Intel Core i7-2600K 3.40GHz,
32Gb RAM,
Intel SSD DC3700 100Gb (только под БД, ОС на отдельном SSD),
от 10 до 20 пользователей в БД ежедневно,
обмен с 5узлами распределённой БД в фоне.

Зловеще, не правда ли? Приступим.

1. Установка PostgreSQL и pgAdmin.

Ставим всё на далее, далее, кроме следующих моментов. Устанавливаем, как сервис (галочка) и задаём параметры учётной записи Windows, не PostgreSQL.

2. Тюнинг PostgreSQL 9.4.2.

Дальше вбиваем себе в голову следующее: перед любым сохранением новых настроек, делайте резервные копии файлов:

которые лежат здесь:

Если Вы ошибётесь хоть в одной букве, после обновления конфигурации PostgreSQL не запуститься. Выяснить что же стало причиной будет сложно, даже смотря в журналы Windows. Поэтому не меняйте много параметров сразу и делайте резервные копии.

Для правки конфига есть удобный инструмент, доступный прямо из главного окна pgAdmin. Вот он:

Что мы здесь меняем:

Если с базой данных возникли какие-то проблемы, возможно, Вам поможет внутреннее или внешнее тестирование.

Эти статьи будут Вам интересны

Восстановление логина и пароля PPPoE в Windows

Многие провайдеры предоставляют доступ в Интернет с помощью присвоения динамического адреса, некоторые прописывают в договоре статические IP адреса, а некоторые предоставляют PPPoE логин и пароль. Эту пару можно вбить либо в роутер или другое сетевое оборудование, а можно создать PPPoE подключение прямо в Windows. Что делать, если Вы пользовались именно таким способом подключения, а теперь решили использовать роутер, а договор, например, потеряли. Рассказываем, как восстановить Ваши PPPoE логин и пароль для доступа в Интернет.

Декларант-Алко: пароль для базы данных в SDF Viewer

21 октября 2016 ВК Tw Fb

SDF-Viewer - отличная программа для управления сырым файлом базы данных Декларант-Алко. Сценариев использования этой программы много. Мы используем её для обрезки разросшейся алкогольной базы данных. Так случилось, что удаляя какую-либо декларацию из интерфейса самого Декларант-Алко, удаляется только строка в списке деклараций, а все данные по ней остаются в БД. Это не круто, особенно когда накопилось деклараций за несколько лет, и все кроме предыдущей лежат мёртвым грузом, замедляя работу с программой. Но при открытии файла .sdf из Вашей папки с БД, Вас ожидает неприятный сюрприз: программа запрашивает пароль. К счастью, он уже давно не является секретом.

23 сентября 2016 ВК Tw Fb

Это статья не претендует на полноту и не является клоном инструкций Windows. Это скорее личные записки с полезной информацией для себя самого, которые используются мной ежедневно. Многие вещи, которые мы долго ищем в проводнике или панели управления можно быстро и легко выполнить через "Выполнить. " (да, это тавтология). Стоит отметить, что все команды, которые мы тут вспомним, можно вводить не только в окно "Выполнить. ", но и прямо в адресную строку проводника Windows, а так же в командную строку cmd. А чуть ниже "Горячие клавиши" Windows. Ускоряемся!

База знаний "Try 2 Fix" Beta

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

Читайте также: