Автор: Paul White https://www.sql.kiwi/2024/12/improved-ghosts-2022/
Уровень изоляции моментального снимка с фиксированным чтением (Read Committed Snapshot Isolation, далее: RCSI) даёт много преимуществ. Главное из них в том, что читатели не будут блокировать писателей (и наоборот). Каждый оператор видит снимок данных на определенный момент времени (за исключением некоторых случаев, таких как использование non-inlined функций, которые оптимизатор не может развернуть внутри запроса). С другой стороны, появляются затраты на поддержание версий строк, необходимых для реализации RCSI.
Речь идет не только о том, чтобы
убедиться, что база данных tempdb (или пользовательская
база данных (если используется ADR - ACCELERATED_DATABASE_RECOVERY) достаточно велика и может
справиться с дополнительной параллельной активностью:
- Писатели,
изменяющие (а иногда и добавляющие) данные, должны создавать версии строк.
- Система
должна уметь проверять и поддерживать компактность хранилища версий в фоновом
режиме.
- Читателям
необходимо найти правильную для них версию каждой строки выборки.
Последний пункт может привести к значительному снижению производительности, если в результате пользовательской активности получились длинные цепочки версий. RCSI подвержен подобным проблемам меньше, чем SI, поскольку каждый оператор RCSI видит более позднюю точку во времени. Но это не исключает проблем с долгими запросами с RCSI, обычно приходящими от отчётных и аналитических приложений. Мы тут не будем в это углубляться, поскольку это не является темой данной статьи.
Рассмотрим пример с часто обновляемой
таблицей в базе данных с использованием RCSI. Этот упрощённый пример использует
сценарий с одной строкой в таблице и с одним проиндексированным столбцом:
DROP TABLE IF EXISTS dbo.Test;
GO
CREATE TABLE dbo.Test (id integer PRIMARY KEY CLUSTERED);
INSERT dbo.Test (id) VALUES (0);
GO
CHECKPOINT;
Чтобы сделать демонстрацию наглядней,
изменим значение 123456 раз:
SET NOCOUNT, XACT_ABORT ON;
SET STATISTICS XML OFF;
DECLARE
@i integer = 1;
WHILE @i < 123456
BEGIN
BEGIN TRANSACTION;
UPDATE TOP (1)
dbo.Test WITH (TABLOCKX)
SET @i = id = @i + 1;
COMMIT TRANSACTION
WITH (DELAYED_DURABILITY = ON);
END;
Примечание: Подсказки TOP (1) и TABLOCKX ускоряют обновления, снижая накладные расходы
на блокировку и избегая в плане запроса ненужный Halloween Protection.
Изменения с RCSI (без оптимизации блокировки) получают
UPDATE блокировки при поиске строк для изменения. Причина, по которой
блокировка таблицы поможет оптимизации, будет объяснена в следующих статьях.
Мы обновляем только одну строку (снова и
снова), поэтому Halloween Protection, для которого добавлен оператор TOP, помогает избавится от оператора Eager Spool для таблицы в плане запроса.
DELAYED_DURABILITY используется
в запросе поскольку мы не собираемся тестировать здесь производительность
журналирования транзакций. Если хотите, можете запустить пример без этих
оптимизаций.
Ниже показан (оптимизированный) предполагаемый план выполнения:
SELECT
[level] = IPS.index_level,
[pages] = IPS.page_count,
[used %] = FORMAT(IPS.avg_page_space_used_in_percent, 'N4'),
[avg size] = IPS.avg_record_size_in_bytes,
[rows] = IPS.record_count,
[ghosts] = IPS.ghost_record_count,
[versions] = IPS.version_record_count,
[ghost versions] = IPS.version_ghost_record_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID(N'dbo.Test', 'U'),
1, -- index id
NULL, -- partition
'DETAILED'
) AS IPS
ORDER BY
IPS.index_level;
SQL Server 2019 - Read Committed
Первый тест выполняем на SQL Server 2019 CU29-GDR (сборка 15.0.4410) — последняя доступная версии на момент написания статьи.
Тестовая база данных настроена на
использование простой модели восстановления и допускает DELAYED_DURABILITY. В ней отключены ADR, SI и RCSI:
ALTER DATABASE CURRENT
SET RECOVERY SIMPLE;
ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE CURRENT
SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE CURRENT
SET DELAYED_DURABILITY = ALLOWED;
ALTER DATABASE CURRENT
SET ACCELERATED_DATABASE_RECOVERY = OFF;
Тут мы устанавливаем базовый уровень
производительности с использованием Read Committed. В такой конфигурации тест выполняется примерно 2 секунды.
Запрос DMV показывает одну страницу в таблице, содержащую одну строку и не содержащую фантомных строк, как и ожидалось:
|
level |
pages |
used % |
avg size |
rows |
ghosts |
versions |
ghost
versions |
|
0 |
1 |
0.1359 |
11 |
1 |
0 |
0 |
0 |
Кстати, без DELAYED_DURABILITY тест выполняется в течение 12 секунд.
Фантомные строки
Когда мы меняем ключевой столбец индекса, изменение
выполняется как удаление с последующей вставкой. При удалении запись отмечается
как фантомная, а вставка добавляет новую строку.
Следующая итерация цикла затронет
фантомную запись во время просмотра индекса и добавит ее в очередь фоновой
задачи очистки фантомных строк. В зависимости от того, насколько быстро
выполняется очистка фантомов, последующие итерации должны будут просмотреть
большое число фантомных записей, прежде чем будет найдена одна не удаленная
строка.
В этом конкретном тесте на ноутбуке без
какой-либо другой активности, задача очистки фантомных строк справляется очень
хорошо — в конце теста в таблице нет фантомов. Это не всегда так даже в
современных версиях SQL Server с их долгой историей борьбы с фантомами, как это
описал Forrest McDaniel в статье Fixing Queues with Watermarks.
SQL Server 2019- RCSI
Давайте теперь перейдем от уровня изоляции транзакций Read Committed к RCSI, изменим в примере только это.
ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
Повторный запуск того же теста даст время
выполнения 1мин 22с. Это значительно больше, чем 2с при использовании уровня
изоляции Read Committed.
Запрос к DMV
показывает:
|
level |
pages |
used % |
avg size |
rows |
ghosts |
versions |
ghost
versions |
|
0 |
86 |
49.8145 |
25 |
1 |
12301 |
12848 |
547 |
|
1 |
1 |
13.7880 |
11 |
86 |
0 |
0 |
0 |
Отличие в этот раз в том, что просмотр не может поставить в очередь для очистки фантомные строки, которые затрагивает UPDATE, поскольку их номер транзакций не старше текущей старейшей активной транзакции. Это штатный алгоритм, с помощью которого SQL Server решает, безопасно ли очищать версии и фантомы или, когда используется уровень изоляции с версиями строк.
Обратите внимание, что это будет иметь
место и с транзакцией с авто-фиксацией, как и с явной, используемой в тесте.
Транзакция с авто-фиксацией также потребует использования FORCED DELAYED_DURABILITY,
поскольку нет оператора COMMIT, к которому можно прикрепить параметр DELAYED_DURABILITY.
Если вы попробуете эту конфигурацию, то обнаружите, что производительность та
же самая. Вы можете сэкономить несколько символов в коде запроса, полагаясь на
неявные транзакции, но, опять же, производительность будет той же самой.
После завершения каждой транзакции UPDATE
можно приступить к обычной очистке от фантомных строк и версий, которая
освободит таблицу от следов удалённых строк. Это асинхронный процесс в
отдельном системном потоке, поэтому он будет запущен тогда же, когда начнётся
следующий UPDATE.
Ситуация, которую мы увидим в конце теста,
демонстрирует промежуточное состояние после того, как некоторые фантомные
строки были очищены, но ещё не все.
После очистки фантомов
Через несколько секунд запрос DMV
возвращает:
|
level |
pages |
used % |
avg size |
rows |
ghosts |
versions |
ghost
versions |
|
0 |
2 |
0.3089 |
25 |
1 |
1 |
1 |
0 |
|
1 |
1 |
0.2965 |
11 |
2 |
0 |
0 |
0 |
Обратите внимание, что индекс приобрел
второй уровень, что означает расщепление исходной страницы во время теста. Это
произошло, потому что страница была заполнена активными версиями-фантомами. Выполняющая
вставку часть оператора UPDATE должна
была расщепить страницу и добавить новый корень для индекса. Это происходило
много раз, как мы можем заключить из предыдущего результата в выводе DMV,
показывающего 86 страниц на листовом уровне, все заполнены примерно на 50%, как
это обычно бывает после расщепления.
Размер строки листового уровня увеличился
с 11 байт до 25 из-за 14-байт накладных расходов на версионирование строк. Другие
уровни не используют версионирование или фантомы.
Единственный оставшийся фантом находится
на исходной странице индекса. SQL Server оставляет одну фантомную запись, когда
она находится на корневой странице индекса, как это можно видеть в системных
метаданных. Одна не удаленная строка находится на другой странице. Все
остальные страницы листового уровня индекса были очищены и удалены, поскольку
они были пустыми и не являются первой страницей.
Страшная правда о фантомах
Ужасная производительность при
использовании RCSI объясняется большим числом фантомных записей, которые
встречаются при каждом просмотре UPDATE и которые
приходится пропускать, чтобы добраться до единственной видимой строки.
SQL Server не только должен найти строку,
чтобы обнаружить, что она помечена как фантом, но и должен заранее запросить
для неё UPDATE-блокировку. Блокировка в конечном итоге
не получена, поскольку она покрыта существующим TABLOCKX, но диспетчеру
блокировок все равно нужно обработать запрос и обнаружить, что она избыточна
из-за уже удерживаемой блокировки. Без подсказки блокировки используются реальные
блокировки, и производительность ещё больше снизится.
Ни один из вышеперечисленных процессов по
отдельности не занимает много времени, но мониторинг показывает, что SQL Server
обрабатывал около 6 миллионов запросов
на блокировку в секунду во время теста (и затронул такое же количество пропущенных
в итоге фантомов).
Помните, что UPDATE-блокировки требуются при поиске соответствующих строк
даже в RCSI, когда недоступна или не включена поддержка Optimized Locking. На момент написания статьи эта поддержка обеспечена только в Azure SQL Database. Я ожидаю, что мы увидим её в SQL Server
2025.
В конечном счете, проблема производительности
во время теста вызвана необходимостью запрашивать блокировку, а затем
пропускать сотни миллионов фантомов. Помните, мы имеем дело с таблицей с одного
столбца с всего одной записью.
Очистка фантомов и версий в RCSI не может
начаться до окончания каждой транзакции, а следующий UPDATE начинается практически сразу, без пауз. Этот UPDATE столкнется с фантомами версий от предыдущих запусков,
а также с теми, которые оно создаст сам.
Но подождите, станет еще хуже.
SQL Server 2019 RCSI с не завершёнными транзакциями
Этот тест является повторением предыдущего,
но с открытой транзакцией. Удалите и заново создайте тестовую таблицу, как
обычно, но затем запустите транзакцию в отдельном сеансе, подключенном к той же
базе данных:
--
Will be RCSI
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
--
Leave this transaction open
BEGIN TRANSACTION;
SELECT COUNT_BIG(*) FROM dbo.AccessMe AS AM;
Важно, чтобы транзакция выполнялась под уровнем изоляции с версионностью строк и была действительно запущена (запросив пользовательскую таблицу в базе данных). В приведенном выше примере кода Вы можете использовать любую таблицу, даже саму таблицу dbo.Test.
Важно то, чтобы транзакция отображалась в DMV sys.dm_tran_active_snapshot_database_transactions. И ещё, обратите внимание, что транзакция не
обязательно должна использовать изоляцию моментального снимка — RCSI подойдет.
Запуск теста с UPDATE теперь длится больше четырех минут. Без DELAYED_DURABILITY
это займёт полчаса. Вспомните, что тест с Read Committed потребовал всего 12 секунд при тех же условиях. RCSI без открытой
транзакции занял 1мин 22с. Мы обновляем всего одну строку 123456 раз!
Запрос к DMV возвращает:
|
level |
pages |
used % |
avg size |
rows |
ghosts |
versions |
ghost
versions |
|
0 |
823 |
50.0148 |
25 |
1 |
0 |
123455 |
123455 |
|
1 |
2 |
66.0675 |
11 |
823 |
0 |
0 |
0 |
|
2 |
1 |
0.2965 |
11 |
2 |
0 |
0 |
0 |
Индекс для нашей таблицы с одной строчкой теперь имеет три уровня, с 823 страницами на листовом уровне. Существует 123455 версий-фантомов — по одному на каждый выполненный UPDATE. Эти фантомы не могут быть очищены, пока транзакция, которую мы оставили открытой, не будет зафиксирована или откатится. У каждого фантома есть строка с версией, которая записана в версионное хранилище.
Каждое из 123456 отдельных изменений записи
должно было проигнорировать на одну фантомную запись меньше, чем его порядковый
номер итерации. Например, стотысячное изменение должно было пропустить 99999 фантомных
записей.
Помните, что каждой пропущенной записи
предшествует запрос на блокировку. Блокировки нельзя избежать, поскольку транзакции
с SERIALIZABLE могут блокировать фантомные записи. Отсутствие блокировок может
привести к нарушению требований ACID.
Надеюсь, теперь понятно, насколько все это
неэффективно и почему потребовалось столько времени. Статистика ожиданий внести
ясности не сможет, потому что SQL Server не ждёт никакой ресурс — он работает
так усердно, как может, запрашивая блокировки и пропуская фантомы с впечатляющей
скоростью.
Если не посмотреть счетчик
производительности, отвечающий за подсчёт пропущенных фантомных записей, то
создается впечатление, что SQL Server тратит около получаса на обновление одной
строки 123456 раз, при этом одно ядро загружено по максимуму.
Не забудьте закрыть открытую транзакцию!
Все фантомные версии будут удалены в течение нескольких секунд после того, как
вы это сделаете.
В SQL Server 2022 стало лучше
На SQL Server 2022 тест с уровнем изоляции
транзакций Read Committed завершается за 2-3 секунды, как и раньше. Тест RCSI
выполняется около 1 мин 28 с, опять же примерно столько же, сколько и на 2019.
Большая разница будет в тесте с открытой
транзакцией. Этот тест теперь выполняется примерно столько же времени, сколько
и без открытой транзакции — 1 мин 32 сек. Это лучше, чем 4 минуту в тесте
раньше, но все еще не очень хорошо.
В самом конце теста DMV показывает, что
некоторые фантомы были очищены ещё во время выполнения:
|
level |
pages |
used % |
avg size |
rows |
ghosts |
versions |
ghost
versions |
|
0 |
231 |
49.9272 |
25 |
1 |
34201 |
34590 |
389 |
|
1 |
1 |
37.0768 |
11 |
231 |
0 |
0 |
0 |
Через несколько секунд очистка будет завершена:
|
level |
pages |
used % |
avg size |
rows |
ghosts |
versions |
ghost
versions |
|
0 |
2 |
0.3089 |
25 |
1 |
1 |
1 |
0 |
|
1 |
1 |
0.2965 |
11 |
2 |
0 |
0 |
0 |
Открытая RCSI транзакция больше не препятствует очистке фантомов, этим и объясняется улучшение производительности. Затрагивается меньше фантомов, поэтому запрашивается меньше блокировок и пропускается меньше записей. Вместо 6 миллионов запросов на блокировку в секунду теперь около 10000, что объясняется меньшим средним числом обнаруженных фантомных версий.
SQL Server 2022 с RCSI позволяет выполнять
очистку фантомных записей на уровне операторов. Это все еще не так быстро, как на
READ COMMITTED, но гораздо лучше, чем было в 2019. Если вам когда-нибудь
понадобится отключить этот алгоритм в SQL Server 2022, используйте этого недокументированный
глобальный флаг трассировки: 7006.
Важно
В SQL Server 2022 очистка фантомов
происходит раньше, только при условии, что существует параллельная активная
транзакция на RCSI. Записи хранилища версий не затрагиваются и будут сохраняться
до конца транзакции, как и прежде.
По крайней мере, так это задокументировано:
Even though READ
COMMITTED transactions using row versioning provides a transactionally
consistent view of the data at a statement level, row versions generated or
accessed by this type of transaction are maintained until the transaction
completes.
Несмотря на то что READ COMMITTED
транзакции, использующие управление версиями строк, обеспечивают согласованное
представление данных на уровне инструкции, версии строк, созданные или
доступные этим типом транзакции, пока транзакция не завершится.
Ожидается дальнейшее улучшение ситуации с этой
проблемой, но в настоящее время оно доступно только при включении
недокументированного флага трассировки и даже с ним не полностью функционально.
Возможно, стоит дождаться выхода SQL Server 2025.
Открытая транзакция на уровне изоляции
снимка по-прежнему предотвращает раннюю очистку фантомов, поскольку этой
транзакции может понадобиться обратиться к фантомам и фантомным версиями, и
именно это обеспечивает состояние снимка базы данных, в котором она была на
момент начала транзакции. Помните, RCSI обеспечивает снимок только того
состояния, которое было на момент начала текущего оператора.
Заключение
Вы не можете быстро обновить таблицу с одной
строкой и получить к ней доступ с блокировкой для просмотра, как было показано
в этих тестах. Тем не менее, фантомы являются тем, с чем приходится
сталкиваться, когда используется уровень изоляции с версиями строк, и
поддерживаются даже для таблицы – кучи.
Просмотр тут ведёт себя просто
отвратительно, но помните, что и любой поиски имеет компонент из набора компонент просмотра, если только это не проверки равенства на Enforced Key («одиночный поиск» в уникальном индексе или
ограничении). Компоненту просмотра при поиске диапазона значений может
потребоваться перелопатить кучу фантомов, чтобы найти строки, которые он может
логически «увидеть».
Версионирование строк сопряжено с
затратами, даже без длинных цепочек версий. Ни один из примеров в этой статье
не привел к появлению более одной записи в каждой цепочке версий. Если это
звучит надуманно, вспомните, что изменение реализовано как удаление с
последующей вставкой. На каждой итерации появляется новая «живая» запись.
Удобства RCSI и SI не даются даром. В
некоторых случаях эффекты могут быть драматичными и их трудно диагностировать,
если вы заранее не знаете, что ищете.
В настоящее время невозможно исключить
некоторые таблицы из управления версиями строк. Настройка действует для всей
базы данных и по принципу «все или ничего», как для часто обновленных таблиц и индексированных
представлений, так и для статичных таблиц.
Указание уровня изоляции транзакций READ COMMITTED
влияет только на то, будут ли получены Shared Locks, но не
поможет избежать фантомов и фантомных версий.
Версии строк всегда генерируются и
поддерживаются, если включен уровень изоляции с управлением версиями строк,
даже если он никогда не используется.
Длительные транзакции - всегда плохая
новость, особенно если они простаивают и непреднамеренны. SQL Server 2022 может
помочь избежать худшего из показанных здесь влияний на производительность, но
базовое время выполнения RCSI (1,5 минуты) все еще довольно большое по
сравнению с блокировкой READ COMMITTED (2 секунды). Это было получено без одновременных
других открытых транзакций.
Комментариев нет:
Отправить комментарий