Разреженные столбцы или sparse columns в MS SQL Server. Реальный опыт применения

в 9:15, , рубрики: filtered indexes, Microsoft SQL Server, sparse columns, sql, sql server, Администрирование баз данных

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

Статья – попытка свести предпосылки и результаты применения этой функциональности (и некоторых других настроек СУБД) в едином месте.

Проблемы и предпосылки

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

В качестве серверной стороны используется СУБД Microsoft SQL Server.

C учетом того, что система уже не «новичок», в структуре БД есть соответствующие механизмы/опции/объекты, использование которых на текущий момент выглядит необоснованным и устаревшим. Постепенно идет отказ от этих объектов, но встречаются ситуации, когда они до сих пор используются.

Предпосылка №1

На очередном из аудитов производительности совместно с Заказчиком обратили внимание на быстрый рост одной из таблиц (назовем ее таблицей X). Объем таблицы X без малого составлял более 350 ГБ (к слову объем всей БД составляет порядка ~2ТБ). При этом распределение по собственно данным таблицы и индексам было следующим:

  • данных было порядка 115 ГБ,
  • весь остальной объем ~235 ГБ приходился на индексы.

Т.е. ситуация достаточно незаурядная, когда индексов на таблице больше самих данных примерно в ~2 раза. Т.е. получаем достаточно высокие накладные расходы, которые в свою очередь негативно влияют на:

  • длительность операций вставки/обновления данных в этой таблице (чем больше индексов, тем «дороже» операция);
  • длительность сервисных операций по обслуживанию(ребилду) этих индексов;
  • длительность времени резервного копирования и восстановления БД в случае сбоя;
  • повышаются требования к дисковому пространству в части объема.

Предпосылка №2

По-крупному схему работы СУБД можно описать следующим образом: все данные перед обработкой загружаются с дисков в буферный пул (кэш). Это позволяет сократить число дисковых операций и ускорить обработку наиболее частоиспользуемых данных. Более детально с механизмом можно ознакомиться, например, в статье. Эффективность использования буферного пула косвенно можно отследить с помощью счетчика Page Life Expectancy – время жизни страницы в буферном пуле.

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

Бездействие могло привести к:

  • значительному увеличению нагрузки на дисковую подсистему;
  • увеличению длительности пользовательских операций.

Как итог было принято решение провести анализ причин.

Поскольку система в эти месяцы не была статичной и постоянно модифицировалась, анализ решили начать с содержимого буферного пула. Для этого использовали данные динамического представления: sys.dm_os_buffer_descriptors.

Пример запроса:

Into_BufferPool

SELECT
indexes.name AS index_name,
objects.name AS object_name,
objects.type_desc AS object_type_description,
COUNT(*) AS buffer_cache_pages,
COUNT(*) * 8 / 1024  AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units
ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions
ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))
OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))
INNER JOIN sys.objects
ON partitions.object_id = objects.object_id
INNER JOIN sys.indexes
ON objects.object_id = indexes.object_id
AND partitions.index_id = indexes.index_id
WHERE allocation_units.type IN (1,2,3)
AND objects.is_ms_shipped = 0
AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY indexes.name,
objects.name,
objects.type_desc
ORDER BY COUNT(*) DESC;

При объеме буферного пула в ~185 ГБ порядка 80-90 ГБ составляли данные кластерного индекса нашей проблемной таблицы X. Объем остальной части буферного пула был распределен между индексами достаточно равномерно. Из этого следовало, что максимальный эффект можно было бы получить, оптимизировав каким-то образом данные таблицы X (в данном случае речь о ее кластерном индексе).

Предпосылка №3

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

Кроме того, когда проанализировали таблицу X на предмет заполненности данными, увидели следующую картину: для практически всех строк заполненным был только определенный набор столбцов (за счет чего достигается гибкость системы и адаптация под конкретные бизнес-требования). Что по сути снова приводит к низкой эффективности хранения и обработки данных, т.к. часть ячеек не хранит информации, но тем не менее, место под эти ячейки резервируется (например, добавление пустого столбца с типом данных int увеличит расходы на хранение таблицы как минимум на [4 байта * кол-во строк в таблице]).

Варианты решения/исправления

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

  • фильтрованные индексы (filtered indexes);
  • сжатие данных средствами СУБД (data compression);
  • разреженные столбцы (sparse columns);
  • разделение таблицы X на несколько более мелких таблиц.

Фильтрованные индексы

Вот что нам говорит официальная документация:
«Фильтруемый индекс — это оптимизированный некластеризованный индекс, особенно подходящий для запросов, осуществляющих выборку из хорошо определенного подмножества данных…Хорошо спроектированный фильтруемый индекс позволяет повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с «полнотабличными» индексами».

Если сказать чуть проще, то речь о возможности создания индекса только для части данных в таблице, например, мы можем создать индекс в таблице X под конкретный бизнес-кейс.
Но для применения индекса необходимо было использовать новую версию ПО, в которой была изменена в том числе структура БД. В частности, в новой версии были изменены значения параметров соединения клиентского ПО с СУБД на режим ON:

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

Сжатие данных

Поскольку у клиента была установлена версия СУБД – 2012, то сжатие данных для этой версии возможно двух видов:

  • cжатие на уровне страниц (page compression);
  • сжатие на уровне строк (row compression).

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

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

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

Разреженные столбцы

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

Тип данных Неразреженные байты Разреженные байты Процент значений NULL
float 4 8 64%
datetime 8 12 52%
varchar 8 12 52%
int 2 4 60%

При этом не каждый столбец м.б. переведен в sparse. Список ограничений и несовместимостей приведен в официальной документации.

Т.е. для оценки возможности перевода в sparse по-крупному необходимо было проанализировать:

  • наличие ограничений из документации на конкретной таблице/столбце;
  • реальный процент NULL-значений в этих столбцах;

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

SparseCandidate


CREATE TABLE #temp (
ColumnName varchar(50),
ColumnID int,
TableName varchar(50),
TableId int,
TypeName varchar(50),
IsParse bit,
IsNullable bit,
NumberOfRow bigint,
NumberOfRowNULL bigint,
Ratio int)
SET NOCOUNT ON

INSERT into #temp
 SELECT DISTINCT
sys.columns.name ColumnName,
sys.columns.column_id ColumnID,
OBJECT_NAME(sys.columns.object_id) AS TableName,
sys.columns.object_id TableID,
CASE systypes.name 
WHEN 'sysname' THEN 'nvarchar'
ELSE systypes.name
END AS TypeName,
sys.columns.is_sparse IsParse,
sys.columns.is_nullable IsNullable,
0,0,0
 FROM sys.columns (NoLock) 
INNER JOIN systypes (NoLock) ON systypes.xtype = sys.columns.system_type_id
 WHERE sys.columns.object_id =  OBJECT_ID('my_table')						-- change table name
and systypes.name NOT IN ('geography', 'geometry', 'image', 'ntext', 'text', 'timestamp')
and sys.columns.is_sparse = 0
and sys.columns.is_nullable = 1
and sys.columns.is_rowguidcol = 0
and sys.columns.is_identity = 0
and sys.columns.is_computed = 0
and sys.columns.is_filestream = 0
and sys.columns.default_object_id = 0
and sys.columns.rule_object_id = 0
and sys.columns.system_type_id=sys.columns.user_type_id

delete tps from #temp tps where exists (
select DISTINCT 'Exists' from sys.columns
inner join sys.indexes i on i.object_id = tps.TableId
inner join sys.index_columns ic on ic.column_id = tps.ColumnID inner join sys.columns c on c.object_id = tps.TableId and ic.column_id = c.column_id
where i.type =1 or i.is_primary_key = 1)
select count(*) from #temp
delete tps from #temp tps inner join sys.partitions p 
on p.object_id = tps.TableId where p.data_compression<>0;

DECLARE @TableName nvarchar(1000)
DECLARE @ColumnName nvarchar(1000) 
DECLARE @vQuery nvarchar(1000) 
DECLARE @result1 INT 
DECLARE @result2 INT
DECLARE tables_cursor CURSOR FAST_FORWARD
 FOR
 SELECT TableName,ColumnName FROM #temp
 OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 -- Search the number of row in a table
 SET @vQuery = 'SELECT @result1= COUNT(*) FROM [' + @TableName + ']'
 EXEC SP_EXECUTESQL 
 @Query = @vQuery 
 , @Params = N'@result1 INT OUTPUT'
 , @result1 = @result1 OUTPUT 
 -- Search the number of row in a table
 SET @vQuery = 'SELECT @result2= COUNT(*) FROM [' + @TableName + '] where [' + @ColumnName + '] is null'
 EXEC SP_EXECUTESQL 
 @Query = @vQuery 
 , @Params = N'@result2 INT OUTPUT'
 , @result2 = @result2 OUTPUT 
 update #temp set NumberOfRow = @result1,NumberOfRowNULL = @result2,Ratio = (@result2*100/@result1) where 
ColumnName=@ColumnName and TableName=@TableName
 FETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName
 END
 CLOSE tables_cursor
DEALLOCATE tables_cursor
--delete from #temp where Ratio>10
select * from #temp
--drop table #temp

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

Особо отмечу, использование механизма не повышает загрузку процессора сервера СУБД (проверяли как на практике, так и нагрузочном тестировании).

Пожалуй, это одна из тех редких ситуаций, которую кратко можно изобразить следующими картинками:

Разреженные столбцы или sparse columns в MS SQL Server. Реальный опыт применения - 1

Разделение таблицы на более мелкие

В данном случае работы можно было также разбить на блоки:

  • внесение изменений в архитектуру системы;
  • модификация всей прикладной разработки под новую архитектуру.

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

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

Эффект от sparse

Ну и вместо выводов хотелось бы отметить в цифрах положительные моменты применения sparse-колонок:

  1. уменьшили объем кластерного индекса таблицы X в ~2 раза (аналогичный эффект по уменьшению объема м.б. при пересоздании некластерных индексов с key-полями, которые были переведены в sparse);
    Разреженные столбцы или sparse columns в MS SQL Server. Реальный опыт применения - 2
  2. исходя из п.1 повысилась эффективность использования буферного пула, т.к. уменьшился объем данных таблицы X в буферном пуле;

    Разреженные столбцы или sparse columns в MS SQL Server. Реальный опыт применения - 3

  3. исходя из п.1-2 увеличилось время жизни страницы в буферном пуле (синия линия), и как следствие уменьшилась нагрузка на диски (желтая линия);

    Разреженные столбцы или sparse columns в MS SQL Server. Реальный опыт применения - 4

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

Если свести данные в таблицу получим:

Показатель Улучшение Примечание
Объем кластерного индекса таблицы X, ГБ в 2 раза На диске (HDD)
Размер кластерного индекса таблицы X в буферном пуле, ГБ В 2 раза В памяти (RAM)
Page Life Expectancy, сек В 2 раза Время жизни страницы в буферном пуле
Disk Transfers/sec, iops В 1.6 раза Число дисковых операций. Нагрузка на СХД уменьшена.

Ограничения sparse

Тем не менее, кроме плюсов появились и ограничения:

  1. необходима периодическая актуализация sparse-столбцов. Через какое-то время распределение NULL и не NULL значений в sparse-колонках может измениться и использование sparse будет неоправданным;
  2. число столбцов, которые можно переводить в sparse ограничено. В случае превышения при обновлении строк может возникнуть ошибка 576.

Детальнее полный список ограничений можно прочесть здесь.

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

Возможно кто-то уже применял настройки описанные в статье на высоконагруженных системах, отписывайтесь к каким результатам пришли вы.

Автор: Алексей

Источник

Поделиться

* - обязательные к заполнению поля