Добрый день. Пытаюсь в новый проект найти замену дорогостоящему 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-сервер и подключиться к нему.

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

авторРолг Хупин,
Возможен подвох:
"Операция UPDATE = DELETE + INSERT" = 0 секунд = 1 сек + (-1) сек


Уважаемый тролль, читай внимательно, в MariaDB: операция DELETE = UPDATE.

При этом я удивлен, что несмотря на то, что MySql по популярности на втором месте после ORACLE, его не допилили, чтобы он устанавливался в три клика, как MS SQL, MS ACCESS*, или для примера тот же браузер Google Chrome. Про установку и настройку ORACLE я вообще молчу - он вообще в рейтинге не первом месте. Кто пытался сделать там SSO (вход без пароля доменным пользователем) - тот в следующий раз наверное застрелится или уволится, лишь бы этого не делать.

И, кстати, уважаемый тролль, по популярности MS ACCESS делит 3-4 место с MS SQL, так что Вы зря ехидно издевались над этой СУБД.

У меня буквально осенью - один небольшой проект на MS ACCESS, другой на MS SQL Server EXPRESS (+интерфейс в MS ACCESS). Во втором проекте под мини OLAP выбрал его, так как скорость в колоночных таблицах просто гиперзвуковая, при этом официально бесплатно. С учетом сжатия данные влезли бы и в одну базу 10 ГБт, но на всякий пожарный сделали под каждый отдельный (независимый) кусок свою базу. Поэтому даже ограничение в 10 ГБт не мешает. И всё это установилось на ноут без проблем за 5 минут вместе с прекрасной бесплатной SQL Server Management Studio.

Всё-таки продолжил изучать MariaDB и ColumnStore. В том числе с целью изучения Docker. Оказалось очень удобная штука.

Основная суть Docker - можно за 5 минут получить уже настроенный LINUX с установленным приложением.

Итак по шагам инструкция.

1. Скачиваем и устанавливаем отсюда docker desktop под Windows 10.
Мне пришлось переставлять для этого ОС, так как был Windows 8.1.

https://www.docker.com/products/docker-desktop

2. Открываем командную строку (Win+R, пишем cmd, жмем OK)

3. Готовый образ MariaDB 10.6 Community Server (with ColumnStore 6.x) нашел в поиске:
https://hub.docker.com/r/mariadb/columnstore
От туда берем командную строку для запуска в cmd:
docker run -d -p 3306:3306 --name mcs_container mariadb/columnstore

4. Ждем несколько минут. И всё готово! Сервер работает.

5. Теперь из cmd в Windows подключаемся к консоли mariadb уже как бы внутри линукса, где можно писать sql-запросы:
docker exec -it mcs_container mariadb

6. В этой консоли создаем себе базу:
CREATE DATABASE olapbi;
show databases;

7. Создаем пользователя pl, даем права:
CREATE USER pl IDENTIFIED BY 'pass';
select user, host from mysql.user;
GRANT ALL PRIVILEGES ON olapbi.* TO pl;

8. Скачиваем и устанавливаем тут ODBC-драйвер под Windows:
https://mariadb.com/downloads/connectors/connectors-data-access/odbc-connector/

9. Вот тут скачиваем и устанавливаем MariaDB Server, но Database instance не ставим на Windows.
Тут нужна только HeidSQL, то есть программа администрирования MariaDB, написание запросов, создание таблиц и т.п.
https://mariadb.org/download/

10. Создаем ODBC нужной битности (в зависимости от битности приложения, которое будет соединяться).
У меня это MS SQL Server Express 64 битный, поэтому я установил 64-битный ODBC.
ODBC создается этой программой:
32-битное ODBC
C:\Windows\SysWOW64\odbcad32.exe
64-битное ODBC
C:\Windows\System32\odbcad32.exe

Добавляем новый DSN с названием MARIADBDOCKER на закладке системных DSN. Выбираем драйвер MariaDB ODBC Driver.
Для соединения к базе указывать IP-адрес вашего Windows-компьютера (если локально можно просто localhost), и порт 3306.
Вводим пользователя pl и пароль pass. Тестируем DNS и выбираем созданную базу olapbi. Сохраняем.

Всё готово!

11. Теперь идем в MS SQL Server -> Server Objects -> Linked Server. Создаем новую запись с названием MARIADBDOCKER.
Provider: Microsoft OLE DB Provider for ODBC
Product name: MARIADBDOCKER
Data Source: MARIADBDOCKER
Catalog: olapbi

Или создаем скриптом:
EXEC master.dbo.sp_addlinkedserver @server = N'MARIADBDOCKER', @srvproduct=N'MARIADBDOCKER', @provider=N'MSDASQL', @datasrc=N'MARIADBDOCKER', @catalog=N'olapbi'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MARIADBDOCKER',@useself=N'False',@locallogin=NULL,@rmtuser=N'pl',@rmtpassword='pass'

12. Теперь создаем таблицы (сидя в MS SQL). Но можно просто в HeidSQL выполнить внутренность скрипта между опострофами '

exec ('CREATE TABLE olapbi.DivisionCS(
PrId int NOT NULL,
Buchgalt_PrId int NOT NULL,
Relation1C varchar(255) NULL,
PrName varchar(1024) NOT NULL,
PrGUID varchar(128) NOT NULL,
Parent_PrId int NULL,
DictionaryType_PrId int NOT NULL,
BaseDivision_PrId int NULL,
PrDivision varchar(255) NOT NULL
)COLLATE="utf8mb3_general_ci"
ENGINE=Columnstore') at MARIADBDOCKER

exec ('CREATE TABLE olapbi.DivisionRS(
PrId int NOT NULL,
Buchgalt_PrId int NOT NULL,
Relation1C varchar(255) NULL,
PrName varchar(1024) NOT NULL,
PrGUID varchar(128) NOT NULL,
Parent_PrId int NULL,
DictionaryType_PrId int NOT NULL,
BaseDivision_PrId int NULL,
PrDivision varchar(255) NOT NULL
)COLLATE="utf8mb3_general_ci"
') at MARIADBDOCKER

--Очистка таблиц (если надо)
exec ('truncate TABLE olapbi.DivisionRS') at MARIADBDOCKER
exec ('truncate TABLE olapbi.DivisionCS') at MARIADBDOCKER

--Заполнение обычной rowstore таблицы в MariaDB из локальной базы в MS SQL
--Это к сожалению выполняется 146 секунд всего на 30 000 строк.
insert into MARIADBDOCKER.olapbi..DivisionRS
select * from OLAPBI.dbo.Division

--Несколько раз копируем таблицу саму в себя, чтобы стал миллион строк.
--Это будет несколько секунд
exec ('insert into olapbi.DivisionRS
select * from olapbi.DivisionRS') at MARIADBDOCKER

--Заполняем ColumnStore таблицу из rowstore таблицы внутри MariaDB
--На миллион строк ушло 14 секунд
--Если вставлять из DivisionCS в DivisionCS, то тот же миллиона вставиться за те же 14 секунд.
exec ('insert into olapbi.DivisionCS
select * from olapbi.DivisionRS') at MARIADBDOCKER

--Теперь смотрим размер обычной rowstore таблицы (смотрел запрос уже в самой HeidSQL): 211 Мегабайт
SELECT table_schema, TABLE_NAME, round(((data_length + index_length) / 1024), 2) "Size in KB", data_length,index_length
FROM information_schema.TABLES
WHERE table_schema = "olapbi"
ORDER BY 1, 2

--Теперь смотрим размер columnstore таблицы: 50 Мегабайт
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, sum(e.DATA_SIZE) AS DATA_SIZE
FROM INFORMATION_SCHEMA.COLUMNSTORE_COLUMNS c
inner join INFORMATION_SCHEMA.COLUMNSTORE_EXTENTS e ON e.OBJECT_ID = c.OBJECT_ID
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME
order by 1, 2;

Сжатие есть, но всего в 4 раза, хотя по факту там 30 копий одинаковых данных по 30 000 строк.

Размер этой же таблицы на миллион строк в MS SQL Server Express:
Rowstore MS SQL - 138 мегабайт (но тут не unicode, а просто varchar)
Columnstore MS SQL - 10 мегабайт.

Итого в MS SQL Server сжатие одинаковых данных - в 13 раз! То есть требуется меньше места на диске, а самое главное - меньше памяти. Поэтому на одном и том же сервере MS SQL будет при большой базе существенно выигрывать.

Теперь остался открытый вопрос: КАК вставлять данные в MariaDB через windows ODBC, чтобы они вставлялись быстро, а не по одной строке. Потому что вставка в ColumnStore таблицу там просто зависает и вставляется по 1 строке в секунду, но при попытке вставить через ODBC даже в обычную таблицу миллион строк - всё со страшной силой зависло, сожрало всю память и даже начал тормозить основной компа на Windows (vmmem от докера сожрал зачем-то всю память компа).

Вот тут предлагают предлагают либо каждый раз гонять все данные через csv-файлы.
Либо устанавливать и настраивать ENGINE=CONNECT через ODBC в линуксе.
Из коробки в этом докер-образе это не работает. Настроить ODBC в урезанном линуксе боюсь задача та ещё.
К тому же не решает проблему вставлять данные в базу с любого клиента ODBC.
https://mariadb.com/kb/en/moving-data-between-sql-server-and-mariadb/