SQL Server. Почему файл лога важнее файла данных?

Лог файл в серверах баз данных, он же журнал транзакций — объект наиболее «критичных» заблуждений многих администраторов СУБД.
Как в отношении «доступности» данных, так и в отношении производительности.
Давайте разберемся, почему…

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

Но в случае SQL Server и некоторых других СУБД, журнал транзакций — это не просто отчет о событиях.
Файл транзакционного лога (хранящий этот самый журнал) — это краеугольный камень в общей системе поддержания целостности данных и обеспечения хорошей производительности операций изменения данных в базах.

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

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

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

Как эта дилемма решается в Microsoft SQL Server?
Упрощенно, непосредственно изменение данных происходит по следующему алгоритму (нужно сказать, что для «братского» Sybase всё очень похоже, да и для других СУБД используются аналогичные протоуолы изменения):
1. Открывается транзакция
2. Если страницы данных нет в памяти, она читается с диска (кэшируется)
3. Происходит выполнение запроса — изменение нужных данных на страницах
4. Информация о произведенных изменениях и том, как измененные данные «выглядели» до изменения, сохраняется в транзакционный лог
5. Запрос завершен, транзакция завершена — клиент получает подтверждение об успешном выполнении.
Шаг 4 (запись информации в лог) выполняется со специальными «флагами», которые указывают как ОС, так и дисковой системе необходимость выполнения записи на долговременный носитель в синхронном режиме, то есть дисковый ответ о выполнении дискового запроса будет получен после сохранения информации на физический диск.

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

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

Итак, какие выводы можно сделать для администраторов БД, желающих «правильно» настроить свой сервер для быстрой и надежной работы:
1. Для размещения файлов транзакционного лога требуются надежные носители.
Совершенно недопустимо располагать файл лога на одиночном диске и тем более — на массиве RAID0. Сбой диска, на которой расположен файл журнала базы данных, приводит как минимум, к её переводу в состояние “suspect” — недоступности. Причем во многих случаях таких сбоев, возможно только восстановление базы данных с последней сохранившейся резервной копии.
2. Для размещения файлов транзакционного лога требуются быстрые носители.
Скорость выполнения запроса на изменение данных зависит самым главным образом именно от скорости диска, на котором размещен файл журнала. Поскольку сами данные пишутся в фоновом режиме, то для большинства систем требования по производительности диска, обслуживающего файлы данных будут ниже. На сегодняшний день требования такие: время выполнения дискового запроса к файлу лога должно укладываться (ориентировочно) в 5-10 миллисекунд, запроса к файлу данных — в 10-20 миллисекунд. Разные источники приводят разные конкретные значения этого показателя, но в любом случае для файла лога очень критична быстрая запись.
3. Если для базы данных настроен режиме восстановления FULL и настроено регулярное резервное копирование как самой базы данных, так и копий журнала, то восстановить базу данных можно даже в том случае, если потерян сам файл данных — нужно только иметь последнюю полную копию и все последующие копии журнала. Последние записи журнала транзакций можно также сохранить, если сам файл журнала не поврежден.

Полезные ссылки:
Журнал транзакций с упреждающей записью
Команда CHECKPOINT
Контрольные точки и активная часть журнала
Основы ввода-вывода в SQL Server Статья написана по 2000 версии, но в 2005 и 2008 изменения с точки зрения понимания принципов работы — непринципиальные.
Использование резервных копий журналов транзакций

Автор: Дмитрий Костылёв

***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***
Полезные Скрипты

Рубрика Проверь себя

Ссылка на наш канал YouTube