MELLOC -erros

[jpcavanaugh]

Can you enable join spill and try rerunning the query?

[adrian.oprea]

Ok i have added more datafile as temp space , but cannot find any parameter that holds the spill value!!!
How to do this !!

[jpcavanaugh]

This is just a test without seeing anything going on with your system so please only try this for testing purposes only (non-production).

select set_vertica_options('EE','ENABLE_JOIN_SPILL');

[adrian.oprea]

HY,
I don't know if this did the trick , because the query takes again a lot of time and my host memory is down to 15 mb, maybe because of the 95% ratio that the general pool is allowed to take!!
Now from i have read and i can understand Vertica when doing join (hash/merge/etc..) on 2 or more tables it stores them in memory , and if data wont fit it will "spill " the data on to disk-but performance is poor!!!!
And what this lead us to think is that Vertica is memory addict when tables are big (which they always are !! this is the work of Vertica).
Why this conclusion:
-after 2 days of "zero" improvement i decided to create a new ENV with the same data but different arch :
ENV 1 (2 node with 2 cpu each and 2 Gb memory) -- query time 770 sec
ENV 2 (single node 8 cpu and 8 Gb memeory)-- query time 420 sec
-where is the cluster help ? so is just the memory increase that will make Vertica run faster !! not good , or this is not what i have read in all those cases !! hhmm imagine Zinga with 20 TB table running it's DB on a let's say 6 GB mem on x nodes , impossible in this ENV.

I have a 3 tables :

Code: Select all

dbadmin=> select count(*) from ANT;
 count
-------
  6341
(1 row)
dbadmin=> select count(*) from BIL;
   count
-----------
 279313436
(1 row)
dbadmin=> select count(*) from SUB;
  count
----------
 19986818
(1 row)

And i will attach my explain plan as well !!!

[adrian.oprea]

Where can i find this options that i have posted here because in the documentation they are not ?

[jpcavanaugh]

I would suggest setting up a time to talk with your SE because this should work good without issues. BTW, the minimum is 2G of ram per core.

[adrian.oprea]

We are already doing so.
Thank you Joe for the help, but please give an opinion on my last post on this thread !since i want to understand better the way Vertica works !!
And i want to add that the documentation need to improve !
THX
i appreciate your effort