Середина 2000-х годов ознаменовалась бурным ростом числа колоночных СУБД. Vertica, ParAccel, Kognito, Infobright, SAND и другие пополнили клуб колоночных СУБД и разбавили гордое одиночество Sybase IQ, основавшей его в 90х годах. В этой статье я расскажу о причинах популярности идеи по-колоночного хранения данных, принцип действия и область использования колоночных СУБД.
Начнем с того, что популярные в наше время реляционные СУБД — Oracle, SQL Server, MySQL, DB2, Postgre и др. базируются на архитектуре, отсчитывающей свою историю еще c 1970-х годов, когда радиоприемники были транзисторными, бакенбарды длинными, брюки расклешенными, а в мире СУБД преобладали иерархические и сетевые системы управления данными. Главная задача баз данных тогда заключалась в том, чтобы поддержать начавшийся в 1960-х годах массовый переход от бумажного учета хозяйственной деятельности к компьютерному. Огромное количество информации из бумажных документов переносилось в БД учетных систем, которые должны были надежно хранить все входящие сведения и, при необходимости, быстро находить их. Такие требования обусловили архитектурные особенности реляционных СУБД, оставшиеся до настоящего времени практически неизменными: построчное хранение данных, индексирование записей и журналирование операций.
Под построчным хранением данных обычно понимается физическое хранение всей строки таблицы в виде одной записи, в которой поля идут последовательно одно за другим, а за последним полем записи в общем случае идет первое следующей записи. Приблизительно так:
[A1, B1, C1], [A2, B2, C2], [A3, B3, C3]…
где A, B и С — это поля (столбцы), а 1,2 и 3 — номер записи (строки).
Такое хранение чрезвычайно удобно для частых операций добавления новых строк в базу данных, хранящуюся как правило на жестком диске – ведь в этом случае новая запись может быть добавлена целиком всего за один проход головки накопителя. Существенные ограничения по скорости, накладываемые НМЖД, вызвали также необходимость ведения специальных индексов, которые позволяли бы отыскивать нужную запись на диске за минимальное количество проходов головки HDD. Обычно формируется несколько индексов, в зависимости от того, по каким полям требуется делать поиск, что увеличивает объем БД на диске иногда в несколько раз. Для отказойустойчивости, традиционные СУБД автоматически дублируют операции в логах, что приводит к еще большему месту занимаемому на дисках. В итоге, например среднестатистическая БД Oracle занимает на диске в 5 раз больше места, чем объем полезных данных в ней. Для среднепотолочной БД на DB2 это отношение еще больше — 7:1.
Однако в 1990-х, с распространением аналитических информационных систем и хранилищ данных, применявшихся для управленческого анализа накопленных в учетных системах сведений, стало понятно, что характер нагрузки в этих двух видах систем радикально отличается.
Если транзакционным приложениям свойственны очень частые мелкие операции добавления или изменения одной или нескольких записей (insert/update), то в случае аналитических систем картина прямо противоположная – наибольшая нагрузка создается сравнительно редкими, но тяжелыми выборками (select) сотен тысяч и миллионов записей, часто с группировками и расчетом итоговых значений (так называемых агрегатов). Количество операций записи при этом невысоко, нередко менее 1% общего числа. Причем часто запись идет крупными блоками (bulk load). Отметим, что у аналитических выборок есть одна важная особенность – как правило, они содержат всего несколько полей. В среднем, в аналитическом SQL-запросе пользователя их редко бывает больше 7–8. Это объясняется тем, что человеческий разум не в состоянии нормально воспринимать информацию больше чем в 5–7 разрезах.
Однако что произойдет, если выбрать, например, только 3 поля из таблицы, в которой их всего 50? В силу построчного хранения данных в традиционных СУБД (необходимого, как мы помним, для частых операций добавления новых записей в учетных системах) будут прочитаны абсолютно все строки целиком со всеми полями. Это значит, что не важно, нужны ли нам только 3 поля или 50, с диска в любом случае они все будут прочитаны целиком и полностью, пропущены через контроллер дискового ввода-вывода и переданы процессору, который уже отберет только необходимые для запроса. К сожалению, каналы дискового ввода-вывода обычно являются основным ограничителем производительности аналитических систем. Как результат, эффективность традиционной СУБД при выполнении данного запроса может снизиться в 10–15 раз из-за неминуемого чтения лишних данных. Причем действие закона Мура на скорость ввода-вывода дисковых накопителей куда слабее, чем на скорость процессоров и объемы памяти. Так что, видимо, дальше ситуация будет только усугубляться.
Решить эту проблему призваны колоночные СУБД. Основная идея колоночных СУБД — это хранение данных не по строкам, как это делают традиционные СУБД, а по колонкам. Это означает, что с точки зрения SQL-клиента данные представлены как обычно в виде таблиц, но физически эти таблицы являются совокупностью колонок, каждая из которых по сути представляет собой таблицу из одного поля. При этом физически на диске значения одного поля хранятся последовательно друг за другом — приблизительно так:
[A1, A2, A3], [B1, B2, B3], [C1, C2, C3] и т.д.
Такая организация данных приводит к тому, что при выполнении select в котором фигурируют только 3 поля из 50 полей таблицы, с диска физически будут прочитаны только 3 колонки. Это означает что нагрузка на канал ввода-вывода будет приблизительно в 50/3=17 раз меньше чем при выполнении такого же запроса в традиционной СУБД.
Кроме того, при поколоночном хранении данных появляется замечательная возможность сильно компрессировать данные, так как в одной колонке таблицы данные как правило однотипные, чего не скажешь о строке. Алгоритмы компрессии могут быть разные. Приведем пример одного из них — так называемого Run-Length Encoding (RLE):
Если у нас есть таблица со 100 млн записей, сделанных в течение одного года, то в колонке «Дата» на самом деле будет храниться не более 366 возможных значений, так как в году не более 366 дней (включая високосные года). Поэтому мы можем 100 млн отсортированных значений в этом поле заменить на 366 пар значений вида <дата, количество раз> и хранить их на диске в таком виде. При этом они будут занимать приблизительно в 100 тыс. раз меньше места, что также способствует повышению скорости выполнения запросов.
С точки зрения разработчика, колоночные СУБД как правило соответствуют ACID и поддерживают в значительной степени стандарт SQL-99.
Резюме
Колоночные СУБД призваны решить проблему неэффективной работы традиционных СУБД в аналитических системах и системах в подавляющим большинством операций типа «чтение». Они позволяют на более дешевом и маломощном оборудовании получить прирост скорости выполнения запросов в 5, 10 и иногда даже в 100 раз, при этом, благодаря компрессии, данные будут занимать на диске в 5-10 раз меньше, чем в случае с традиционными СУБД.
У колоночных СУБД есть и недостатки — они медленно работают на запись, не подходят для транзакционных систем и как правило, ввиду «молодости» имеют ряд ограничений для разработчика, привыкшего к развитым традиционным СУБД.
Колоночные СУБД применяются как правило в аналитических системах класса business intelligence (ROLAP) и аналитических хранилищах данных (data warehouses). Причем объемы данных могут быть достаточно большими — есть примеры по 300-500ТБ и даже случаи с >1ПБ данных.
Ссылки для дальнейшего чтения:
[1] Перевод статьи М. Стоунбрекера "«One Size Fits All»: An Idea Whose Time Has Come and Gone"-- citforum.ru/database/articles/one_size_fits_all
[2] История о том как Zynga использует Vertica для реал-таймовой игровой платформы. С ней можно познакомиться по этой ссылке — tdwi.org/blogs/wayne-eckerson/2010/02/zynga.aspx
[3] Единственный мне известный Open Source вариант коммерческой колоночной СУБД — InfoBright Community Edition www.infobright.org
По наводке Олега Цибульняка:
[4] LucidDB — Изначально оpensource-ная колоночная СУБД. Позиционируется как замена MySQL для аналитических задач www.luciddb.org
PS. Если есть еще интересные материалы о колоночных СУБД — давайте ссылки, вставлю в текст.
Начнем с того, что популярные в наше время реляционные СУБД — Oracle, SQL Server, MySQL, DB2, Postgre и др. базируются на архитектуре, отсчитывающей свою историю еще c 1970-х годов, когда радиоприемники были транзисторными, бакенбарды длинными, брюки расклешенными, а в мире СУБД преобладали иерархические и сетевые системы управления данными. Главная задача баз данных тогда заключалась в том, чтобы поддержать начавшийся в 1960-х годах массовый переход от бумажного учета хозяйственной деятельности к компьютерному. Огромное количество информации из бумажных документов переносилось в БД учетных систем, которые должны были надежно хранить все входящие сведения и, при необходимости, быстро находить их. Такие требования обусловили архитектурные особенности реляционных СУБД, оставшиеся до настоящего времени практически неизменными: построчное хранение данных, индексирование записей и журналирование операций.
Под построчным хранением данных обычно понимается физическое хранение всей строки таблицы в виде одной записи, в которой поля идут последовательно одно за другим, а за последним полем записи в общем случае идет первое следующей записи. Приблизительно так:
[A1, B1, C1], [A2, B2, C2], [A3, B3, C3]…
где A, B и С — это поля (столбцы), а 1,2 и 3 — номер записи (строки).
Такое хранение чрезвычайно удобно для частых операций добавления новых строк в базу данных, хранящуюся как правило на жестком диске – ведь в этом случае новая запись может быть добавлена целиком всего за один проход головки накопителя. Существенные ограничения по скорости, накладываемые НМЖД, вызвали также необходимость ведения специальных индексов, которые позволяли бы отыскивать нужную запись на диске за минимальное количество проходов головки HDD. Обычно формируется несколько индексов, в зависимости от того, по каким полям требуется делать поиск, что увеличивает объем БД на диске иногда в несколько раз. Для отказойустойчивости, традиционные СУБД автоматически дублируют операции в логах, что приводит к еще большему месту занимаемому на дисках. В итоге, например среднестатистическая БД Oracle занимает на диске в 5 раз больше места, чем объем полезных данных в ней. Для среднепотолочной БД на DB2 это отношение еще больше — 7:1.
Однако в 1990-х, с распространением аналитических информационных систем и хранилищ данных, применявшихся для управленческого анализа накопленных в учетных системах сведений, стало понятно, что характер нагрузки в этих двух видах систем радикально отличается.
Если транзакционным приложениям свойственны очень частые мелкие операции добавления или изменения одной или нескольких записей (insert/update), то в случае аналитических систем картина прямо противоположная – наибольшая нагрузка создается сравнительно редкими, но тяжелыми выборками (select) сотен тысяч и миллионов записей, часто с группировками и расчетом итоговых значений (так называемых агрегатов). Количество операций записи при этом невысоко, нередко менее 1% общего числа. Причем часто запись идет крупными блоками (bulk load). Отметим, что у аналитических выборок есть одна важная особенность – как правило, они содержат всего несколько полей. В среднем, в аналитическом SQL-запросе пользователя их редко бывает больше 7–8. Это объясняется тем, что человеческий разум не в состоянии нормально воспринимать информацию больше чем в 5–7 разрезах.
Однако что произойдет, если выбрать, например, только 3 поля из таблицы, в которой их всего 50? В силу построчного хранения данных в традиционных СУБД (необходимого, как мы помним, для частых операций добавления новых записей в учетных системах) будут прочитаны абсолютно все строки целиком со всеми полями. Это значит, что не важно, нужны ли нам только 3 поля или 50, с диска в любом случае они все будут прочитаны целиком и полностью, пропущены через контроллер дискового ввода-вывода и переданы процессору, который уже отберет только необходимые для запроса. К сожалению, каналы дискового ввода-вывода обычно являются основным ограничителем производительности аналитических систем. Как результат, эффективность традиционной СУБД при выполнении данного запроса может снизиться в 10–15 раз из-за неминуемого чтения лишних данных. Причем действие закона Мура на скорость ввода-вывода дисковых накопителей куда слабее, чем на скорость процессоров и объемы памяти. Так что, видимо, дальше ситуация будет только усугубляться.
Решить эту проблему призваны колоночные СУБД. Основная идея колоночных СУБД — это хранение данных не по строкам, как это делают традиционные СУБД, а по колонкам. Это означает, что с точки зрения SQL-клиента данные представлены как обычно в виде таблиц, но физически эти таблицы являются совокупностью колонок, каждая из которых по сути представляет собой таблицу из одного поля. При этом физически на диске значения одного поля хранятся последовательно друг за другом — приблизительно так:
[A1, A2, A3], [B1, B2, B3], [C1, C2, C3] и т.д.
Такая организация данных приводит к тому, что при выполнении select в котором фигурируют только 3 поля из 50 полей таблицы, с диска физически будут прочитаны только 3 колонки. Это означает что нагрузка на канал ввода-вывода будет приблизительно в 50/3=17 раз меньше чем при выполнении такого же запроса в традиционной СУБД.
Кроме того, при поколоночном хранении данных появляется замечательная возможность сильно компрессировать данные, так как в одной колонке таблицы данные как правило однотипные, чего не скажешь о строке. Алгоритмы компрессии могут быть разные. Приведем пример одного из них — так называемого Run-Length Encoding (RLE):
Если у нас есть таблица со 100 млн записей, сделанных в течение одного года, то в колонке «Дата» на самом деле будет храниться не более 366 возможных значений, так как в году не более 366 дней (включая високосные года). Поэтому мы можем 100 млн отсортированных значений в этом поле заменить на 366 пар значений вида <дата, количество раз> и хранить их на диске в таком виде. При этом они будут занимать приблизительно в 100 тыс. раз меньше места, что также способствует повышению скорости выполнения запросов.
С точки зрения разработчика, колоночные СУБД как правило соответствуют ACID и поддерживают в значительной степени стандарт SQL-99.
Резюме
Колоночные СУБД призваны решить проблему неэффективной работы традиционных СУБД в аналитических системах и системах в подавляющим большинством операций типа «чтение». Они позволяют на более дешевом и маломощном оборудовании получить прирост скорости выполнения запросов в 5, 10 и иногда даже в 100 раз, при этом, благодаря компрессии, данные будут занимать на диске в 5-10 раз меньше, чем в случае с традиционными СУБД.
У колоночных СУБД есть и недостатки — они медленно работают на запись, не подходят для транзакционных систем и как правило, ввиду «молодости» имеют ряд ограничений для разработчика, привыкшего к развитым традиционным СУБД.
Колоночные СУБД применяются как правило в аналитических системах класса business intelligence (ROLAP) и аналитических хранилищах данных (data warehouses). Причем объемы данных могут быть достаточно большими — есть примеры по 300-500ТБ и даже случаи с >1ПБ данных.
Ссылки для дальнейшего чтения:
[1] Перевод статьи М. Стоунбрекера "«One Size Fits All»: An Idea Whose Time Has Come and Gone"-- citforum.ru/database/articles/one_size_fits_all
[2] История о том как Zynga использует Vertica для реал-таймовой игровой платформы. С ней можно познакомиться по этой ссылке — tdwi.org/blogs/wayne-eckerson/2010/02/zynga.aspx
[3] Единственный мне известный Open Source вариант коммерческой колоночной СУБД — InfoBright Community Edition www.infobright.org
По наводке Олега Цибульняка:
[4] LucidDB — Изначально оpensource-ная колоночная СУБД. Позиционируется как замена MySQL для аналитических задач www.luciddb.org
PS. Если есть еще интересные материалы о колоночных СУБД — давайте ссылки, вставлю в текст.
