Нужна помощь консультанта?
Затраты на СУБД MS SQL и PostgreSQL:
В связи со стремительной девальвацией рубля, покупать СУБД Microsoft SQL стало очень дорого, а для некоторых компаний стоимость этих лицензий стала совсем «неподъемной». В данный момент чтобы развернуть сервер Microsoft SQL для 20 пользователей необходимо купить такие лицензии:
1 лицензия на операционную систему (WinSvrStd 2012R2 )
20 лицензий на подключение к серверу (WinSvrCAL 2012)
1 лицензия на сервер СУБД (SQLSvrStd 2014)
20 лицензий на подключение к СУБД (SQLCAL 2014)
Ориентировочная стоимость такого пакета 275 000 руб., что для компании, в которой всего 20 человек достаточно дорого. Данных затрат можно избежать, если создать сервер СУБД на свободном ПО. Поставить операционную систему семейства Linux и бесплатную версию СУБД – PostgreSQL . На таком сервере без проблем можно развернуть сервер 1С предприятия, а также другие роли, которые потенциально могут быть совмещены c ролью баз данных, например WebServer или файловое хранилище.
Так как использовать свободное ПО очень привлекательно с финансовой точки зрения, было решено проверить, на сколько это хорошо с точки зрения производительности.
Тестирование производительности 1С:
Для выполнения теста было взято оборудование и программное обеспечение, указанное в таблице 1. Физический сервер для обоих стендов использовался один и тот же, менялось только ПО. Настройки обоих СУБД использовались по-умолчанию и в статье мы их подробно не расписываем. Дистрибутив PostGreSQL с соответсвующими патчами были взят с сайта компании 1С, версия — последняя из доступных на данном сайте.
Таблица 1. Тестовые стенды
Windows Server 2012R2
Microsoft SQL Server 2012R2
Intel Core i 5 3330 (3.0 Ghz )
24 GB DDD 3 1333 Ghz
SSD 240 Gb Intel
Для начала был выполнен «тест Гилева», который показал незначительное преимущество стенда номер 2, против стенда со свободным ПО.
Результаты смотрим ниже, разница в значениях получилась всего 3%.
Для информации: «тест Гилева» — популярный синтетический тест 1С, который выполняет ряд стандартных операций – чем быстрее тест выполняется, тем выше оценка. Оценка выполняется в условных единицах. Полученную оценку можно сравнить с прилагаемой к тесту шкале, которая покажет на сколько высока производительность текущей системы.
Рисунок 1. Результат теста Гилева. Стенд №2 СУБД MS SQL
Рисунок 2. Результат теста Гилева. Стенд №1 СУБД PostgreSQL
Далее решено было тестирование выполнить по методике APDEX . Суть метода заключается в измерении времени выполнения основных операций в 1С, замеры проводятся несколько раз на протяжении определенно периода времени. Далее полученный результат сравнивается с приемлемым временем выполнения той или иной операции.
Для этого взяли реальную рабочую базу одной из самых тяжелых конфигураций 1С, характеристики базы указаны в таблице №2.
Таблица 2. Характеристики тестовой базы
Управление производственным предприятием
Замерялось время выполнения 7-ми стандартных операций с объектами в базе. Каждый тест выполнялся 10 раз и выводилось среднее значение. Замеры проводились с использованием толстого клиента через локальную сеть. Клиент устанавливался на рабочую станцию под управлением Windows 7. Тесты также пробовали запускать с клиента установленного на Ubuntu Linux , но он работал не стабильно и все тесты решено было выполнять только с клиента на Windows .
Таблица 3. Результаты APDEX
Время выполнения в секундах
Проведение нового документа
Документ объект: Заказ клиента
Анализ доходов расходов
Ведомость по партиям товаров
Ведомость по товарам на складах
Расчеты с клиентами
В среднем наша реальная база при использовании MSSQL работала на 45% быстрее, чем на стенде со свободным ПО. На некоторых тестах отрыв был очень значителен, а на таких как, например проведение нового документа составлял всего 11%.
Вывод:
1C на СУБД MSSQL работает примерно в 1,5 быстрее, чем на PostgreSQL. Соответственно, если есть возможность купить или арендовать лицензии MSSQL, лучше использовать его для более высокой производительности. Для небольших и ненагруженных баз можно попробовать использовать версию MSSQL Express. Тестов с ней мы не проводили, поэтому она может показать себя по производительности как лучше так и хуже PostgreSQL. Данная редакция ограничена использованием 1 процессора и 1 Гб ОЗУ, также не работает с базами более 10Гб. Если база дорастет до такого размера, то она остановиться и перестанет работать полностью, но как показывает практика, если в базе работает 15-20 пользователей, то комфортно можно работать при размере базы 4-5ГБ, далее база начинает сильно тормозить.
Оценка «тестом Гилева» показывает крайне незначительное превосходство MSSQL, что позволяет сделать предположение о том, что другие базы 1С могут работать на PostgreSQL так же хорошо, как и на MSSQL, а возможно и быстрее. Перед выбором СУБД рекомендуем провести тесты на своей конкретной базе и сравнить полученные результаты.
Использование СУБД PostgreSQL для развертывания на нем 1С является приемлемым решением в условиях ограниченного бюджета. База будет работать не так быстро как на MSSQL , но зато не нужно платить за лицензии.
В конце 2017 года мы провели новые тесты и опубликовали их в очередной статье.
Ещё одним способом сэкономить при использовании 1С является решение — взять сервер 1С в аренду.
Давайте признаемся честно, хоть 1С Предприятие и совместимо со многими СУБД, но по факту 99 процентов работают либо в MS SQL или в бесплатной PostgreSQL.
Другими словами эти две «субдешки» завоевали рынок клиент-серверной 1С.
И можно смело предполагать, что если компания не работает в MS SQL то, скорее всего, просто используют PostgreSQL.
Соответственно сравнивать «Постгрес» есть смысл разве что только с MS SQL.
Сегодня много пишут, как об MS SQL так и о PostgreSQL, но обычно объективно не сравнивают их.
В этой статье мы же разберем основные технические моменты бесплатной PostgreSQL, сравнивая ее c MS SQL.
Что позволит Вам в будущем сделать оптимальный выбор и быть готовым к разным «неожиданностям» или что будет более правильно «особенностям» работы в этой бесплатной СУБД.
Оценивать будем все как есть, не приплюсовуя Постгресу тех заслуг, которых у него нет и, не приукрашивая платный MS.
Сразу отвечу на вопрос, который волнует многих новичков!
ДА! MS SQL работает быстрее PostgreSQL, это факт! И на это есть ряд причин!
Возможно, я кого-то прямо сразу разочаровал, и возможно, Вы не согласны с таким утверждением, извиняюсь, но сама физика работы этой бесплатной СУБД не позволяет ему опередить MS SQL, особенно если мы говорим о такой связке как «Монстр 1С» и PostgreSQL.
Подобные доводы Вы не раз встретите на различных конференциях и семинарах посвященных этой СУБД. Никто ничего не скрывает и не отрицает, факт есть факт.
Тем не менее, производительности PostgreSQL вполне достаточно, для того, чтоб пользователи могли комфортно работать в 1С.
Будь это десяток пользователей или даже несколько сотен, одновременно работающих в 1С Предприятии.
Почему «Монстр 1С» ?
Собственно так 1С видит PostgreSQL без установленных специальных «патчей» и расширений.
Да, что называется из коробки, скачав дистрибутив PostgreSQL на оф. сайте Вы не сможете использовать его для работы в связке с 1С. 1С-ка будет жутко тормозить и просто останавливаться, отказываться работать.
Почему так происходит, и зачем «патчи»?
Дело в том что 1С Предприятие создает огромное количество временных таблиц в процессе своей работы, речь может идти о тысячах таблиц в секунду, а если взять, например регистр «Срез последних» — «ОстаткиИОбороты», там вполне могут и по миллиону строк быть.
Дело в том что по умолчанию (без «патчей») PostgreSQL не считает статистику по этим большим временным таблицам, другими словами оптимизатор запросов который руководствуется данными из статистики (а она как помним пуста, нечего считать) грубо говоря, делает выборку методом SELECT * что конечно будет работать очень и очень медленно!
Отсюда грандиозные тормоза в 1С!
Конечно это не все проблемы, которые нужно решить, чтоб PostgreSQL работал в паре с 1С нормально. Нужны будут и другие «патчи» и специальные расширения и после 15-20 пользователей, еще и доп. настройки в «конфиге»
Да, на самом деле в реалии все выглядит намного сложнее, чем я описал выше, но вот так если сильно упростить и будет выглядеть основная проблема медленной работы 1С с PostgreSQL.
Второе что мне сильно не нравится в PostgreSQL это отсутствие многопоточности в рамках одного запроса в сравнении с MS SQL.
(Начиная с версии 9.6 сделали первую попытку распараллеливания запросов, но пока работает плохо, иногда эффект обратный ). но за попытку 5! )
Что конечно влияет на производительность, чтоб Вы понимали простым языком —
PostgreSQL способен уложить Ваш 48-ми ядерный сервер, одним большим запросом!
Все просто, распараллеливания потоков в рамках одного запроса нет и один большой запрос «грузит» только одно ядро.
Да, если запросов много, тогда все ядра будут нагружены, и все будет работать хорошо.
И чуть не забыл, сравниваем мы PostgreSQL c MS SQL Standard не Express!
Express хоть и можно использовать в коммерческих целях, но целый ряд ограничений
таких как 10 Гб на базу, использование одного процесора, 1 Гб оперативной памяти,
делает использование такого продукта почти нереальным для работы в 1С Предприятии.
Разве что у вас очень маленькая база и всего пара пользователей, (да и то бывают тормоза 1 гб для СУБД очень мало).
Так что сравниваем PostgreSQL с популярной версией Standard.
СКРИПТЫ.
PostgreSQL это прежде всего скрипты в сравнении с MS SQL, большинство операций приходится делать руками, да можно установить конечно pgAdmin 4 и некоторые базовые вещи выполнять через интерфейс, но подчеркну, что базовые , а шаг влево шаг вправо и нужно писать скрипт, или БАШ на Линуксе или cmd, powershell наWindows.
Просмотр и анализ трассировок с помощью приложения SQL Server Profiler.
Всем известный SQL Server Profiler в PostgreSQL отсутствует, причем под словом «отсутствует» я имею, введу напрочь, увы, нет ничего подобного в PostgreSQL.
Есть, конечно, утилиты, которые позволяют, если успеть перехватить запрос или поставить точку останова 1С в отладчике и что-то получит и посмотреть, но в сравнении с Профайлером как говорится и близко не стояло.
Можно настроить лог и потом это все перебирать — но долго!
Вот пример:
Программист 1С пытается отладить какой-нибудь большой запрос, он долго выполняется, например 30 минут, так вот в PostgreSQL, чтоб данные попали в лог, этот запрос должен выполнится! Представляете, как долго можно отлаживать такой запрос?
В то время как в MS SQL можно прервать выполнение запроса и в Профайлере его разобрать, так как он там уже будет, но со статусом «failed».
По разновидности создания «бэкапов» Постгресу нет равных!
Здесь Вам и инкрементный «бекап» и полное резервное копирование и непрерывное WAL архивирование.
Как собственно есть и частичное резервное копирование и частичное восстановление данных.
Можно настроить непрерывное архивирование и восстановление на момент времени (Point-in-Time Recovery (PITR)).
Также репликация, доступна изначально в PostgreSQl без каких либо «патчей» утилит и дополнений!
- Каскадная репликация
- Потоковая репликация
- Синхронная репликация
- Непрерывное архивирование на резервном сервере
Все это есть, уже изначально в PostgreSQl и конечно нет в «экспрессе» и недоступно на версии MS SQL Standard.
Чтоб получить все выше перечисленное в MS SQL, нужно покупать очень дорогой MS SQL Enterprise, сейчас что-то около 15 000$ долларов.
Чего нет в сравнении с MS SQL ?
НЕТ диференциального «бэкапа»
Да в PostgreSQl нет дифференциального «бэкапа», но есть различные аналоги инкрементного создания «бэкапов».
Например, инкрементный «бэкап» на уровне блоков.
ЕСТЬ разделение TABLESPACE-ов, что уже по умолчанию поддерживает 1С!
Которого к слову нет в MS SQL!
Например, Вы можете настроить на каком диске у вас будут «индексы» и на каком диске будет находиться «таблица», очень удобно при планировании IТ инфраструктуры, когда речь идет о больших базах данных 1С.
PostgreSQl намного быстрее обновляет статистику (если точнее — считает ее)
Например тот же MS SQL в терабайтной базе может считать статистку час, а вот PostgreSQL минуты 2-3. )
Внимание!
MS SQL в «бэкап» помещает уже посчитанную статистику, чего не делает PostgreSQL!
И когда Вы восстановитесь из такого «бэкапа» будут тормоза, пока не сделаете ONLINE_ANALYZE, чтоб пересчитать статистику. Тоже самое касается файла *dt.
Выгрузили – загрузили, нужно считать статистику ONLINE_ANALYZE!
Используя PostgreSQl очень редко нужен REINDEX!
Фактически стоит использовать только когда, есть подозрения, что целостность базы данных повреждена.
Можно делать «бэкапы» с исключением таблиц!
Например, у вас в компании работают несколько программистов 1С, они гарантированно будут делать себе резервные копии создавать «бэкапы» для дальнейшей разработки.
В итоге страдают пользователи, база тормозит во время создания большого «бэкапа» особенно если в этой базе есть такие вещи как различные прикрепленные файлы, архивы, документы из писем. Такие таблицы с файлами запросто могут содержать сотни гигабайт. И их же можно исключить в PostgreSQl создавая «бэкап», тем самым малого размера, и со всем функционалом одновременно.
Так мы лишний раз не нагружаем сетевые устройства, не забиваем канал, тратим намного меньше времени на создание такого «бэкапа»
В итоге все в выигрыше! И пользователи, и программисты и админы спят спокойно.
В этой статье мы разобрали лишь базовые отличия PostgreSQl от MS SQL, (есть и другие) но определится с выбором в пользу той или иной СУБД, статья должна помочь!
Общие положения
В документе описывается настройка PostgreSQL версий 9.2-9.4 на максимальную производительность для платформы 1С. Предполагается, что сервер, используемый для PostgreSQL является достаточно производительным и имеет приблизительно:
- 4 — 512 Gb RAM
- 2 — 256 CPU cores
- RAID 0-1 или SSD
Данный документ подразумевает хотя бы поверхностное знакомство с архитектурой PgSQL. Приведенные в документе параметры являются приблизительными и стартовыми для тонкой настройки.
Настройки сервера для PostgreSQL
- Рекомендуется отключать HyperThreading. Для программ типа систем управления базами данных от него скорее вред чем польза.
- Также рекомендуется отключать Energy Saving, поскольку в противном случае могут непредсказуемо вырастать задержки ответов БД.
- Надо запретить своппинг разделяемой памяти SYSV/posix (FreeBSD: kern.ipc.shm_use_phys=1)
Обозначения
- RAM — объем оперативной памяти сервера. Если сервер используется не только для PostgreSQL, то надо уменьшить эту величину на объем занятой памяти.
- NCores — суммарное число ядер на всех CPU сервера
- max_connections — максимальное число коннектов (или сессий) к PgSQL. Задается в конфигурационном файле.
- WAL — Write Ahead Log, опережающий лог действий с таблицами и индексами. Основная задача — целостность и отказоустойчивость базы данных при одновременном росте производительности.
- checkpoint — точка восстановления база данных. Все WAL данные, записанные до checkpoint становятся не нужны.
- X..Y — диапазон значений от X до Y включительно
Параметры производительности
Количество памяти, выделенной PgSQL для совместного кеша страниц. Эта память разделяется между всеми процессами PgSQL.
Максимальное количество страниц для временных таблиц. Т.е. это верхний лимит размера временных таблиц в каждой сессии.
work_mem = RAM/32..64 или 32MB..128MB
Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически, максимально потребная память равна max_connections * work_mem, на практике такого не встречается потому что большая часть сессий почти всегда висит в ожидании. Это рекомендательное значение используется оптимайзером: он пытается предугадать размер необходимой памяти для запроса, и, если это значение больше work_mem, то указывает экзекьютору сразу создать временную таблицу. work_mem не является в полном смысле лимитом: оптимайзер может и промахнуться, и запрос займёт больше памяти, возможно в разы. Это значение можно уменьшать, следя за количеством создаваемых временных файлов:
Лимит памяти для обслуживающих задач, например вакуум, автовакуум или создания индексов.
Оценка размера кеша файловой системы. Увеличение параметра увеличивает склонность системы выбирать IndexScan планы. И это хорошо.
Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Для одиночного диска = 1, для RAID — 2 или больше.
Стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PgSQL к выбору планов с сканированием всей таблицы (PgSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). И это плохо.
Включение автовакуума. Не выключайте его!
Количество процессов автовакуума. Общее правило — чем больше write-запросов, тем больше процессов. На read-only базе данных достаточно одного процесса.
Время сна процесса автовакуума. Слишком большая величина будет приводить к тому, что таблицы не будут успевать вакуумиться и, как следствие, вырастет bloat и размер таблиц и индексов. Малая величина приведет к бесполезному нагреванию.
Время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на checkpoint процесс и процессы, обслуживающие сессии (backend). Малое значение приведет к полной загрузке одного из ядер.
Параметры, управляющие интенсивностью записи фонового процесса записи. За один цикл bgwriter записывает не больше, чем было записано в прошлый цикл, умноженное на bgwriter_lru_multiplier, но не больше чемbgwriter_lru_maxpages.
Выключение синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность.
Максимальное количество сегментов WAL между checkpoint. Слишком частые checkpoint приводят к значительной нагрузке на дисковую подсистему по записи. Каждый сегмент имеет размер 16MB.
Степень "размазывания" checkpoint’a. Скорость записи во время checkpoint’а регулируется так, что бы время checkpoint’а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_target.
Минимальное и максимальный объем WAL файлов. Аналогично checkpoint_segments.
Выключение шифрования. Для защищенных ЦОД-ов шифрование бессмысленно, но приводит к увеличению загрузки CPU.
Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания. Внимание: если RAID имеет кеш и находиться в режиме write-back, проверьте наличие и функциональность батарейки кеша RAID контроллера! Иначе данные записанные в кеш RAID могут быть потеряны при выключении питания, и, как следствие, PgSQL не гарантирует целостность данных.
Групповой коммит нескольких транзакций. Имеет смысл включать, если темп транзакций превосходит 1000 TPS. Иначе эффекта не имеет.
Дисковое пространство для временных таблиц/индексов. Помещение временных таблиц/индексов на отдельные диски может увеличить производительность. Предварительно надо создать tablespace командой CREATE TABLESPACE. Если характеристики дисков отличаются от основных дисков, то следует в команде указать соответствующий random_page_cost. См. статью.
Отключение контроля разрешения уровня записи.
Максимальное количество открытых файлов на один процесс PostreSQL. Один файл это как минимум либо индекс либо таблица, но таблица/может состоять из нескольких файлов. Если PostgreSQL упирается в этот лимит, он начинает открывать/закрывать файлы, что может сказываться на производительности. Диагностировать проблему под Linux можно с помощью команды lsof.
Параметры для платформы 1С:Предприятия
Разрешить использовать символ для экранирования.
Не выдавать предупреждение о использовании символа для экранирования.
Максимальное число блокировок индексов/таблиц в одной транзакции.
Количество одновременных коннектов/сессий.
Параметры для PgBadger
При большой нагрузке может влиять на производительность по причине большого потока записи на диск. Лучше вынести на отдельный шпиндель.
Примечание. Здесь пока никак не рассматриваются вопросы ротации логов и использования самого PgBadger’a.
Параметры дополнительных модулей
plantuner
Исправляет чрезмерную пессимистичность оптимизатора посгтреса на пустых, недавно созданных таблицах.
online_analyze
Автоматически анализировать временные таблицы при их изменении. Фоновый analyze может заметно отставать, и, как результат, планер ошибается.
Отключение излишней болтливости автоматического analyze.
