Это третья статья серии, посвященной базе данных, которой необходимо, образно говоря, «похудеть к Новому году».
В первой статье (опубликованной в Windows IT Pro/RE № 3 за 2016 год) мы говорили о том, почему размер так важен и для самой базы данных, и для всех ее копий, что применимо для большинства организаций. Затем мы рассмотрели вопрос об оценке дополнительного объема дискового пространства, получаемого при использовании сжатия по методу ROW. Этот метод позволил сжать базу данных нашего клиента, изначально занимавшую 3,8 Тбайт дискового пространства, до 2,6 Тбайт без каких-либо модификаций кода. Более того, сжатие таблиц и индексов дало возможность значительно повысить производительность приложения, активно использующего подсистему ввода-вывода.
Во второй статье я показал, каким образом применение алгоритмов ROW и PAGE приводит к изменению внутренней структуры страниц базы данных, и объяснил, почему сжатие по алгоритму PAGE позволяет столь эффективно сокращать объем данных.
Сжатие ROW или сжатие PAGE?
Мне еще не попадалась система, в которой применение сжатия по алгоритму ROW влекло бы за собой хотя бы небольшую проблему. На странице умещается больше строк, количество операций ввода-вывода сокращается, число строк, размещаемых в буферах памяти, увеличивается — похоже, что все говорит об эффективности такого решения.
Это особенно важно в тех случаях, когда мы имеем дело с такими типами Unicode-данных, как nchar и nvarchar. В предыдущей статье было показано, что при восстановлении исходного размера сжатой страницы для хранения одного символа nvarchar требуется 2 байт; эта схема известна как кодирование UCS-2 (UCS-2 encoding). В системах SQL Server начиная с SQL Server 2008 R2 при сжатии страницы SQL Server использует стандартную схему SCSU (Standard Compression Scheme for Unicode), которая позволяет резко сокращать объемы данных. С более подробными данными по схеме SCSU можно ознакомиться в статье по адресу: https://en.wikipedia.org/wiki/Standard_Compression_Scheme_for_Unicode. В случае использования английского и многих других языков объем текста, сохраненного в форматах nvarchar или nchar, может быть дополнительно сокращен на 50%. Это, в сущности, покрывает издержки, возникающие вследствие применения упомянутых более широких типов данных. Некоторые языки не дают столь выраженного эффекта. Объемы текстов на японском языке обычно сокращаются всего на 15%, но и этот результат оправдывает затраченные усилия.
Иное дело сжатие по алгоритму PAGE. При его использовании вопрос о том, как сжимать данные, размещенные в той или иной строке, не рассматривается. Решения принимаются применительно ко всей странице данных в целом. Такой метод подходит в случаях, когда требуется извлечь данные или когда нужно выполнить процедуру вставки в конце таблицы, но не в тех ситуациях, когда оператору приходится постоянно изменять содержимое строк на этих страницах.
Если повсеместное использование алгоритма ROW, как кажется, дает положительный результат, то применение сжатия по методу PAGE во всех таблицах, по всей вероятности, приведет к снижению производительности при выполнении модификаций данных.
Поэтому оптимальным решением для большинства систем обычно считается применение алгоритмов ROW и PAGE в том или ином сочетании, а не выбор одного из них с исключением другого.
Сказанное справедливо и для индексов. Если целесообразным методом сокращения объема таблицы может считаться сжатие по методу PAGE, то оптимальным решением для некластеризованных индексов в той же таблице, возможно, будет сжатие по алгоритму ROW. Допускается применение любых сочетаний алгоритмов.
На уровне разделов
Далее я представлю еще одну базовую концепцию, касающуюся сжатия таблиц. Хотя мы ранее рассматривали примеры с целыми таблицами или индексами, сжатие может применяться и на уровне разделов, а не просто на уровне таблиц или индексов.
Различные части каждой таблицы могут использоваться разными способами. К примеру, изменения, вносимые в содержащую историю транзакций таблицу, возможно, будут чаще всего касаться самых последних данных; при этом более старые данные в той же таблице могут вообще не подвергаться каким-либо изменениям.
Отсюда следует, что разумная стратегия для работы с подобной таблицей может выглядеть так, как показано на рисунке.
.jpg) |
| Рисунок. Вариант использования сжатия таблицы |
В этом случае более старые данные в таблице (показанные зеленым цветом) сжимаются по алгоритму PAGE, текущие данные (показанные красным) — по алгоритму ROW, а некластеризованные индексы для обеих категорий данных сжимаются по алгоритму ROW. При наличии индексов, выровненных по разделам, к ним вполне может применяться стратегия, используемая в отношении базовой таблицы.
Когда применяется сжатие PAGE
Разработчики Microsoft начали оснащать системы SQL Server динамическими административными dynamic management views (DMV) еще в версии SQL Server 2005 и за прошедшее с тех пор время усовершенствовали их. Мы можем задействовать DMV для определения того, каким образом используется та или иная таблица либо индекс. Большую помощь в этом деле может оказать DMV sys.dm_db_index_operational_stats. В представление включаются столбцы, показывающие, как часто осуществляются операции сканирования (scan) и применяются обновления различных типов. Я намеренно исключил таблицы, подготовленные Microsoft (см. листинг 1).
В листинге выводится процент времени, в течение которого раздел индекса или таблицы подвергался проверке или обновлению. Представленный код базируется на коде, опубликованном в руководстве «Data Compression: Strategy, Capacity Planning and Best Practices» (https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).
aspx), и обновлен на основе упомянутого кода. В этом руководстве Санджай Мишра представил исчерпывающий набор рекомендаций. Мой опыт подтверждает многие изложенные в нем положения.
Вообще я рекомендую в большинстве случаев применять сжатие по методу ROW, но на разделах, проверяемых более чем 70% времени и обновляемых менее 15% всего времени, на мой взгляд, следует применять сжатие по алгоритму PAGE. Эти цифры не слишком отличаются от показателей, приведенных в указанном руководстве, и базируются на решениях, которые я нахожу эффективными.
В нашем случае мы могли бы переделать код листинга 1 так, чтобы он учитывал предложенные рекомендации (см. листинг 2).
Не забывайте, что приведенный в листинге 2 код работает со статистикой индексов, которая в данный момент находится в памяти системы. Эти статистические данные удаляются при перезагрузке сервера. В целом я не советую придерживаться этих рекомендаций за исключением тех случаев, когда служба SQL Server работает без перерывов на протяжении не менее трех месяцев.
Пишем INSERT — читаем UPDATE?
Как правило, приведенный в листинге 2 код хорошо справляется с формулированием рекомендаций. Но имеется еще один сценарий, к которому мне хотелось бы присмотреться внимательнее. В упомянутом коде мы рассматриваем операции INSERT как своего рода модификацию. В каком-то смысле так оно и есть, однако операции INSERT не всегда оказывают на процедуру сжатия по алгоритму PAGE такое же воздействие, как другие модификации данных.
Я прихожу к заключению, что если все вставки в таблицу производятся в конце кластеризованного индекса для таблицы, то я могу игнорировать эти вставки для соответствия целям рекомендаций. К сожалению, не существует простого метода определить с помощью программных средств, так ли это на самом деле.
Иногда мы можем воспользоваться весьма простым решением. Речь идет о случаях, когда в таблице имеется столбец IDENTITY и этот столбец является кластерным ключом (а часто, хотя и не всегда, и первичным ключом). В таких ситуациях изменения на странице, которые привлекают наше внимание при анализе сжатия по алгоритму PAGE, по-видимому, не имеют особого значения.
Однако это не единственный способ выяснить, что же, собственно, происходит, и во многих случаях определить порядок, в котором осуществляются операции INSERT, мы можем, лишь располагая конкретной информацией относительно имеющегося приложения.
Применение сжатия по методу ROW или PAGE
Сжатие выполняется в тот момент, когда данные вводятся в страницы, поэтому для того, чтобы получить эффект сжатия, мы должны переписать все страницы. Это можно сделать при использовании команды ALTER INDEX с целью перестройки индекса, или команды ALTER TABLE с целью перестройки кучи, или бинарного дерева HoBT (heap or binary tree) для данной таблицы.
В качестве примера применения сжатия по методу ROW ко всем индексам таблицы Production.Product базы данных AdventureWorks мы можем выполнить следующий фрагмент кода:
ALTER INDEX ALL ON Production.Product REBUILD WITH (DATA_COMPRESSION = ROW);
Итак, какое же отношение все это имеет к нашей клиентской базе данных, которой необходимо «сесть на диету»? Как я уже отмечал, после применения ко всем данным сжатия по методу ROW объем базы сократился с 3,8 до 2,6 Тбайт. После выборочного сжатия отдельных компонентов по алгоритму PAGE в соответствии с изложенными в статье рекомендациями объем базы данных сократился до 1,4 Тбайт, и при этом быстродействие приложения возросло. И хотя расход ресурсов процессора в пересчете на страницу возрос, стоит отметить, что внушительное сокращение числа страниц компенсирует значительную часть этой нагрузки.
И что же, «похудение» на этом закончилось? Ничуть не бывало. Я проделал над рассматриваемой базой данных множество дополнительных манипуляций и расскажу о них в следующей статье. Большие строки и значения XML еще не были сжаты, этим-то мы и займемся.
SELECT t.name AS TableName, i.name AS IndexName, i.index_id AS IndexID, ios.partition_number AS PartitionNumber, FLOOR(ios.leaf_update_count * 100.0 / ( ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count )) AS UpdatePercentage, FLOOR(ios.range_scan_count * 100.0 / ( ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count )) AS ScanPercentage FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios INNER JOIN sys.objects AS o ON o.object_id = ios.object_id INNER JOIN sys.tables AS t ON t.object_id = o.object_id INNER JOIN sys.indexes AS i ON i.object_id = o.object_id AND i.index_id = ios.index_id WHERE ( ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count) <> 0 AND t.is_ms_shipped = 0 ORDER BY TableName, IndexName, PartitionNumber;
DECLARE @ScanCutoff int = 70; DECLARE @UpdateCutoff int = 15; WITH PartitionStatistics AS ( SELECT t.name AS TableName, i.name AS IndexName, i.index_id AS IndexID, ios.partition_number AS PartitionNumber, FLOOR(ios.leaf_update_count * 100.0 / ( ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count )) AS UpdatePercentage, FLOOR(ios.range_scan_count * 100.0 / ( ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count )) AS ScanPercentage FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ios INNER JOIN sys.objects AS o ON o.object_id = ios.object_id INNER JOIN sys.tables AS t ON t.object_id = o.object_id INNER JOIN sys.indexes AS i ON i.object_id = o.object_id AND i.index_id = ios.index_id WHERE ( ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count) <> 0 AND t.is_ms_shipped = 0 )SELECT TableName, IndexName, IndexID, PartitionNumber, UpdatePercentage, ScanPercentage, CASE WHEN UpdatePercentage <= @UpdateCutoff AND ScanPercentage >= @ScanCutoff THEN 'PAGE' ELSE 'ROW' END AS Recommendation FROM PartitionStatistics ORDER BY TableName, IndexName, PartitionNumber;