1 / 50

FastTrack Data Warehouse

FastTrack Data Warehouse. Особая благодарность Алексею Халяко из SQLCAT. Иван Косяков Technology Architect, MTC Moscow i-ivanko@microsoft.com. Что такое FastTrack Data Warehouse?.

nizana
Download Presentation

FastTrack Data Warehouse

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

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

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

  6. Сбалансированная архитектура

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

  8. Характеристики нагрузок хранилищ данных • Интенсивные сканирования • Hash Joins • Агрегации SELECT L_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 FROM LINEITEM GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG, L_LINESTATUS

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

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

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

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

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

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

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

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

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

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

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

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

  21. Оптимизация схемы хранилища для интенсивных сканирований RAID GP05 RAID GP04 RAID GP03 RAID GP02 RAID GP01 03 04 05 06 07 08 09 10 • Конфигурация LUN’ов базируется на RAID10 • Предоставляет оптимальный уровень доступа к дискам • «Размазывание» данных по дискам происходит на уровне файлов SQL Server (разбиение на файлы в файловой группе) • Наблюдаемая производительность одной RAID пары >= 130 MB/s LUN6 LUN3 LUN0 (Logs) LUN8 LUN5 LUN4 LUN7 SP A 01 02 LUN1 HS LUN2 SP B

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

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

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

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

  26. Как оптимизировать сканирование • 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/O’s близок к 512K

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

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

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

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

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

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

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

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

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

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

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

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

  39. Секционирование для доступности INSERT / UPDATE FactMSC_Online MSCFactCDR (View) 2010-08 • Исторические и актуальные данные находятся в разных таблицах, в разных файловых группах FactMSC_History 2010-01 to 2010-07 ALTER VIEW + SWITCH 2009 2008 SELECT ... FROM MSCFactCDR 2007

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

  41. Архитектура Pri_A Pri_B Pri_C Pri_D Log Hot Spare Hot Spare MSA2000 DAE Primary Storage 8 Drives (4 RAID1 Pairs) Logs 2 Drives (1 RAID1 Pair) Spares 2 Drives

  42. Результат Цена за TB (8TB) – Cal : $22K / TB Цена за TB (16TB) – Cal: $13K / TB

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

  44. Европейский Телеком Описание • Реляционная часть разработана на : • 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

  45. Производительность Оценка производительности была произведена с помощью: • 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.

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

  47. Fast Track «подобная» системаTable Scan • 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 Disk Read Bytes / sec = ~ 4 GB/s Current Disk Queue Length = ~ 670 (достаточное время отклика, учитывая объем и глубину outstanding I/O) Read-ahead pages/sec is почти на том же уровне, что и pages/sec. Avg.Disk Bytes/Read = ~ 500 KB

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

  49. Рекомендация • Изучите «FastTrack Methodology and Reference Architectures for Data Warehouse»http://www.microsoft.com/sqlserver/2008/en/us/fasttrack.aspx Дополнительные ресурсы: • Data Loading Performance Guide http://msdn.microsoft.com/en-us/library/dd425070.aspx • SQLCAT Top 10 DW Best Practices http://go.microsoft.com/fwlink/?LinkId=141862

  50. Questions?

More Related