SQL в вопросах и ответах: Сага о транзакциях

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

Пол С. Рэндал

Журнальное очищение

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

Ответ Вы правы: в модели полного восстановления и модели с неполным протоколированием журнал транзакций очищается (части журнала помечаются как предназначенные для повторного использования), только если выполняется его резервное копирование. В простой модели восстановления журнал транзакций очищаются контрольными точками. Подробнее о журналах и восстановлении см. мою статью Understanding Logging and Recovery in SQL Server в TechNet:.

Однако есть один нюанс. Хотя операция очистки журнала транзакций происходит при завершении резервного копирования этого журнала, нет никаких гарантий, что так или иная часть журнала транзакций будет на самом деле удалена. Одного факта, что создана резервная копия той или иной части журнала транзакций, недостаточно для очистки. Серверу SQL Server эта часть журнала транзакций не должна когда-либо потребоваться для каких-либо целей.

Серверу SQL Server может понадобиться доступ к части журнала транзакций, потому что резервное копирование (например, полное резервное копирование) все еще в процессе. Резервная копия данных должна содержать часть журнала транзакций. Ему как минимум понадобятся данные журнала транзакций, сгенерированные во время копирования из базы данных. И даже больше.

Это означает, что во время резервного копирования очистка журнала не может произойти. Это верно даже во время параллельного резервного копирования журналов транзакций (параллельное резервное копирование данных и журналов поддерживается с версии SQL Server 2005).

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

Такое поведение называется отложенным усечением журнала. Именно это у вас и происходит. Похоже, что резервное копирование очищает журнал транзакций, но это никак не связано с параллельным резервным копированием журнала транзакций.

Свет мой зеркальце…

Вопрос Мы находимся в процессе реализации зеркального отображения для предотвращения потерь данных. Не подскажете, что нам нужно отслеживать, чтобы знать, что наше зеркальное отображение работает, как положено?

Ответ При реализации зеркального отображения обязательно надо отслеживать очереди SEND и REDO. Они напрямую связаны с потерей данных и простоями.

Очередь SEND отслеживает, какой объем журнала транзакций пока еще не отправлен на зеркальный сервер. Эта часть журнала транзакций описывает изменения в основной базе данных, которые будут потеряны в случае аварии.

Многие думают, что если сконфигурировать зеркальное отображение для обеспечения высокой безопасности и доступности (это называется синхронное зеркальное отображение), очередь SEND должна всегда быть пустой. Транзакция в основной базе данных не фиксируется, пока все используемые в этой транзакции журналы не отправлены на зеркальный сервер. Однако это не так. Бывает так, что при определенных обстоятельствах серверы основной и зеркальной базы данных теряют связь друг с другом, но основная база данных остается в интерактивном режиме. В этом случае увеличивается очередь SEND. Это повышает риск потери данных. Нулевая потеря данных гарантируется только при синхронизации зеркального состояния.

Очередь REDO показывает, какой объем журнала транзакций пока еще не скопирован в зеркальную базу данных. Широко распространено заблуждение, что когда основной сервер отправляет журнал транзакций на зеркальный сервер, он также немедленно воспроизводится в зеркальной базе данных. Это также неверно. Все, что нужно, — обеспечить запись журнала транзакций в надежное хранилище на зеркальном сервере.

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

При переходе на зеркальный сервер зеркальная база данных перейдет в интерактивный режим, пока не будут воспроизведены журналы транзакций. Задержка примерно пропорциональна объему не воспроизведенного журнала транзакций. Это также обусловливает простой приложения, который заметят пользователи во время перехода на зеркальный сервер.

Надо внимательно относиться к размеру очереди REDO, если планируется использовать моментальные снимки зеркальной базы данных для создания отчетов. При создании моментального снимка БД процесс восстановления базы данных должен обрабатывать текущие транзакции, чтобы моментальный снимок приставлял целостную картину базы данных с точки транзакций. Чем больше очередь REDO, тем дольше будет создаваться моментальный снимок базы данных. Он также повлияет на производительность зеркального сервера, что также приведет к росту очереди REDO.

Есть также другие показатели, за которыми надо следить, такие как задержки сетевой связи между основным и зеркальным серверами. Это создает дополнительные издержки в расчете на одну транзакцию, которые следует относить на реализацию синхронного зеркального отображения. Для наблюдения за этими показателями можно использовать монитор производительности (Performance Monitor) (динамическое представление sys.dm_os_performance_counters). Можно также задействовать монитор зеркального отображения баз данных (Database Mirroring Monitor), имеющийся в Management Studio и описанный в электронной документации по SQL Server. Этот инструмент позволяет легко создавать предупреждения при достижении определенных уровней заполнения очереди.

Повышение производительности

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

Ответ Нет, это неверно. К сожалению, я слышу о таких рекомендациях очень часто.

Увеличение числа файлов данных способствует снижению конкуренции в подсистеме ввода/вывода. В некоторых случаях (обычно в БД tempdb) оно позволяет снизить конкуренцию в структурах выделения пространства под базы данных в памяти. Существует масса документов с рекомендациями, сколько файлов данных создавать.

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

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

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

Сокращение числа операций ввода/вывода, повышение производительности

Вопрос Мы изучаем возможность использования твердотельных дисков для повышения производительности операций ввода/вывода, но мы сомневаемся, какие базы данных на них размещать. Не могли бы вы подсказать, как лучше всего задействовать твердотельные диски?

Ответ Это интересный вопрос. В интернет-форумах много «четких» рекомендаций, например: «всегда размещайте БД tempdb на твердотельных дисках» или «журналы транзакций обязательно размещать на твердотельных дисках». Обе они не совсем верны.

Есть ряд обстоятельств, о которых надо помнить при планировании, как лучше приспособить твердотельные диски к использованию на SQL Server:

• Твердотельные диски дороги, поэтому надо стараться добиться максимальной рентабельности их использования.
• Твердотельные диски дают максимальный выигрыш в производительности при использовании для случайных, а не последовательных операций ввода/вывода.
• Твердотельные диски обеспечат прирост производительности в любой подсистеме ввода/вывода независимо от вида операций ввода/вывода — только за счет значительного сокращения задержек при чтении и записи.
• Непосредственно подключенные твердотельные диски дают значительно больший выигрыш в производительности, чем такие диски, подключенные с использованием какой-либо коммуникационной инфраструктуры.
• В журналах транзакций запись ведется последовательно и чтение в основном также осуществляется последовательно (может быть заметный объем случайного чтения, если в системе часто выполняются откаты транзакций).
• Базы данных Tempdb на SQL Server обычно нагружены несильно. Даже при среднем их использовании в них не происходит много операций записи данных.

Ввиду этих обстоятельств понятно, что размещение БД tempdb и журнала транзакций на твердотельных дисках не лучший способ их использования. Определите части своей подсистемы ввода/вывода, являющиеся узким местом, тормозящим производительность работы. Это могут быть файлы данных, используемых для активных случайных операций OLTP-расчетов, или журнал транзакций в базе данных, в которую активно загружаются данные — это даже могут быть базы данных tempdb. Это все кандидаты на размещение на твердотельных дисках. Такой способ намного лучше, чем выбор какой-либо части хранилища SQL Server для размещения на твердотельных дисках без какого-либо исследования.

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

Большинство людей не учитывают, что корень проблемы фрагментации не только в задержке упреждающей записи. Один из сторонних эффектов, приводящих к фрагментации, (они называются «разбиением страниц») заключается в том, что возможно значительное увеличение неиспользуемых страниц в файлах данных — эта величина может достигать 40-50% (этот показатель обратно пропорционален плотности страницу).

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

Пол С. Рэндал носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу Twitter.com/PaulRandal.