Настоящий краудфандинг. Даешь сравнение двух СУБД !
Предисловие
Как можно сравнить архитектуру 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
Таблицы
Индексы
[ Один из типов – btree. Индекс физическом уровне держится в страницах. Нижние страницы указывают на содержимое таблицы, внутренние – на дочерние страницы. Из одной страницы может быть много ссылок. Кроме ссылок в страницах индекса содержатся ключи данных. Страницы индексов на диске должны располагаться удобно для последовательного чтения. Нарушение последовательности, вызванное добавлением страниц - внешняя фрагментация. Чрезмерное свободное место на странице - внутренняя фрагментация. При чтении из индекса, получаем упорядоченные данные. ]
[ MS Нижний слой кластерного индекса - исходная таблица. ]
[ PG Нижний слой кластерного индекса – указатели на исходную таблицу. Поскольку в таблице содержится несколько версий данных, для получения актуальной версии требуется дополнительная информация. ]
[ Для выбора лучшего плана запроса используется дополнительная информация:
- Гистограмма распределения количества строк по ключевым столбцам индекса. Обычно содержит 100-200 интервалов. Приблизительное количество строк в таблице.
- Данные о зависимости между столбцами.
- Данные о избирательности (плотности) индекса – количество различных значений.
Для временных таблиц обязательно создается временная статистика. При создании индекса статистика пересчитывается. Дефрагментация индекса не изменяет распределение данных. Обновление статистики может вызвать перекомпиляцию запросов. ]
[ MS На уровне базы данных предусмотрены параметры: AUTO_UPDATE_STATISTICS обновлять статистику перед запросом, при необходимости, в зависимости от количества (процента) измененных строк, количества строк в таблице. AUTO_UPDATE_STATISTICS_ASYNC запрос выполняется без обновления статистики, даже если статистика устарела, но СУБД пытается восстановить статистику перед следующим выполнением запроса. СУБД может создавать дополнительную статистику, если установлено AUTO_CREATE_STATISTICS.
Пересчет статистики выполняется по приблизительной схеме, если не указать FULLSCAN. Статистику можно обновлять после регламентных операций, связанных с изменением большого количества строк. Для просмотра DBCC SHOW_STATISTICS. ]
[ PG Значения статистики автоматически обновляются командами VACUUM, ANALYZE. Параметры обновления в файле postgresql.conf. Для просмотра pg_stats. ]
Буферный кеш
Контрольная точка
[ Процесс, записывающий все «грязные» страницы в базу данных. Необходим для целей восстановления. Когда все изменения содержатся в базе данных на диске - это более устойчивое положение. Процесс восстановления на основании контрольной точки запускается автоматически при старте СУБД, если начальное состояние не совпадает с контрольной точкой. Контрольная точка может выполняться по плану в зависимости от условий, при завершении работы СУБД или создании резервной копии, вручную. Длительные незафиксированные транзакции препятствуют созданию контрольных точек.
Условия создания контрольных точек отличаются в (pg) и (ms), аргументами могут быть: время между точками, количество «грязных» страниц в буфере, и т.д. ]
[ MS Параметр, влияющий на расписание recovery interval. После MS2012 между двумя контрольными точками для уменьшения количества "грязных" страниц запускается "косвенная контрольная точка"]
[ PG Параметр, влияющий на расписание checkpoint_timeout. Между двумя контрольными точками для уменьшения количества "грязных" страниц запускается процесс bgwritter. Статистика работы в представлении pg_stat_bgwriter. Указание на последнюю контрольную точку хранится в файле $PGDATA/global/pg_control ]
Журнал ...
Уровни изоляции
[ Четыре уровня установлены стандартом 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 могут использовать укрупнение. ]
