standalone pool memory error (concurrent queries)

[dbmsuser7]

I have a huge table I do inserts into; the table is partitioned by a key that's basically YYYYMM, and the work is divided into tasks with each task corresponding to one partition.

I recently decided to speed things up by running four tasks (queries) in parallel, since we had enough RAM for it. So I increased the memorysize and maxmemorysize to 100G for this pool, and set plannedconcurrency and maxconcurrency to 4. I ran a test with real data and it was fine.

Last night, what seems to have happened is that by the time the fourth worker process asked for RAM, it was told there wasn't enough:

Code: Select all

ERROR 3587:  Insufficient resources to execute plan on pool reallybigtableonly [Timedout waiting for resource request: Request exceeds limits: Memory(KB) Exceeded: Requested = 24903680, Free = 21233663 (Limit = 99614720, Used = 78381057) (queueing threshold)]

Given how I read the documentation, I don't see how this could have happened. I would have thought that each of the four queries would each get 100/4 = 25G of RAM. But the fourth guy (I guess there was nominally some kind of race condition) came up short. How could that happen?

For what it's worth, I started reading this thread:
viewtopic.php?f=44&t=1441
but haven't had time to digest all of it.

[dbmsuser7]

For what it's worth, I followed the advice of scutter at viewtopic.php?f=44&t=1441&start=10#p4834:

Look in your vertica.log files for messages like "RESOURCE RESERVATION FAILURE". Those will include a dump of the memory usage by the each transaction/statement_id at the time of the resource acquisition failure. The lines with > are holding resources, the ones with Q are queued. It also shows the "overflow queries" that are borrowing memory from the general pool (perhaps all of your statements use the general pool anyway). It's a little tricky to interpret this the first time you work through it.

It seems that what's happening is that these resource hungry queries start out using "too much" memory in some sense, then get replanned with join spills. (Members of my team put in code in the INSERT to allow join spills a long time ago.) After the query is replanned, it's "behaved" in the sense that it uses no more RAM than the ostensible memory target, which from the document page "Target Memory Determination for Queries in Concurrent Environments" appears to be MEMORYSIZE/PLANNEDCONCURRENCY. What I don't quite understand is why there's this short window where the query is allowed to grab resources in excess of that target.

Addendum: I'm using v7.1.1-3.

[scutter]

The query budget is used for query planning purposes - for the allocation of memory to query plan operators. It doesn’t impose a limit on the amount of memory that the query uses. Some operations such as hash joins can use significant amounts of memory for building the in-memory hash table for the “inner relation”. It can use more and more memory until:

- the hash table is built
- the resource pool memory limit is hit
- the available memory is consumed
- the user’s memorycap is hit

If you want to constrain these INSERTs, set the memorycap for the user to 25G. The cap needs to be at least as high as the query budget.

You can also force ENABLE_JOIN_SPILL on with a query hint which avoids that first step of trying to fit the hash join’s hash table in memory. It goes straight to ENABLE_JOIN_SPILL’s algorithm - a sort-merge-join.

It may be worth reviewing if you can make this INSERT more efficient via a merge join.

—Sharon

[dbmsuser7]

Sharon,

Thanks for the very informative reply.

So the statement

Code: Select all

SELECT SET_VERTICA_OPTIONS('EE', 'ENABLE_JOIN_SPILL');

isn't enough?

And if I do a query hint, what's the syntax for multiple hints? We already use /*+ direct */.

[scutter]

That SET_VERTICA_OPTIONS() should do the trick. Worth verifying that the query is truly retrying. You can tell if you see it in the QUERY_REQUESTS output with consecutive transaction_id/statement_id values, or check the dc_requests_retried table.

I like add_vertica_options() better than set_vertica_options(). If you ever need to turn off a feature (post upgrade woes, whatever), set_vertica_options() will override any options that you already have in place. The add_vertica_options() will be additive.

You can include two hints by separating them with commas. Some discussion here:

https://community.dev.hp.com/t5/Vertica ... true#M6881

—Sharon

[dbmsuser7]

Thanks again.

The only reason why I'm claiming it retried is from what I saw in vertica.log. I'm pretty sure it grabbed more RAM than I wanted it to, then decided t replan, then settled down with a new plan and the RAM I had thought I'd been limiting it to.

I'll look into your suggestions---thanks for all the help.

[dbmsuser7]

It may be worth reviewing if you can make this INSERT more efficient via a merge join.

Do you mean by adding projections, so that the join column/key is sorted in both tables?