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

25.03.23

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

Первый вариант сравнения двух СУБД. Каждый может внести правку и получить 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 могут использовать укрупнение. ]

См. также

Создаем сценарии обслуживания SQL в Центре Контроля Качества 1С (Центр Администрирования)

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

Данная статья научит вас, как создавать скрипты обслуживания MS SQL для Центра Контроля Качества (ЦКК) или Центра Администрирования (ЦА).

20.03.2024    512    Silenser    0    

3

Поинтегрируем: сервисы интеграции – новый стандарт или просто коннектор?

Обмен между базами 1C Администрирование СУБД Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

В платформе 8.3.17 появился замечательный механизм «Сервисы интеграции». Многие считают, что это просто коннектор 1С:Шины. Так ли это?

11.03.2024    4488    dsdred    53    

71

Инструкция по установке Postgres для OLTP приложений и 1С. Часть 1. Базовая конфигурация

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

В Postgres достаточно подробная документация, и, видимо, поэтому при инсталляции Postgres для 1С большинство параметров приходится выставлять самим. Параметров в Postgres много, а составить эффективную комбинацию не так просто. Все упрощается, если рассмотреть профиль нагрузки, например, 1С это прежде всего профиль OLTP нагрузки – так устроены его метаданные (объекты). Если сосредоточиться на оптимизации профиля OLTP, понимание Postgres сразу упростится.

15.02.2024    2331    1CUnlimited    14    

27

Очистка устаревших патчей в конфигурациях на базе БСП

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

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

01.02.2024    1433    Sergey1CSpb    20    

15

Как запустить сервер лицензирования 1С на примере облачной платформы

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

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

25.01.2024    1648    doctor_it    15    

17

Обслуживание индексов MS SQL Server: как, когда и, главное, зачем?

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

Казалось бы, базовое знание: «индексы надо обслуживать, чтобы запросы выполнялись быстро». Но обслуживание индексов выполняется долго и может мешать работе пользователей. Кроме того, в последнее время популярны разговоры о том, что индексы можно вообще не обслуживать – насколько это оправданно? Рассмотрим: на что влияет обслуживание индексов, когда надо и когда не надо его выполнять, и если надо – как это сделать так, чтобы никому не помешать?

16.01.2024    5789    Филин    13    

45

Резервное копирование журнала транзакций, наконец-то!

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

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

04.12.2023    5865    n_mezentsev    15    

24

Дефрагментация индексов MS SQL для платформы 8.3.22

Инструменты администратора БД Администрирование СУБД Абонемент ($m)

Начиная с 8.3.22 фирма "1С" убрала блокировки БД на уровне страниц; если БД расположена в MS SQL, то стала возникать проблема при дефрагментации индексов с использованием команды ALTER INDEX REORGANIZE. Предлагаю модификацию известного скрипта, который позволяет обойти эту проблему.

1 стартмани

15.11.2023    2245    17    baturo    6    

13