Header RSS-подписка на обновления сайта eMail-подписка на обновления сайта

Как перестать называть журнал транзакций SQL Server лог-файлом и прекратить борьбу за его размер. Часть 7/12.













Вновь о «круговороте» журнала транзакций.

Возвращаемся в основную «колею» нашего рассказа. Мы, напомню, прервались на том месте, где вставка 2-х тысяч строк делает записей в лог меньше чем наполовину. Но при всем при этом, вставка еще трехсот таких же строк оказывается невозможной из-за переполнения лога. И пока не совсем понятно — как наше последнее «побочное» исследование касательно ROLLBACK-а и его взаимоотношений с логом может помочь в разрешении этого «ребуса». Да, откат транзакций вносит в лог записей существенно больше, чем это ожидается интуитивно. Это факт. Однако в нашей задаче мы никаких ROLLBACK-ов не производим! Мы уверены, что транзакция будет успешна и даем движку сервера четкое указание по ее фиксации. Какой ROLLBACK? Подвох здесь кроется в невинной фразе «мы уверены». Мы-то уверены. А вот движок — нет. И, на всякий случай, готовит «плацдарм для отступления». С учетом старой и проверенной веками истины «надеясь на лучшее — готовься к худшему» он правильно делает, между прочим. Ибо ROLLBACK может случиться каждую секунду и далеко не всегда причины его нами контролируемы или, по крайней мере, осознаваемы «наперед». Когда такой нежданный ROLLBACK происходит мы бодро хлопаем себя по лбу, кричим «ну конечно! вот здесь же запятая должна быть!», ставим запятую и транзакции вновь работают без сбоев. Но один ROLLBACK уже случился, хотя «всегда OK было».

Одним словом, внося в лог «прямые» записи проистекающие из инструкций активной транзакции, движок сервера в обязательном порядке проверяет — а есть ли в логе место для «встречных» транзакций, если таковые придется создавать. Разумеется, сами записи в зарезервированное место внесутся только «если что случится», но вот место под них резервируется без всяких «если» и вполне безусловно. Указанный механизм известен в литературе под термином log space reservation. И именно он является ключом для решения нашего «ребуса». Для проведения успешной транзакции, в логе должно быть не N свободного места (где N=объем записей журнала, генерируемых в течении всей транзакции), а 2*N минимум (в реальности — несколько больше). А у нас этот минимум не выполняется.

Самые пытливые из читателей могут возразить, что, наверное, у нас частный случай и наша «беда» лишь от того что мы «залочили» размер лога на 1MB и не даем ему расти. Возражение вполне корректно, если изменить определение для файла лога нашей тестовой базы на:

1
2
3
4
5
6
7
 ...
LOG ON (
    NAME = 'TstLog_Log',
    FILENAME = 'c:\sqlCMD.ru\TstLog_Log.ldf',
    SIZE = 1 MB,
    FILEGROWTH = 5 MB,
    MAXSIZE = 10 MB )

то 2300 строк вставятся без всяких проблем, хотя при этом (что характерно), до транзакции у нас будет 4 VLF-а, а к концу — целых 8! При том, что в реальности использоваться будут только первых 3, и причем последний еще и останется пустым процентов на 90. То есть, транзакция будет закончена успешно, при этом место на диске будет, по сути, «растранжирено». А вы как думали — надежность дорогого стоит!

Впрочем разговор о том «чем лог прирастает» у нас с вами впереди. Пока же вернемся к версии базы с «залоченым» лог файлом, и признаем что нашим «потолком» является вставка 2-х тысяч строк максимум (ну может еще сотня, от силы) и подумаем — а может можно оптимизировать сам запрос? Иными словами, нельзя ли так устроить наш T-SQL код, что бы лог оставался фиксированным по размеру, а мы могли вставлять 3, 5 и более тысяч строк аналогичных строкам предыдущих примеров?

Первая мысль приходящая в голову — оптимизировать транзакцию. Что у нас с ней сейчас? Одна транзакция и в ней 2000 (или около того) вставок. Что если каждую отдельную вставку оформить как персональную транзакцию? Тогда в целом скрипт останется без изменений, изменится лишь оформление его цикла, вот на такое:

1
2
3
4
5
6
7
8
9
declare @i int = 0
WHILE @i < 500 --loop counter
    BEGIN
        BEGIN TRAN
        insert into T1 VALUES (17, REPLICATE('S', 20))
        set @i += 1
        COMMIT TRAN
    END
go

Иными словами не транзакция будет содержать в себе цикл, а ровно наоборот — цикл содержать транзакцию. Понятно, что число транзакций будет на три порядка больше чем в первом варианте. Но и объем каждой уменьшается пропорционально! А меньше объем — меньше требуется резервирования на случай ROLLBACK-а. В общем, как минимум, стоит попробовать.

Снова начнем со значения счетчика 500. Тогда отчет будет таким:

FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
2       253952      8192        71      2       64      0
2       253952      262144      72      2       64      0
2       253952      516096      0       0       0       0
2       278528      770048      0       0       0       0

Весьма похоже на отчет предыдущего варианта с одной транзакцией (при том же значении счетчика, разумеется) но есть и явное отличие: в предыдущем варианте к этой точке был задействован лишь первый VLF. Теперь же — два первых. В целом — логично и ожидаемо: ведь каждая транзакция это, как минимум, пара лишних записей в логе, с кодами операций LOP_BEGIN_XACT/LOP_COMMIT_XACT — согласны? Вот за 500 «оборотов» и набежало. Впрочем, как вы понимаете, это не значит что VLF2 уже забит «под завязку». Скорей всего там прямо сейчас еще полно свободного места.

OK, увеличим значение счетчика до одной тысячи. Каков будет отчет команды DBCC LOGINFO на сей раз? А вот такой:

FileId  FileSize    StartOffset FSeqNo  Status  Parity  CreateLSN
2       253952      8192        71      0       64      0
2       253952      262144      72      2       64      0
2       253952      516096      73      2       64      0
2       278528      770048      0       0       0       0

Как это будет выглядеть в графическом представлении? Ну — где-то так:

Log003

Хмм... А куда девались записи первого VLF?? Кто их «постирал»? Никуда не девались, все там же. Просто указанный VLF перешел в такое состояние что движку сервера разрешено проводить новые записи поверх старых, тем самым используя место в логе повторно. В этом и заключается суть «цикличности» или «оборачиваемости» файла лога: отдельные VLF-ы его составляющие периодически меняют свои статусы с «занято» на «свободно», и снова на «занято». И, таким образом, за свою «жизнь» один VLF более чем способен «прокачать» через себя объем лог-записей превосходящий объем самого VLF-а на порядки. Разумеется, в каждый отдельный момент времени записей в VLF-е будет максимум столько, сколько в него «влезает» — чудес-то не бывает, а никакие методики типа «zip-on-fly» при ведении лога не используются. Но благодаря возможности вот такой «многослойной» записи имеющийся объем VLF-а используется чрезвычайно эффективно.

Читателей, должно быть, интересуют два вопроса связанных с последним абзацем. Первый — как автор узнал о смене статуса именно (и только) первого VLF-а? Ответ: соотнесите колонку Status последнего и предпоследнего отчетов и все станет очевидно. Вопрос второй — а какие они, статусы VLF-ов, бывают и когда как меняются? Вот! А это как раз повод сделать очередной «ход конем» и временно сместить фокус нашего внимания на вещи имеющие, строго говоря, второстепенное значение, но тем не менее знать о принципах функционирования которых крайне желательно. А иначе практически невозможно будет понять как работает «главный механизм», т.е. журнал транзакций.

VLF-статусы и их смена. Понятие о Minimum Recovery LSN.

Вообще-то, если кто подзабыл, когда мы изучали колонки в отчете команды DBCC LOGINFO то интересующие нас статусы были кратко упомянуты при разборе значений колонки Status. Но там о них была сказана совсем уж пара слов. Конечно они, как «главные действующие лица» (ну, по крайней мере — одни из), заслуживают много большего. Итак — поехали.

Каждый VLF при своем создании безусловно переводится в первый (из четырех возможных) статус: Unused. Это означает не только то, что в данный момент никаких записей в VLF нет, но и так же что записей и не было. Никогда. Т.е. VLF «чист как первый снег» и ожидает пока в него «польются» самые первые записи лога.

Когда этот счастливый для каждого VLF момент начала «заливки» в него первых записей наступает, то, во-первых, VLF теряет статус Unused навсегда. Ведь теперь про данный VLF невозможно будет сказать «никогда не содержал никаких записей» — согласны? А во-вторых, VLF получает новый статус Active. Как дать формальное определение — какой VLF обладает этим статусом в указанной точке времени? Очень хочется ограничиться фразой «VLF со статусом Active это тот VLF, куда поступит очередная запись транзакционного лога». И это определение даже корректно! Но, увы, не полно. Описанный в приведенной фразе VLF безусловно будет обладать указанным статусом. Однако это не означает что ни один из прочих VLF-ов принципиально не может обладать им же и в тот же самый момент времени. Может! И даже не один, а сколько угодно. Так вот для полного формального определения тех VLF что обладают данным статусом нам, как ни крути, придется «ввести в игру» еще одну сущность — минимальный регистрационный номер восстановления в журнале транзакций, он же minimum recovery log sequence number, он же minimum recovery LSN, он же minimum LSN, он же (проще всего и потому наиболее распространенный термин) MinLSN. Автор не зря столь скрупулёзно перечислил все термины под которыми вводимая сущность может вам встретиться в литературе. Дело в том, что этот самый минимальный номер играет в журнале транзакций роль, важность которой переоценить практически невозможно. А поэтому необходимо не только понимать эту самую его роль и принципы его работы, но и знать все его названия, дабы уметь моментально определять и находить нашего «подопечного» в книгах/статьях/заметках. Считайте что MinLSN это еще (помимо всего прочего) некий «якорь», «зацепившись» за который авторам проще излагать материал связанный (обычно) с работой журнала, а вам, соответственно, проще этот материал понимать и изучать.

Стало быть — что есть MinLSN? Уважаемый BOL дает четкое и ясное определение этому понятию:

Одним из элементов данных ... является номер LSN первой записи журнала, при отсутствии которой успешный откат в масштабе всей базы данных невозможен. Такой номер LSN называется минимальным номером LSN восстановления (MinLSN).

По крайней мере такое определение кажется (по всей видимости) «четким и ясным» для самого BOL-а. Чего не скажешь об авторе данного материала и, сильно подозреваю, не только о нем. Например, что есть «откат в масштабе базы» лично автору неизвестно (сказал бы кто), но, надо думать, продолжая эту «BOL-идею», нас вскоре будут ожидать откаты на уровне отдельно взятой таблицы, а так же ее отдельной строки. Кроме того, как во все эти «откаты» укладываются механизмы несомненно влияющие на местоположения метки MinLSN в журнале (и мы будем о таких механизмах говорить через пару абзацев), но не имеющие никакого отношения к вопросам восстановления баз, будь то хоть restart recovery, хоть restore recovery?

В общем, единственное рациональное зерно извлеченное автором из указанного определения сводится к тому, что MinLSN — это, несомненно, «какой-то LSN». :) Иными словами, в каждый момент времени в журнале существует некоторая запись и только эта единственная запись имеет «маркер» MinLSN. С течением времени этот маркер может быть передан другой записи, но в каждый конкретный момент времени ровно одна запись является MinLSN-записью. Все прочие записи, в обсуждаемом смысле, являются «обычными». Напомню, кстати, что LSN вообще есть ничто иное как идентификатор отдельной записи журнала транзакций, и что он может быть без проблем просмотрен нами для любой записи с помощью инструкции DBCC LOG. К сожалению эта команда не предлагает удобного способа отслеживания истории изменения местоположения указанного маркера. То есть, в принципе вывести из отчета команды DBCC LOG историю перемещений маркера можно, но ручного труда и «сканирования глазами» при этом будет «выше крыши». А вот и хорошая новость: точное положение маркера играет (по крайней мере с практической точки зрения) роль весьма незначительную. Гораздо большую весомость имеет вопрос «в каком VLF в данный момент находится маркер». Т.е. нам достаточно знать его положение с точностью до отдельного VLF, а не с точностью до конкретной записи внутри него. Что много, много проще. Собственно говоря, для этого достаточно информации другой команды — DBCC LOGINFO. VLF со цифрой 2 в колонке Status и наименьшим значением в колонке FSeqNo среди всех VLF-ов с тем же статусом будет содержать маркер на момент вызова отчета. Для базы данных находящейся в модели восстановления SIMPLE означенное правило безусловно верно. Для баз находящихся в двух альтернативных моделях — верно с оговорками. Какой «профит» мы можем извлечь из такого знания станет ясно из дальнейшего материала.

Разумеется, мы не можем не заинтересоваться вопросом «а когда, собственно, происходит переключение MinLSN на иной LSN в журнале»? Отчасти нам в этом вновь поможет BOL, любезно сообщающий, что MinLSN в каждый момент времени есть минимальный (важно!) LSN из:

  1. номера LSN начала контрольной точки;
  2. номера LSN начала старейшей активной транзакции;
  3. номера LSN начала старейшей транзакции репликации, которая еще не была доставлена базе данных распространителя.

Пример: отчет инструкции DBCC LOG сообщил нам, что, допустим, LSN с номером 00000048:000001b7:0001 символизирует собой начало контрольной точки. Согласно п.1 нашего списка (точнее «BOL-списка») маркер MinLSN должен «отойти» именно этой записи, что и происходит. Хорошо, теперь у нас MinLSN «зафиксирован» на LSN=00000048:000001b7:0001. Что если позже этого момента, скажем на LSN=00000049:0000002b:0012, откроется новая активная транзакция? Или в LSN=00000049:00000030:001c случится транзакция «нужная» репликации? Будет ли хоть в одном из этих случаев иметь место «переход» маркера? Ни в коем случае! Читайте определение MinLSN внимательнее: из всех «претендентов на маркер» выбирается безусловно тот, что обладает наименьшим LSN. Поэтому в описываемом сценарии MinLSN останется на месте, у LSN=00000048:000001b7:0001. Хорошо, а вот если бы до начала контрольной точки, скажем в LSN=00000048:0000008a:0005, открылась бы новая транзакция и к моменту LSN=00000048:000001b7:0001 (начало контрольной точки) она оставалась бы открытой? А вот тогда бы сработал пункт 2 того же списка и маркер был бы передан началу этой самой транзакции, а начало контрольной точки было бы проигнорировано (в смысле передачи ей маркера, разумеется; так-то, сама по себе, это была бы точка как точка). Понимаете теперь как работает каждый пункт этого списка?

Хорошо, но понимание значимости каждого из пунктов это пол-дела, причем меньшая. Большая половина откроется нам когда мы попытаемся ответить на вопрос «а почему список таков каков он есть»? Или: «а упоминание в нем, допустим, активной транзакции, имеет под собой технические причины или это авторам BOL-а так захотелось»? Иными словами, нам обязательно и всенепременно требуется понимание принципов формирования этого списка. Без этого — никак.

Обсуждение этого большего «пол-дела» начнем с констатации факта: в SQL Server встроен механизм называемый усечением лога (log truncation, а иногда truncation of virtual log files, что более длинно, но и более правильно). Сам этот механизм очень интересный, важный до чрезвычайности, и, несомненно, заслуживающий отдельного раздела (а по хорошему — парочки статей), которая и последует в рамках данного материала в свое время. Если вы с этим механизмом знакомы — вообще отлично (хотя, конечно, и не повод прекращать чтение ;) ). Для читателей же впервые увидевших данный термин (что, вообще-то, маловероятно, но не исключено) сообщу пока максимально кратко: усечение лога есть процесс в результате которого некоторые из существующих VLF могут быть помечены как пригодные для повторного использования (иными словами, такие VLF будут готовы принять новые записи «поверх» существующих). Некоторые рассматривают этот процесс как чистку лога. И хотя это не совсем так (а на физическом уровне нет ничего дальше от истинного положения вещей чем такая точка зрения), свое рациональное зерно есть и в таком видении вещей: действительно, по окончании данного процесса свободное место в логе «как бы прибавляется», а поэтому в определенном смысле лог «как бы очищается». В общем, как мы договорились, нюансы этого процесса будут разобраны отдельно, а мы пока сосредоточимся на той их части, что имеет непосредственное отношение к пониманию принципов формирования того самого «BOL-списка». Так вот в этом разрезе самый важный для нас нюанс гласит:

MinLSN по сути, «заградительный барьер» на пути усечения лога. Он не дает движку сервера усечь «нужную» часть лога, т.е. «нужный» VLF.

Разумеется, давайте сразу— что есть «нужный» VLF и как он отделяется от «не нужного»? Очень просто: если VLF обладает статусом Active (мы, кстати, именно этот статус сейчас и обсуждаем — не забыли? ;) ) либо статусом Recoverable (а его описание последует как только мы разберемся со статусом текущим) — он «нужен». Если у VLF статус любой иной (а их, собственно, остается пара: Unused, о котором мы уже знаем, и Reusable, о котором нам только предстоит узнать) — он «не нужен». Ну а MinLSN тут причем? А притом, что VLF с этим маркером «нужен» 100%. И любой VLF содержащий хотя бы одну запись с LSN большим чем MinLSN «нужен» 100%. Можно сказать и так: VLF содержащий маркер возглавляет список «нужных» VLF-ов. Не подлежат усечению VLF с маркером и все VLF-ы «правее него», если мы представим лог в той графической интерпретации, что была использована ранее несколько раз. Правда, рассматривая термин «VLF-ы правее от» нужно не забывать о «закольцованности» лога — принципе который мы и разбираем в данном разделе статьи и который нами пока до конца не изучен.

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

Log004

Новые элементы рисунка:

  • MinLSN — собственно, он и есть; :)
  • MaxLSN — последняя поступившая в лог запись. Иногда ее называют текущей (current), иногда — самой «свежей» (most-recent) записью. На суть происходящего все эти варианты наименования не влияют абсолютно;
  • активный лог — все записи лога между записью первой (MinLSN) и второй (MaxLSN);
  • логический лог (немного тавтологии, ну да не беда) — все записи от первой записи первого «нужного» VLF-а и вновь до MaxLSN.

Итого, для лога чье графическое изображение вы наблюдаете в данный момент не подлежат усечению VLF2 (как содержащий маркер) и VLF3 (как расположенный «правее» предыдущего, и, несомненно, содержащий записи с LSN-ми большими чем MinLSN). VLF1 может быть усечен без всяких проблем и более того — с ним это уже случилось, как явствует из последнего отчета команды DBCC LOGINFO. VLF4 так же может быть подвергнут данной процедуре без каких-либо проблем, но в данный момент это лишено какого-либо смысла, статус этого VLF-а и без того Unused, куда уж его дальше «усекать»?

Так вот, как же проявляется «заградительность» маркера MinLSN и как из этого его свойства проистекает состав анализируемого нами в настоящий момент списка? Давайте разберем — почему в каждом из пунктов списка должен быть выставлен этот самый «барьер» и почему именно в этой точке:

  1. Мы не можем усекать (а эта операция, как не сложно догадаться, безусловно влечет за собой потерю части записей лога, если не моментальную, то уж потенциальную как минимум) никакие записи лога после последнего CHECKPOINT. Резон очень простой. Допустим такие записи усекать/затирать/терять можно. OK, случился CHECKPOINT, пользователи еще немного поработали с системой, затерли обсуждаемые записи лога, и сразу же после этого «погас свет». Через 5 мин. свет вернулся. Загрузились по новой. Результат: клиенты еще до аварии получили уведомления что их транзакции успешно зафиксированы, а у нас (и у сервера) на руках только старые данные (в виде страниц данных до пропадания электричества) отражающие их состояние до фиксации транзакции. И никаких указаний к тому как эти данные привести к состоянию после этой фиксации. Соответственно, наше допущение некорректно и затирать эту часть лога нельзя, BOL права.
  2. У нас активная транзакция — вообще без комментариев. Что, скажите пожалуйста, вы планируете затереть в записях транзакции которая неизвестно чем закончится?
  3. И тут BOL снова права но уже по иной причине. Если разрешить усекать записи не доставленные дистрибутору репликации, то последний, очевидно, никогда их и не получит. А подписчики, соответственно, никогда не узнают, что клиенту X присвоен VIP-статус и он имеет скидку в 10% на все товары. Что, разумеется, нас устроить никак не может.

Так что BOL, подчас, тоже содержит весьма здравые мысли и рассуждения. :roll: А в чем BOL не права, так это в попытке «прикинуться», что указанный список — исчерпывающий. Вот это — отнюдь. Возьмем тоже зеркалирование баз. В самой сущности происходящего при этом — та же доставка записей транзакционного лога что и в случае транзакционной репликации. Разумеется, все обставлено куда как с большим «шиком», но «точка отправления» обоих механизмов одна и та же — записи транзакционного лога исходной базы (пусть она называется хоть «распространителем», хоть «принципалом», а и хоть «примари» — кстати говоря — если переключиться на еще один, уже третий, подобный механизм, доставку журналов, он же log shipping). Разве мы можем допустить что бы хоть в одном из этих случаев наш горячо любимый клиент X по нашей вине утратил столь бережно им взращенный VIP-статус? Да никогда в жизни! Кстати говоря, не думайте что вот теперь-то список точно полон. Связь с транзакционным логом проявляется в целом ряде и иных механизмов, встроенных в SQL Server, подчас не совсем ожидаемых, например в том же Change Data Capture. Другое дело, что 3 пункта обозначенных в BOL наиболее часто оказывают влияние на положение маркера MinLSN в логе и в этом смысле с лихвой перекрывают все прочие возможные причины «сдвига» MinLSN вместе взятые. Однако, согласитесь, хорошо когда представляешь себе общую картину, а не ее частные (хоть и наиболее распространенные) случаи.

Итого, будучи совершенно неудовлетворенным определением маркера MinLSN данному в BOL и обладая только что изложенными фактами, для «внутреннего потребления» автор применяет, для той же сущности, такое определение: MinLSN указывает место в журнале транзакций начиная с которого и до конца логического журнала находятся «нужные» записи, не могущие в данный момент времени быть подвергнутыми никаким модификациям (а проще говоря — удалению, ибо иные формы модификации с существующими записями лога не предусмотрены). Критерий «нужности записи» фактически приведен выше: «нужные» VLF-ы содержат «нужные» записи и наоборот. Возможно вам понравится более практичное определение того же термина: MinLSN фактом своего нахождения в указанном LSN определяет первый из VLF-ов не подлежащих усечению. Прочие VLF-ы так же не подлежащие усечению определяются из соотношений их номеров записей (LSN) с MinLSN.