MariaDB ColumnStore With MEMORY Storage Engine

[Shashank Patel]

Hi,

I am working on in-memory database solution. I have come to know with MariaDB Memory engine is good solution for in-memory. I have try to implement this solution as instructed in (https://mariadb.com/kb/en/mariadb/memory-storage-engine/) but I am facing issue.

The issue is it is taking more time than disk base database. My disk base databse is taking 2.99 Sec for one query same thing is taking 1 min 42 sec in Memory engine.

I am not understanding what is wrong in my implementation.

https://mariadb.com/kb/en/mariadb/performance-of-memory-tables/

I have set max_heap_table_size to 10 GB

and created data base with engine as MEMORY

CREATE TABLE `XXXXXXXXX` (

) ENGINE=MEMORY DEFAULT CHARSET=utf8

can someone help on this?

[Dipti Joshi]

Hello Shashank:

What are your trying to do with MariaDB ColumnStore ?

Regards,

Dipti

Director Product Management

E: dipti...@mariadb.com

Twitter: dipti_smg

--
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-columnstore+unsub...@googlegroups.com.
To post to this group, send email to mariadb-columnstore@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/6886c321-57d6-4f55-9863-5a045c9fb01e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Shashank Patel]

Hi Dipti,

I want to improve my query performance. Current scenario is I am using tables with "InfiniDB" engine that is giving me result in 2.99 sec with 4GB of data in main table along with joining 2-3 child tables. As I was reading performance improvement blogs I come to know about "MEMORY" engine whicch loads data in RAM. As far as I know reading data from RAM is less time consuming than reading data from harddisk but in my case It is reverse It is taking 50 times more time. I want to know where I am wrong in implementation or understanding?

Thank you for responding to my post.

Thanks,

Shashank P.

[David Thompson]

Hi Shashank,

ColumnStore will cache the disk blocks in memory in the PM with LRU eviction. An easy way to test for this is to run the same query twice. The second time the blocks should be in memory and performance faster. You can also confirm this with calling select calGetStats(); after the query and checking for 0 physical I/O.

 

It might help to understand what you are trying to achieve and what you are trying to join?

 

Using the memory engine could be valuable for temporarily caching intermediate results for example.

--

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 post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/47438a99-49ff-4827-85f3-ab309b099575%40googlegroups.com.

[Shashank Patel]

Hello David,

Thank you for your knowledge sharing.

Can you please elaborate more on MEMORY engine? How can i implement it and does that helpful to improve performance?

Thank You.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[Andrew Hutchings]

Hi Shashank,

Sorry, I thought I had sent this a few days ago but I couldn't find it
in the list:

The different engines are optimised for different use cases. ColumnStore
(which is a fork of InfiniDB and it is what is used when you request the
InfiniDB engine) is designed for complex analytical queries and doesn't
use indexing. MEMORY engine is designed for basic queries and requires
the use of indexes to get any good performance out of it.

Although ColumnStore uses an on-disk storage it will use a large portion
of available RAM to cache data so that it doesn't have to hit the disk
often.

Both engines store data in a very different way, MEMORY stores it in
complete rows whereas ColumnStore stores it in columns. This means that
the way the two filter and access data is very different.

There are a number of resources available to learn more about the
engines that come with MariaDB and how to use them available at:
https://mariadb.org/learn/

Kind Regards
Andrew

On 26/04/17 06:21, Shashank Patel wrote:
> Hello David,
>
> Thank you for your knowledge sharing.
>
> Can you please elaborate more on MEMORY engine? How can i implement it
> and does that helpful to improve performance?
>
>
> Thank You.
>
> On Wednesday, April 26, 2017 at 6:23:36 AM UTC+5:30, David Thompson wrote:
>
> Hi Shashank,
>
> ColumnStore will cache the disk blocks in memory in the PM with LRU
> eviction. An easy way to test for this is to run the same query
> twice. The second time the blocks should be in memory and
> performance faster. You can also confirm this with calling select
> calGetStats(); after the query and checking for 0 physical I/O.
>
>
>
> It might help to understand what you are trying to achieve and what
> you are trying to join?
>
>
>
> Using the memory engine could be valuable for temporarily caching
> intermediate results for example.
>
>
>

> *From:*mariadb-c...@googlegroups.com <javascript:>
> [mailto:mariadb-c...@googlegroups.com <javascript:>] *On Behalf Of
> *Shashank Patel
> *Sent:* Monday, April 24, 2017 10:04 PM
> *To:* MariaDB ColumnStore <mariadb-c...@googlegroups.com <javascript:>>
> *Cc:* shasha...@gmail.com <javascript:>
> *Subject:* Re: MariaDB ColumnStore With MEMORY Storage Engine

> <https://mariadb.com/kb/en/mariadb/memory-storage-engine/>)

> but I am facing issue.
>
>
>
> The issue is it is taking more time than disk base database.
> My disk base databse is taking 2.99 Sec for one query same
> thing is taking 1 min 42 sec in Memory engine.
>
> I am not understanding what is wrong in my implementation.
>
>
>
> https://mariadb.com/kb/en/mariadb/performance-of-memory-tables/
> <https://mariadb.com/kb/en/mariadb/performance-of-memory-tables/>
>
>
>
> I have set max_heap_table_size to 10 GB
>
> and created data base with engine as MEMORY
>
> CREATE TABLE `XXXXXXXXX` (
>
>
>
> ) ENGINE=MEMORY DEFAULT CHARSET=utf8
>
>
>
>
>
> can someone help on this?
>
>
>
> --
> 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
> <javascript:>.

> To post to this group, send email to mariadb-c...@googlegroups.com

> <javascript:>.

> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mariadb-columnstore/47438a99-49ff-4827-85f3-ab309b099575%40googlegroups.com

> <https://groups.google.com/d/msgid/mariadb-columnstore/47438a99-49ff-4827-85f3-ab309b099575%40googlegroups.com?utm_medium=email&utm_source=footer>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> 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
> <mailto:mariadb-columns...@googlegroups.com>.

> To post to this group, send email to
> mariadb-c...@googlegroups.com

> <mailto:mariadb-c...@googlegroups.com>.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/mariadb-columnstore/d20ceb18-54e6-4c4b-8d8c-2616d7a7ab65%40googlegroups.com
> <https://groups.google.com/d/msgid/mariadb-columnstore/d20ceb18-54e6-4c4b-8d8c-2616d7a7ab65%40googlegroups.com?utm_medium=email&utm_source=footer>.

> For more options, visit https://groups.google.com/d/optout.

--
Andrew Hutchings (LinuxJedi)
Senior Software Engineer, MariaDB

[Shashank Patel]

Hi Andrew,

Thank you Very much for clearing concepts about engines. Now I can understood why MEMORY engine is taking more time in my case.

Thanks.

>     send an email to mariadb-columnstore+unsub...@googlegroups.com

>     <javascript:>.
>     To post to this group, send email to mariadb-c...@googlegroups.com
>     <javascript:>.
>     To view this discussion on the web visit
>     https://groups.google.com/d/msgid/mariadb-columnstore/47438a99-49ff-4827-85f3-ab309b099575%40googlegroups.com
>     <https://groups.google.com/d/msgid/mariadb-columnstore/47438a99-49ff-4827-85f3-ab309b099575%40googlegroups.com?utm_medium=email&utm_source=footer>.
>     For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
> --
> 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-columnstore+unsub...@googlegroups.com
> <mailto:mariadb-columnstore+unsub...@googlegroups.com>.