Question about compatibility with MariaDB Community Server 10.6.7

[Glauber Alexandre Brossi da Cunha]

Good morning everybody

I'm checking the possibility of starting to use CS on the MariaDB Community Server 10.6.7 server, as I have two historical data tables that already have 60 million rows each. And I'm gathering as much information as possible to pass on to the team responsible for the server.

My doubts:

1 - How can I use the latest version of stable CS on my MariaDB 10.6.7 server?
2 - Can I use tables with InnoDB and CS on the same server? If yes, is it possible to do JOIN involving tables from different engines?
3 - Once everything is configured, can I insert the data from the InnoDB table to the table with CS by doing a select insert?

I only have these doubts at the moment, I will continue reading all the topics in this group that are helping a lot.

Thank you very much in advance

Att
Glauber

[allen....@mariadb.com]

Yes you should try the columnstore plugin, historical data is perfect for this, and 60 mill isn't large at all for this engine.
I would recommend considering enterprise for support/latest updates /advice and more.

1) 10.6.7 comes with CS 6.2.3. if you want a newer columnstore version you need to upgrade your mariadb version
2)
Yes you can use both engines on the same server. 
Yes you can cross engine join the two engines together in a query.
However remember these are different engines with different strengths and weaknesses, so focus on OLAP use cases for columnstore. I.e avoid constant updates/deletes and batch these operations to be an infrequent as possible. Historical append only is best.

3) Yes you can load data from innodb to columnstore
insert into columnstore_table select * from innodb.
depending on how much data is being transferred though, you'll like need larger hardware as columnstore loves more cpu as it massive parallelizes everything it can.

[Glauber Alexandre Brossi da Cunha]

Thanks for your reply Allen

Let's start the installation now, I hope everything goes well.

Any questions can I post here in this topic, or is it recommended to create a new one?

Thank you very much
Att
Glauber

[Glauber Alexandre Brossi da Cunha]

Allen,

I'm looking for the version of CS indicated by you, but I'm not finding it, I managed to find only this version 1.2.5
mariadb-columnstore-1.2.5-1-bionic.amd64.bin.tar.gz
mariadb-columnstore-1.2.5-1-bionic.amd64.deb.tar.gz

https://downloads.mariadb.com/ColumnStore/latest/ubuntu/dists/bionic/main/binary_amd64/

Just to reinforce, our server is not Enterprise Server, we are using Community Server in version 10.6.7

I found here in this link below, this file (MariaDB-columnstore-engine-10.6.7_6.2.3-1.x86_6..>), this installation file is correct?
https://archive.mariadb.org/mariadb-10.6.8/yum/opensuse150-amd64/rpms/

In this other link below I found CS 6.2.3, but on the page it says it is for MariaDB Enterprise Server, can I install it on MariaDB Community Server?

https://mariadb.com/docs/server/release-notes/mariadb-enterprise-columnstore-6/6-2-3/

Thanks
Att
Glauber

[Allen Herrera]

Yes, cs 6.2.3 for mariadb 10.6.7

I found here in this link below, this file (MariaDB-columnstore-engine-10.6.7_6.2.3-1.x86_6..>), this installation file is correct?
https://archive.mariadb.org/mariadb-10.6.8/yum/opensuse150-amd64/rpms/

If you used our repo setup script should be a simple yum install/ apt install and it will grab the right version

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup |  bash -s -- --mariadb-server-version=mariadb-10.6.7

yum install MariaDB-columnstore-engine -y;
or
sudo apt install libjemalloc2 libmariadb3 mariadb-plugin-columnstore -y

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/928b30c6-a144-44f9-98d1-59bf2e915a07n%40googlegroups.com.

--

Allen Herrera

Customer Engineer | MariaDB Corporation

+1.360.888.3938 | calendly.com/allen-herrera | allen.herrera@mariadb.com

[Glauber Alexandre Brossi da Cunha]

Sorry Allen,

The team responsible for the server warned me that it is not, yum, Ubuntu

I'll pass these commands that you sent to them.

I also found this folder, let's try here.
https://archive.mariadb.org/mariadb-10.6.7/repo/ubuntu/pool/main/m/mariadb-10.6/

Thanks
Att
Glauber

[Glauber Alexandre Brossi da Cunha]

Allen,

We managed to configure the CS here on our DEV server.

We also made the adjustment with CrossEngine after giving an error

And the initial tests are already very interesting, I am currently doing a test with a table of 5 million records, soon I will evolve to the table of 60 million.

I wanted to know if there are specific CS variables\parameters that can be adjusted to obtain better performance?

Thanks
Att
Glauber

[Allen Herrera]

Hey Glauber,

Check /etc/columnstore/Columnstore.xml

There's only so much I can help here, maybe someone from the community can respond or you can find it online.
I recommend you reach out to sales for enterprise support for deeper expertise / support / triage and recommendations.

I do wish you best of luck in self exploration! 

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/d7255c02-a842-47bc-ad7f-dd2a76e94425n%40googlegroups.com.

[Frederick Lubbe]

Hi Glauber, et al,

I don't post here often, but in this case, I believe this could help you understand how CS works, and why it is so blistering fast with high-volume data.

We append load/insert and query a single table with 96 columns and 4,87 billion rows every 15 minutes. We have multiple InnoDB tables for receiving the streaming data from IoT devices and every 15 minutes move the previous 15 minutes data to the CS table. We have over 100 analytics users on our database and they do over 300,000 queries a week. Some queries are quite predictive and repeat queries with varying filters and conditions in the WHERE clause. Other queries are unpredictable and very random explorative in nature. With most database engines you will partition and index the tables for any possible query, but not always successful. With CS you don't need to do any of that. 

Understanding the internal workings of the CS storage engine and query optimizer is important. How and where you deploy the hosts and its dbroots is critical. If you have a clustered setup, be sure that the dbroots are not on the same storage subsystem as the other storage engines. CS is not that I/O heavy, but CPU heavy. Having more CPU's is better. Fast disk (SSD) does help but it will not be the main performance factor.

Please read the CS architecture documentation (https://mariadb.com/docs/server/architecture/components/enterprise-columnstore/) about segments, extents, extent elimination, extent maps and partitions. You have control over some of the parameters, but it needs to be configured at initiation. It is not something to change later on. To be honest, we kinda kept our installation vanilla. Then again we have top notch hardware (Nutanics on-premises) to run this on and we learnt a lot through trial and error.

Data types are also an important factor. Making every column VARCHAR/CHAR will only degrade performance. Any calculation or aggregation on FLOAT data types is an absolute no-no. Get the actual data type correct from the start by doing column profiling before you decide on the final table structure and data types.

If you can, insert/load the data in CS pre-ordered/pre-sorted - it helps speed up the bulk loads.  

And finally, user education is vital. They need to think 'columnar' data with every query they write, meaning no more SELECT * FROM... we find this type of query quite often:

SELECT A,B,C,D, SUM(E)

 FROM (SELECT * FROM TABLE_A) -- TABLE WITH 50 COLUMNS

GROUP BY A,B,C,D

This type of query for CS is no good. I am sure you can see why. But for MariaDB ISAM, Aria, or InnoDB, this is great because it was designed to retrieve entire rows of data, hence SELECT *. The best is to choose the exact fields you will need in the projection list.

Push it to your imaginable limits and then some more. Have fun!

F

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/CAKW43SgDFwAAWs8A93kWYA9qExN8cV%2BwxO6bjMb95qvyj9K%2Bdw%40mail.gmail.com.

[pantonis]

Hi  Glauber,

Why don't you use latest Community version 11.0.2?

Frederick if we have columns with string what is the best data type of storing them. I was told on another post to avoid using text for small text and instead using VARCHAR. 

I agree with Glauber. A performance guide regarding what needs to be changed initially for better performance would be highly valuable. New users of MariaDb CS like me and Glauber although you can read a lot of how MariaDb CS works still has hundreds of settings that it is impossible to go over those one by one.

Don't get me wrong guys but MariaDb CS looks promising, community helps a lot but a simple performance guide is a must. That is how you attract new users and you make sure that they use your product and adapt it. If I use it and it is slow because I didn't modify a setting. I would not spend time finding which setting is good and not without any guidance. I would try another columnstore solution 

Thank you

[Ferran Gil Cuesta]

+1 on having some kind of performance guide or some general settings for a few cases. Otherwise it is hard to know if the performance you're seeing is the expected one or not. 

Make sure you do NOT select *  but specific columns.
One important page that everybody should read when using ColumnStore is https://mariadb.com/kb/en/mariadb-columnstore-performance-concepts/ 

Ferran Gil (+34) 93-611-6048 fg...@g-n.com

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/bd5dffc4-5f97-4fb2-a86f-b93a770f2d8an%40googlegroups.com.

[pantonis]

I have already gone through that article.

and SELECT * I know is not the best practice. There are some specific case though that you need to select 90% of the columns.

I hope MariaDb Team creates a guide for that. This way its gonna help more devs evaluate correctly MariaDb CS before discarding it due to lack of documentation which unlocks the true potential.

[Glauber Alexandre Brossi da Cunha]

Good morning, thank you for your time.

I'm working with the infra team to set up a test server with the version you indicated, Community Server 11.0.2

With this version I will be able to use which version of ColumnStore?

ColumnStore 6
ColumnStore 22.08
ColumnStore 23.02

https://mariadb.com/docs/server/products/mariadb-enterprise-columnstore/

Today on my community server 10.6.7, I managed to install and I'm testing ColumnStore 6.2.3

Thank you very much
Att
Glauber

[Roland Noland]

Hi,

Community MDB 11.0.2 is shipped with MCS6.

Community MDB 11.1.1 is shipped with the latest stable MCS 23.02.

Regards,

Roman

пн, 3 июл. 2023 г., 16:51 Glauber Alexandre Brossi da Cunha <glaub...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/dbcb4454-26de-4cd5-aeaa-e04ef3bcc562n%40googlegroups.com.

[Glauber Alexandre Brossi da Cunha]

Good morning Roman,
Thank's for your time

Can I also use MCS 22.08.8 on Community MDB 11.0.2 as per the documentation below?

https://mariadb.com/docs/server/release-notes/mariadb-enterprise-columnstore-22-08/22-08-8/

As I am on MDB 10.6.7 with MCS 6.2.3, I would like to use the most current version of MCS as possible as we are planning to upgrade the MDB Server version.

Thank you very much
Att
Glauber

[drrtuy]

The notes you mentioned are for MariaDB Enterprise server and 22.08.8 is available with MariaDB Enterprise server subscription. JFYI the most recent Community MDB 11.1.1 has MCS 23.02.

Regards,

Roman

вторник, 4 июля 2023 г. в 16:35:54 UTC+3, Glauber Alexandre Brossi da Cunha:

[Glauber Alexandre Brossi da Cunha]

Thanks for the feedback

Is there any forecast for the release of the Stable version with MCS 23.02?

Att
Glauber

[drrtuy]

Unfortunately there are some issues that disallow us to backport 23.02 to the previous Community MDB releases so 23.02 and future versions will mature with MDB. I might be too optimistic but 11.1.3 might be already a stable one.

Regards,

Roman

среда, 2 августа 2023 г. в 17:00:22 UTC+3, Glauber Alexandre Brossi da Cunha:

[Glauber Alexandre Brossi da Cunha]

Thank you very much for the return

Great job to all of you

[drrtuy]

The next 11.1.2 release will be GA.

Regards,

Roman

вторник, 8 августа 2023 г. в 16:37:16 UTC+3, Glauber Alexandre Brossi da Cunha:

[pantonis]

Versioning in MariaDB is a nightmare. 

Hope now with the new versioning system it becomes much more straightforward...

[drrtuy]

The matching b/w MDB and MCS version is a nightmare indeed. It is hard to streamline the versioning though b/c it is almost impossible to sync MDB and MCS releases.

I was once thinking about an oracle to tell the actual mapping b/w these two.

Roman

среда, 9 августа 2023 г. в 18:21:55 UTC+3, pantonis: