ERROR 8617: Request size too big (MaxParsedQuerySizeMB)

vasmez81

Hello!
I have a problem with selecting data from table which contains varchar (64000) records.

select start_timestamp, request, ... from projection_used where start_timestamp > '...'
ERROR 8617:  Request size too big
DETAIL:  Total allocated memory (MiB) = 1024.006 is about to exceed limit imposed by MaxParsedQuerySizeMB (MiB) = 1024.000

Column "request" has the type varchar (64000)

Every hour we save new records from query_requests and dc_projection_used to a regular table. And then we make extracts from the table for technical analysis.
Everything worked fine for over a year (9.0, 9.1), but after upgrading to 9.2 there was a problem.
If the incremental extract contains more than 200k-400k records, then this leads to problems with the MaxParsedQuerySizeMB value = 1 GB

I tried to increase the value, but with a large interval of data (29M records) this stops helping even with 16GB

Vertica version is v9.2.0-0 CE

Jim_Knicely

Every time you change the value of MaxParsedQuerySizeMB, only new sessions created afterwards will be affected.

vasmez81

Every time you change the value of MaxParsedQuerySizeMB, only new sessions created afterwards will be affected.

That's not the problem.
1. It's very likely that a vertica 9.2 has a problem with selecting rows which contains long values. In my case it's varchar(64000) and ~300k rows with MaxParsedQuerySizeMB = 1024
2. Why do I need to increase the size of MaxParsedQuerySizeMB for simple selects?
Table "projection_used" - is a regular non-system table, which contains history of v_internal.dc_projections_used

Jim_Knicely

Vertica 9.2 started checking the MaxParsedQuerySizeMB parameter which is why you did not get the error in 9.0 and 9.1.

You can disable this check by setting MaxParsedQuerySizeMB to 0.

dbadmin=> SELECT set_config_parameter('MaxParsedQuerySizeMB', 0);
    set_config_parameter
----------------------------
 Parameter set successfully
(1 row)

There is an open Jira ticket to get this issue addressed. I will keep this post updated when a fix is released!

arun_aiyadurai

we are also facing the same issue after 9.2.1-1, is the fix in 9.2.1-4, allows us to run our jobs with the same 1024MB or do we still have to tweak this parameter?

Bryan_H

Hi, unfortunately this has not been fixed for 9.2.1-4. The release notes will indicate when this fix is ready.

LenoyJ

https://www.vertica.com/docs/ReleaseNotes/9.2.x/Vertica_9.2.x_Release_Notes.htm#9.2.1-4
 
@Bryan_H, VER-68111?

arun_aiyadurai

If it not fixed, could you please tell me what is downside of disabling this parameter?

Bryan_H

Hi @arun_aiyadurai, I was thinking of a different issue here. @LenoyJ is correct that this issue about "Request size too big" for large result sets is fixed in 9.2.1-4 so please upgrade and test. However, there is still another issue around this setting that could affect complex queries.
The downside of disabling this parameter is possibility of OOM in case of very complex query or limited memory on the node.

arun_aiyadurai

Thanks @Bryan_H , Could you please provide me more details about the other issue. So we can check on our side whether are we hitting the same problem.

Alim

Hi All,
@Jim_Knicely --Thank you so much by below step provided by you issue has been resolved .
Vertica 9.2 started checking the MaxParsedQuerySizeMB parameter which is why you did not get the error in 9.0 and 9.1.

You can disable this check by setting MaxParsedQuerySizeMB to 0.

dbadmin=> SELECT set_config_parameter('MaxParsedQuerySizeMB', 0);

set_config_parameter

Parameter set successfully

Thanks ,
Alim Shaikh