Testing issue / -not enought host memory to execute the query


#1

We are testing Mapd community edition on Amazon Ec2 p3.8xlarge instance with Nvidia Tesla V100 GPU. When we was executing query like:
SELECT col1, COUNT(), COUNT(distinct col2), COUNT(distinct col3) FROM cdrs WHERE (col4=‘value1’ OR col4=‘value2’) AND col3 = value4’ AND col5=‘value3’ GROUP BY col1 HAVING COUNT() > 100 ORDER BY 2 DESC LIMIT 200;
We got an error: Exception: Not enough host memory to execute the query
This error happens if disk size of database is 1,4Gb or 32Gb. It happens also if we use 4 Tesla V100 GPUs.

We also get error ‘Exception: Sorting the result would be too slow’ when we execute query:
SELECT col1, SUM(col2*1) FROM cdrs WHERE col3=‘value1’ AND col4=‘value2’ GROUP BY col1 ORDER BY 2 DESC;

Any idea what is going wrong?

Bostjan


#2

likely the watchdog is blocking the first query because the count distinct would allocate too much memory; if my hypothesis is correct the cause of the problem are the maximum values ​​contained in the fields.

another user got more or less the same problem in this topic

in the second one the watchdog is blocking becuse of performance; you can disable it changing mapd.conf configuration addin enable-watchdog= false and restarting mapd server
if you just need to get top-N results add limit N to your query, because the code path is different and very fast


#3

Hi @bostjan,

In addition to disabling watchdog as @aznable suggested, one other thing you may want to try is replacing those count distinct clauses with approx_count_distinct, as it will scale much better for high cardinality columns (i.e. many distinct values). You can find it documented here.

Let us know how if that makes the query run.

Regards


#4

Hi @darwin

Query which returns exception ‘Sorting the result would be too slow’ is executed when I disabled watchdog.
But query with exception ‘Not enough host memory to execute the query’ was not executed with disabling watchdog.
I tried to use ‘APPROX_COUNT_DISTINCT(x,10)’ and query was executed. But I am not sure that approx count is good enough for my case.
There is no other way to let MapD to use more memory in case of COUNT(DISTINCT x)?
I see that mapd_server consumes 170/224Gb durring query execution and 3/16Gb GPU RAM on each of 4 GPUs before it returns exception. So there is still some host memory left.

Regards


#5

Oh, yeah, with @bostjan we work on same project.

Regards


#6

Hi @dprelc,

you could try to use workaround i suggested to another user in the topic i linked in the first reply, or you can rewrite the query (if you are allowed to)

SELECT col1, sum(number_or_records), COUNT(distinct col2), COUNT(distinct col3) FROM
(select col1,count() number_or_records,col2,col3 from cdrs WHERE (col4=‘value1’ OR col4=‘value2’) AND col3 = value4’ AND col5=‘value3’ group by 1,3,4)
GROUP BY col1
HAVING sum(number_or_records) > 100 ORDER BY 2 DESC LIMIT 200;

the problem is a mix of cardinality of col1 and the max values of col2 or col3


#7

Hi @aznable

Thanks for your effort, but also this solution didn’t work for me. I still get same error.

Regards