Выпуск 20

Sybase IQ – первые впечатления

Выпуск 20

Зачем нужны хранилища данных

Хранилища данных (ХД) достаточно дорогостоящее удовольствие и редко используются в качестве тиражных продуктов (хотя  у каждого вендора ХД и есть свои модели под определенные отраслевые задачи, но они не тянут на статус готового решения). Зачем же они нужны? Рассмотрим на примере:

Есть некое предприятие, занимающееся производством и/или продажей определенной продукции или услуг. Работа этого предприятия автоматизирована одни или несколькими системами автоматизированного управления предприятием. Все работает под управлением OLTP серверов, где данные поступают в базу, рассчитываются по бизнес правилам предприятия, ведется учет и аналитика по формам отчетности предприятия. Пока имеются формы известной отчетности и каждая система отвечает за свои данные, все исправно работает и проблем нет. Однако предприятие решает повысить эффективность своей работы и проводить аналитику по всей своей информации, хранящейся на OLTP серверах, за все года накопленной информации, где аналитики могли бы, используя такую информацию, моделировать различные ситуации и видя картину в целом «Что было», прогнозировать «Что будет» и искать более эффективные методы работы предприятия. Для того, чтобы осуществить задуманное, предприятию пришлось бы:

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

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

3.      Эффективный анализ не возможен без аналитического ПО, позволяющего его проводить. Здесь предприятию, пришлось бы выбирать между простыми недорогими решениями, такими как MS Excel, где аналитикам нужно было писать запросы к БД на SQL, а так же дорогими, имеющими расширенные возможности не только по проведению аналитики, но и по переводу схемы модели базы ХД в доступные бизнес термины, которыми наиболее легко оперировать при анализе информации.

Резюмируя вышесказанные пункты, можно сказать, что предприятие будет выбирать дешевое или дорогое решение из 2 вариантов со следующими схемами:

1. OLTP и другие источники данных=>Процедуры преобразования и закачки=>ХД=>Excel
2. OLTP и другие источники данных =>ETL=>ХД=>BI

При любом выбранном варианте, ХД является ключевой фигурой – ведь именно оно получает данные, хранит их и выполняет по ним запросы. Какие же основные требования ожидаются от хранилища данных? Вот основные пункты:

1. Доступность в режиме 24x7 с допустимыми короткими остановками не более 10-15 минут.для профилактических работ (например обновление версии ОС или самого сервера ХД).
2. Эффективные механизмы быстрой заливка данных – никто не будет ждать неделю, пока зальются данные за один день.
3. Доступность данных конечным пользователям при их изменениях во время заливки (то есть отсутствие блокировок) – аналитики должны иметь постоянный доступ к информации, даже если она обновляется.
4. Надежные механизмы хранения, распределения и архивирования информации – информации будет много и ее объем будет постоянно расти, поэтому добавление нового диска в дисковый массив, распределение на него части информации с существующих таблиц, архивирование базы без блокировки пользователей и замедления работы ХД являются обязательными требованиями.
5. Эффективное выполнение ad-hoc запросов – в отличие от отчетности, все аналитические отчеты используют произвольные запросы, где заранее невозможно предугадать, какие столбцы каких таблиц и в каких условиях и агрегатных операциях будут участвовать. С учетом того, что аналитические отчеты используются для сравнения и анализа, то время ожидания выполнения таких запросов должно быть адекватным и в идеале не превышать 3 минут.

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

Sybase – свой взгляд на ХД

 Если рассмотреть рынок существующих серверов для построения хранилищ данных, то сразу видно, что здесь присутствуют как и известные вендоры, имеющие OLTP решения (Microsoft, Oracle, IBM, Sybase, …) , так и специализированные (Teradata, SAS, …). Сами решения ХД тоже делятся по направлениям:

1. Классическая модель (РСУБД) описание и хранения данных – фактически это тот же OLTP сервер, но с расширенной функциональностью хранения и обработки больших объемов данных, где данные хранятся в таблицах по полям и записям и доступ к ним осуществляется через SQL с расширенной поддержкой OLAP. Среди основных достоинств такой модели является совместимость с OLTP серверами и приложениями, использующих SQL, быстрая заливка данных, достаточно компактное хранение данных, среди недостатков большие накладные расходы по выполнению ad-hoc запросов.
2. OLAP модель – ХД изначально хранит информацию как вычисляемые агрегатные срезы информации, где для доступа к информации используется свой язык MDX. В качестве основных достоинств таких систем можно назвать минимальные накладные расходы по выполнению ad-hoc запросов, среди недостатков – долгая заливка, где вся информация должна быть агрегирована и посчитана по измерениям, а так же невозможность многоуровневой детализации информации из-за предрасчитанных агрегатов и значительно занимаемого ими места.

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

Компания Sybase, как крупнейший вендор-производитель, естественно не осталась в стороне и имеет собственный специализированный сервер для организации ХД, с коротким, но многозначительнрым названием “IQ”.

Если посмотреть истории продаж и внедрений Sybase IQ в мире, то можно смело сказать, что этот продукт является достаточно популярным, имеет большое кол-во инсталляций и является одним из лидеров серверов ХД, обслуживающих самые большие хранилища данных крупных компаний, объем которых исчисляется терабайтами. Как и положено настоящему лидеру, Sybase IQ имеет свои «ноу-хау»:

1. Не смотря на то, что внешне IQ выглядит как обычный РСУБД с таблицами, полями, поддержкой языка запросов SQL, имеющий уровень изоляции снапшот и т.д., сервер имеет свой способ хранения данных и хранит их не как обычные OLTP сервера, то есть записи со всеми колонками на странице, а колонки по записям на странице. Очень часто его формат хранения называют поколоночным, но это утверждение верно отчасти, так как IQ не просто хранит на страницах значения колонки по записям, но еще и в виде индекса. Такой формат хранения информации имеет массу существенных для ХД преимуществ – при выполнении ad-hoc запросов читается информация только по тем колонкам, которые участвуют в запросе. Раз данные на странице хранятся только по одной колонке, значит колонки можно быстрее читать и писать, тратится меньшее время на конвертацию типов, их можно эффективно сжимать для хранения, по ним можно значительно быстрее строить и перестраивать индексы, эффективнее хранить в кэше только информацию по затребованным столбцам, чем по страницам таблицы, где могут быть еще много не нужных столбцов, так же получается меньше затрат на поддержку уровня изоляции снапшот для читающих сессий (опять же для сессии будут по версиям создаваться только страницы со столбцами, использующихся в запросе). Так же значительным достоинством такой схемы хранения данных является то, что она позволяет хранить денормализованные данные, где из множества таблиц серверов источников, данные записываются в одну широкую таблицу, что убирает множество соединений в запросах, а значит ускоряет их выполнение и упрощает их написание. Недостаток такой схемы очевиден – это медленная скорость модификации данных обычными SQL запросами по единичным записям, однако с учетом того, что IQ и не планировался работать в качестве OLTP сервера, то такой недостаток можно смело назвать не критичным.
2. Как известно, чем больший по размеру объем информации, тем сложнее его контролировать и хранить. Сервер IQ использует простой и эффективный способ контроля и хранения информации – он просто хранит в базе всю информацию в сжатом виде на физическом уровне, вполне справедливо используя утверждение, что «Процессор всегда быстрее диска», где по времени будет быстрее считать и распаковать сжатую информацию в память, чем читать ее в несжатом виде с носителя. В результате сжатого способа хранения данных и индексов, IQ в том числе позволяет существенно экономить клиентам на дисковых носителях, где размер базы IQ в итоге вместе с данными и индексами в десятки раз меньше объема исходных данных без индексов.
3. Для эффективного выполнения произвольных запросов, в IQ существует множество различных индексов, специализирующихся на различных условиях использования столбцов, где можно к примеру указать быстрые и эффективные бит-мап индексы для столбцов, имеющих ограниченный круг значений, индексы для столбцов, использующихся в условиях отбора записей и сравнения друг с другом. Индексы для столбцов, по которым группируется информация, а так же индесы для столбцов, по которым агрегируется информация, есть отдельные индексы для работы со столбцами имеющими тип Дата-Время, позволяющими эффективно обрабатывать запросы, в которых используются функции выделения части дат и времени, так же есть индексы для оптимизации соединения в запросе таблиц с большим кол-вом записей и даже индекс для полнотекстового поиска по блоб полям. Для эффективного создания индексов на одну таблицу с данными по множеству столбцов, IQ имеет специальный SQL оператор  распараллеливания создания индексов, где в блоке BEGIN PARALLEL IQ … CREATE INDEX … END PARALLEL IQ указываются создания всех индексов и при выполнении этого скрипта, IQ одновременно создает все указанные индексы, снижая требования к ресурсам и повышая скорость построения индексов во время их создания. При всех операциях на индексах, сессии, выполняющие запросы по таблицам, на которых создаются индексы, не блокируются и продолжают работать.
4. Интеллектуальный оптимизатор IQ не требует настроек и хинтов и имеет множество эффективных алгоритмов для выполнения самых сложных запросов, где в числе его достоинств можно назвать распараллеливание выполнение запроса между процессорами сервера и способность использовать одновременно множество индексов на одну таблицу при выполнении запроса. Все это, вкупе с сжатым способом хранения информации в базе данных, значительно облегчает оптимизацию выполнения сложных запросов – нет необходимости создавать составные индексы и оптимизировать выполнение запроса хинтами или опциями, достаточно просто на каждый столбец таблицы создать подходящие индексы, дальше оптимизатор все сделает сам.
5. IQ имеет несколько механизмов быстрой заливки данных с источников: через текстовые файлы с разделителями (CSV), через временные таблицы IQ, где источники выгружают данные во временные таблицы и далее через DML запросы данные этих временных таблиц распределяются по таблицам IQ (как пример, логику заливки можно сделать через хранимые процедуры IQ или же с внешнего приложения через вызовы SQL), плюс IQ может сам подключаться к внешним серверам и обращаясь к их таблицам через SQL, импортировать данные в свои таблицы. IQ ставит ряд ограничений для пишущих в таблицы сессии – не разрешается более одной сессии пытаться изменить данные таблицы. Это ограничение было бы критическим для любого OLTP сервера, но не является таковым для ХД, где данные заливаются порциями и далее по ним идет работа только на чтение для выполнения аналитических запросов. Во время изменения данных в таблицах, прочие сессии продолжают работать в обычном режиме и выполнять любые запросы. Для того, чтобы они могли увидеть, что в таблицах произошли изменения, им необходимо сделать COMMIT, то есть закрыть текущую транзакцию перед выполнением очередных запросов.
6. Для хранения больших объемов данных  и использования пространства для внутренних вычислений, в IQ реализована стратегия распределения информации по пространствам (DB Space), где администратор может в любой момент к работающему серверу присоединить новое пространство следующих типов: Основной (для хранения таблиц и индексов БД), Временный (для хранения временных таблиц, для хранения промежуточных результатов выполнений запросов и версий страниц работающих сессий)  и Локальный (для локального хранения собственных данных в рамках текущего сервера, что актуально для мультиплексной организации работы IQ, о которой будет рассказано ниже). Пространства могут являться файлами в файловой системе или же быть логическими дисками в указанных партициях, где IQ уже сам форматирует указанный диск партиции и ведет напрямую с ним работу, минуя логическую структуру файловой системы ОС, что повышает скорость работы с пространством. Распределением объектов между пространствами IQ ведет автоматически, если например, в таблице хранится 100 миллиардов записей и свободное место в основном пространстве подходит к концу, то будет достаточно добавить еще одно основное пространство в сервер без его остановки и новые добавляемые данные, принадлежащие таблице и ее индексам, автоматически начнут заполняться на свободное пространство. То же самое относится и к остальным видам пространств – достаточно просто серверу указать создать новое временное или локальное пространство, чтобы он тут же его начал использовать для хранения информации. Такой подход к хранению информации обеспечивает минимальные требования к администрированию сервера, где операция добавления нового пространства может быть произведена в визарде утилиты администрирования IQ и процедура добавления нового пространства к базе данных не требует перезапуска сервера или перераспределения существующей информации.
7. В случаях, когда размер базы данных достигает больших объемов и с ней работает большое кол-во пользователей, IQ предлагает распределенную архитектуру обработки информации по запросам под названием мультиплекс. Суть ее состоит в том, что на предприятие ставится ряд IQ серверов, где один из них назначается Писателем и становится ответственным за изменение модели данных базы данных и загрузку информации в нее. Прочие сервера назначаются Читателями и могут только выполнять запросы по базе данных, не имея возможности изменять информацию. Каждый их серверов должен видеть все файлы и партиции пространств базы данных и фактически каждый из них работает самостоятельно, имея свои временные и локальные пространства, подключения и т.д. Пишущий сервер, при проведении изменений, автоматически информирует сервера  Читателей о происходящих изменениях в метаструктуре БД и данных. Таким образом, технология мультиплекс позволяет без каких либо затрат, организовать распределенную отказоустойчивую вычислительную сеть по выполнению запросов к ХД, где в любой момент работы без остановки или перестройки ХД, можно добавить новый сервер Читатель или же перераспределить выполнение запросов из за остановки сервера на другие сервера IQ. Так же сервера  Читатели будут работать при остановке сервера  Писателя, что позволяет говорить о полноценной работе в режиме 24x7, без каких либо видимых остановок ХД в работе для клиентов.
8. Как и полагается культурному хранилищу данных,  IQ имеет поддержку ANSI SQL 92/99/2003, OLAP расширения для SQL, два языка хранимых процедур WatcomSQL (совместимый с Sybase ASA) и TSQL (совместимый с Sybase ASE и MSSQL), поддержку всех стандартных интерфейсов доступа к информации (ODBC, JDBC, OLEDB, ADO,NET, …) и развитые консольные и графические утилиты для администрирования и работы с сервером для разработчиков и администраторов. Все это позволяет провести полный цикл построения модели ХД и работы с ней, как используя сторонние средства (такие как ETL), так и используя собственный код загрузки данных на стороне IQ.
9. Сервер ХД не должен ограничивать своих клиентов в выборе той аппаратной платформы, которая им наиболее подходит, поэтому Sybase IQ поддерживает все популярные серверные ОС, такие как Windows, Linux, IBM, Solaris и т.д.

Sybase IQ – тесты скорости

 Для демонстрации скорости работы с Sybase IQ я взял таблицу с 29 полями, на которые были сделаны 18 индексов, для которой был подготовлен CSV файл с 13,5 миллионов записей со всеми заполненными полями, размером 2,2 гигабайта. Сервер использовался самый простой – обычный 4 ядерный Intel с установленной памятью 4 гб, ОС Windows 2003.

Загрузка данных в пустую таблицу с автоматическим перестроением всех индексов с CSV файла в таблицу IQ заняло 101 сек Объем занимаемой вместе с индексами информацией в базе составил 664 мб, то говорит о том, что сервер сжал исходные данные в 3 раза.

Копирование данных с таблицу в другую IQ таблицу с аналогичной структурой и индексами через INSERT INTO SELECT, заняло 466 секунд.

Добавление к таблице NOT NULL столбца типа NUMERIC(20, 0) с установленным. значением 0 заняло всего 0,187 сек. Его же удаление заняло еще меньше – 0,094 секунд.

Создание одновременно трех HighGroup индексов (которые эффективны для столбцов, использующихся в DISTINCT и GROUP BY) на столбцы NOT NULL с типом UNSIGNED BIGINT в параллельном потоке заняло 6,45 секунд.

Загрузка данных из одной колонки в другую колонку с типами UNSIGNED BIGINT в пределах одной таблицы через UPDATE SET заняло 6,61 секунд.

Выгрузка таблицы в CSV файл заняла 97 секунд.

Удаление всех записей таблицы через DELETE FROM заняло 0,015 секунд.

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

При проведении тестов работы IQ на других моделях ХД, я обратил внимание на то, что наиболее эффективно выполнялись запросы IQ там, где модель была денормализована и кол-во JOIN в запросах было минимально, а в тех хранилищах, которые повторяли нормализованную структуру источников из РСУБД, времени на выполнение запросов тратилось больше.

С точки зрения работы с IQ стороннего ПО, ETL и BI различных компаний, я не увидел особых сложностей настройки их работы с IQ, что и не удивительно, так как для них он выглядит как обычный РСУБД, к которому можно подключаться по стандартным протоколам и работать через стандартный SQL. С точки же зрения производительности работы с IQ, у каждого производителя ETL и BI своя концепция работы с данными, поэтому и  скорость работы у каждого производителя с IQ будет своя. Оптимальнее и быстрей всего будут работать те программные продукты, которые хорошо знакомы с возможностями IQ и могут использовать их в полном объеме. Те же программные продукты, что привыкли полагаться на себя, то есть проводить всю работу с данными на своей стороне, вряд ли дадут IQ показать себя со всей эффективностью, так как он тогда будет в данном случае использоваться как простейший сервер хранилища данных с несложными по форме запросами, требующие большие кол-ва данных.

Резюмируя все написанное

 Когда для сервера не вопрос удалить 950 миллионов записей из таблицы с 200 миллиардами, по которым одновременно в этот момент выполняются сложные агрегатные и OLAP запросы пользователей, для любого OLTP сервера это выглядит полной фантастикой. Самое, что обожают делать специалисты Sybase на конференциях, презентациях или у клиентов в пилотных проектах – это на обычном ноутбуке с установленным IQ, прямо у всех на глазах создать новую чистую базу данных и прогрузив в нее пару десятков миллионов записей за несколько минут, тут же для всех желающих дать возможность выполнить запросы к данным любой сложности. Сомнения и вопросы по производительности после этого отпадают  сами собой, остаются прочие вопросы – как получить помощь в организации своего хранилища данных и во сколько выйдет общая стоимость владения IQ. Ответы на эти вопросы от специалистов Sybase тоже не разочаровывают заинтересованных руководителей и специалистов.

Думаю IQ достоин своего названия (IQ-интеллект) и стоит того, чтобы на него посмотреть поближе как на достойный сервер хранилищ данных.