Проблемы конфигурации журнала транзакций

Автор: Paul Randal, Transaction Log Configuration Issues

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

Примечание переводчика: начиная с SQL Server 2022 описываемое в этой статье поведение журнала транзакций претерпело значительное изменение.

Слишком много VLF

Журнал транзакций разбивается на фрагменты, называемые виртуальными файлами журнала (VLF), чтобы система управления журналом могла легко отслеживать, какие части журнала транзакций доступны для повторного использования. Существует формула, определяющая, сколько VLF вы получите при создании журнала транзакций, его ручном увеличении или автоматическом росте:

Размер файла	Количество VLF	Размер каждого VLF
До 1 МБ	2	примерно 1/2 от общего размера
от 1 МБ до 64 МБ	4	примерно 1/4 от общего размера
от 64 МБ до 1 ГБ	8	примерно 1/8 от общего размера
Более 1 ГБ	16	примерно 1/16 от общего размера

Например, если вы создадите журнал транзакций размером 8 ГБ, вы получите 16 VLF, каждый примерно по 512 МБ. Если затем увеличить журнал ещё на 4 ГБ, вы получите дополнительные 16 VLF, каждый примерно по 256 МБ, итого 32 VLF.

Примечание: этот алгоритм немного изменился для SQL Server 2014, чтобы уменьшить проблемы фрагментации VLF — подробности см. в этом сообщении блога.

Общей рекомендацией является установка автоматического роста журнала на значение, отличное от 10% по умолчанию, чтобы вы могли контролировать паузу, необходимую при инициализации нового пространства журнала транзакций. Скажем, вы создаёте журнал транзакций размером 256 МБ и устанавливаете автоматический рост 32 МБ, а затем журнал вырастает до устойчивого размера 16 ГБ. Согласно приведённой выше формуле, это приведёт к тому, что ваш журнал транзакций будет иметь более 2000 VLF.

Такое количество VLF, скорее всего, приведёт к проблемам с производительностью для операций, которые обрабатывают журнал транзакций (например, аварийное восстановление, очистка журнала, резервное копирование журнала, транзакционная репликация, восстановление баз данных). Эта ситуация называется фрагментацией VLF. Как правило, любое количество VLF более тысячи или около того будет проблематичным и требует внимания (самое большое количество, о котором я слышал, — 1,54 миллиона VLF в журнале транзакций размером более 1 ТБ!).

Узнать, сколько у вас VLF, можно с помощью недокументированной (и полностью безопасной) команды DBCC LOGINFO. Количество строк в выводе — это количество VLF в вашем журнале транзакций. Если вы считаете, что их слишком много, способ их уменьшить следующий:

1. Дайте журналу очиститься.
2. Вручную уменьшите журнал (shrink).
3. Повторяйте шаги 1 и 2, пока журнал не достигнет небольшого размера (что может быть сложно на загруженной производственной системе).
4. Вручную увеличьте журнал до нужного размера, шагами не более 8 ГБ, чтобы каждый VLF был не более примерно 0,5 ГБ.

Вы можете прочитать больше о проблемах фрагментации VLF и процессе их исправления в:

• Статье базы знаний Microsoft, рекомендующей уменьшать количество VLF.
• Статье Can log files growth affect DML?
• Статье 8 steps to better transaction log throughput.

Tempdb

Для tempdb также необходимо настраивать журнал транзакций, как и для любой другой базы данных, и он может расти так же, как и любая другая база данных. Но у него также есть коварное поведение, которое может доставить вам проблемы. Когда экземпляр SQL Server перезапускается по любой причине, файлы данных и журнала tempdb возвращаются к размеру, который был установлен последним. Это отличается от всех других баз данных, которые остаются в текущем размере после перезапуска экземпляра.

Это поведение означает, что если журнал транзакций tempdb вырос, чтобы справиться с обычной рабочей нагрузкой, вы должны выполнить ALTER DATABASE, чтобы установить размер файла журнала, иначе его размер уменьшится после перезапуска экземпляра, и ему придётся расти снова. Каждый раз, когда файл журнала растёт или автоматически увеличивается, новое пространство должно быть инициализировано нулями, и активность журналирования приостанавливается на время этого процесса. Поэтому, если вы не управляете размером файла журнала tempdb должным образом, вы будете платить штраф за производительность по мере его роста после каждого перезапуска экземпляра.

Регулярное уменьшение файла журнала

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

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

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

Если ваш журнал транзакций постоянно вырастает до размера X, оставьте его в покое! Заранее установите его на размер X, управляя VLF, как я объяснил выше, и примите размер X как необходимый для вашей обычной рабочей нагрузки. Больший журнал транзакций не является проблемой.

Несколько файлов журнала

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

Меня часто спрашивают, есть ли веская причина удалять второй файл журнала или можно оставить его на месте. Ответ: вам следует удалить его, как только сможете.

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

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

Возврат из снимка базы данных

Последняя проблема в моём списке — это фактически ошибка в SQL Server. Если вы используете снимок базы данных как способ быстрого возврата к известной точке во времени без необходимости восстанавливать резервные копии (известный как возврат из снимка), вы можете сэкономить много времени. Однако есть и большой недостаток.

Когда база данных возвращается из снимка, журнал транзакций пересоздаётся с двумя VLF по 0,25 МБ. Это означает, что вам придётся снова увеличивать журнал транзакций до оптимального размера и количества VLF (или он будет автоматически расти сам), со всеми паузами из-за инициализации нулями и влиянием на рабочую нагрузку, о которых я говорил ранее. Очевидно, это не желаемое поведение.

Резюме

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

Если вы сможете позаботиться обо всех этих вещах, у вас будут здоровые журналы транзакций. Но на этом всё не заканчивается, вам нужно убедиться, что вы отслеживаете свои журналы транзакций, чтобы получать оповещения о таких вещах, как автоматический рост и чрезмерные задержки чтения и записи ввода-вывода. Я расскажу о том, как это сделать, в будущей статье.