SQL Server 2012: Более качественная бизнес-аналитика

Рекомендованные архитектуры предоставляют проверенную инфраструктуру настройки SQL Server для решения задач бизнес-аналитики.

Стивен Стронг

Низкопроизводительные запросы — бич всех OLTP-систем. Можно попытаться решать проблему путем улучшения оборудования, но лучше всего оптимизировать такие запросы.

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

Но такой подход редко работает в хранилищах данных, особенно при росте размера и уровня параллелизма в базе данных. Форумы полны вопросов администраторов БД, которым нужна помощь в повышении производительности сложных запросов в хранилищах данных.

В типичной системе бизнес-аналитики (BI) есть много стандартных регулярно выполняемых отчетов, но есть нерегламентированные запросы, поступающие и разных других средств. Такие запросы практически невозможно прогнозировать и ими сложно управлять. Пользователи также регулярно модифицируют свои запросы, часто выполняя это с помощью BI-средств, в попытке уточнить и получить более полезные бизнес-данные. Есть такое ощущение, что любые попытки оптимизировать эти запросы дело заведомо безнадежное.

От безнадежности менеджеры ИТ часто стараются погасить подобный пожар, направляя в проблемные места больше оборудования. Обычно приобретается новый максимально производительный в рамках имеющегося бюджета сервер, к которому подключается самое крупное хранилище данных. Если вы хотите потерять доверие своих пользователей, можете двигаться в этом направлении. Подобный подход редко позволяет решить проблему, не говоря уже о том, что он самый дорогой, — не забывайте о расходах на приобретение лицензий.

Microsoft объединила усилия с рядом уважаемых поставщиков оборудования с тем, чтобы повернуть ситуацию с ног на голову. Компании спрашивают: «Что произойдет, если принять тот факт, что пользователи часто просматривают крупные таблицы фактов и выполняют сложные запросы группировки? Что произойдет, если каждый компонент решения сможет работать на своей максимальной скорости и его работа не будет задерживаться другими компонентами? Каков будет мир в этом случае?»

Выбираем Fast Track

Нужно воспользоваться Fast Track Data Warehouse (Fast Track DW) для SQL Server. С 2005 года Microsoft тратит значительные средства на оптимизацию SQL Server для решения задач BI. А в 2009 году Microsoft выпустила первую версию рекомендованной архитектуры Fast Track для хранилищ данных. Сейчас выходят новые рекомендованные архитектуры, в которых учитываются особенности функциональности SQL Server 2012.

Fast Track DW это не продукт, а набор продуманных и протестированных рекомендованных конфигураций ПО и оборудования, оптимизированных для решения задач хранилищ данных. Нет специального оборудования или магического программного обеспечения. Все строится на общедоступных компонентах, таких как Windows Server и SQL Server.

Скорее всего многие из этих компонентов уже успешно используются в вашей среде. Не хватает одного — перехода от оперативного способа мышления к предотвращению чтения больших объемов данных. Fast Track DW активно поощряет такой переход настолько, что фактически только от него зависит высочайшая производительность этой архитектуры.

Если вы когда либо выполняли тестирование оборудования, то наверняка обнаружили, что большинство систем предпочитают последовательное чтение. При анализе работы BI оказывается, что от 80 до 90% работы состоит из последовательного чтения. Что произойдет, если сконструировать систему в расчете только на последовательное чтение? Что будет, если все компоненты — процессоры, память, шину PCI, адаптеры главной шины (HBA), сеть, подсистему хранения, SQL Server и файлы базы данных — также спроектировать в расчете на это?

Возможны проблемы. Что, если у вас медленные диски, недостаточно памяти, главная шина не поспевает за дисками или шина PCI перегружена? Но хуже всего, когда после устранения одного узкого места — добавления дополнительной памяти или замены медленных дисков на быстрые — узким местом становится другой компонент.

Типичный сценарий выглядит следующим образом: администратор БД просит ответственную за инфраструктуру команду установить побольше памяти на SQL Server. Расширение памяти решает исходную проблему, но производительность повышается только на 3%. После обновления памяти узким местом становится дисковая подсистема. Когда в следующий раз ИТ-отдел будет просить о дополнительном бюджете, к их требованиями отнесутся существенно прохладнее.

Для решения этой задачи специалисты по оборудованию и ПО разработали ряд сбалансированных систем, которые могут обслуживать хранилища данных различного размера. Система начального уровня рассчитана на пять терабайт, а более мощные системы способны обслуживать базы данных объемом до 100 ТБ. Для хранилищ данных, рассчитанных на сотни терабайт, Microsoft и партнеры-поставщики оборудования собрали «коробочный» продукт, который называется Parallel Data Warehouse.

Большинство команд, ответственных за инфраструктуру, могут построить подобные сбалансированные системы, но у них редко бывает время тщательно исследовать и подгонять компоненты для обеспечения максимальной пропускной способности. Если у вас, к примеру, 20-терабайтное хранилище на дисках емкостью 600 ГБ, вы должны определить, сколько главных шин и сколько портов коммутатора нужно, чтобы обеспечить 100-процентную загрузку восьмиядерных процессоров. Также нужно подумать о таких нюансах, как количестве процессоров хранилища, которые стабильно обеспечат пропускную способность в 6 ГБ в секунду.

Многие инфраструктурные команды учитывают эти нюансы в своих планах, но подобные сбалансированные системы редко добираются до производственной среды. Рекомендуемые архитектуры избавляют от необходимости гадать.

Проблемы с реализацией инфраструктуры очень часто становятся причиной задержек проекта. Fast Track DW призван свести к минимуму время реализации. В каждой рекомендованной инфраструктуре приводится список комплектующих. Его можно быстро превратить в спецификацию для отправки вашему поставщику оборудования.

Так как в подготовке рекомендуемых архитектур участвовали поставщики оборудования, им проще выполнять такие заказы. Больше не придется несколько раз уточнять и согласовывать с поставщиком список нужных компонентов. Больше не нужно дискутировать, что выбрать: процессор с тактовой частотой 2,4 или 2,5 ГГц, Fibre Channel или iSCSI — все это указано в спецификации.

При получении заказанного оборудования членам инфраструктурной команды не нужно решать, как лучше реализовать решение — все подробности описаны в рекомендованной архитектуре. Они включают расположение физических дисков, проводку, версии ПО и драйверов, микропрограммы, конфигурацию подсистемы хранения, длину очереди HBA, конфигурацию SQL Server и даже размещение файлов базы данных.

Работа Fast Track DW

Fast Track DW работает по принципу максимальной тактовой частоты ядра (MCR). Он определяет максимальное число мегабайт в секунду, которое может обработать ядро процессора. Современные многоядерные процессоры способны потреблять 300400 МБ данных в секунду на каждое ядро. Например, на сервере с двумя разъемами под процессор и восемью ядрами на каждом процессоре это означает примерно 6 ГБ в секунду. Для обеспечения такого объема потребуются двухпортовые адаптеры главной шины с максимальной обеспечиваемой пропускной способностью 6,4 ГБ в секунду. Каждые массив хранения содержит четыре набора физических дисков с конфигурациями RAID 10, причем каждый набор выдает «на гора» 1,6 ГБ в секунду, весь массив хранения — 6,4 ГБ в секунду.

В рекомендованных архитектурах Fast Track обычно для размещения сети хранения на выделенном коммутаторе предусмотрены сети 10 Гбит iSCSI или 8 Гбит Fibre Channel. В отличие от типичной среды SAN, где подсистема хранения совместно используется несколькими рабочими нагрузками, такими как файловые серверы, серверы баз данных и хосты виртуальных машин, все хранилище выделено исключительно для сервера Fast Track.

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

Построение и конфигурирование собственного высокопроизводительного решения хранилища данных на самом современном оборудовании представляет собой непростую задачу. Чтобы повторить известную сборку, членам инфраструктурных команд приходится тратить часы на штудирование руководств по установке, записей в блоге и форумов — только так можно построить все необходимые сложные сценарии.

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

Усовершенствованное индексирование

В SQL Server 2012 появился новый тип индексов, которые называются ColumnStore. Индексы ColumnStore призваны повысить производительность и соотношение цена/производительность. В запросе, не использующем ColumnStore, строки обрабатываются одна за одной. При наличии ColumnStore сервер SQL Server обрабатывает строки пакетами. Данные нескольких строк в столбце не только хранятся в одной странице данных, но их можно обрабатывать «пачками». Кроме того, данные сильно сжаты — примерно в отношении 7:1.

ColumnStore обеспечивает намного более высокую пропускную способность, потому что на обработку запросов требуется меньше процессорных мощностей. При обработке требуется меньше операций ввода/вывода и памяти, что также отлично подходит для архитектур Fast Track DW. В целом индексы ColumnStore обеспечивают повышение производительности в 10—100 раз по сравнению с обычными «строковыми» индексами.

Но нужно помнить, что не во всех запросах можно применять индексы ColumnStore. Последние тесты производительности показали, что в смешанных задачах совокупно производительность повышается примерно в два раза. Но в любом случае 100-процентное повышение производительности при приложении сравнительно небольших усилий стоит того. Но должна же быть ложка дегтя? Индексы не подлежат обновлению ColumnStore, но большинство приложений хранилищ данных справляются с этим ограничением в процесс операций извлечения, преобразования и обработки.

В новых рекомендуемых архитектурах также появилась поддержка высокой доступности Fast Track DW. Это очень хорошие новости, потому что бизнес-аналитика становится все важнее для бизнеса. Доступные сейчас рекомендуемые архитектуры используют технологию отказоустойчивых кластеров Windows Server, которая уже более десяти лет используется в OLTP-системах.

Одно из ключевых преимуществ рекомендуемых архитектур Fast Track является то, что в них используется стандартное ПО, такое как Windows Server и SQL Server. Это удобно для системных администраторов, администраторов БД и сотрудников отдела поддержки. Хотя разработчикам нужно будет смириться с ограничениями индексов, обычный код на T-SQL в Fast Track работает, как обычно. Так как Fast Track представляет собой рекомендуемую архитектуру, а не коробочный продукт, управление исправлениями также выполняется прямолинейно. Сервер надо просто включить в стандартную процедуру управления исправлениями.

Интеграция Fast Track

Fast Track строится на основе SQL Server 2012, поэтому хорошо интегрируется с большинством стандартных архитектур BI. Системы-источники могут поставлять данные в Fast Track DW через выделенный сервер интеграции на базе SQL Server или оперативное хранилище данных. А предоставлять доступ к данным можно через витрины данных подразделений, построенные на базе SQL Server Analysis Services или предоставить BI-средствам прямой доступ к Fast Track.

Все чаще в распоряжение пользователей предоставляются порталы данных с панелями управления, PowerPivot или PowerView с SharePoint. SQL Server Reporting Services позволяет создавать структурированные отчеты через кубы аналитических служб. Пользователи могут создавать нерегламентированные отчеты в Report Builder или использовать для этого PowerPivot for Excel. Все эти возможности и усовершенствованные функции показывают, как такая масштабируемая платформа, как Fast Track DW для SQL Server 2012, может стать ключевым компонентом вашей стратегии бизнес-аналитики.

Стивен Стронг (Stephen Strong) — более 25 лет занимается базами данных — от создания архитектур приложений и обучения администраторов БД, до проектирования инфраструктуры и архитектуры. Последние девять лет, работая в связке с Microsoft Services, занимался архитектурным проектированием и поддержкой одной из самых крупных и сложных реализаций SQL Server в Австралии.