Исследование быстродействия СУБД MS SQL Server Developer 2016 и PostgreSQL 10.5 для 1С

Спецификация гипервизора:
Model: Supermicro SYS-6028R-TRT
CPU: Intel® Xeon® CPU E5-2630 v3 @2.40GHz (2 sockes * 16 CPU HT = 32CPU)
RAM: 212 GB
ОС: VMWare ESXi 6.5
PowerProfile: Performance

Дисковая подсистема гипервизора:
Контроллер: Adaptec 6805, Cache size: 512MB
Volume: RAID 10, 5.7 TB
Stripe-size: 1024 KB
Write-cache: on
Read-cache: off
Диски: 6 шт. HGST HUS726T6TAL,
Sector-Size: 512 Bytes
Write Cache: on

PostgreSQL был настроен следующим образом:

1. postgresql.conf:
   Базовая настройка делалась по калькулятору — pgconfigurator.cybertec.at, параметры huge_pages, checkpoint_timeout, max_wal_size, min_wal_size, random_page_cost менялись на основе информации, полученной из источников, упомянутых в конце публикации. Значение параметра temp_buffers увеличивалось, исходя из предложения, что 1С активно использует временные таблицы:

   listen_addresses = '*'
   max_connections = 1000
    
   #Выделяемый под кэш данных размер ОЗУ. Работа со строками происходит в основном в этом участке памяти. На системах с 32ГБ ОЗУ рекомендуется выделять около 25% от общего объема памяти.
   shared_buffers = 9GB
    
   #Использование больших страницы памяти(Настройка ядра Linux - vm.nr_hugepages).
   huge_pages = on
    
   #Лимит памяти для временных таблиц на сессию.
   temp_buffers = 512MB
   #Лимит памяти на одну операцию типа ORDER BY, DISTINCT, merge joins, join, hash-based aggregation, hash-based processing of IN subqueries.
   #Выставлен из расчета, что 1С делает сложные большие запросы (профиль "Mostly complicated real-time SQL queries" в калькуляторе). Возможно стоит уменьшить до 64MB.
   work_mem = 128MB
    
   #Лимит памяти для служебных операций. VACUUM, создание индексов, etc.
   maintenance_work_mem = 512MB
    
   #Совместно с настройками ядра (vm.dirty_background_bytes, vm.dirty_bytes), данные параметры позволяют устранить всплески нагрузки на IO в процессе CHECKPOINT.
   checkpoint_timeout = 30min
   max_wal_size = 3GB
   min_wal_size = 512MB
   checkpoint_completion_target = 0.9
    
   seq_page_cost = 1.0
    
   #Настройки для планировщика запросов. Значение по-умолчанию - 4. Для RAID10 рекомендуется уменьшать.
   random_page_cost = 2.5
   #Указание планировщику примерного потенциального размера всей занимаемой postgres памяти, включая страницы в PageCache.
   effective_cache_size = 22GB
   

2. Ядро, параметры ОС:
   Настройки заданы в формате файла профиля для демона tuned:

   [sysctl]
   #Параметры задающие объем грязных страниц (PageCache), по достижении которого ядро должно начинать фоновую/принудительную запись этих страниц на диск.
   #По-умолчанию объем задан в процентах(10,30) что на современных системах с большим количеством ОЗУ приводит к всплескам нагрузки на систему ввода/вывода.
   #Важно для оптимизации производительности CHECKPOINT и устранения всплесков на I/O.
   #Заданные абсолютные значения применимы для использования с RAID-контроллером имеющим write-back cache объемом 512MB.
   vm.dirty_background_bytes = 67108864
   vm.dirty_background_ratio = 0
   vm.dirty_bytes = 536870912
   vm.dirty_ratio = 0
    
   #Использовать SWAP по-минимуму. Совсем отключать не стоит, чтобы минимизировать вероятность OOM.
   vm.swappiness = 1
    
   #Планировщик подразумевает, что заданный период времени процесс использует кеш CPU.
   #Увеличение этого параметра снижает количество миграций процессов с одного CPU на другой.
   #Параметр заметно влияет на производительность.
   kernel.sched_migration_cost_ns = 5000000
    
   #Отключение группировки процессов по CPU на основе сессии.
   #Для серверов этот параметр нужно выставлять в 0. Заметно влияет на производительность.
   kernel.sched_autogroup_enabled = 0
    
   #Выделение памяти под большие страницы. Параметр заметно влияет на производительность.
   #Способ расчетам описан в документации - https://www.postgresql.org/docs/11/kernel-resources.html#LINUX-HUGE-PAGES
   vm.nr_hugepages = 5000
    
    
   [vm]
   #Отключение прозрачных больших страниц. Так как СУБД не использует однородные продолжительные сегменты памяти, этот параметр рекомендуется отключать. Тем более, что включены нормальные большие страницы.
   transparent_hugepages=never
    
    
   #Параметры энергосбережения CPU. В виртуальной машине едва ли имеют смысл, но на железном сервере просто необходимы.
   [cpu]
   force_latency=1
   governor=performance
   energy_perf_bias=performance
   min_perf_pct=100
   

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

   #Создание ФС:
   #stride и stripe_width рассчитывались для упомянутого RAID 10 из 6-ти дисков с размером stripe в 1024kb 
   mkfs.ext4 -E stride=256,stripe_width=768 /dev/sdb
   #Опции монтирования:
   /dev/sdb /var/lib/pgsql ext4 noatime,nodiratime,data=ordered,barrier=0,errors=remount-ro 0 2
   #noatime,nodiratime - отключить запись времени доступа к файлам и каталогам
   #data=ordered -  Журнал включен только для метаданных. Метаданные записываются после данных
   #barrier=0 - Барьер обеспечивает последовательную запись данных журнала ФС. На RAID-контроллерах с батарейкой барьер можно отключить.
   

MS SQL был настроен следующим образом:

и

Настройки кластера 1С оставили стандартными:

и

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

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

1. Блок 1 «СПР_ПТУ» — «Поступление товаров и услуг»
   • Открывается справочник «Контрагенты»
   • Создается новый элемент справочника «Контрагенты» с видом «Поставщик»
   • Создается новый элемент справочника «Договоры» с видом «С поставщиком» для нового контрагента
   • Открывается справочник «Номенклатура»
   • Создается набор элементов справочника «Номенклатура» с видом «Товар»
   • Создается набор элементов справочника «Номенклатура» с видом «Услуга»
   • Открывается список документов «Поступления товаров и услуг»
   • Создается новый документ «Поступление товаров и услуг» в котором заполняются табличные части «Товары» и «Услуги» созданными наборами данных
   • Формируется отчет «Карточка счета 41» за текущий месяц (если указан интервал дополнительного формирования)

2. Блок 2 «СПР_РТУ» — «Реализация товаров и услуг»
   • Открывается справочник «Контрагенты»
   • Создается новый элемент справочника «Контрагенты» с видом «Покупатель»
   • Создается новый элемент справочника «Договоры» с видом «С покупателем» для нового контрагента
   • Открывается список документов «Реализация товаров и услуг»
   • Создается новый документ «Реализация товаров и услуг» в котором заполняются табличные части «Товары» и «Услуги» по указанным параметрам из ранее созданных данных
   • Формируется отчет «Карточка счета 41» за текущий месяц (если указан интервал дополнительного формирования)
3. Формируется отчет «Карточка счета 41» за текущий месяц

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

1. Формируется отчет «Карточка счета 41» с начала года на конец месяца
2. Формируется отчет «Оборотно-сальдовая ведомость» с начала года на конец месяца
3. Выполняется регламентная процедура «Закрытие месяца»

По итогу выполнения выдается информация о времени проведения теста в часах, минутах, секундах и миллисекундах.

Основные возможности скрипта тестирования:

1. Возможность отключения/включения отдельных блоков
2. Возможность указания общего количества документов для каждого из блоков
3. Возможность указания количества документов для каждого из блоков за день
4. Возможность указания количества товаров и услуг внутри документов
5. Возможность задания списков количественных и ценовых показателей для записи. Служит для создания различных наборов значений в документах

План основных тестов для каждой из баз:

1. «Первый тест». Под одним пользователем создается небольшое количество документов с простыми таблицами, формируются «закрытия месяцев»
   • Ожидаемое время выполнения — 20 минут. Заполнение на 1 месяц. Данные: 50 документов «ПТУ», 50 документов «РТУ», 100 элементов «Номенклатура», 50 элементов «Поставщиков» + «Договор», 50 элементов «Покупателей» + «Договор», 2 операции «Закрытие месяца». В документах 1 товар и 1 услуга

2. «Второй тест». Под одним пользователем создается существенное количество документов с заполнением таблиц, формируются закрытия месяцев
   • Ожидаемое время выполнения — 50-60 минут. Заполнение на 3 месяца. Данные: 90 документов «ПТУ», 90 документов «РТУ», 540 элементов «Номенклатура», 90 элементов «Поставщиков» + «Договор», 90 элементов «Покупателей» + «Договор», 3 операции «Закрытие месяца». В документах 3 товара и 3 услуги

3. «Третий тест». Под двумя пользователями запускается одновременное выполнение скрипта. Создается существенное количество документов с заполнением таблиц. Итоговым временем выполнения теста считается максимальное
   • Ожидаемое время выполнения — 40-60 минут. Заполнение на 2 месяца. Данные: 50 документов «ПТУ», 50 документов «РТУ», 300 элементов «Номенклатура», 50 элементов «Поставщиков» + «Договор», 50 элементов «Покупателей» + «Договор». В документах 3 товара и 3 услуги

План дополнительных тестов для каждой из баз:

1. Изменение структуры базы данных, проверка времени реструктуризации таблиц базы данных:
   • Изменение справочника Договора
   • Изменение справочника Контрагенты
   • Изменение документа «Реализация товаров и услуг»
2. Перепроведение документов «Поступление товаров и услуг» и «Реализация товаров и услуг» за указанный период
3. Выгрузка базы данных в файл формата 1С "*.dt" и загрузка из него обратно
4. Выполнение регламентной процедуры «Закрытие месяца» для одного из старых периодов

первый запуск:

второй запуск:

третий запуск:

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

второй запуск:

третий запуск: