Error IDB-2001 happening even with memory available and AllowDiskBasedJoin enabled.

[Vinicius Yosiura]

Hi everyone,

I'm running a MariaDB Columnstore 1.1.4 with 1 UM and 4 PM's on Red Hat 7.5 and frequently some of my queries get the error "IDB-2001: Join or subselect exceeds memory limit".  

This is the configuration of my each node:

• UM 
• 185GB RAM 
• Each PM 
• 68GB RAM

After a monitoring the server for a while, I could see that most of the time the error happened when I was out of memory in the UM. 

Looking at the documentation (https://mariadb.com/kb/en/library/columnstore-disk-based-joins/ ) it says in some cases is advised to enable the "AlowDiskBasedJoin" configuration, and even after the disk based join was enabled I still got this errors, though less often than before.  

The tmp directory has 800GB and has never ran out of space due to the tmp files generated by Columnstore. 

After I did everything described above, I was monitoring one of the moments that the error happened and here is the output of the "ma GetSystemMemory":

Module  Mem Total  Mem Used  Cache    Mem Usage %  Swap Total  Swap Used  Swap Usage %  

------  ---------  --------  -------  -----------  ----------  ---------  ------------  

um1     194431196  46524920  17889324     23       0           0               0      

pm1     71867816   55221472  7042444      76       0           0               0      

pm2     71867816   55065068  5891540      76       0           0               0      

pm3     71867816   55052176  5835088      76       0           0               0      

pm4     71867816   55121244  6006940      76       0           0               0      

Can someone went through a similar problem? 

Thanks

[Kain Bokz]

Have you tried to use 1.2.2 instead of 1.1.4?

[Roland Noland]

Greetings,

The first thing to see is an output of calgettrace[1] to check for actual disk based join steps taken.

1. https://mariadb.com/kb/en/library/analyzing-queries-in-columnstore/#query-plan-trace

Regards, 

Roman

ср, 5 дек. 2018 г., 20:14 Vinicius Yosiura vinicius...@performancedb.com.br:

--
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/0d312275-0765-4b96-9e21-67e8e5387225%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vinicius Yosiura]

Hi Kain,

I'm looking forward to it, actually, I have already started to test 1.2.2 as soon as it became available, but I didn't have time trying to replicate the errors on it. 
I'll try to do it and show the results here.

Thanks!