Колоночные СУБД — принцип действия, преимущества и область применения

    Середина 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 раз меньше чем при выполнении такого же запроса в традиционной СУБД.
    image

    Кроме того, при поколоночном хранении данных появляется замечательная возможность сильно компрессировать данные, так как в одной колонке таблицы данные как правило однотипные, чего не скажешь о строке. Алгоритмы компрессии могут быть разные. Приведем пример одного из них — так называемого 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. Если есть еще интересные материалы о колоночных СУБД — давайте ссылки, вставлю в текст.
    Поделиться публикацией
    Ой, у вас баннер убежал!

    Ну. И что?
    Реклама
    Комментарии 51
    • +5
      Статья интересная, но хотелось бы побольше конкретики в сравнении с традиционными субд.
      Фразы типа:
      «У колоночных СУБД есть и недостатки — они медленно работают на запись» — не совсем полно отображают ситуацию, имхо. Насколько медленно?
      • 0
        Разные колоночные СУБД тормозят при записи по-разному, так как могут иметь разную архитектуру механизмов записи, кроме того смотря как писать — часто, но построчно, или редко, но большим массивом (bulk load). Но в общем случае — в разы, и может быть даже в десятки раз медленнее. Словом, слишком медленно для того, чтобы их можно было использовать в учетных (OLTP) системах.
        • 0
          Сильно ли можно выиграть в скорости, храня редкоменяющиеся данные в колоночной БД, а постоянно растущие таблицы (например, теги или свзяи) в обычной реляционной? Учитывая возрастающее потребление памяти и необходимость запущенному скрипту держать два соединения.
          Как у колоночных СУБД обстоит дело с масштабируемостью?
          • 0
            Обычно делают по-другому — если много мелких операций записи, то пишут в обычную СУБД, а потом раз в час или раз в день массивом перегружают в колоночную. Хранить справочники в одной СУБД, а операции в другой не есть хорошо, так как джойн лучше делать внутри одной СУБД. Правда последние версии некоторых колоночных СУБД научились хорошо работать и с потоком мелких операций записи в реал-тайме — Vertica (с которой я немного сталкивался) довольно часто используется для click-stream аналитики.

            Что касается масштабирования — то масштабируются они неплохо, так как изначально заточены под большие и очень большие объемы памяти. 50-100Тб данных обычно не есть проблема. читал про случаи и по петабайту данных.
            • 0
              Спасибо!
              • 0
                Вставка данных будет сильно медленнее при поколоночном хранении в силу того, что все равно будет обращение ко всем колонкам, равно чтение/запись по строкам? А насколько сильно?
        • 0
          Ссылка [2] не работает, можно поправить?
          Хотелось бы прочитать, вопрос интересный.
        • 0
          спасибо. очень интересно. ведь действительно если сохранять столбец и пожать его тем же зипом на лету он может нехило пожаться.
          • 0
            Больше того, для разных типов полей могут использоваться разные типы компрессии. Для текстовых — зипование. Для целых — RLE. Для флоат — еще какой-нибудь. И т.д.
          • 0
            А что происходит в случае с SSD?
            • +1
              ссд всегда портит хорошие оптимизации =)
              ведь в нем произвольное время доступа.

              это тот случай, когда нужно переставать выдумывать, и говорить что какая-то технология лучше, лишь потому что железо не
              развивалось.
              • 0
                примечание: портит в смысле делает их бесполезными.
              • +2
                Лично не тестировал на SSD, но подозреваю что расстановка существенно не поменяется, только все будет в несколько раз быстрее за счет меньшего seek time. Если речь идет не о специализированном устройстве типа Oracle Exadata (с флеш-памятью вместо дисковых контроллеров), то SSD подключаются к обычному контроллеру ввода/вывода. А его пропускная способность не безгранична — так что и для SSD чем меньше данных проходит через контроллер, тем лучше.
                • 0
                  Как не поменяется? Если есть запись из 50 полей, а нужно прочитать только 3 — смещаемся к первому полю, читаем первое, смещаемся ко второму, и т.д. В обычных винтах сместиться и прочитать всё — не велика разница, а вот у SSD можно не гнать всю запись за счёт быстрого seek'а. Естественно это актуально для полей фиксированной длины, но BLOB — это отдельная статья, операции с ними никогда особо эффективными не будут.
                  Я не спорю, колоночные скорее всего останутся выгоднее, но уже не с таким отрывом.
                  • 0
                    > у SSD можно не гнать всю запись

                    Можно, но подозреваю что СУБД не различает работает она с SSD или с шпиндельным диском. Поэтому тянет всю запись целиком в любом случае.

                    Кстати есть программно-аппаратные комплексы database appliances, где выборка полей проходит как раз на уровне дискового контроллера ввода-вывода, но это отдельная история.
                    • 0
                      Насчет всех СУБД не скажу, а MS SQL читает страницами по 8 (?) КБ, так что выборочно поля он точно прочитать не может
                    • +2
                      Все накопители работают с блоками данных — кластерами. Так что тебе нужно будет в любом случае считывать целиком блок в несколько килобайт. SSD — не исключение.

                      И там, как я понимаю, это ограничение физически обосновано, т.е. там дело не только в том, что SATA-контроллер не умеет работать на уровне байтиков.

                      Плюс даже оперативная память быстрее рубит когда данные читаются последовательно. И процессор с его кешами ей у этом помогает. Так что даже если все данные БД будут в оперативке — колоночная БД все равно зарулит в определенных ситуациях.
                      • 0
                        Раньше минимальный объём для считывания был 512 байт (сектор). На некоторых новых винтах — 4Кб. Как с SSD дела обстоят — не знаю. Но в целом — да, выигрыша может и не быть.
                        • 0
                          Даже 512 байт — уже много. Возьми, например, какую-нибудь аналитику типа подсчитать сумму проданных товаров по месяцам. Нужные для этого колонки могут занимать по 10-20 байт, а вся строка — все 512 вполне.

                          Сейчас SSD работают через интерфейс, придуманный для винтов, и который весь рассчитан на выдачу больших последовательных блоков.

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

                          Возможно если сделать к флеш-памяти интерфейс, как-то получше умеющий работать с произвольно расположенными данными, можно будет считывать байтики в шахматном порядке без сильного ущерба для скорости. Но я пока про такое не слыхал.
                          • 0
                            Да я ещё в предыдущем сообщении согласился :)
                            А в шахматном порядке как ни крути выгодно не будет. Банальные потери на передачу адреса и размера данных будут сравнимы с самими данными.
                            • 0
                              >Возможно если сделать к флеш-памяти интерфейс, как-то получше умеющий работать с произвольно расположенными данными, можно будет считывать байтики в шахматном порядке без сильного ущерба для скорости. Но я пока про такое не слыхал.
                              Флеш память ещё больше ориентирована на блоки. Записывается и стирается она обычно блоками по несколько килобайт(для маленьких объёмов бывает например 64 байта, но такие микросхемы имеют ёмкость 1 Мб например). Если вам надо изменить один байт то в буфер в контролере считывается весь блок меняется нужный байт и весь блок записывается назад. Частое изменение малых порций данных привод к очень быстрому изнашиванию ячеек.
                              Так что современные дисковые интерфейсы достаточно хорошы и для флеш-дисков.
                      • 0
                        Чем меньше, тем лучше?
                        Даже для такого чудо-диска?
                    • 0
                      Кстати, если я правильно все понял, к таким базам можно очень хорошо применить индексно-последовательный метод поиска данных.
                      Т.е. есть колонка отсортирована в какую либо сторону, то можно использовать интервальные значения для позиционирования.

                      Например, если в колонке числа по порядку 1,2,3,8,9,15,...999231,293231313, можно хранить индекс типа, 10,20,100,200,1000 и позиционировать указатель для чтения сразу очень близко к нужному месту. Т.е. мы сразу знаем, на какой позиции находятся числа, например до 200 и сразу за этой позицией начинаются числа, которые более 200 по значению.
                      • 0
                        Да, приблизительно так и есть.
                      • 0
                        Да, решение очень хорошее для аналитических систем. Одрнако получается что для какой-то большой системы прийдётся держать две базы — одну для данных, другую для аналитики? Потому что актуальная база изменяется чуть ли не каждую секунду (условно говоря), но аналитика нужна только по окончанию рабочего дня. Тут и сосут поколоночные базы.

                        Не могу придумать ситуаций, где поколоночная база выиграет традиционной в повседневной жизни.
                        • +1
                          Для больших систем действительно делают две базы, одна из которых для аналитики.

                          См. также мой коммент выше.
                          • 0
                            Так обычно и есть. Есть транзакционная (оперативная) база — а есть хранилище. Мало кто нагружает оперативную базу запросами аналитики. В случае, если необходимы актуальные данные для каких-либо аналитических отчетов, организуют репликацию данных (как правило, актуализация «ноль-в-ноль» нужна не для всех отчетов, поэтому объем реплицируемых данных не очень велик).
                            • 0
                              А можно даже три: строчную, колоночную и nosql :)
                            • 0
                              В случае колоночных БД увеличивается количество seek'ов, так что при уменьшении объема читаемых данных они тем не менее могут работать медленнее.
                              • 0
                                Это значит, что не важно, нужны ли нам только 3 поля или 50, с диска в любом случае они все будут прочитаны целиком и полностью, пропущены через контроллер дискового ввода-вывода и переданы процессору, который уже отберет только необходимые для запроса.
                                Это, мягко говоря, не правда. Если есть покрывающий индекс по трем полям и только эти три поля указаны в выборке, то до данных в таблице дело даже не дойдет, все данные приползут из индекса за одно обращение.
                                Ну и вся остальная статья тоже сплошное передергивание, например — очень долго рассказывается что база занимает много места на диске, но так и не сказано чем это плохо.
                                OLAP-ы действительно требуют немного других схем хранения, но причем тут RDBMS и OLTP?
                                • 0
                                  Согласен, опередили немножко.
                                  Сами подумайте, когда перед разработчиками стоит задача оптимизировать СУБД, наверное, чтение всей строки таблицы перед ними будет маячить как красный флаг для быка.
                                  Не уверен, что пример будет показательный (слишком много нюансов может быть), но попробовал выполнить два запроса (DB2 9.5, RHEL 5). Таблица содержит 25`000`000 строк и 12 столбцов. Индекс на ID в таблице есть.
                                  select * from analytics.docs

                                  Время выполнения запроса: 0.235 sec
                                  select id from analytics.docs

                                  Время выполнения запроса: 0.188 sec
                                  • 0
                                    А если колонка, кроме id, какой результат?
                                    • +1
                                      Примерно такой же.

                                      select doctype from analytics.docs

                                      Время выполнения запроса 0.187 sec

                                      select doctype, lastsigndate from analytics.docs

                                      Время выполнения запроса 0.204 sec
                                • 0
                                  Поправьте «Причем в часто запись идет крупными блоками»
                                • 0
                                  Сорри за тупость, не очень понял вот это:
                                  «Если у нас есть таблица со 100 млн записей, сделанных в течение одного года, то в колонке «Дата» на самом деле будет храниться не более 366 возможных значений, так как в году не более 366 дней (включая високосные года). Поэтому мы можем 100 млн отсортированных значений в этом поле заменить на 366 пар значений вида <дата, количество раз> и хранить их на диске в таком виде. При этом они будут занимать приблизительно в 100 тыс. раз меньше места, что также способствует повышению скорости выполнения запросов.
                                  »
                                  Как, если мы заменим все значения этого поля на пары «дата, количество раз», мы сможем потом ассоциировать с каждой записью конкретную дату?
                                  • 0
                                    Сможем. Можно искать последнюю запись с индексом <= искомому, например.
                                  • 0
                                    мне что-то кажется, что индекс по любому полю в строчной (реляционной) БД и есть та структура, которая приближается к хранению данных колонками. Да собственно индексы для того и нужны, чтоб облегчить работу при «аналитическом режиме».
                                    Ну, понятно, что только приближается, хранение по столбцам конечно эффективнее, но вот БД в итоге работает в смешанном режиме и нужно оценивать, какая стратегия хранения даст наибольший кумулятивный эффект. Наверное при проектировании современных движков БД об этом подумали.
                                    • 0
                                      Что ж вы главное то не указали? Если мне нужно выбрать весь объект целиком, все 50 полей, то в колоночной бд — это 50 разных запросов.
                                      • 0
                                        С точки зрения SQL-клиента это обычная реляционная СУБД, поэтому для выборки всех полей достаточно 1-го запроса. Таблицы видны как обычно — таблицами.
                                        • 0
                                          Но физически это будут 50 разных выборок с соответствующими пенальти для производительности. И тут встает вопрос о клиентском фреймворке, так как очень мало кто умеет корректно работать с частично выбранными из базы объектами. В большинстве случаев объекты и выбираются, и записываются в базу целиком, вместе с «ненужными» полями.
                                          • 0
                                            Особый клиентский фреймворк не нужен. Еще раз — для клиента это обычная реляционная СУБД понимающая обычный SQL и имеющая ODBC, ADO.NET и прочие драйверы.

                                            Количество физических операций чтения с диска может быть разное — тут сложно сказать. Многое зависит от архитектуры колоночной СУБД. В Vertica например, использует т.н. проекции — по сути вьюшки сделанные из нескольких колонок с разным порядком сортировки, кроме того могут быть партиции, и сегменты разнесенные по разным кластерам. Атомарные колонки на физическом уровне недоступны для клиента в принципе.

                                            Если хотите разобраться в принципе работы более детально — рекомендую почитать вот этот документ — www.vertica.com/writable/knowledge_articles/file/verticaarchitecturewhitepaper.pdf
                                      • 0
                                        Заголовок «Колоночные СУБД — принцип действия, преимущества и область применения», в тексте только 1 раздел «Резюме». =\
                                        • 0
                                          Самое интересное — не отдельная БД, а просто отдельный движок — я про InfiniDB — который просто движок-плагин. Тогда вполне можно соединять в одной базе даже мускула разные типы хранения данных.
                                          • +1
                                            UPD: Ко мне обратился ivann, к которому в свою очередь обратился человек по имени Руслан Засухин, у которого нет эккаунта на хабре и нет времени написать чего-нибудь умное в песочнице, но который хотел бы с хабрасообществом поделиться информацией о том, что (цитирую):
                                            есть такая база данных Valentina
                                            разрабатывается еще с 1995 года
                                            Русланом Засухиным изначально
                                            Украина Херсон
                                            www.valentina-db.com

                                            так что SyBase точно не был в гордом одиночестве :)
                                            мы может и раньше нарисовали это…

                                            Судя по описанию на сайте, это колоночная СУБД, родившаяся в 90-х на просторах украинских степей. Не знаю насколько она хороша, но вполне процветающий вид сайта намекает на то, что деньги за нее видимо кто-то таки платит.
                                            • 0
                                              Примеры не помешали бы. Ведь есть колоночные engines для MySQL и Postgres
                                              • 0
                                                Примеры чего, уточните плиз.
                                                • 0
                                                  Список доступных (бесплатных или около того) колоночных баз
                                                  • 0
                                                    Бесплатных — см. ссылку [3] в конце статьи. Остальные — в начале.

                                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                            Самое читаемое