SQL в вопросах и ответах: Узкие места и журналы транзакций

  • Статья

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

Пол С. Рэндал

Дисковая подсистема как узкое место

Вопрос: Я пытаюсь определить причину узкого места в своей системе. Я убежден, что оно находится на уровне системы хранения, так как видел, что длина дисковой очереди превышает значение 2. Я читал, что это хороший способ, доказывающий, что SQL Server перегружает дисковую систему. Правда ли это? Если так, то что мне делать с этим?

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

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

Но нас интересует вопрос, на что же все-таки смотреть, чтобы узнать, является ли подсистема ввода/вывода узким местом? В объекте производительности «Физический диск» (Physical Disk) есть два счетчика, на которые надо обратить внимание.

  • Среднее время чтения с диска (с) (Avg. Disk sec/Read)
  • Среднее время записи на диск (с) (Avg. Disk sec/Write)

Они предоставляют информацию в миллисекундах о времени выполнения операций ввода/вывода. Если показания постоянно выше (или часто превышают) нормы (то есть 5-12 мс), то физический диск является узким местом ввода/вывода. Конечно, физическим диском может быть LUN-массив SAN, но наши возможности ограничены возможностями Windows.

Если на физическом диске расположены данные и журналы SQL Server, возможно потребуется выяснить, какие файлы создают нагрузку на систему ввода/вывода. Используйте динамическое представление sys.dm_io_virtual_file_stats и выполните некоторое простое исследование времени на основе результатов.

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

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

  • Проанализируйте рабочую нагрузку запросов базы данных и выясните, не выполняют ли они излишние просмотры таблицы из-за неправильной стратегии индексации или неудачных планов выполнения, созданных с использованием устаревшей статистики.
  • Перенесите некоторые файлы в другую часть подсистемы ввода/вывода.
  • Увеличьте размер памяти на сервере, чтобы серверу SQL Server был доступен пул буферов большего размера (кеш страниц файлов данных в памяти) и снизить число операций ввода/вывода.

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

Размер имеет значение

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

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

Первый — базовый уровень RAID. Различные уровни RAID имеют различные плюсы и минусы относительно производительности и избыточности. Например, самая доступная конфигурация RAID, предоставляющая некоторую избыточность, это RAID-5, однако эта конфигурация рассчитана на сбой только одного диска (если не используется RAID-6, или не настроены резервные диски), и иногда может вызывать снижение производительности для рабочих нагрузок с множеством операций записи в зависимости от числа дисков в массиве.

Журнал транзакций должен всегда иметь возможность увеличиваться автоматически. Это особенно верно в критических ситуациях, когда отказывает или бесполезен мониторинг размера журнала. Представьте себе, к примеру, что у вас есть уведомление агента SQL Server, срабатывающее при показаниях счетчика производительности «Процент использования журнала» (Percent Log Used), превышающих 90 %, и сотрудник, ответственный за разрешение ситуации, уведомлен по электронной почте или через пейджер, но он отсутствует из-за болезни. Если у журнала нет возможности увеличиваться, все текущие транзакции, изменяющие базу данных, останавливаются и откатываются. Это приводит к простою и прекращению выполнения бизнес-операций.

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

  • Самую объемную транзакцию вставки, обновления или удаления в рабочей нагрузке (независимо от того, неявная ли это транзакция, состоящая из одного оператора и затрагивающая миллионы строк таблицы, или явная транзакция, выполняющая много операций).
  • Самую объемную массовую операцию в рабочей нагрузке, например BULK INSERT. Если на вашем сервере используется модель полного восстановления, можно сократить объем данных, поступающих в журнал транзакций, используя модель восстановления с неполным протоколированием. Но имейте в виду, что в такой модели страдает способность восстановления после аварии. Подробнее см. сообщение в моем блоге: A SQL Server DBA myth a day: BULK_LOGGED recovery model.”
  • Перестроение наибольшего кластеризованного индекса. Здесь также может помочь использование модели восстановления с неполным протоколированием.

При анализе этих операций надо учитывать не только количество создаваемых записей в журнале транзакций, но пространство, которое система управления журналом транзакций «резервирует» для надлежащего отката транзакций. Если транзакция создает 100 МБ записей в журнале транзакций, система зарезервирует примерно столько же пустого пространства в журнале транзакций, чтобы гарантировать возможность в случае прерывания транзакцию правильно откатить ее. Это защитный механизм, предотвращающий возникновение несогласованности данных в базе. Скорее всего, именно поэтому вы наблюдали увеличение журнала транзакций, хотя и считали, что предусмотрели в нем достаточно пространства для самой крупной транзакции.

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

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

Обнаружив непонятное увеличение размера журнала транзакций, лучше «спросить» о причинах сам сервер SQL Server:

SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO

Такой запрос сообщит о причине, по которой не удается удалить некоторые записи журнала и освободить место для новых записей (это называется «очисткой» или «усечением» журнала).

Как видите, факторов, влияющих на размер журнала транзакций, не очень много, даже если дополнительно учитывать базу данных tempdb. Подробнее об этом см. запись в моем блоге «Importance of proper transaction log size management» (Важность правильного управления размером журнала транзакций) и в статье в журнале TechNet Magazine «Ведение журнала и восстановление в SQL Server.»

Принудительное протоколирование

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

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

Усечение таблицы всегда выполняется с неполным протоколированием независимо от модели восстановления. Другие операции (такие как построение или перестроение индекса и массовая загрузка) выполняются с неполным протоколированием только в простой модели восстановления и модели с неполным протоколированием (BULK_LOGGED).

Единственные совсем не протоколируемые операции в SQL Server — те, что влияют на хранилище версий в tempdb, которое обеспечивает такую функциональность, как изоляция моментальных снимков и интерактивная индексация. Они могут не протоколироваться, потому что никогда не требуется откатывать операцию хранилища версий или восстанавливать после аварии базу данных tempdb.

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

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

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

Paul Randal

Пол С. Рэндал*(Paul S. Randal) носит звание 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.*

Дополнительные материалы