Один из наших достаточно крупных клиентов, в системе электронного документооборота которого ежедневно работает более 10 000 пользователей одновременно, использовал так называемые разреженные столбцы или разреженные столбцы.
Статья представляет собой попытку обобщить предпосылки и результаты использования данного функционала (и некоторых других настроек СУБД) в одном месте.
Проблемы и предыстория
Чтобы углубиться в тему, несколько слов о системе: система — это продукт, разработка которого началась в 2000-х годах.В настоящее время система активно развивается.
Продукт имеет клиентско-северную архитектуру с несколькими серверами приложений.
В качестве серверной части используется СУБД Microsoft SQL Server. Учитывая тот факт, что система уже не «новичок», в структуре базы данных имеются соответствующие механизмы/опции/объекты, использование которых на данный момент кажется неразумным и устаревшим.
От этих объектов постепенно отказываются, но бывают ситуации, когда они все же используются.
Посылка №1
На очередном аудите производительности совместно с Заказчиком мы заметили быстрый рост одной из таблиц (назовем ее таблицей Х).Объем таблицы X составил почти более 350 ГБ (кстати, объем всей базы данных около ~2ТБ).
При этом распределение по фактическим табличным данным и индексам было следующим:
- было около 115 ГБ данных,
- весь оставшийся объём ~235 ГБ пришлось на индексы.
Довольно необычна ситуация, когда индексы в таблице примерно в ~2 раза больше самих данных.
Те.
получаем достаточно высокие накладные расходы, которые в свою очередь негативно влияют:
- продолжительность операций добавления/обновления данных в эту таблицу (чем больше индексов, тем «дороже» операция);
- продолжительность сервисных операций по поддержанию (восстановлению) этих показателей;
- продолжительность резервного копирования и восстановления базы данных в случае сбоя;
- Требования к дисковому пространству по объему возрастают.
Предпосылка №2
В целом схему работы СУБД можно описать так: все данные перед обработкой загружаются с дисков в буферный пул (кэш).Это позволяет сократить количество дисковых операций и ускорить обработку наиболее часто используемых данных.
Более подробную информацию о механизме можно найти, например, в статья .
Косвенно эффективность использования буферного пула можно отслеживать с помощью счетчика Page Life Expectancy — времени жизни страницы в буферном пуле.
На интервале в несколько месяцев была выявлена отрицательная динамика по сокращению времени жизни страницы в буферном пуле.
Бездействие может привести к:
- значительное увеличение нагрузки на дисковую подсистему;
- увеличение продолжительности операций пользователя.
Поскольку в эти месяцы система не была статичной и постоянно модифицировалась, анализ решили начать с содержимого буферного пула.
Для этого мы использовали данные динамического представления: sys.dm_os_buffer_descriptors .
Пример запроса: Into_BufferPool
При объеме буферного пула ~185 ГБ около 80-90 ГБ составляли данные кластерного индекса нашей проблемной таблицы X. Объем остального буферного пула был распределен между индексами довольно равномерно.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;
Из этого следовало, что максимальный эффект можно получить, каким-то образом оптимизировав данные таблицы X (в данном случае речь идет о ее кластерном индексе).
Посылка №3
Опыт показывает, что накопление большого объема данных в одной таблице рано или поздно отразится на производительности если не всех, то хотя бы некоторых операций, связанных с этой таблицей.Ситуация нелинейно ухудшается, когда в этой таблице имеется большое количество столбцов.
Кроме того, когда мы проанализировали таблицу X на предмет заполнения ее данными, мы увидели следующую картину: практически для всех строк был заполнен только определенный набор столбцов (что обеспечивает гибкость системы и адаптацию к конкретным бизнес-требованиям).
Что по сути опять-таки приводит к низкой эффективности хранения и обработки данных, т.к.
некоторые ячейки не хранят информацию, но тем не менее для этих ячеек резервируется место (например, добавление пустого столбца с типом данных int увеличит стоимость хранения таблице как минимум на [4 байта * количество строк в таблице]).
Решения/Исправления
С учетом всех исходных данных, представленных выше, были определены 4 направления дальнейшего развития:- фильтрованные индексы;
- сжатие данных с использованием СУБД (сжатие данных);
- редкие столбцы;
- разделение таблицы X на несколько меньших таблиц.
Отфильтрованные индексы
Вот что говорит нам официальная документация: «Фильтрованный индекс — это оптимизированный некластеризованный индекс, который особенно подходит для запросов, которые выбирают из четко определенного подмножества данных.Хорошо спроектированный фильтрованный индекс может повысить производительность запросов и снизить затраты на обслуживание и хранение индекса по сравнению с «полным индексом».
индексы таблицы».
.
Если говорить немного проще, то речь идет о возможности создания индекса только для части данных в таблице, например, мы можем создать индекс в таблице X для конкретного бизнес-кейса.
Но для использования индекса необходимо было использовать новую версию программного обеспечения, в которой также была изменена структура базы данных.
В частности, в новой версии значения параметров подключения клиентского ПО к СУБД изменены на режим ВКЛ:
- НАБОР ANSI_NULLS НА;
- НАБОР QUOTED_IDENTIFIER НА;
- НАБОР CONCAT_NULL_YIELDS_NULL НА.
Более того, использование фильтрованных индексов также не предполагалось, поскольку это делало неэффективным, например, использование опции принудительной параметризации.
Сжатие данных
Поскольку на клиенте была установлена СУБД версии 2012, сжатие данных для этой версии возможно в двух видах:- сжатие на уровне страницы;
- Сжатие строк.
Поэтому мы более подробно остановились на первых двух.
В соответствии с документация Сжатие на уровне страниц — это более ресурсоемкая операция, чем сжатие на уровне строк.
Исходя из этого, вариант сжатия на уровне страницы был сразу отброшен.
Далее была попытка использовать сжатие строк, но в документации мы также встретили упоминание о том, что даже это потребляет дополнительные ресурсы.
А поскольку ресурс процессора очень ограничен, от этого варианта тоже пришлось отказаться.
Разреженные столбцы
Разреженные столбцы — это обычные столбцы, в которых оптимизировано хранилище для значений NULL. Разреженные столбцы уменьшают пространство, необходимое для хранения значений NULL, но увеличивают стоимость получения значений, отличных от NULL. Для достижения положительного эффекта каждый конкретный столбец должен иметь определенный процент значений NULL. Этот процент зависит от типа данных в столбце, например:| Тип данных | Неразреженные байты | Разреженные байты | Процент значений NULL |
|---|---|---|---|
| плавать | 4 | 8 | 64% |
| дата и время | 8 | 12 | 52% |
| варчар | 8 | 12 | 52% |
| интервал | 2 | 4 | 60% |
Список ограничений и несовместимостей представлен в официальная документация .
Те.
Для оценки возможности перехода на разреженность в больших масштабах необходимо было проанализировать:
- наличие ограничений из документации по конкретной таблице/столбцу;
- фактический процент значений NULL в этих столбцах;
Выполнение самого запроса на больших объемах занимает довольно много времени; рекомендуется указать конкретную таблицу, которую необходимо проанализировать.
Разреженный кандидат
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')
Теги: #Администрирование базы данных #sql #Microsoft SQL Server #sql-сервер #разреженные столбцы #фильтрованные индексы
-
Психография Против Демографии
19 Oct, 24 -
Центр Кс. Впечатление
19 Oct, 24 -
Как Мы Пришли К Взаимопониманию
19 Oct, 24 -
Пасхальное Яйцо В Блокноте И Калькуляторе
19 Oct, 24 -
Сказки О Юзабилити Или Интерфейсе Будущего
19 Oct, 24 -
Первое Знакомство С Ос Inferno
19 Oct, 24
