Cжатие данных в SQL Server 2008.
Недавно мне пришлось выполнять миграцию своего хранилища данных с SQL Server 2005 на SQL Server 2008. Как известно, одним из новшеств SQL Server 2008 является сжатие данных. Эта возможность призвана увеличить производительность базы данных за счет сжатия данных и индексов в таблицах и индексированных представлениях и, как следствие, уменьшения операций ввода-вывода. Также, благодаря сжатию, может существенно уменьшится размер базы, что облегчает администрирование и управление. Все это звучало заманчиво, и я решил использовать эту возможность.
Причиной, побудившей меня более внимательно разобраться с данной функциональностью, и в конечном итоге написать эту статью, стало получение совершенно иного результата чем ожидалось :)
Я решил провести сжатие трех самых крупных таблиц фактов, представляющих собой структуры, с максимально компактными, насколько это возможно, столбцами, ссылающимися на справочники-измерения и содержащими ряд показателей, которые агрегируются в отчетах. Ниже будет приведена типичная структура таблицы фактов и типичный запрос к этой таблице, в упрощенном виде.
Но к моему удивлению, после сжатия, я не только не получил прироста производительности, но наоборот - выполнение запросов замедлилось. В некоторых случаях производительность осталась на прежнем уровне.
Было решено исследовать эффект от сжатия данных и провести тестирование, чтобы ответить на вопрос о падении производительности.
Подготовка теста.
Итак, есть таблица, назовем ее ProductMMR содержащая некие факты в нескольких разрезах.
Вот ее структура:
[ID] [int], -- PK
[StockID] [smallint], -- склад
[ProductID] [int], -- товар
[DateID] [smallint], -- дата
[StockFormatID] [smallint], -- тип склада
[Qty] [smallint] -- количество
Исходный размер таблицы - 16 ГБ, индексов - 18 ГБ (я воспользовался системной ХП sp_spaceused для определения размеров данных и индексов).
Теперь самое время решить по каким критериям будем оценивать эффективность сжатия.
Поскольку практически каждого разработчика или администратора волнует производительность его серверов и приложений, очевидно, что главным критерием оценки эффективности сжатия будет время, затраченное на выполнение запросов, количество операций ввода-вывода, количество затраченного процессорного времени. Также будет принято во внимание освободившееся дисковое пространство в результате сжатия.
Вот тестовый запрос на выборку к данной таблице :
SET STATISTICS TIME ON -- для измерения времени выполнения запроса
SET STATISTICS IO ON -- для измерения логических и физических операций ввода-вывода
GO
SELECT
fact.DateID,
fact.StockID,
SUM(fact.Qty) AS Qty
FROM fact.ProductMMR fact
WHERE (fact.DateID BETWEEN @DateIDBegin AND @DateIDEnd)
GROUP BY fact.DateID, fact.StockID
Был задан временной промежуток 30 дней (в таблице фактов это соответствует более 150 млн. записей).
Определение стратегии сжатия и его реализация.
Подробно про реализацию сжатия можно почитать в MSDN. Там же описана реализация сжатия для страниц и для строк. Эффект от сжатия зависит от данных в таблице - насколько много там повторяющихся значений и каков тип данных.
Теперь перейдем к реализации сжатия, предварительно определив его стратегию.
Sunil Agarwal в своем блоге приводит ряд рекомендаций по этому поводу, позволю себе их обобщить и привести здесь:
1. Не имеет смысла сжимать данные или индексы имеющие малый размер. Если таблица занимает 10 страниц, и будет сжата до одной, то это не принесет выгоды в данном случае. Необходимо помнить о том, что сжатые данные должны быть распакованы каждый раз, когда к ним осуществляется доступ. Перед применением сжатия, вы должны оценить текущий размер таблицы/индекса и прогнозируемый размер после сжатия.
2. Если таблица интенсивно используется операторами DML и SELECT, то в результате сжатия вы можете получить чрезмерную нагрузку на процессоры в результате распаковки при каждом обращении к этим данным. В этом случае необходимо особо тщательно подойти к вопросу целесообразности сжатия таблицы/индекса.
3. Если экономия от сжатия невысока, тогда сжатие проводить не рекомендуется. Бывают такие случаи, когда размер сжатых данных оказывается больше несжатых. Это говорит о том, что в таблице используются наиболее компактные типы данных.
4. Если у вас типичное OLTP-приложение, в общем случае вам следует выбирать сжатие типа ROW. Этот тип сжатия менее затратный с точки зрения распаковки данных. Однако, как правило, сжатие типа PAGE более эффективно, в плане потенциального свободного пространства.
Оценить выгоду от сжатия можно либо в мастере, либо при помощи хранимой процедуры sp_estimate_data_compression_savings.
В моем случае я получил такие результаты :
Таблица 1.
Эффективность сжатия данных.
Тип сжатия | Размер до сжатия | Размер после сжатия | % сжатия |
ROW | 33,4 ГБ | 22,7 ГБ | 32 % |
PAGE | 33,4 ГБ | 18,3 ГБ | 45 % |
Как видно из таблицы, получить эффект от сжатия данных можно. Хотя в данном случае, это не самый хороший показатель, во многих случаях данные сжимаются на 70-80%.
Во многих случаях коэффициент сжатия будет больше, а в некоторых – намного больше, чем получился у меня в этом тесте. Все зависит от типов данных и от того, насколько много повторяющихся данных в ваших таблицах.
Реализовать сжатие таблицы типа PAGE/ROW можно через мастер сжатия, генерирующего подобный код:
ALTER TABLE [fact].[ProductMMR] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)
Применить сжатие типа PAGE можно, применив параметр DATA_COMPRESSION = PAGE.
Указав DATA_COMPRESSION = NONE можно отменить сжатие данных.
Я не буду приводить здесь синтаксис сжатия индексов и партиций, интересующийся без труда найдет их в BOL.
Надо помнить, что перед включением или отключением сжатия строки или страницы, необходимо столько же места на диске, как и для создания или перестройки индекса.
Результаты тестирования.
Итак, до и после сжатия по типу PAGE был выполнен тестовый запрос.
Вот его результаты, на «разогретом» кэше:
Таблица 2.
Результаты теста № 1*.
Тип сжатия | Время выполнения запроса(мс) | Операций логического чтения** | Затраченное процессорное время (мс) |
Без сжатия | 26 147 | 1 419 113 | 308 736 |
Сжатие PAGE | 41 104 | 709 360 | 486 453 |
*Запрос выполнялся на сервере с 12 ядрами и 32 Гб ОЗУ, дисковая подсистема 10 RAID.
**Показаны только операции логического чтения, т.к. физического чтения не было – данные находились в кэше.
Увидев эти результаты, можно удивиться – ведь операций логического чтения на сжатых данных было произведено в два раза меньше, но время выполнения запроса оказалось на 36 % больше. А все дело видимо в том, что хоть операций чтения меньше и читается все из памяти, но велики накладные расходы на распаковку данных. Ведь распаковывается не страница целиком, а каждая запись по отдельности.
Можно предположить, что если данные будут незакэшированы, то в этом случае можно добиться прироста производительности, за счет уменьшения количества дорогостоящих операций физического чтения, в случае сжатых данных.
Поэтому было решено провести еще один цикл тестов, но уже на холодном кэше.
Был выполнен то же самый тестовый запрос, но предварительно был очищен кэш процедур и буфер, при помощи команд DBCC FREEPROCCACHE и DBCC DROPCLEANBUFFERS.
Вот результаты тестового запроса до и после сжатия, на «холодном» кэше:
Таблица 3.
Результаты теста № 2.
Тип сжатия | Время выполнения запроса(мс) | Операций физического чтения | Операций логического чтения | Затраченное процессорное время (мс) |
Без сжатия | 43 077 | 1 419 105 | 1 420 868 | 235 266 |
Сжатие данных PAGE | 48 887 | 707 495 | 710 105 | 416 689 |
Вот эти результаты подтверждают ранее высказанное предположение. Как видно, время выполнения отличается на 12 %, вместо 36 % из первого теста.
Статистика по операциям логического чтения такая же, но в данном тесте присутствует физическое чтение, которое существенно сказывается на производительности запроса (сравните время выполнения с первым тестом). И, судя по всему, сжатие данных будет давать положительный эффект в плане производительности тогда, когда будут выполняться запросы к редко используемым, большим массивам данных, сжатие которых позволит сэкономить на операциях физического чтения, по сравнению с которыми распаковка данных из буферного пула менее дорогостоящая операция. Хорошим примером может быть сжатие партиций с данными за самые ранние периоды в хранилище или сжатие других больших редко используемых таблиц. Напомню, что сжимать данные можно как на уровне таблиц, так и на уровне партиций и индексов. Причем можно комбинировать типы сжатия.
Мне удалось добиться того, что на тестовой таблице выборка сжатых данных, за больший период, стала выполняться примерно на одном уровне, с выборкой несжатых данных за тот же период, т.е. чем больше массив данных, к которым обращался запрос, тем положительный эффект от сжатия был больше, т.к. серверу сначала приходилось обращаться за большим объемом данных к дисковой системе а не буферному пулу.
Но самая главная причина того, что в моем случае упала производительность запросов - это относительно невысокий коэффициент сжатия, менее 50 %. Я провел еще несколько тестов и обнаружил, что на тех таблицах, которые сжимались на 60-75 %, производительность запросов увеличивалась по сравнению с несжатыми таблицами.
Очевидно, что чем выше процент сжатия, тем сильнее это скажется на приросте производительности.
Сергей Харыбин, MCTS SQL Server.
Комментариев нет:
Отправить комментарий