FastTrack Data Warehouse Иван Косяков Technology Architect, MTC Moscow Особая благодарность Алексею Халяко из SQLCAT

SQL Server Design Win Program Нацелена на работу с самыми инновационными и сложными проектами 10+ TB DW, 3k/tran/s OLTP, больше 500GB+ кубы, миграции с конкурентных платформ, сложные имплементации, консолидации серверов (1000+) Инвестиции в самые масштабные проекты в мире, на которые в дальнейшем смогут ориентироваться другие клиенты Предоставляем техническую экспертизу группы Provide SQLCAT и опыт работы в проектах Осуществляем пересмотр архитектуры, фокусируясь на производительности, надёжности, масштабируемости и доступности Предоставляем лабораторию в Редмонде и возможность прямого общения с разработчиками.

Что такое FastTrack Data Warehouse? Метод построения эффективной по затратам, сбалансированной системы для загрузки, типично для хранилищ данных Эталонные аппаратные конфигурации разработаны с поставщиками оборудования Рекомендации размещения, загрузки и управления данными Используется только для реляционных хранилищ – не для SSAS, IS, RS

Темы Сбалансированная архитектура, как подход к построению DW Примеры справочных архитектур FastTrack DW Оптимизация хранилищ, загрузки и поддержка Примеры внедрений Выводы

Архитектура компонентов FastTrack DW Server Windows Server OS SQL Server Storage Interconnect Storage Enclosure Disk Array Host Storage Adaptor Storage Processor

Потенциальные узкие места в системе FC HBA FC HBA A A B B FC HBA FC HBA A A B B FC SWITCH STORAGE CONTROLLER STORAGE CONTROLLER A A B B A A B B CACHE SERVER CACHE SQL SERVER WINDOWS CPU CORES CPU Feed Rate HBA Port Rate Switch Port Rate SP Port Rate A A B B DISK LUN DISK LUN SQL Server Read Ahead Rate SQL Server Read Ahead Rate LUN Read Rate Disk Feed Rate

Сбалансированная архитектура КомпонентСбалансирован для … CPUМаксимальное потребление данных из кэша для определенных наборов запросов (на следующих слайдах) Controller (Service Processor) Пропускной канал для поставки данных ядрам CPU (базируется на наборе запросов) HBA (Host Bus Adapter) Агрегирует потоки данных, поставляемые контроллером SwitchВыровнен с пропускной способностью HBA и оптимизирован для последовательного ввода-вывода DisksАгрегирует потоки данных с контроллера /емкость хранилища

Cбалансированная система Построить систему, состоящую из сервера и хранилища, в которых пропускная способность ввода-вывода может достаточно загрузить SQL Relational DW Избегайте разделения хранилища с другими серверами Избегайте избыточного инвестирования в диски Обращайте внимание на производительность scan операций, а не IOPS Располагайте данные так, чтобы максимально использовать сканирование диапазонов Минимизировать фрагментацию данных

Характеристики нагрузок хранилищ данных Интенсивные сканирования Hash Joins Агрегации SELECTL_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY, AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER FROMLINEITEM GROUPBYL_RETURNFLAG, L_LINESTATUS ORDERBYL_RETURNFLAG, L_LINESTATUS

Проверка системы Для подтверждения корректной настройки Фазы тестирования: Синтетические тесты ввода-вывода Проверка системы хранения, сети, операционной системы SQLIO для генерации операций ввода-вывода Perfmon – для мониторинга Тестирование SQL Server Проверка производительности стека SQL Server Maximum Consumption Rate (MCR) – данные из памяти для обработки запроса процессором Benchmark Consumption Rate (BCR) – данные с диска для обработки типичной нагрузки процессором Финальный шаг процесса внедрения

Демонстрация тестирования ввода-вывода

Сбалансированная система - CPU Определить объем потребления данных на ядро процессора для набора запросов Пример: Предположим TPC-H запрос 2 – типичная загрузка для системы Выполнить запрос на тестовом сервере с данными полностью загруженными в кэш Запрос выполняется параллельно с MAXDOP 4 Убедиться в загрузке 100% CPU на 4 ядрах Засечь время выполнения и определить количество прочитанных #страниц (Set Statistics IO on; Set Statistics Time on) Расчет нагрузки на ядро = (# Logical Reads * 8K)/(CPU Time)

Можно сделать еще корректнее В принципе, запросы, которые выполняют достаточно сложные вычисления, форматирование, объединения измерений – потребляют больше CPU Сложные запросы будут «медленней» потреблять мощность ядер, чем простые Измерить потребление данных на ядро для разных запросов и вычислить «средний вес» Стандартный подход при расчете вычислительной емкости системы

Или давайте это сделаем мы… Мы протестировали набор TPCH запросов, которые соответствуют «типовой» загрузке для Data Warehouse Сделали вывод, что SQL Sever 2008 на нынешней x64 ядерной платформе потребляет ~200 MB/Sec на ядро в среднем для такого типа загрузки Использовали эти выводы как базу для опубликованной «эталонной» архитектуры Однако, Ваша нагрузка может отличаться! Для точного выбора архитектуры и объемов используйте свои измерения

Примеры загрузки CPU Пример 1: Характеристики запроса: Сканирование одного кластерного индекса, hash match, агрегации по 8 столбцам Statistics IO: logical reads , physical reads 0, Readahead reads 0 Statistics Time: CPU time = ms Нагрузка на ядро: ( * 8K) / (144690) = 185 MB/s per core Пример 2: Характеристики запроса: 3 объединения таблиц, одна агрегация, множественные hash joins, агрегация по одному столбцу Statistics IO: logical reads (total all tables) , physical reads 0, Readahead reads 0 Statistics Time: CPU time = ms Нагрузка на ядро: ( * 8K) / (121167) = 137 MB/s per core Quad Core AMD Opteron 2384 (Shanghai)

Fast Track калькулятор Определив типы запросов к системе используйте калькулятор:

Сбалансированная система - хранилище Количество ядер CPU и пропускная способность загрузки помогут определить количество контроллеров и «корзин» для представления суммарной нагрузки # контроллеров определит минимальное количество дисков для предоставления пропускной способности сканирования Определить требуемую емкость / # дисков исходя из ожидаемого объема дискового пространства Оставить достаточно пространства для TempDB или особенно большим таблицам в системе (для административных задач)

Сбалансированная система - IO Используем для начала 2-x четырех ядерных сервера Убедиться, что скорость потребления данных на ядро может быть предоставлена всеми компонентами в стеке ввода-вывода Максимальная теоретическая пропускная способность IO стека оптимизированного для 8 ядерной Fast Track архитектуры ( предполагая 200 MB/s на ядро) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core)

Сбалансированная система – хранилище (2) Теоретические максимумы - всегда только теоретические Тесты для получения реальных параметров могут быть необходимы Наблюдаемая пропускная способность на 8 ядерной системе Fast Track при выполнении SQLIO CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core)

Сбалансированная система – масштабирование Server Fiber Switch HBA Storage Enclosure Storage Processor RAID-1 CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 Storage Enclosure Storage Processor RAID-1 CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) CPU Socket (4 Core) HBA

Темы Предпосылки Сбалансированная архитектура, как подход к построению DW Примеры справочных архитектур FastTrack DW Оптимизация хранилищ, загрузки и поддержка Примеры внедрений Выводы

Оптимизация схемы хранилища для интенсивных сканирований Конфигурация LUNов базируется на RAID10 Предоставляет оптимальный уровень доступа к дискам «Размазывание» данных по дискам происходит на уровне файлов SQL Server (разбиение на файлы в файловой группе) Наблюдаемая производительность одной RAID пары >= 130 MB/s SPASPA SP BSP B RAID GP02 LUN3 LUN RAID GP01 LUN1 LUN RAID GP03 LUN5 LUN RAID GP04 LUN7 LUN RAID GP05 LUN0 (Logs) HS

Влияние схемы хранилища на SQL Server Создать один файл данных на LUN для каждой файловой группы Файловая группа TempDB размещается на тех же LUN, что и другие базы Лог размещается на отдельных дисках в каждой «полке» Разбиение на stripes используя SQL Лог может размещаться на тех же дисках, что и файлы загрузки и резервирования

Влияние схемы хранилища на SQL Server LUN16 LUN 2LUN 3 Local Drive 1 Log LUN 1 Permanent DB Log LUN 1 Temp DB TempDB.mdf (25GB)TempDB_02.ndf (25GB)TempDB_03ndf (25GB)TempDB_16.ndf (25GB) Permanent FG Permanent_1.ndf Permanant_DB Stage DB Stage FG Stage_1.ndfStage_2.ndfStage_3.ndf Stage_16.ndf Stage DB Log Permanent_2.ndf Permanent_3.ndf Permanent_16.ndf

Секционирование таблиц

Обзор фрагментации Логическая фрагментация Величина, показывающая степень несоответствия порядка размещения физических страниц логическому ключу(по всем файлам) sys.dm_db_index_physical_stats: Logical_Fragmentation 1:32 B-tree Page 1:31 1:36 1:53 1:35 1:34 1:33 1:54 Логический порядок индекса 1:38 1:37 1:60 1:80 1:42 1:41 1:40 1:39 Фрагментация Листовые страницы

Обзор фрагментации Фрагментация экстентов Величина, показывающая на сколько размещение экстентов является упорядоченным (по всем файлам) sys.dm_db_index_physical_stats: Avg_Fragment_Size_in_Pages, Fragment_Count Idx 1 Idx 2 Экстенты внутри файла данных Idx 2 Idx 1

Как оптимизировать сканирование SQL Server выполняет большое количество асинхронных read-ahead запросов выполняя сканирование Пытается выполнить столько операций I/O, чтобы поддерживать CPUзанятым Размер I/O зависит от «продолжительности» фрагмента в файле данных Размер I/O может быть в диапазоне от 8K до 512K Средний размер read-ahead запроса может быть выяснен с помощью avg_fragment_size_in_pages в составе sys.dm_index_physical_stats Значения >= 64 страниц означает, что размер I/Os близок к 512K

Read-Ahead операции в действии Кластерный индекс: упорядоченный ключ 1.Каждый следующий запрошенный диапазон страниц определяется при поиске в B-дереве следующего диапазона ключей 2.Страницы в диапазоне отсортированы 3.I/O запрос выполняется для каждого непрерывающегося диапазона страниц (до 64 страниц в запросе) Heap: порядок размещения Сканируются GAM страницы, чтобы определить следующий диапазон страниц 1.I/O запрос выполняется для каждого непрерывающегося диапазона страниц (до 64 страниц в запросе)

Read-Ahead операции в действии B 1:32 B 1:32 A 1:31 A 1:31 B 1:38 B 1:38 D 1:37 D 1:37 C 1:36 C 1:36 B 1:35 B 1:35 B 1:34 B 1:34 B 1:33 B 1:33 Физический порядок страниц B-tree Page C 1:40 C 1:40 B 1:39 B 1:39 A 1:46 A 1:46 A 1:45 A 1:45 A 1:44 A 1:44 A 1:43 A 1:43 A 1:42 A 1:42 D 1:41 D 1:41 Определение следующего диапазона страниц для запроса, основываясь на упорядоченном ключе (пример: ключи A-B) Группирование в физическом порядке B 1:32 B 1:32 A 1:31 A 1:31 B 1:38 B 1:38 B 1:35 B 1:35 B 1:34 B 1:34 B 1:33 B 1:33 B 1:39 B 1:39 A 1:46 A 1:46 A 1:45 A 1:45 A 1:44 A 1:44 A 1:43 A 1:43 A 1:42 A 1:42 3. Выполнение I/O запросов для каждого непрерывного куска Disk

Приемы для увеличения эффективности сканирования Параметры запуска: -E – экстенты до 2 Мбайт -T1117 – равномерный рост всех файлов в файловой группе Минимизировать использование некластерных индексов на таблице фактов Использовать техники загрузки данных, позволяющих избегать фрагментацию Загрузка в порядке сортировки кластерного индекса (допустим, по дате) если это возможно Создавать индекс всегда с MAXDOP 1, SORT_IN_TEMPDB Изолировать «активные» таблицы в другие файловые группы Изолировать стейджинговые таблицы в отдельные файловые группы или базы Периодические административные операции

«Обычный» тип загрузки приводит к фрагментации Bulk Insert в кластерный индекс со «средним» размером пакета Каждый пакет отсортирован независимо Пересекающиеся пакеты приводят к «расщеплениям» страниц 1:32 1:31 1:35 1:34 1:33 1:36 1:38 1:37 1:40 1:39 1:36 1:38 1:37 1:40 1:39 1:32 1:31 1:35 1:34 1:33 Порядок сортировки по ключу

Альтернативные пути загрузки Использование heap Полезно, если запрос сканирует всю секцию или…использование BATCHSIZE = 0 Допустимо. Если параллелизм при загрузке не требуется или…загрузка в два приема 1.Загрузка в стейджинговую таблицу (heap) 2.INSERT-SELECT из стейджинговой таблицы в целевую с CI В результате таблица не фрагментирована В шаге 1 можно использовать параллелизм, что критично при загрузке больших объемов данных

Двух шаговая загрузка – варианты Вариант A: высокий параллелизм при загрузке архивных данных Обычно в секционированную таблицу Использование временных таблиц (heap), секционированных по тому же принципу, что и целевая таблица Использование множественных потоков при загрузке во временную таблицу с «умеренным» размером пакетов batchsize (SSIS, Bulk Insert, и т.д.) INSERT-SELECT в раздельные секции целевой таблицы (параллелизм) Использование ALTER TABLE SET (LOCK_ESCALATION = AUTO) Внимание: если памяти не хватает, то TempDB будет перегружена операциями сортировки sorting

Двух шаговая загрузка – варианты Вариант B: избегаем нагрузку на TempDB во время загрузки данных Использовать стейджинговые таблицы, которые используют индексы, аналогичные целевой таблице Загрузка в стейджиговые таблицы с «умеренным» размером пакетов: batchsize (< 1M rows) Финальный INSERT-SELECT в целевую таблицу будет сортированным! Однако мы платим журналированием вставки в стейджинговую таблицу Внимание: ограниченный параллелизм при «накладке» диапазонов вставки

Другие рекомендации по избеганию фрагментации НЕ использовать Autogrow для файловых групп Заранее назначать размер файловой группе, исходя из ожиданий использования базы Если нужно, то производить операцию «вручную» добавляя сразу большие «куски» «Активные таблицы» - в отдельную файловую группу Таблицы, которые часто перестраиваются, или куда данные добавляются маленькими порциями Если архивные данные загружаются параллельно, можно подумать о разделении файловых групп для разделения секций, к ним привязанных и избежать фрагментации экстентов

Иногда фрагментации не избежать Если «дозаливки» пересекаются с уже существующими диапазонами данных в кластерном индексе – расщеплений страниц не избежать Периодические административные действия могут помочь уменьшит/избежать фрагментации Секционирование по историческому ключу (date key) может помочь уменьшить объем административных задач

Администрирование Использовать ALTER INDEX … REBUILD … … WITH (MAXDOP = 1, SORT_IN_TEMPDB) Один поток -- избегаем создания фрагментации экстентов Можно ограничиться перестроением «актуальной» секции Избегайте использовать ALTER INDEX … REORGANIZE Страницы будут упорядочены на физическом уровне, однако может отразиться серьезной фрагментацией экстентов

Управление «долгосрочной» фрагментацией Иногда проще «начать с начала» : Создать новую файловую группу, чтобы перенести данные. Удалить старую группу Создать пустую копию таблицы в новой файловой группе С совпадающими ключами секционирования и кластеризации INSERT-SELECT из старой таблицы в новую Создать вторичные индексы Удалить оригинальную таблицу и переименовать новую Все шаги могут выполняться онлайн

Стратегии индексирования Если большинство операций (запросов) характеризуются сканированием диапазонов – нужен ли нам кластерный индекс? Ключ секционирования не обязательно должен быть кластерным Меньше проблем при параллельной заливке данных Какая нагрузка возлагается на некластерные индексы? Возможные блокировки при заливке данных Неактуальная статистика может привести к неэффективным CI или RID Lookup Необходим «облегченный! Вариант индексирования

Секционирование для доступности to INSERT / UPDATE MSCFactCDR (View) MSCFactCDR (View) SELECT... FROM MSCFactCDR ALTER VIEW + SWITCH ALTER VIEW + SWITCH Исторические и актуальные данные находятся в разных таблицах, в разных файловых группах

Пример 1: Страховая компания-- массивная загрузка за ограниченное время Задача: Загрузить и дополнить данные объемом в 50 GB за менее, чем 1 час Выполнимо только при высоком параллелизме загрузки Используется секционирование таблицы Секционирование по ключу customer Кластерный индекс по дате! # секций = # ядер Параллельная загрузка во временные таблицы Разделение файловых групп (группа – секция) не допускают пересечения загрузок

Архитектура Primary Storage 8 Drives (4 RAID1 Pairs) Logs 2 Drives (1 RAID1 Pair) Spares 2 Drives MSA2000 DAE

Результат Существующее решениеSQL Server Fast Track DW Сравнение Loading – Subject Area 1 5:10:21 total time51:31 total time SQL Server 6x faster Loading – Subject Area 2 4:36:08 total time1:50.01 total time SQL Server 2.5x faster Время запроса– Subject Area 1 3:03 avg query time (using 9 benchmark queries) 0:15 avg query time (using 9 benchmark queries) SQL Server 12x faster Время запроса – Subject Area 2 56:44 avg query time (using 4 benchmark queries) 8:09 avg query time (using 4 benchmark queries) SQL Server 7x faster Цена за TB (8TB) – Cal : $22K / TB Цена за TB (16TB) – Cal: $13K / TB

Пример 2: Телеком– изначальная загрузка данных Загрузка 400 GB в «новый» кластерный индекс на 8-ядерном сервере в течении 7часов Целевая таблица- 8 секций поделенных по историческим диапазонам 3-шаговая загрузка, использующая секционирование Load, Index, Switch Все шаги используют параллелизм Минимальное журналирование

Европейский Телеком Описание Реляционная часть разработана на : HP DL785 G6 с 8 x 6 ядрами AMD 196GB RAM EMC SAN с 12 x EMC AX4, где каждый 20 x 450 GB дисков. Общая ёмкость примерно 38 TB без сжатия и 76 TB при консервативной оценке сжатия в 50% Windows Server 2008 R2 Enterprise Edition SQL Server 2008 R2 Enterprise Edition

Производительность Оценка производительности была произведена с помощью: SQLIO SQL Server обрабатывал актуальные данные Результаты: SQLIO показал общую пропускную способность системы в 9,6GB/sec, что является теоретическим максимумом. SQL Server производил сканирование таблиц со скоростью в 8,8 до 9.0GB/sec. SQLIO показал комбинированную скорость записи в 4,7 до 5.1 GB/sec SQL Server произвел запись 1 TB за менее, чем 20 минут при использовании параллельных пакетов SSIS. SQL Server показал скорость создания резервной копии в более, чем 3 GB/sec.

Почти FastTrack Многие клиенты следуют рекомендациям FastTrack без точного следования описанной архитектуре: НЕ использовать разделяемое хранилище данных Инвестировать в большее количество «полок» и HBA для обеспечения соответствующей пропускной способности Повысить эффективность операций сканирования используя техники загрузки данных

Fast Track «подобная» система Table Scan Current Disk Queue Length = ~ 670 (достаточное время отклика, учитывая объем и глубину outstanding I/O) Disk Read Bytes / sec = ~ 4 GB/s Read-ahead pages/sec is почти на том же уровне, что и pages/sec. Avg.Disk Bytes/Read = ~ 500 KB Storage – MSA60 –5 x HP SAS P800 controllers with 512MB cache. –Каждый конроллер подключен MSA60 «полке» LUN Configuration –24 Data LUNs, One RAID1 Pair per LUN –1 Log LUN –50 spindles total

А если нагрузка включает много Random IO? Принципы FastTrack позволят получить приемлемую скорость для операций сканирования. Особенно учитывая количество контроллеров и HBAs Однако Возможно придется дополнительно инвестировать в большее количество дисков для обеспечения поддержки высокого уровня random IO в секунду 100+ дисков – не редкость

Рекомендация Изучите «FastTrack Methodology and Reference Architectures for Data Warehouse» fasttrack.aspx fasttrack.aspx Дополнительные ресурсы: Data Loading Performance Guide SQLCAT Top 10 DW Best Practices

Questions?