Ещё больше ЗОЖ для журнала транзакций

Автор: Paul Randal, Trimming More Transaction Log Fat

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

Множество очень маленьких транзакций

Время от времени SQL Server сбрасывает часть журнала транзакций на диск. Сброс журнала происходит всякий раз, когда:

• Генерируется запись журнала фиксации транзакции (commit).
• Генерируется запись журнала прерывания транзакции (abort) в конце отката транзакции.
• С момента предыдущего сброса журнала накопилось 60 КБ записей журнала.

Наименьший возможный сброс журнала — это один блок размером 512 байт. Если все транзакции в рабочей нагрузке очень маленькие (например, вставка одной маленькой строки таблицы), то будет происходить множество минимальных сбросов журнала. Сбросы журнала выполняются асинхронно, чтобы обеспечить приемлемую пропускную способность журнала транзакций, но существует фиксированный предел в 32 одновременных операции ввода-вывода сброса журнала (в SQL Server 2012 он увеличен до 112).

Это может иметь два возможных последствия:

1. На медленной подсистеме ввода-вывода объём крошечных записей в журнал транзакций может перегрузить подсистему ввода-вывода, что приведёт к задержкам записи и последующей деградации пропускной способности журнала транзакций. Эта ситуация выявляется по высоким задержкам записи для файла журнала транзакций в выводе sys.dm_io_virtual_file_stats.
2. На высокопроизводительной подсистеме ввода-вывода записи могут выполняться чрезвычайно быстро, но предел в 32 одновременных сброса журнала создаёт узкое место при попытке сделать записи журнала долговечными на диске. Эта ситуация выявляется по низким задержкам записи и почти постоянному числу незавершённых операций записи в журнал транзакций, близкому к 32, в агрегированном выводе sys.dm_io_pending_io_requests.

В обоих случаях увеличение размера транзакций (что очень неинтуитивно!) может снизить частоту сбросов журнала транзакций и повысить производительность. Кроме того, в случае №1 переход на более производительную подсистему ввода-вывода может помочь, но может привести к случаю №2. В случае №2, если транзакции нельзя сделать более длинными, единственной альтернативой является распределение рабочей нагрузки по нескольким базам данных, чтобы обойти фиксированный предел в 32 одновременных сброса журнала, или обновление до SQL Server 2012 или новее.

Автоматический рост журнала транзакций

Всякий раз, когда в журнал транзакций добавляется новое пространство, оно должно быть инициализировано нулями (запись нулей для перезаписи предыдущего использования этой части диска), независимо от того, включена ли функция мгновенной инициализации файлов или нет. Это относится к созданию, ручному увеличению и автоматическому росту журнала транзакций. Пока выполняется инициализация нулями, записи журнала не могут быть сброшены в журнал, поэтому автоматический рост во время рабочей нагрузки, изменяющей данные, может привести к заметному падению пропускной способности, особенно если размер автоматического роста установлен большим (например, гигабайты) или оставлен по умолчанию 10% (это стало лучше начиная с SQL Server 2022 - описываемое в этой статье поведение журнала транзакций претерпело значительное изменение).

Таким образом, по возможности следует избегать автоматического роста, позволяя журналу транзакций очищаться, чтобы всегда оставалось свободное пространство для новых записей журнала. Очистка журнала транзакций (также известная как усечение журнала транзакций, не путать с уменьшением журнала транзакций) выполняется резервными копиями журнала транзакций при использовании полной или массово-журналируемой моделей восстановления и контрольными точками при использовании простой модели восстановления.

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

Вы можете прочитать больше об этом здесь и здесь.

Функции высокой доступности

Некоторые функции высокой доступности также могут задерживать очистку журнала транзакций:

• Зеркалирование баз данных и группы доступности при работе в асинхронном режиме могут накапливать очередь записей журнала, которые ещё не были отправлены в резервную копию базы данных. Эти записи журнала должны храниться до тех пор, пока они не будут отправлены, задерживая очистку журнала транзакций.
• Транзакционная репликация (а также отслеживание изменённых данных CDC) использует задание агента чтения журнала для периодического сканирования журнала транзакций на предмет транзакций, изменяющих таблицу, входящую в публикацию репликации. Если агент чтения журнала по какой-либо причине отстаёт или намеренно запускается редко, все записи журнала, которые ещё не были просканированы заданием, должны храниться, задерживая очистку журнала транзакций.

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

Кстати, аналогичная проблема может возникнуть, если сама подсистема ввода-вывода синхронно зеркалируется — с потенциальной задержкой для всех операций записи, выполняемых SQL Server.

Резюме

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

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