Настоящий краудфандинг. Даешь сравнение двух СУБД!

Публикация № 1234263

Администрирование - Администрирование СУБД

Первый вариант сравнения двух СУБД. Каждый может внести правку и получить SM. Приветствуются конструктивные комментарии, начинающиеся словами "Автор ничего не понимает".

Настоящий краудфандинг. Даешь сравнение двух СУБД !

 

Предисловие

Как можно сравнить архитектуру MS SQL и PostgreSQL?. Постепенно пришло понимание, что такое сравнение можно создать коллективным трудом на нашей любимой площадке. Ниже первый вариант статьи. Всех желающих участвовать ожидает sm-вознаграждение. Предложения по статье присылайте в личку или в комментарии.

Правила разработки статьи

Максимально сокращаем текст, выбираем только существенные черты. Не используем копирование источников, пишем своими словами. Не обсуждаем архитектурные решения, которые:

  • применяются редко, опционально. Примером таких решений (на мой взгляд) могут быть buffer pool extension (ms), cache prewarm (pg), blockchain extension (pg).
  • недоступны типовыми средствами 1С. Например, платформа 1С не использует некоторые типы индексов

Если Вы не согласны – давайте обсудим.

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

Соглашение о терминах и сокращениях

  • Диск = Энергонезависимая память (СХД, HHD, SSD, RAID)
  • Память = Оперативная память, RAM
  • PG= PostgreSql
  • MS = MS SQL
  • SR = SERIALIZABLE
  • RR = REPEATABLE READ
  • RC = READ COMMITTED
  • RCSI = READ COMMITTED SNAPSHOT ISOLATION
  • RUnC = READ UNCOMMITTED

Таблицы

[ Логические таблицы могут состоять из нескольких файлов. Файлы на физическом уровне состоят из страниц. Размер страницы обычно 8 кб. ]

[ PG Таблица содержит несколько версий данных, с указанием диапазона транзакций, в которых данные актуальны. ]

[ MS Файл данных имеет расширение mdf. 8 страниц объединяются в экстенты 64 кб, размер экстента должен совпадать с размером физической страницы диска. ]

Индексы

[ Один из типов – btree. Индекс физическом уровне держится в страницах. Нижние страницы указывают на содержимое таблицы, внутренние – на дочерние страницы. Из одной страницы может быть много ссылок. Кроме ссылок в страницах индекса содержатся ключи данных. Страницы индексов на диске должны располагаться удобно для последовательного чтения. Нарушение последовательности, вызванное добавлением страниц - внешняя фрагментация. Чрезмерное свободное место на странице - внутренняя фрагментация. При чтении из индекса, получаем упорядоченные данные. ]

MS Нижний слой кластерного индекса - исходная таблица. ]

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

см. также https://its.1c.ru/db/metod8dev/content/1590/hdoc

Статистика

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

  • Гистограмма распределения количества строк по ключевым столбцам индекса. Обычно содержит 100-200 интервалов. Приблизительное количество строк в таблице.
  • Данные о зависимости между столбцами.
  • Данные о избирательности (плотности) индекса – количество различных значений.

Для временных таблиц обязательно создается временная статистика. При создании индекса статистика пересчитывается. Дефрагментация индекса не изменяет распределение данных. Обновление статистики может вызвать перекомпиляцию запросов. ]

MS На уровне базы данных предусмотрены параметры: AUTO_UPDATE_STATISTICS обновлять статистику перед запросом, при необходимости, в зависимости от количества (процента) измененных строк, количества строк в таблице. AUTO_UPDATE_STATISTICS_ASYNC запрос выполняется без обновления статистики, даже если статистика устарела, но СУБД пытается восстановить статистику перед следующим выполнением запроса. СУБД может создавать дополнительную статистику, если установлено AUTO_CREATE_STATISTICS.

Пересчет статистики выполняется по приблизительной схеме, если не указать FULLSCAN. Статистику можно обновлять после регламентных операций, связанных с изменением большого количества строк. Для просмотра DBCC SHOW_STATISTICS. ]

PG Значения статистики автоматически обновляются командами VACUUM, ANALYZE. Параметры обновления в файле postgresql.conf. Для просмотра pg_stats. ]

Буферный кеш

[ Чтобы уменьшить количество операций чтения-записи к диску, страницы СУБД предварительно считывают с диска в память, измененные (грязные) страницы записывают обратно, когда будет удобно. Физическим чтением называется считывание страниц с диска, логическим – чтение страниц буферного кеша. Если в кеше недостаточно места, (невостребованные) данные вытесняются, заменяются другими. Временные таблицы в буферном кеще не хранятся: их не нужно восстанавливать после сбоя. ]

[ MS Показатели представления sys.dm_os_performance_counters, perfmon.exe:

  • Коэффициент попадания в буферный кэш (Buffer cache hit ratio)
  • Ожидаемый срок жизни страницы (Page life expectancy) ]

[ PG Размер устанавливается параметром shared_buffers. Есть рекомендация выделять 1/4 памяти. Расширение для наблюдения: pg_buffercache]

 Контрольная точка

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

Условия создания контрольных точек отличаются в (pg) и (ms), аргументами могут быть: время между точками, количество «грязных» страниц в буфере, и т.д. ]

[ MS  Параметр, влияющий на расписание recovery interval. После MS2012 между двумя контрольными точками для уменьшения количества "грязных" страниц запускается "косвенная контрольная точка"]

[ PG Параметр, влияющий на расписание checkpoint_timeout. Между двумя контрольными точками для уменьшения количества "грязных" страниц запускается процесс bgwritter. Статистика работы в представлении pg_stat_bgwriter. Указание на последнюю контрольную точку хранится в файле $PGDATA/global/pg_control ]

Журнал ...

[ В терминах MS - журнал транзакций, в терминах PG - журнал предзаписи, WAL. Итак, «грязные» страницы обрабатываются в памяти и периодически записываются на диск. Если произойдет сбой в памяти, то может потеряться информация после контрольной точки. Чтобы избежать потери, перед записью в базу делаем «запасную» запись в журнал. Временные файлы и временные таблицы записывать в журнал нет смысла.

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

Журнал позволяет выполнять:

  • откат транзакций
  • восстановление незавершенных транзакций при запуске СУБД
  • восстановление состояния СУБД на момент
  • репликацию СУБД;

Сокращать журнал имеет смысл только после выполнения контрольной точки. ]

MS При модели восстановления SIMPLE журнал усекается автоматически при достижении контрольной точки, при модели восстановления FULL или BULK-logged после резервного копирования журнала. ]

[ PG Применяется несколько способов проверки записи WAL: при включенном или отключенном кеше диска, использовать для проверки одну из функций fdatasync, fsync, open. ]

 Уровни изоляции

[ Четыре уровня установлены стандартом SQL92, объявляются при начале транзакции СУБД командой SET TRANSACTION.

Уровень изоляции

Фантомное чтение

Неповторяющееся чтение

«Грязное» чтение

Потерянное обновление

SERIALIZABLE

+

+

+

+

REPEATABLE READ

-

+

+

+

READ COMMITTED

-

-

+

+

READ UNCOMMITTED

-

-

-

+

Подробнее об использовании блокировок платформой 1С смотрите Руководство разработчика пункт 9.3. Механизм управляемых блокировок, управление блокировками.

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

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

Serializable - Самый высокий уровень изолированности; транзакции полностью изолируются друг от друга. На этом уровне применяются блокировки диапазонов (предикатные блокировки), которых захватывают граничные значения. ]

[ MS RUnC Выполняется запросом с хинтом nolock ]

[ PG RUnC Невозможно стандартными средствами СУБД, только дополнительной утилитой]

[ MS RC Использует блокировки. Перед чтением строки устанавливается блокировка, после чтения – снимается. Возможно раньше окончания транзакции ]

[ MS RCSI Использует мультиверсионность. В транзакции, при первом обращении к данным, происходит снимок данных (Snapshot), который хранится в tempdb (до версии 19)]

[ PG RC Использует мультиверсионность. Все версии данных хранятся в таблицах. Каждая версия содержит интервал номеров транзакций, в которых она актуальна. Неактуальные версии данных очищаются в фоновом режиме заданием vacuum]

[ MS RR При изменении данных параллельными транзакциями происходит ошибка в транзакции, которая начинается последней. Блокировки в разделяющем режиме применяются ко всем данным, считываемым любой инструкцией транзакции, и сохраняются до её завершения. Это запрещает другим транзакциям изменять строки, которые были считаны незавершённой транзакцией. Однако другие транзакции могут вставлять новые строки, соответствующие условиям поиска инструкций, содержащихся в текущей транзакции ]

[ PG RR использует снимок на начало транзакции. более строгое, чем предусматривает стандарт. При изменении данных параллельными транзакциями происходит ошибка в транзакции, которая завершается последней. Допускается параллельная транзакция чтения. Применяются предикатные блокировки, которые могут сниматься после (!) окончания транзакции и укрупняться на уровень таблицы. ]

[ PG SR параллельные транзакции не допускаются. Применяются предикатные блокировки, которые могут быть сняты до или после (!!) транзакции. Блокировки можно видеть в pg_locks. Предикатные блокировки строк могут укрупняться в одну предикатную блокировку уровня таблицы из-за нехватки памяти. ]

Транзакции и блокировки

[ Транзакция - последовательность операторов DML со свойствами:

  • Atomicity — Атомарность
  • Consistency — Перевести базу данных из одного согласованного состояния в другое.
  • Isolation — Параллельные транзакции не должны оказывать влияния.
  • Durability — Стойкость (надежность диска)

Протокол 2PL: установка, снятие. В базовом протоколе используются два типа блокировок: Shared и Exclusive locks.

Неявные транзакции СУБД: создание индекса, реструктуризация базы. В терминах 1С неявные транзакции – например, запись объекта. При чтении объекта с табличной частью в режиме совместимости после 8.2.17 нет неявной транзакции (!).

Блокировка есть информация. При установке блокировки, СУБД проверяет наличие существующей блокировки.

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

[ MS  Информация о блокированных строках хранится в оперативной памяти. При большом количестве блокированных строк, для предотвращения большого расхода памяти, происходит укрупнение гранулярности (эскалация) блокировки. ]

[ PG Исключительная блокировка строк RC отражается как версия данных, на той же странице. Нет потребности в эскалации для экономии памяти. Для разделяемых блокировок применяются так называемые мультитранзакции (MultiXact). Это группа транзакций, которой присвоен отдельный номер. Предикатные блокировки в режиме RR могут использовать укрупнение. ]

Специальные предложения

Вознаграждение за ответ
Показать полностью
Комментарии
Избранное Подписка Сортировка: Древо развёрнутое
Свернуть все
1. Mari_Kuznetzova 41 11.05.20 12:50 Сейчас в теме
Здравствуйте, коллеги ! Получать SM и кидать помидоры в аффтора здесь !!
2. capitan 1622 11.05.20 14:32 Сейчас в теме
Мария Андреевна, сравнивать что то можно только применительно к очень конкретной задаче.
Это как кружевное белье и спортивный бра сет.
Они полезны каждая в своем месте. И на своем месте ;)
Ну или если хотите из детства - кому то веревочка, а кому то полезный подарок.

Постгри - бесплатный, но все его настройки масштабирование и т.д. и т.п. работают только в умелых руках. Спасибо постгрипро, что они пилят версию для 1С с очень хорошими дефолтными настройками.
А Скуль мелкомягких - дорого, но сел и поехал, 90% могут вообще не задумываться о настройках.
Применительно к 1С - фреш работает на постгри, это о многом говорит. 1С подкрутит под постгри и платформу.
Пока -на порядок хуже работа с временными таблицами (читай запросами), остальное на базах до 100 пользователей практически не чуствуется.
А на быстрых дисках и запросы не чуствуются.
Не буду рекламировать себя - поищите поиском - есть сравнение производительности средних баз 1С под обе СУБД.
Tavalik; awk; +2 Ответить
3. Mari_Kuznetzova 41 11.05.20 15:27 Сейчас в теме
(2) Спасибо, но интересует только архитектура.
4. capitan 1622 11.05.20 18:55 Сейчас в теме
(3)Архитектура у них принципиально разная, противопоставление версионников и блокировочников если на бытовом уровне, но 1С и в принципе приложениям прикладного уровня это вообще не интересно до определенного масштаба базы, т.к. стандарт ANSI SQL еще никто не отменял.
А для 1С кстати и после определенного масштаба базы, потому что самостоятельное например добавление индексов противоречит лицензионному соглашению.
8. 3vs 12.05.20 06:45 Сейчас в теме
(4)А товарищ Юрий Пермитин балуется, да, ябеда, а что! :-)
Хотелось бы, чтобы архитекторы 1С прислушивались к советам такого гиганта мысли, как
Юрий Пермитин!
5. acanta 74 11.05.20 19:07 Сейчас в теме
Если можно для "новеньких". Разница версионник или блокировочник - это про восстановление из бакапа на какой-то момент времени (номер транзакции)?Единый журнал транзакций в принципе отсутствует и размазан по всем таблицам или это просто дополнительное удобство? У PG нет режимов simple/bulk?
6. awk 714 11.05.20 21:11 Сейчас в теме
(5) Нет. Это про политику. Версионник на каждую транзакцию делает срез БД. За счет чего достигается высокая параллелизация работы ценой потребления ресурсов. Блокировщик использует политику: "блокирну, авось клиент не отвалится по таймауту". Параллелизация к черту, зато все быстро, целостно и не ресурсоемко.

Это ооочень грубо...
7. 3vs 12.05.20 06:42 Сейчас в теме
Интересно, а сайт www.gosuslugi.ru на PG крутится?

Вчера, после сообщения товарища Путина о единовременном пособии на детей до 16 лет по 10000 рублей на ребёнка, можно будет оформить на госуслугах с сегодняшнего дня, некоторые ретивые родители ломанулись уже вчера на сайт и обрушили его! :-)
Кто не выдержал, СУБД или WEB-сервер?
SuhoffGV; +1 Ответить
9. acanta 74 12.05.20 07:29 Сейчас в теме
Правильно ли я понимаю, что PG при изменении данных сначала находит актуальные данные среди версий, затем записывает в них номер финальной транзакции, после чего создает новую версию и пишет номер открывающей транзакции и так по каждой таблице, включая итоги?
А можно так чтобы справочники и первичка были в PG, а служебные данные в MS?
Можно ли получить отчет по этим версиям в PG из 1с когда версионирование средствами платформы отключено?
14. awk 714 12.05.20 17:07 Сейчас в теме
10. VmvLer 12.05.20 09:56 Сейчас в теме
к этой теме необходимы не см, а набор линеек.
первые попытки измерений уже случились.

не думаю, что кроме метрики увижу тут че-нить полезное - буду вести наблюдение.
11. acanta 74 12.05.20 11:43 Сейчас в теме
Про инструменты и лицензионное соглашение понимают все., Вопрос скорее в том, можно ли комментировать, если предложить нечего.
12. VmvLer 12.05.20 12:46 Сейчас в теме
я не внимательно читал стартовое сообщение, с первых абзацев захотелось спать от сухой стиля толкового словаря.

собственно вопрос для внимательных: в стартовом сообщении есть ответ на вопрос "зачем это нужно?"
-может это повысит добычу угля на 300%
-коровы рекордистки станут давать по три ведра сливок за раз
-может автор собирается колонизировать Луну
-...

есть простой ответ на вопрос?
16. vasilev2015 1809 12.05.20 19:31 Сейчас в теме
(12) Здравствуйте ! Просто сравнить в энциклопедическом стиле. Деньгами здесь не пахнет, советов "как обмануть начальника" нет. В общем, читать нечего.
13. acanta 74 12.05.20 12:53 Сейчас в теме
"Все познаётся в сравнении". Например я искала ответ на вопрос об "утечке памяти", почему в MS она есть а в PG нет.
Но пока не нашла, а споткнулась о версии.
Для меня это выглядит как сравнение двух моделей черных ящиков, причем один из них некий эталон, но давно снят с производства, а другой в стадии разработки.
15. vasilev2015 1809 12.05.20 19:29 Сейчас в теме
(13) Здравствуйте, Анна ! Утечки памяти есть во всех программах, без исключения. Обычно говорят, что на PG нет эскалации блокировок строк до уровня таблицы.
17. Gilev.Vyacheslav 1849 13.05.20 23:07 Сейчас в теме
(0)
надежность бэкапов постгреса на фоне ms sql server оставляет желать лучшего
18. Gilev.Vyacheslav 1849 13.05.20 23:08 Сейчас в теме
(0) "MS Файл данных имеет расширение mdf." это по дефолту
можно нарезать кучу ndf файлов (а при желании вообще не указывать расширение)
19. Gilev.Vyacheslav 1849 13.05.20 23:10 Сейчас в теме +5 $m
(0) основное архитектурное отличее крупно это способ хранения временных таблиц
в ms для кучи баз будет одна tempdb и при дизбалансе нагрузки придется подымать дополнительные экземпляры ms чтобы разнести нагрузку на временные таблицы
как в pg хранятся временные таблице в основной базе думаю пояснять не надо
20. Mari_Kuznetzova 41 15.05.20 09:33 Сейчас в теме
(19) Спасибо за поддержку, буду читать документацию.
Оставьте свое сообщение

См. также

Выявляем и оптимизируем ресурсоемкие запросы 1С:Предприятия

Производительность и оптимизация (HighLoad) Администрирование СУБД Технологический журнал Структура метаданных v8::Запросы Бесплатно (free)

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

24.05.2020    4023    0    DataReducer    22    

Эти занимательные временные таблицы

Производительность и оптимизация (HighLoad) Администрирование СУБД v8 Бесплатно (free)

Кое-что интересное о временных таблицах и работе платформы 1С с ними.

06.04.2020    8704    0    YPermitin    0    

1С + Apache + SSL: Перевод опубликованной базы на защищенное соединение https с сертификатом от Let's encrypt windows

Администрирование СУБД v8 1cv8.cf Бесплатно (free)

Есть куча инструкции про связку с ISS, решил добавить свои 5 копеек, как я это настраивал на Apache на Windows.

02.03.2020    2312    0    rst_filippov    5    

Ошибка при обновлении: Записи регистра сведений стали неуникальными: Двоичные данные файлов

Администрирование СУБД v8 Бесплатно (free)

Способ обойти ошибку обновления Записи регистра сведений стали неуникальными: ДвоичныеДанныеФайлов.

26.02.2020    2604    0    dubovenko_m    9    

Контроль места на дисках

Администрирование СУБД v8 1cv8.cf Бесплатно (free)

Один из последних случаев на работе. Диск, на котором хранились файлы базы, "развалился", база потеряна. Начали искать копию базы. Копии базы делались на другой диск, но оказалось, что на том диске нет места и копии не делались несколько дней. Так было потеряно несколько дней работы фирмы, кому-то выговор, кого-то уволили((.

20.02.2020    2705    0    wowik    20    

Нюансы лицензирования 1С

Администрирование СУБД v8 1cv8.cf Россия Бесплатно (free)

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

19.02.2020    7578    0    fixin    112    

Как мы научились автоматически отслеживать ошибки в 1С

Администрирование СУБД v8 1cv8.cf Россия Бесплатно (free)

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

04.02.2020    11630    0    slozhenikin_com    27    

Автономный сервер. Часть 2 - утилита управления

Администрирование СУБД v8 Бесплатно (free)

Утилита управления "Автономным сервером" может не только управлять. Какие возможности можно использовать уже сегодня? Разбираем с примерами и ищем отличия от привычных методов.

21.12.2019    8051    0    -vito-    26    

Автономный сервер. Часть 1 - новый вариант сервера

Администрирование СУБД v8 Бесплатно (free)

В Платформе версии 8.3.14 появился новый вариант серверной архитектуры - "Автономный сервер" (бета-версия). Выясняем, что это такое, какова сфера его применения, что он позволяет уже сейчас, чего можно ожидать.

21.12.2019    10150    0    -vito-    19    

Обслуживание баз данных. Не так просто, как кажется

Производительность и оптимизация (HighLoad) Администрирование СУБД v8 1cv8.cf Бесплатно (free)

Считаете, что обслуживание индексов и статистик дело простое? Что ж, это не всегда так.

14.10.2019    15206    0    YPermitin    28    

Кластер серверов 1С

Администрирование СУБД v8 Бесплатно (free)

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

24.09.2018    40313    0    a.doroshkevich    67    

Оптимизируй это! Или MS SQL и Экспертный подход творят чудеса!

Производительность и оптимизация (HighLoad) Администрирование СУБД v8 Бесплатно (free)

В статье речь пойдет про взаимодействие сервера 1С с MS SQL. Мы очень часто слышим, как важно оптимизировать все критические участки системы заблаговременно, в плановом режиме, как надо, «от и до» во всех деталях. Но в реальной жизни бывает по-другому. Очень часто клиенты обращаются к нам, когда система уже не дает работать: «спасите, помогите, болит очень сильно, надо решать». Об одном из таких случаев я и хотел бы вам сегодня рассказать.

11.07.2017    32035    0    R.Tsarenko    32    

PostgreSQL на Windows – реальная альтернатива для высоконагруженных систем на базе 1С

Производительность и оптимизация (HighLoad) Администрирование СУБД v8 Бесплатно (free)

Многие интересуются PostgreSQL, но не знают, насколько хорошо будет она работать с уже существующими системами. «Инфософт» - одна из первых компаний, кто опробовал PostgreSQL на Windows. О своем опыте перехода рассказывает руководитель отдела информационных технологий компании.      

23.06.2017    41549    0    a.doroshkevich    113    

Пример работы с SQLDMO

Администрирование СУБД v8 Бесплатно (free)

SQLDMO из 1С.

27.06.2013    3543    2    imm0rtal    2