Описание алгоритмов ведения журнала и хранения данных, которые расширяют надежность данных в SQL Server

Исходная версия продукта: SQL Server 2014 г., SQL Server 2012 г., SQL Server 2008 г., SQL Server 2005 г.
Исходный номер базы знаний: 230785

Сводка

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

Дополнительные сведения о базовых концепциях ядр и алгоритме для восстановления и изоляции семантики (ARIES) см. в следующем документе ACM Transactions on Database Systems (в томе 17, число 1, март 1992 г.):

Внешняя ссылка: ARIES: метод восстановления транзакций, поддерживающий блокировку Fine-Granularity и частичный откат с помощью Write-Ahead ведения журнала

В этом документе рассматриваются SQL Server методы повышения надежности и целостности данных, связанные со сбоями.

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

• Описание кэширования контроллеров дисков в SQL Server

• Сведения об использовании кэшей дисков с SQL Server, которые должен знать каждый администратор базы данных

Термины, используемые в этой статье

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

Протокол Write-Ahead ведения журнала (WAL)

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

Документ ARIES определяет wal следующим образом:

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

Дополнительные сведения о ведении журнала с опережающей записью см. в разделе Журнал транзакций с опережающей записью на веб-сайте SQL Server документации.

SQL Server и WAL

SQL Server использует протокол WAL. Чтобы убедиться, что транзакция правильно зафиксирована, все записи журнала, связанные с транзакцией, должны быть защищены в стабильном хранилище.

Чтобы прояснить эту ситуацию, рассмотрим следующий конкретный пример.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

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

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

При более подробном просмотре примера можно спросить, что происходит при выполнении процессов LazyWriter или CheckPoint. SQL Server выдает все соответствующие сбросы в стабильное хранилище для записей журнала транзакций, связанных с грязное и закрепленной страницей. Это гарантирует, что страница данных протокола WAL никогда не может быть записана в стабильное хранилище до тех пор, пока не будут удалены связанные записи журнала транзакций.

SQL Server и стабильное хранилище

SQL Server улучшает операции со страницами журналов и данных, включая сведения о размерах секторов диска (обычно 4096 или 512 байт).

Чтобы сохранить свойства ACID транзакции, SQL Server должны учитывать точки сбоя. Во время сбоя многие спецификации дисков гарантируют только ограниченное количество операций записи в секторе. Большинство спецификаций гарантируют завершение записи в одном секторе при возникновении сбоя.

SQL Server использует страницы данных размером 8 КБ и журнал (если он удален) для кратных размеров сектора. (Большинство дисков используют 512 байт в качестве размера сектора по умолчанию.) В случае сбоя SQL Server может учитывать операции записи, превышающие сектор, используя методы четности журналов и разорванной записи.

Обнаружение разорванной страницы

Этот параметр позволяет SQL Server обнаруживать неполные операции ввода-вывода, вызванные сбоем питания или другими сбоями системы. Если значение true, это приводит к тому, что каждый 512-байтовый сектор на странице базы данных размером 8 килобайт (КБ) будет перевернут для каждого 512-байтового сектора при записи страницы на диск. Если бит находится в неправильном состоянии при последующем чтении страницы SQL Server, то страница была написана неправильно; обнаруживается разорванная страница. Разорванные страницы обнаруживаются во время восстановления, так как любая страница, написанная неправильно, скорее всего, будет прочитана восстановлением.

Хотя SQL Server страницы базы данных имеют размер 8 КБ, диски выполняют операции ввода-вывода с помощью 512-байтового сектора. Таким образом, для каждой страницы базы данных записывается 16 секторов. Разрыв страницы может возникнуть в случае сбоя системы (например, из-за сбоя питания) между временем записи операционной системой первого 512-байтового сектора на диск и завершением операции ввода-вывода размером 8 КБ. Если первый сектор страницы базы данных успешно записан до сбоя, страница базы данных на диске будет отображаться как обновленная, хотя она, возможно, не выполнена.

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

Четность журналов

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

Влияние на производительность

Все версии SQL Server открывать файлы журнала и данных с помощью функции Win32 CreateFile. Член dwFlagsAndAttributes включает FILE_FLAG_WRITE_THROUGH параметр при открытии SQL Server.

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

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

Многие диски (SCSI и IDE) содержат встроенные кэши размером 512 КБ, 1 МБ или больше. Однако кэши дисков обычно полагаются на емкости, а не на решение с поддержкой батареи. Эти механизмы кэширования не могут гарантировать запись в цикле питания или аналогичной точке отказа. Они гарантируют только завершение операций записи в секторе. Именно поэтому в SQL Server 7.0 и более поздних версиях встроено обнаружение разрыва записи и четности журналов. По мере того как объем дисков продолжает увеличиваться, кэши становятся больше, и они могут предоставлять большие объемы данных во время сбоя.

Многие поставщики оборудования предоставляют решения для контроллеров дисков с поддержкой батареи. Эти кэши контроллеров могут хранить данные в кэше в течение нескольких дней и даже разрешать размещение оборудования кэширования на втором компьютере. При правильном восстановлении питания незаписанные данные сбрасываются, прежде чем будет разрешен дальнейший доступ к данным. Многие из них позволяют установить процент кэша чтения и записи для оптимальной производительности. Некоторые из этих областей содержат большие объемы памяти. Фактически, для определенного сегмента рынка некоторые поставщики оборудования предоставляют высокопроизводительные системы контроллеров кэширования дисков с поддержкой батареи с 6 ГБ кэша. Это может значительно повысить производительность базы данных.

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

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

Упорядочение секторов

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

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

При участии кэша записи данные по-прежнему считаются в энергонезависимом хранилище. Однако при вызове Win32 API WriteFile, точно SQL Server видит действие, был получен код успешного возврата. SQL Server или любой процесс, использующий вызов API WriteFile, может определить, что данные правильно получили стабильное хранилище.

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

При оценке оптимальных факторов производительности для сервера базы данных необходимо учитывать множество факторов. Самый важный из них: "Разрешает ли моя система допустимые FILE_FLAG_WRITE_THROUGH возможности?"

Тестирование показало, что многие конфигурации дисков могут содержать кэширование записи без соответствующей резервной копии аккумулятора. Диски SCSI, IDE и EIDE используют все преимущества кэшей записи. Дополнительные сведения о том, как диски SSD работают вместе с SQL Server, см. в следующей статье блога инженеров CSS SQL Server:

SQL Server и диски SSD — учебные заметки RDORR — часть 1

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

Диски SCSI также имеют кэши записи. Однако эти кэши обычно могут быть отключены операционной системой. Если есть какие-либо вопросы, обратитесь к производителю диска за соответствующими служебными программами.

Стек кэша записи

Стек кэша записи аналогичен упорядочению секторов. Следующее определение было взято непосредственно на веб-сайте ведущего производителя дисков IDE:

Как правило, этот режим активен. Режим кэша записи принимает данные записи узла в буфер до тех пор, пока буфер не будет заполнен или передача узла не будет завершена.

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

Автоматическое перераспределение записи (AWR)

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

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

Это может быть мощной функцией, если для кэша предусмотрено резервное копирование батареи. Это обеспечивает соответствующее изменение после перезапуска. Предпочтительнее обнаруживать ошибки диска, но для обеспечения безопасности данных протокола WAL снова потребуется сделать это в режиме реального времени, а не в отложенном порядке. В параметрах WAL метод AWR не может учесть ситуацию, когда запись журнала завершается сбоем из-за ошибки сектора, но диск заполнен. Ядро СУБД должно немедленно знать о сбое, чтобы транзакция могла быть прервана правильно, администратор может получать оповещения и принимать правильные меры для защиты данных и исправления ситуации сбоя мультимедиа.

Безопасность данных

Администратор базы данных должен принять несколько мер предосторожности, чтобы обеспечить безопасность данных.

• Всегда рекомендуется убедиться, что стратегии резервного копирования достаточно для восстановления после катастрофического сбоя. Хранение на месте и другие меры предосторожности подходят.
• Часто тестируйте операцию восстановления базы данных в базе данных-получателе или тестовой базе данных.
• Убедитесь, что любые устройства кэширования могут справиться со всеми ситуациями сбоя (сбои питания, плохие секторы, плохие диски, сбой системы, блокировки, скачок питания и т. д.).
• Убедитесь, что устройство кэширования:
  • Имеет встроенную функцию резервного копирования батареи
  • Может перевыпускать записи при включении питания
  • Может быть полностью отключен, если это необходимо
  • Обработка неправильного перераспределения секторов в режиме реального времени
• Включите обнаружение разорванной страницы. (Это мало влияет на производительность.)
• Настройте диски RAID, чтобы обеспечить горячую переключение на неисправный диск, если это возможно.
• Используйте новые контроллеры кэширования, которые позволяют добавить больше места на диске без перезапуска ОС. Это может быть идеальным решением.

Тестовые диски

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

Корпорация Майкрософт выполнила тестирование на нескольких дисках SCSI и IDE с помощью служебной SQLIOSim программы. Эта служебная программа имитирует интенсивные асинхронные операции чтения и записи на имитированное устройство данных и устройство журнала. Статистика производительности теста показывает среднее количество операций записи в секунду в диапазоне от 50 до 70 для диска с отключенным кэшированием записи и диапазоном в секунду от 5200 до 7200.

Дополнительные сведения о служебной SQLIOSim программе см. в следующей статье базы знаний Майкрософт:

Использование служебной программы SQLIOSim для имитации действий SQL Server в дисковой подсистеме

Многие производители компьютеров заказывают диски, отключив кэш записи. Однако тестирование показывает, что это может быть не всегда так. Поэтому всегда проверяйте полностью.

Устройства данных

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

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

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

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

Первый вопрос, который может возникнуть: "У меня есть диск интегрированной среды разработки, который кэширование. Но когда я отключил его, моя производительность стала меньше, чем ожидалось. Почему?"

Многие диски интегрированной среды разработки, протестированные корпорацией Майкрософт, работают со скоростью 5200 об/мин, а диски SCSI — со скоростью 7200 об/мин. При отключении кэширования записи на диске интегрированной среды разработки механическая производительность может стать фактором.

Чтобы устранить разницу в производительности, необходимо выполнить следующий метод: "Укажите скорость транзакций".

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

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

Тестирование показывает, что высокая активность операций записи буферов размером менее 512 КБ или больше 2 МБ может привести к снижению производительности.

Рассмотрим следующий пример.

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

Ниже приведены примеры результатов тестирования для SQL Server.

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

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

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

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

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server требуется, чтобы системы поддерживали гарантированную доставку на стабильный носитель, как описано в документе о скачивании SQL Server требований к проверке надежности программы ввода-вывода. Дополнительные сведения о требованиях к входным и выходным данным для ядра СУБД SQL Server см. в разделе требования к входным и выходным данным ядро СУБД Microsoft SQL Server.