Using RMariaDB with Columnstore Tables

[Mosk915]

I am trying to use the RMariaDB package to query MariaDB columnstore tables. Queries that should take seconds are taking much longer.

For example a simple query like dbGetQuery(conn, "select count(*) from example_table"), where example_table has about 300 million rows, takes under a second using other IDEs or using the odbc R package. But when using RMariaDB it takes much longer.

Is there some setting or option that needs to be changed to make the RMariaDB package perform optimally with columnstore tables?

[krlmlr]

Thanks, this is really weird (and doesn't look like a duplicate of #131). Is this still a problem? Is the client doing any work (=full load on one or more CPU cores)? How can I replicate this?

[Mosk915]

Yes, still a problem. Our DBAs took a look at this and noticed that when queries are executed using RMariaDB, it is not multi-threading. They ran the vmstat command on the MariaDB host and saw that the CPU idle time only dips slightly below 100. When the queries are executed using the odbc package or through some other IDE, the idle time dips into the single digits.

As far as how to replicate this, I'm not sure. When you run a select count(*) on a columnstore table with several hundred million rows, does the result come back within seconds or take several minutes? We use RStudio Server Pro, but I've also tested this using RStudio on my local Windows machine and I have the same issue, which is why we are thinking it has something to do with the RMariaDB package. If you don't have the same issue, I'm hoping it's just a matter of us setting some configuration that allows the queries to multi-thread when they are executed.

[krlmlr]

IIUC, the database host has multiple CPUs, and only one is utilized by SELECT COUNT(*) when issued from RMariaDB.

According to https://mariadb.com/kb/en/mariadb-columnstore-performance-concepts/, SELECT COUNT(*) performs a scan over one column. This explains why multiple processors work faster on the task.

One thing RMariaDB does differently is the use of parametrized queries throughout. I have no evidence that this might be the actual reason, it's worth a try. Would you be able to run a source installation when #147 is implemented?

[Mosk915]

Yes I can run a source installation. Just let me know when.

Just to clarify, select count(*) was just a simple example of a query that is slower when using RmariaDB, but it's really any query run against columnstore tables that is slower. In order to reproduce an example using a more complicated query, you'd need tables of a sufficiently large size so that the query doesn't complete instantaneously. I'm not sure how to go about providing a reproducible example like that, but in the event you find a solution for select count(*) queries but not all queries in general, I can look into it more at that time.

[krlmlr]

Blocked by #147 which I had to postpone.