[Vertica][VJDBC](3587) ERROR: Insufficient resources to ...

Moderator: NorbertKrupa

kleysonr
Newbie
Newbie
Posts: 17
Joined: Thu Oct 10, 2013 2:09 pm

Re: [Vertica][VJDBC](3587) ERROR: Insufficient resources to

Post by kleysonr » Fri Oct 11, 2013 2:23 pm

I'm taking a stab here ...
Sorry for that :) ...

And sorry again to keep asking, but some things is not so clear to me yet.

Theoretically, what should happen if the RUNNING_QUERY_COUNT achieve PLANNEDCONCURRENCY ?

Is PLANNEDCONCURRENCY working like a constraint or new QUERIES should fall in some kind of queue waiting to be processed ?

Because what I realized is that PLANNEDCONCURRENCY is working like some kind of constraint. Once achieved by RUNNING_QUERY_COUNT all queries running stop to work until timeout raise an exception.

Kleyson Rios.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: [Vertica][VJDBC](3587) ERROR: Insufficient resources to

Post by scutter » Sat Oct 12, 2013 2:40 am

Hi Kleyson,

You can set the QUEUETIMEOUT of the resource pool to be longer if you would like the loads to wait for longer before timing out.

When you increase PLANNEDCONCURRENCY, you decrease the QUERY_BUDGET_KB that shows in RESOURCE_POOL_STATUS. The "query budget" is the amount of memory that a query will "target" using but it's not necessarily the amount that the query will use:

- simple queries without an ORDER BY or where the ORDER BY matches a projection's ORDER BY will use very little memory - they don't need to sort for example

- queries with "externalizing operators" - that is, operators that can spill to disk - sort, group by, hash join - will start with a default buffer size for all operators and will then split the remaining memory among the externalizing operators up to the "query budget"

- if the minimum buffer size for all operators exceeds the "query budget", then the query will use more than the "query budget".

Because simple queries use less memory, you can actually run more queries concurrently than PLANNEDCONCURRENCY. That's where MAXCONCURRENCY can be used to put an actual limit on the number of concurrent queries.

In the case of your COPY statements, it sounds like they are all using the "query budget".

Be aware that when you make PLANNEDCONCURRENCY higher, and thus the "query budget" smaller, you are forcing the sorts for COPY to get less memory, and they will have to spill more to disk, so they will be slower.

As already stated, Vertica wants 2GB of memory per core, and you're running with less than that. 16GB is really on the low end for a vertica cluster.

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

kleysonr
Newbie
Newbie
Posts: 17
Joined: Thu Oct 10, 2013 2:09 pm

Re: [Vertica][VJDBC](3587) ERROR: Insufficient resources to

Post by kleysonr » Tue Oct 15, 2013 2:07 pm

Hi Sharon,
In the case of your COPY statements, it sounds like they are all using the "query budget".
I don't know if my problem is exactly problem with QUERY_BUDGET.

As I said before, my process achieve a peak of 11 simultaneous queries. If I have PLANNEDCONCURRENCY = 10 (more QUERY_BUDGET_KB) I get the error, but If I have PLANNEDCONCURRENCY = 12 (less QUERY_BUDGET_KB) the process runs without problem. Even with less QUERY_BUDGET_KB the process is able to finish sucessfully, but, PLANNEDCONCURRENCY must be greater than 11 (peak of simultaneos queries).

Reading carefully your post, I thought the PLANNEDCONCURRENCY should be just an estimate, where I can get more or less simultaneous queries using the amount of memory defined in QUERY_BUDGET_KB, right ?

So, If I have unlimited MAXCONCURRENCY and PLANNEDCONCURRENCY is just an estimate, I could run out of memory.

So, I tried to set up PLANNEDCONCURRENCY = 10, MAXCONCURRENCY = 10 and EXECUTIONPARALLELISM = 1, to be sure that my limit of simultaneos queries would be only 10 and doesn't run out of memory.

But even using those configuration, I'm still getting the same error.

Kleyson Rios.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: [Vertica][VJDBC](3587) ERROR: Insufficient resources to

Post by scutter » Tue Oct 15, 2013 7:56 pm

How long does each of your loads take to complete, and what's the queuetimeout on the resource pool?

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

kleysonr
Newbie
Newbie
Posts: 17
Joined: Thu Oct 10, 2013 2:09 pm

Re: [Vertica][VJDBC](3587) ERROR: Insufficient resources to

Post by kleysonr » Wed Oct 16, 2013 12:24 pm

Hi,

Is not possible to know how long each load take to complete because I'm using Pentaho Kette to load the data, and Kettle runs its step in parallel when a new record achieve the step.

But, monitoring resource_pool_status table I can see that running_query_count changes frequently, I mean, queries start and end in a very short period of time, releasing slots for new ones.

So I don't believe that the queries take longer than 300 seconds.

For some reason, on the exact moment that 'running_query_count' get equal to 'planned_concurrency', everything stop to work !

queue_timeout_in_seconds = 300

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: [Vertica][VJDBC](3587) ERROR: Insufficient resources to

Post by scutter » Wed Oct 16, 2013 12:56 pm

You can verify how long each of the statements is actually taking by looking in the QUERY_REQUESTS system table. The original message you posted was a timeout. If you're still seeing the same message, then try increasing the queuetimeout to 600 or longer.

Btw - in an earlier post you had experimented with EXECUTIONPARALLELISM. Be sure to set that back to AUTO if you haven't already. There are use cases for changing that, but it's not necessary here.

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

kleysonr
Newbie
Newbie
Posts: 17
Joined: Thu Oct 10, 2013 2:09 pm

Re: [Vertica][VJDBC](3587) ERROR: Insufficient resources to

Post by kleysonr » Wed Oct 16, 2013 3:12 pm

Verifying QUERY_REQUEST system table, the biggest query takes about only 36s to run.

Code: Select all

select request_id, start_timestamp, end_timestamp, (end_timestamp - start_timestamp) as time, request_duration_ms, request from QUERY_REQUESTS where user_name='bisaude' order by request_duration_ms desc;

Code: Select all

request_id          | 3502
start_timestamp     | 2013-10-15 12:25:01.61092-03
end_timestamp       | 2013-10-15 12:25:37.54238-03
time                | 00:00:35.93146
request_duration_ms | 35932
request             | COPY dimensao.d_conta_bancaria ( dcba_id, dcba_codigo_banco, dcba_desc_banco, dcba_agencia, dcba_numero_conta ) FROM LOCAL STDIN NATIVE VARCHAR ENFORCELENGTH RETURNREJECTED AUTO NO COMMIT

Post Reply

Return to “Vertica Error Codes”