Добрый день. Пытаюсь в новый проект найти замену дорогостоящему MS SQL SERVER с Columnstore таблицами + OLAP.

Итак домашняя работа (для любителей вставить свои три копейки):

Есть таблица фактов 500 000 000 строк, около 20 колонок, только числовые поля и даты. В Columnstore занимает 10 GB. В обычном row-store виде занимает 100-200 GB. Сжимаются в среднем в 20 раз.
И справочников около 40 000 000 строк. В колоночном виде тоже 10 GB (сжимаются всего 2-3 раза).
Итого 20 GB.

Проблема в том, что Multidimensional OLAP жрет много места (+80 GB), и на некоторых запросах пользователей (из сводной таблицы EXCEL) зависает иногда на несколько минут. Памяти потребляет немного, поэтому много времени уходит на чтения с диска.

TABULAR OLAP работает быстро, поскольку по сути это просто колоночная база с онлайн расчетами - занимает +20 GB, но в памяти процесс занимает 40 GB (так как ему обязательно требуется всё хранить и делать в памяти), а пике при обновлении куба - 80 GB.
И это только начало проекта, в реальной жизни вполне может вырасти в 2-3 раза.

Купить Standart Edition не подойдет, так как он для TABULAR OLAP поддерживает только 16 GB памяти . А Enterprise достаточно дорого.

Посему думаем вместо использования OLAP запилить запросы прямо к колоночной базе. Купив, например, минимальный Standart Edition на два ядра. Благо в MS SQL колоночные таблицы работают в среднем за секунду (даже на списанном серваке). Использование памяти менее или сравнимо с размером базы. Использование процессора ничтожно малое. Размер на диске - только сама база.

Бесплатный Ms Sql Express пробовали, но из-за ограничения по памяти менее 1 ГБ. Каждый запрос идет к диску и затягивается на пол минуты даже на SSD.

В соседней ветке выяснили, что для PostgreSQL расширение колоночных таблицы citus не позволяет вставлять данные из MS SQL по ODBC, и совсем не позволяет делать UPDATE и DELETE даже внутри самого PostgreSQL. Что в реальном проекте неприемлемо.

https://www.sql.ru/forum/1302707-1/column-store-greenplum

На изучение каждой технологии уходит масса времени, а, в итоге, оказывается, что впустую.


Поэтому вопросы, к тем, кто реально пробовал колоночные таблицы в бесплатном MariaDB Community Server
https://mariadb.com/docs/features/mariadb-columnstore/

Насколько хорошо они сжимаются?
Реальна ли скорость выполнения запрос на 500 млн строк - за 1-3 секунды на обычном сервачке?
Много ли требуют оперативной памяти (по сравнению с размером базы)?
Много ли требуют процессорного времени?
И самое главное - есть ли там встроенная возможность прозрачно использовать UPDATE и DELETE (без пересоздания таблицы)?
Можно ли туда вставлять данные небольшими порциями (чтобы потом сервер сам их объединял и сжимал большими кусками как MS SQL)?
Или там каждый раз создается новый сегмент, который сжимается только сам с собой и будет только разрастание базы, пока не пересоздашь таблицу (как в Postgre)?

Siemargl,

Да я сам и буду в основном писать. Оптимизировать SQL-запросы. Это как раз моё любимое занятие.
Об этом будет скоро написано на задней обложке в нашем с батей новом учебнике для школьников по Астрономии (продолжение легендарных учебников по Физика: Дойти до самой сути, кто хочет сможет найти - пираты уже посканировали и выложили в Телегу и т.п.).

Поэтому делиться опытом с другими людьми и получать их опыт, не ожидая какой-то наживы, - это моё кредо.

Однако прошу за модератора: не уводить тему в сторону стебательства и подколов.
А эти два сообщения предлагаю модераторам удалить.

Ролг Хупин,

Да, именно благодаря Columnstore это всё взлетело на MS SQL , иначе откуда цифры.
Я же привожу фактические данные. А не гипотетические.
Без Columnstore это всё тормозило даже при десятках миллионов строк.

Теперь задача сделать это на OpenSource, либо убедиться,
что это на данный момент невозможно, либо слишком сложно, либо надо много железа.
По PostGreSQL вчера стало ясно, что вообще не вариант.

Поэтому по-прежнему прошу не засорять форум пустым флудом...

Пока жду здесь советы про MariaDB, параллельно смотрю в сторону ClickHouse.
Это будет следующей темой. Там главное, чтобы можно было сделать синхронный update/delete, или хотя бы update.
Подписался на их чат, но там очень много сообщений. Вопросы похожие есть. но так как это чат, то ответы на него найти нереально.

Хупи Ролинг, перестань вставлять свои три копейки. Написал же всё четко:

MS SQL SERVER - для rowbased таблица слишком большая, поэтому обойти по ней OLAP-запросу быстро невозможно.
Columnstore на том же сервере сжимается в 20 раз, и работает в 1000 раз быстрее. И этот результат устраивает технически!
Интересует опыт коллег по другим поставщикам СУБД (бесплатным, а не MS SQL).
Что еще непонятно? Зачем заполнять форум передёргиванием?

Интернет у меня работает, но на нем не получишь реальный опыт.
Форум для того и нужен, чтобы одни люди задали вопрос.
Вторые - написали ответ исходя из реального опыта.
Третьи - воспользовались уже готовым вопросом и ответом через поисковик.

Сколько времени ушло на PostgreSQL - а всё зря.
Зато в той ветке ответ, что там это не жизнеспособно (студенческая поделка).
Наверняка и с точки зрения надежности тоже все печально, раз даже функциональность страдает.

Пусть на форуме будут ответы на вопросы, а передёргивайте где-нибудь на других сайтах.

Ролг Хупин,

Ссылку открывал, ни слова про возможность удаления / обновления записей. Как и в PostGreSQL. Это узнаешь, только когда все поставишь.

Пробовать RowBased в PostGreSQL нет смысла, так как даже первичное считывание таблицы память (даже если купить очень много памяти) 200 GB, будет чисто физически занимать много времени. Индексы тут не помогут, это не OLTP. У меня большой опыт работы с обычными таблицами в СУБД других поставщиков. Зачем проверять очевидное.

авторРолг Хупин,
всё-всё, молчу, чудо есть, главное верить

Вы намекаете на то, что нет достойных бесплатных конкурентов у MS SQL,
или на то, что тут никто не ответит по-существу?

авторDimitry Sibiryakov,

А у других поставщиков СУБД такой размер таблицы считается детским и OLАP-запрос обходит её без проблем. Полегчало?..

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

У нас в ORACLE тоже такие запросы, надо было всего лишь соединить две-три таблички по млрд строк, и прогруппировать по паспорту (около 150 млн), затем посчитать разные количества. И вот это прямо проблема. Длилось часами. Забивало temp tablespace на 500 ГБ и валилось. В итоге пришлось каждую из табличек искусственно партиционировать по ключу соединения (в итоге сделали по первым двум цифрам документа). И делать цикл по всем партициям, группировать класть в другую таблицу, и уже по ней делать основные запросы. И все равно это длится около часа.

Напомню в ORACLE (12) нет колоночного хранения данных, только какая-то дополнительная конструкция in memory column store, которая занимает дополнительное место в памяти помимо хранения данных на диске в обычном построчном виде и обычных строковых блоков данных в buffer cache.

PS: неужели на форуме по использованию columnstore в MariaDB (MySQL) нет ни у кого опыта?

авторPizzaPizza,

мои 5 копеек

"UPDATE и DELETE" нужно очень тщательно оценивать, так как эти операции деградируют индекс и сами очень тяжелы на исполнение

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

Вот только что понадобилась ручная операция DELETE из колоночной таблицы в MS SQL. Загрузили данные не из той базы.
15 000 строк удалилось за 0 секунд. Поэтому никаких проблем не вижу.

Подождать ноль секунд не так много, чтобы говорить о том, что это настолько дорогая операция, что надо в проекте совсем отказаться от операции "UPDATE и DELETE".

Операция UPDATE = DELETE + INSERT. Если нужны не часто (например, обновляется лишь 1% записей), то никаких проблем нет.

Неужели никто в MySQL не пробовал колоночные таблицы? Есть там UPDATE и DELETE?
Насколько быстро работают select и insert?

авторРолг Хупин,

Случайно зашел в веточьку.
Вы до сих пор искренне считаете, что в этом вопросе: "что за другие СУБД, для которых запрос ко всей таблице целиком на 500 млн строк обходится без проблем за секунды?" хардваре никакой роли не играет?

Уважаемый троль, у меня всё это работает в MS SQL на списанном тестовом сервере 2006 года на HDD. Но оперативки много.

На другом серваке более новом, где памяти мало - тестировал. Запросы идут хорошо. А вот соединения больших объемов плюс вставка в другую колоночную таблицу - существенно медленнее.

А также на старенькой персоналке на бесплатном MS SQL Express Edition (памяти есть менее 1 ГБ), которая каждый раз читает с диска SDD.
И всё это работает тоже за десятки секунд.

Просто Dimitry Sibiryakov написал, что 500 млн перелопатить - это не проблема для других СУБД.
Вот интересует - это он что-то напутал, или специально вводит в заблуждение.

MS ACCESS, на миллионе строк уже просто умирает. Так что высказывание Дмитрия в общем случае ложное.

авторРолг Хупин,

1. Уважаемый троЛЛь, вы не ответили на мой мэсыджь: хардваре никакой роли не играет?

2. MS ACCESS, на миллионе строк уже просто умирает . Вас обманули, MS ACCESS - это не СУБД, а так, дурилка картонная.

Да, извините, конечно же тролль.

MS ACCESS - смотрите в Википедию, это СУБД.
По функциональности похожа на SQLLite, но плюсы MS ACCESS в том, что там есть GUI-интерфейс, и можно лабать небольшие базёнки прямо на ней. И переслать по почте как обычный EXCEL-файл. Плюс там же можно подключать внешние таблицы или query из больших баз. Плюс опять же можно передать программу в открытом виде. А не в скомпилированном exe, где в случае замены программиста - исходников не найдешь. В целом для своего сегмента вещь незаменимая.

Про хардваре - написал же несколько вариантов. результаты сравнимые - время выполнения секунды-минуты, а не часы.


авторНа другом серваке более новом, где памяти мало - тестировал. Запросы идут хорошо. А вот соединения больших объемов плюс вставка в другую колоночную таблицу - существенно медленнее.

Про это разборались, почему медленнее, там на сервере отключено параллельность(Max degree of parallelism=1), так как там базы OLTP, поэтому и вставка в колоночную таблицу медленнее, так как используется только одно ядро.


По сути топика неужели никто ничего не знает?

Нашел годное видео на русском языке про MariaDB columnstore от разработчика из MariaDB Corporation. Рекомендую.

Эх, потратил весь выходной на эту MariaDB. Было уже отчаялся, когда увидел тут, что MariaDB Community Server не входит ColumnStore
https://mariadb.com/products/
И вот тут ссылка с community сервер ведет на enterprise columnstore. который ставится только на платный Enterprise сервер.
https://mariadb.com/downloads/community/columnstore/
https://mariadb.com/docs/features/mariadb-enterprise-columnstore/

Но в итоге нашел, как поставить таки ColumnStore на бесплатный MariaDB Community Server.
https://mariadb.com/docs/deploy/topologies/single-node/community-columnstore-cs10-6/

Все настройки не осилил, но принципиально заработало.
На это уйдет пол дня при условии установленной ubuntu.
И вот опять простой тест скорости не удалось сделать даже к 4 часам ночи.
Сделал ODBC (в Windows к виртуалке на ubuntu).
В MS SQL подключил Linked server к нему через ODBC.
Создал табличку (для этого рекомендую скачать MariaDB для Windows чтобы клиент был визуальный, ставится за два клика мышкой).

insert into MARIADBTEST.mydb..Client(PrId, PrName, INN)
select top 100 PrId, PrName, INN
from Client

И что мы видим - одна запись вставляется за 1 секунду, 10 записей за 10 секунд, 100 строк за 100 секунд и т.д.
Это судя по видео-ролику эквивалентно вставке по одной строке. У них любой отдельный insert - секунда.

Получается, что если у Вас есть источники базы, то вот так нахаляву по db-link Вы её не загрузите и не протестируете.
Надо выгружать в csv и разбираться с загрузчиками в виде отдельных программ.

В обычную row-store таблицу по Linked server вставилось 30 000 строк за ДВЕ МИНУТЫ!
Поэтом из этой таблицы уже внутри MariaDB вставилось в колоночную за 3 секунды.
Но это тоже очень медленно. В Sql server express это всё выполняется за 0 секунд.

Вставить 400 000 000 строк таблицы фактов даже кусками через обычную таблицу при такой скорости уйдет 18 суток.
Хотя должно уходить всего менее часа.

Соответственно делать загрузку данных (ETL) на стороне MS SQL невозможно. Работает очень неторопливо.

Размер (в килобайтах) колоночной таблицы MariaDB не показывает.
Поэтому сжатие оценить запросом или в интерфейсе клиента невозможно.

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

Верю, что если загружать данные какими-то особенными загрузчиками можно добиться хорошей скорости,
но грузить данные из базы в базу - удобнее всего. Лишиться этого - это жесть.