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

Преобразование базы данных

Я уже писал о том, насколько важен такой метод обработки, как сжатие таблиц, а также о результатах, получаемых при его применении. Но надо сказать, что один из первых шагов, всегда совершаемых мною перед применением метода сжатия таблиц, состоит в определении, какие таблицы (а если они разделены, то какие разделы таблиц) следует сжимать с помощью алгоритма ROW или PAGE. В третьей статье серии «Сочетание разных типов сжатия по алгоритмам ROW и PAGE» (опубликована в Windows IT Pro/RE № 8 за 2016 год) я представил код, который можно использовать для определения подходящего способа сжатия. Применять метод ROW ко всем таблицам (или разделам), равно как и метод PAGE, нецелесообразно, так что предварительная классификация имеет большое значение. Но поскольку в ходе выполнения предложенного мною кода решения принимаются на базе динамических административных представлений, которые получают новые значения при каждом перезапуске системы, эти решения следует реализовывать лишь в тех системах, которые функционируют в течение некоторого времени без перезапуска.

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

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

Маскировка данных, идентифицирующих личность сотрудника, а также конфиденциальных сведений

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

  • Используют обычную копию производственной базы данных. Надеюсь, что сегодня такой подход применяется нечасто, ибо при этом полностью игнорируются все требования к защите конфиденциальных данных. Но, к сожалению, мне приходится сталкиваться с большим числом компаний, сотрудники которых поступают именно таким образом.
  • Передают испытателям и разработчикам базы данных, содержащие лишь ссылочные сведения, предоставляя им право ввода необходимых данных. В результате неизменно получаются базы данных, не имеющие ничего общего с реальными производственными базами данных, в которых редко применяются нестандартные методы заполнения. Так, в коде приложений вы не обнаружите примеров некорректного синтаксического анализа, за исключением случаев применения специальных символов в местах, где их использование не предполагается.
  • Применяют сгенерированные данные. Это хороший способ, особенно в случае, когда требуется резко расширить диапазон тестируемых значений. К примеру, одно дело, когда для сохранения имени человека используется столбец с данными типа nvarchar, и совершенно другое, когда в ходе тестирования в этом столбце помещается широкий набор символов в формате Unicode. Одна из проблем, возникающих при использовании данного метода, состоит в том, что разработчикам и тестировщикам применяемые данные часто не представляются реальными. Некоторые инструменты предполагают возможность расширения, так что вы можете применять собственные генераторы. Так, у меня была возможность создавать генератор телефонных номеров США или генератор телефонных номеров Австралии. Однако набор средств для генерации псевдореальных данных не особенно велик.
  • Используют копию производственной базы данных, подвергнутую процедуре маскировки. Вообще с помощью этого метода можно создавать базы данных, весьма напоминающие исходные, однако сам процесс маскировки может приводить к ошибкам. К примеру, недавно мне предоставили для работы базу данных, прошедшую процедуру маскировки, но я обнаружил все детали, касающиеся клиентов, в метаданных к изображениям PDF, которые хранились внутри этой же базы данных. Забавно, что сами файлы PDF при этом были заменены.

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

«Разбухающая» база данных

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

Наступили первые выходные, и несколько таблиц было перекомпоновано. Каково же было наше удивление, когда мы обнаружили, что операция перекомпоновки, занявшая не более 2 часов на нашей системе, на оборудовании заказчика выполнялась свыше 6 часов. Это привело к простоям, причем более продолжительным, чем планировалось. Мы выполняли перекомпоновку в автономном, а не в оперативном режиме, с тем чтобы сократить временные затраты, и потому, что отключение было нами предусмотрено.

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

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

В следующие выходные мы работали с таблицами меньших размеров. И снова на выполнение операций уходило гораздо больше времени, чем мы предполагали. Причем на этот раз мы обратили внимание на то, что общий объем базы данных увеличился. Уж этого мы никак не предполагали. Мы взялись за исследование отдельных таблиц. Объем первой объемной таблицы, подвергнутой перекомпоновке, составил не 12% исходного, как мы ожидали. Таблица оказалась примерно вдвое больше, чем раньше.

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

Мы поняли, что происходит нечто странное. Компоновка данных не давала ответа на вопрос, почему получаются такие результаты.

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

Как и следовало ожидать, сжатие осуществлялось постранично (см. экран 1).

 

Информация о сжатии данных
Экран 1. Информация о сжатии данных

И только в ходе проверки sys.indexes мы заметили, что значение fill_factor составляет 10. Мы вновь перепроверили сценарии и обнаружили, что предварительно не указали значение FILLFACTOR.

Коэффициенты заполнения

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

При создании новой таблицы значение FILLFACTOR по умолчанию тоже составляет 0. Это означает, что таблица не была изменена. Далее все будет работать точно так же, как если бы указанное значение равнялось 100; главное, чтобы не изменялось применяемое по умолчанию значение на уровне сервера.

Важно понять, что, изменяя значение FILLFACTOR, мы ищем компромисс между быстродействием беспорядочных операций INSERT и быстродействием операций SELECT. Чем меньше значение FILLFACTOR, тем быстрее мы можем вставить данные в середину таблицы (в отличие от присоединения к концу таблицы).

FILLFACTOR и быстродействие операций SELECT

Применение малых значений FILLFACTOR имеет один недостаток: системе для извлечения того же объема данных приходится прочитывать большее количество страниц. Так, если у вас это значение составляет 50, для считывания того же числа строк вам придется прочесть в два раза больше страниц.

Важно очень внимательно проанализировать вопрос о том, какой объем данных считывает система (в отличие от того, какой объем данных она записывает). Всякий раз, когда я обсуждаю с заказчиками вопрос о том, какая часть выполняемых их системами операций ввода-вывода приходится на операции записи, они всегда высказывают предположение, что эта часть составляет от 20 до 30%. Но каждый раз, когда я измеряю указанную долю (с помощью обычных средств обработки транзакций в реальном времени), ее реальное значение оказывается ближе к уровню в 1-2%.

Это означает, что, если я намереваюсь изменить настройки таким образом, чтобы операции записи выполнялись быстрее, а операции считывания — медленнее, мне нужно иметь для этого достаточно веские основания. Ведь понятно, что в общем случае оптимизацию нужно проводить для 98% выполняемых операций, а никак не для 2%. Разумеется, бывают исключения — ситуации, когда критически важным является время выполнения операций по вставке данных. Но в таких случаях я обращаюсь к перекомпоновке таблицы и пытаюсь сделать так, чтобы дополнительные операции вставки выполнялись не произвольно по всему пространству таблицы, а в нескольких местах внутри нее.

В приложении SharePoint мне не нравится одна особенность: каждому индексу по умолчанию задается значение FILLFACTOR, равное 70%. По-видимому, делается это потому, что таблицам присваиваются значения кластеризованного уникального идентификатора (GUID). Такое решение представляется мне неудачным; значение 70% используется для того, чтобы не допустить снижения быстродействия на операциях вставки. Однако при этом немедленно и в значительной мере снижается быстродействие при выполнении операций считывания SELECT.

В данном случае мы проверили значение на уровне сервера, и каков был результат, показано на экране 2.

 

Коэффициент заполнения индекса был причиной «разбухания» базы данных
Экран 2. Коэффициент заполнения индекса был причиной «разбухания» базы данных

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

Несмотря на все мои старания, я не могу вообразить обстоятельства, в которых эта настройка была бы целесообразной. Тем не менее сложившаяся ситуация заставила нас (уже в который раз) усвоить очень важный урок.

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

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

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