Предисловие
Несколько лет назад мне в руки попала «Настольная книга эксперта» Евгения Филиппова. Оказалось, что запросы можно писать лучше, заставить программу 1С работать быстрее. С тех пор я подмечал интересные статьи, идеи, доклады. Один из них – доклад Виктора Богачева http://v8.1c.ru/expert/cts/expert.htm о работе в ОАО «Деловые линии». Работа хорошо документирована в рамках проекта ЦКТП и широко освещалась в медиа, в том числе на Инфостарте.
Статья //infostart.ru/public/850217/ - попытка реконструировать ситуацию из доклада с использованием MS SQL 2012. В ней можно найти краткий конспект доклада. Продолжение статьи с использованием PostgreSQL было неизбежно. Итак, мы планируем запускать тестовые задачи в связке 1С + PostgreSQL и наблюдать за поведением хоста и СУБД.
Выбор тестовых задач
Будем использовать 1С + PostgreSQL + Windows, это достаточно популярное решение. Пора развенчать миф о недоступности PostgreSQL.
Для создания нагрузки, будем запускать ФоновыеЗадания.Выполнить() несколько клиентов. Запросы в цикле. Задача - оценить пиковую нагрузку. В основном, тестовые задачи будут те же, что и в статье //infostart.ru/public/850217/. Это позволит сравнить результаты работы различных СУБД с одной конфигурацией 1С. Выгрузка базы данных прилагается, кто захочет – сможет повторить.
- ВЫБРАТЬ 1+1 КАК Поле2
- ВЫБРАТЬ Тестирование.Код ИЗ Справочник.Тестирование КАК Тестирование
- ВЫБРАТЬ 1+1 КАК Поле2 ПОМЕСТИТЬ ВремТаблица
- ВЫБРАТЬ 1+1 КАК Поле2 ПОМЕСТИТЬ ВремТаблица ИНДЕКСИРОВАТЬ ПО Поле2
- Полное соединение
- Левое соединение
- Декартово произведение
Также посмотрим поведение СУБД PostgreSQL для разных случаев:
- Настройка конфигурации «из коробки».
- Получение в технологическом журнале плана запросов
- Проверим влияние параметра конфигурации fsync по статье https://its.1c.ru/db/metod8dev#content:5825:hdoc, https://its.1c.ru/db/metod8dev#content:1576:hdoc
- Сравнение с MS SQL 2012
Влияние параметров конфигурации, связанных с Checkpoint и транзакциями, проверять не будем.
Подготовительная работа
ОС - Windows 10, 64 бит, жесткий диск SSD, 16 Гб RAM, процессор i3-6100 @ 3.7 GHz (два ядра), гигабитная сеть.
Скачал и установил программы:
1С:Предприятие 8.3 (8.3.11.3034)
Адрес https://releases.1c.ru ссылка PostgreSQL, версия 9.6.7-1.1C каталог установки C:\Program Files\PostgreSQL\9.6.7-1.1C
В состав пакета входит графическая оболочка pgAdmin версия 4. Есть более свежая версия СУБД, но она требует 1С версия 8.3.13.
Для использования pgBadger настроим файл postgresql.conf по статье http://pgcookbook.ru/article/pgbadger.html (аналогичная информация есть на ИТС)
|
log_line_prefix = '%t [%p]: [%l-1] ' |
формат логов |
|
log_duration = on |
Включает логирование запросов |
|
log_min_duration_statement = 0 |
минимальное время миллисекунд выполнения запроса. В документации указано =0. |
Если установить log_min_duration_statement = -1, то сообщения об ошибке нет, но статистика будет собираться неверно, отражая заоблачные значения производительности.
Адрес http://strawberryperl.com/ ссылка Strawberry Perl 5.28.0.1 (64bit) каталог установки C:\Strawberry
Среда Perl нужна для исполнения скрипта pgBadger.
Адрес https://github.com/darold/pgbadger/releases ссылка pgbadger-10.1.tar.gz каталог установки C:\pgbadger-10.1
Командный файл для запуска скрипта pgBadger из каталога временных файлов C:\Users\vasiliev_ng\temp автоматически формируется 1С при запуске теста, включает одну минуту журнала, которая содержит результаты последнего теста, например:
xcopy "C:\Program Files\PostgreSQL\9.6.7-1.1C\data\pg_log\*.*" "C:\Users\vasiliev_ng\temp\" /Y
C:\Strawberry\perl\bin\perl c:\pgbadger-10.1\pgbadger -a 1 -b "2018-11-30 22:38:46" -e "2018-11-30 22:40:26" --anonymize C:\Users\vasiliev_ng\temp\postgresql-Fri.log
Копировать лог СУБД в каталог C:\Users\vasiliev_ng\temp, запустить perl с указанием скрипта, параметры, файл логов для анализа. В ответ будет создан новый файл Out.html. Таблицу параметров из документации перевел на русский, файл perevedi.xlsx в приложении. Скрипт формируется и исполняется 1С (Выгрузка базы данных прилагается).
Общая информация о тестировании.
Информацию о состоянии хоста будем получать средствами perfmon. Показатели выбираем в соответствии со статьей https://its.1c.ru/db/metod8dev#content:2923:hdoc: очередь к процессору, очередь к диску, сетевой интерфейс - бит всего. Оперативную память проверял – почти не расходуется. Сетевой интерфейс бывает загружен несущественно (до 5%). Очередь к диску практически не изменяется, добавил показатель «Скорость обмена с диском байт/секунду». Файлы шаблона сборщиков данных прилагаются.
Информацию о состоянии СУБД PostgreSQL будем получать из самой СУБД, с использованием pgBadger. Этот бесплатный, но мощный инструмент СУБД PostgreSQL до сих пор упоминался в публикациях Инфостарта два раза, рассмотрим его подробнее.
Все тесты запускал много раз, добиваясь устойчивых, прозрачных результатов. Вначале картина была неполная: на сервере СУБД количество запросов в секунду не росло выше определенного предела, упиралось в потолок, а свободные ресурсы были. После переноса кластера 1С на этот же хост стало видно узкое место: процесс rphost при таких тестах потребляет значительные ресурсы ЦП, а сервер кластера 1С был недостаточно производительным. Выбрал более мощный сервер для кластера 1С.
Для каждого фонового задания запускается отдельный экземпляр PostgreSQL. Количество сеансов и циклов в тесте подбирал так, чтобы загрузка процессора была максимальной в течение одной минуты. Такие тесты пригодятся, чтобы сделать поверхностные выводы, но настоящие нагрузочные тесты намного сложнее и дольше.
1. Настройка конфигурации «из коробки»
|
Номер запроса |
Клиенты Циклы |
Пик, тысяч запросов в секунду |
Файлы pgBadger, Perfmon |
|
1 |
90 9999 |
27
|
Simply_081512 |
|
2 |
90 9999 |
20
|
RefQuery_081548 |
Простейшие запросы должны показывать большее число запросов в пике по сравнению с более сложными запросами. Например, если в простейший Запрос 1 просто добавить текстовое поле, пик упадет на 5%. В этом смысле видно предел быстродействия.
Когда выполнял Запрос 1, то получил ошибку «FATAL: sorry, too many clients already». В логе pgBadger отображается на закладке «Events». Проверил конфигурацию: max_connections = 100, shared_buffers = 128MB. Изменять не стал )).
|
3 |
26 9999 |
6,9 |
TempTable_091216 |
|
4 |
26 9999 |
5,4 |
IndexTable_091210 |
Запросы №3,4 создают временную таблицу (индекс), записывают данные на диск. Самые медленные запросы в этих опытах – работа с временными таблицами и индексами:
- DROP TABLE IF EXISTS tt1 CASCADE; CREATE TEMPORARY TABLE tt1
- INSERT INTO pg_temp.tt1
- CREATE INDEX
- DROP INDEX IF EXISTS
Распределение запросов по типам:
- Запрос № 3 select 33%, insert 33%
- Запрос № 4 select 17%, insert 17%, DDL 50%
|
5 |
9 3333 |
2.4 |
FullJoin_081628 |
|
6 |
9 3333 |
5.6 |
LeftJoin_081724 |
|
7 |
9 3333 |
5.6 |
Decart_081733 |
Здесь используем, что запросы
- ВЫБРАТЬ * ИЗ ВремТаблица КАК ВремТаблица1 ПОЛНОЕ СОЕДИНЕНИЕ ВремТаблица КАК ВремТаблица2 ПО ИСТИНА
- ВЫБРАТЬ * ИЗ ВремТаблица КАК ВремТаблица1 ЛЕВОЕ СОЕДИНЕНИЕ ВремТаблица КАК ВремТаблица2 ПО ИСТИНА
- ВЫБРАТЬ * ИЗ ВремТаблица КАК ВремТаблица1, ВремТаблица КАК ВремТаблица2
Дают одинаковый результат (если в декартовом произведении отсутствует null). Однако СУБД интерпретирует их по-разному.
В списке самых медленных запросов видим запросы работы с временными таблицами и запрос типа SELECT … FROM … INNER JOIN … ON … INNER … ON … INNER JOIN … ON … WHERE … UNION ALL SELECT … INNER JOIN … ON … INNER JOIN … ON … WHERE NOT (EXISTS ( SELECT … FROM …
Откуда становится ясно, как именно PosqreSQL вычисляет полное соединение. Кстати, один из вариантов замены полного соединения рассмотрен в статье //infostart.ru/public/794859/ с математическим доказательством эквивалентности запросов методом математической индукции.
В опытах 5-7 оператор полного соединения на ВремТаблица (625 строк) дает скорость в два раза меньше, чем операторы левого и декартового соединения в эквивалентных запросах. Причем, разница в скорости может возрастать нелинейно с ростом количества строк. Однако, не нужно бросаться переписывать все операторы полного соединения для использования PostgeSQL. Как указал Е. Филиппов, «работать нужно с существующими проблемами».
2. Конфигурация «из коробки» после включения ТЖ.
Включим ТЖ: сбор планов, SDBL, DBPOSTGRS. Файл logcfg.xml прилагается. Результаты в таблице.
|
Номер запроса |
Клиенты Циклы |
Пик, тысяч запросов в секунду |
Файлы pgBadger, Perfmon |
|
7 |
9 3333 |
5,7 |
Decart_081748 |
Численное значение пиков практически не поменялось, но в списке самых медленных запросов появились запросы типа EXPLAIN ANALYSE SELECT.., количество которых равно количеству «целевых» запросов. Фактически, каждый запрос выполняется два раза – один раз дополнительно, чтобы получить план запроса. Можно сказать, что «целевых» запросов стало в два раза меньше.
3. Изменяем конфигурацию.
Запросы 3,4 работали с временными таблицами, использовали жесткий диск. Проведем эксперимент – установим параметр fsync = off ( По умолчанию fsync = on ). В приложении bat-файл, который останавливает службу Postgres, копирует измененный config и запускает службу. Выполним нагрузочный тест.
|
Номер запроса |
Клиенты Циклы |
Пик, тысяч запросов в секунду |
Файлы pgBadger, Perfmon |
|
3 |
26 9999 |
7,2 |
TempTable_091233 |
|
4 |
26 9999 |
5,7 |
IndexTable_091226 |
В обоих опытах значения пиков запросов больше на 4-6 процентов. Чем активнее запрос работает с диском, тем больше он чувствителен к параметру fsync. Дисковые операции стали выполняться несколько быстрее, но вероятность io ошибки тоже выросла.
4. Сравнение PostreSQL 9.6 и MS SQL 2012
Создадим базу данных на MS SQL 2012, используя ту же базу 1С (выгрузка прилагается).
|
Номер запроса |
Клиенты Циклы |
Пик, тысяч запросов в секунду |
ФайлPerfmon |
|
1 |
90 9999 |
21,3 |
Simply_091647 |
|
2 |
90 9999 |
17,3 |
RefQuery_090841 |
Значения ниже, чем в опытах PostgreSQL, но есть важный нюанс. Загрузка процессора существенно ниже, чем в первых опытах и не понятно, чем вызвано ограничение числа запросов. Конфигурация серверов не изменялась, но возможно MS SQL работает с сервером кластера активнее и сервер кластера опять стал узким местом.
|
3 |
39 9999 |
11.7 |
TempTable_090636 |
|
4 |
39 3333 |
11 |
IndexTable_090817 |
В опытах 3,4 появились характерные блокировки Latch. При этом есть корреляция между ростом числа блокировок и падением числа запросов в секунду. Возможно, это основной фактор, мешающий росту. Загрузка процессора доходит до 70%. Это существенное значение, но очередь меньше 2 на ядро. В опытах PostgreSQL загрузка доходила до 90%, очередь выше 2 на ядро.
|
6 |
27 3333 |
6.8 |
LeftJoin_090830 |
Значения сопоставимые с PostrgeSQL. Полное соединение и декартово произведение не тестировал.
Заключение
В публикации проведена серия тестов, результаты полностью открыты для Сообщества. В целом, PostgreSQL произвел на меня положительное впечатление. Есть ощущение развития и комфорта. Инструмент pgBadger - просто бомба, поэтому использовал его как логотип статьи. Хочется пожелать успехов pg-разработчикам.
Послесловие
Кстати, мне удалось получить ответ от Виктора Богачева о использовании PostgreSQL в ОАО «Деловые линии»:
- В докладе вы сказали, что рассматривали две СУБД: MS SQL и DB2 ? Скажите, рассматривали ли PostgreSQL и если нет - то почему ?
- На тот момент Postgres менее активно продвигался в том числе фирмой «1С» …
Отдельное спасибо техподдержке info@postgrespro.ru Постгрес Профессиональный https://postgrespro.ru за оперативность. Они действительно помогают. Тикет OTRS 2018112355000121 можно закрывать.
*********
С 4 по 6 февраля 2019 года в стенах Московского государственного университета состоится конференция по PostgreSQL – PGConf.Russia 2019. Ежегодно она собирает более 500 разработчиков, администраторов баз данных и IT-менеджеров для обмена опытом и профессионального общения.
На этот раз PGConf.Russia будет особенной. Инфостарт совместно с Postgres Pro организует на конференции секцию «Postgres+1C». Мы приглашаем участников сообщества посетить PGConf и даже выступить в качестве докладчика.
