Presto query error : Query exceeded max memory size of 148 GB

Presto
Enthusiast

Presto query error : Query exceeded max memory size of 148 GB

Hi All,

We are connecting from Teradata to Hadoop. Teradata DB version is 15.10.02.06e and Presto version is:148t.

Ww have defined views which points to Hadoop. When we are trying to query those views, we are getting below error:

"[7810][TblOp][ImportExrcute]:Presto query failed (#20170412_101039_2g2tf): Query exceeded max memory size of 148GB"

The query I'm using is below:

SELECT COUNT(*) FROM <Presto View Name based on Hive Table>;

1. Is the any limitation for max memory size for Presto or this can be configured?

2. If this can be configured, where should I look for the parameter and which parameter?

 

Please let me know if any other details are required.

Any help in this regard will be appretiated.

 

Thanks & Regards,

Arpan.

10 REPLIES
Teradata Employee

Re: Presto query error : Query exceeded max memory size of 148 GB

Hi,

Presto memory limit per query can be configured using query.max-memory server property.

This is the limit that you are most likely exceeding.

There is also per node, per query limit that can be configured using query.max-memory-per-node.

You can set this in your presto configuration on each node manually (by default in /etc/presto/config.properties) and then restart your cluster.

If you are using presto admin you can change that in presto admin configurations, in both coordinator/config.properties and workers/config.properties and then use following commands:

$ ./presto-admin server stop

$ ./presto-admin configuration deploy

$ ./presto-admin server start

to deploy configurations and restart Presto.

 

Additionally you can set both of those limit on session basis without reconfiguring server by using query_max_memory and query_max_memory_per_node session properties. You can only set that to values lower then global server config that way, though.

You can set those by executing queries like:

SET SESSION query_max_memory=200GB;

SET SESSION query_max_memory_per_node=50GB;

 

Be careful though as setting those value to close to the actual available memory on cluster may cause Presto to behave in unstable manner.

 

More about configuration options including those mentioned in this e-mail can be found in this chapter of the doc:

https://teradata.github.io/presto/docs/current/admin/properties.html

 

Also, please note that memory available for presto server is not only limited by system resources, but also by -Xmx flag of jvm, which can be changed in jvm.config file in /etc/presto or in prestoadmin coodrdinator/ and workers/ directories.

 

I hope that helps,

Andrzej

Enthusiast

Re: Presto query error : Query exceeded max memory size of 148 GB

Hi Andrzej, Thank you very much for quick and helpfull response.

I'm trying to run the query from BTEQ prompt (from a bteq script). 

To set query_max_memory for the session, should I run 

".SET SESSION query_max_memory=200GB;" from the bteq prompt or this needs to be run from presto server?

When I tried to run .SET SESSION query_max_memory=200GB; from bteq prompt, I got below error:

-sh-4.1$ bteq

 Teradata BTEQ 14.10.00.00 for LINUX.
 Copyright 1984-2013, Teradata Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.logmech LDAP;

.logmech LDAP;
 Teradata BTEQ 14.10.00.00 for LINUX. Enter your logon or BTEQ command:
.logon <tdpid>/<userid>

.logon  <tdpid>/<userid>
Password:


 *** Logon successfully completed.
 *** Teradata Database Release is 15.10.04.02
 *** Teradata Database Version is 15.10.04.02j
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:
.SET SESSION query_max_memory=200GB;

.SET SESSION query_max_memory=200GB;
 *** Error: Unrecognized SET command QUERY_MAX_MEMORY
 BTEQ -- Enter your SQL request or BTEQ command:

Requesting your help in this regards.

 

Thanks & Regards,

Arpan.

Teradata Employee

Re: Presto query error : Query exceeded max memory size of 148 GB

It seems like there is also hidden limitation I wasn't aware of, that you can effectively only modify query_max_memory to values smaller than per server config so increasing this limit on per session basis is not allowed anyway, sorry for the confusion, I modified my previous answer acordingly. So in your case you probably will have to modify global Presto server configuration.

As for setting session properties from bteq level, when you have forign server added for T2P using Query Grid, you can probably use following syntax to call queries directly on Presto and setup session property that way.

SELECT *
FROM FOREIGN TABLE(
   SET SESSION ...
)@presto_server AS presto_query

you can check if this worked properly by using 

SELECT *
FROM FOREIGN TABLE(
   SHOW SESSION
)@presto_server AS presto_query

 

Teradata Employee

Re: Presto query error : Query exceeded max memory size of 148 GB

Hi Arpan,

 

Please consult QueryGrid 2.0 documentation on how to change the session-level Presto properties from the Teradata side.

 

Thanks,

Kamil

 

Enthusiast

Re: Presto query error : Query exceeded max memory size of 148 GB

Hi Kamil,

Thanks for your response. I'm not able to find the document which describe how to change the session-level Presto properties from the Teradata side. Can you please share the link which document I should refer?

 

Thanks & Regards,

Arpan.

Teradata Employee

Re: Presto query error : Query exceeded max memory size of 148 GB

Arpan,

 

Could you please confirm you use QG 2.0? We highly recommend that.

The docs are here: http://www.info.teradata.com/doclist.cfm?Prod=1101&ProdName=QueryGrid

 

Thanks,

Kamil

Teradata Employee

Re: Presto query error : Query exceeded max memory size of 148 GB

Are you using QueryGrid 1.0 T2P or are you using QueryGrid 2.0? From what I was able to gather from the thread this appears to be a QG 1.0 installation? 

Enthusiast

Re: Presto query error : Query exceeded max memory size of 148 GB

Hi Kamil,

Currently we are using QG 1.2, but we have a plan to upgrade the same to 2.0. This upgrade will be done recently. Presto version is .148t.

Enthusiast

Re: Presto query error : Query exceeded max memory size of 148 GB

Hi,

We are using QG 1.2 and Presto .148t. We are trying to query Hive table. We have created view using FORAIGN TABLE syntax in Teradata side and we are trying to use the same view in BTEQ script. While doing that, we are getting error " Query exceeds max memory size of 148GB". In Prosto config file, we have below values:

coordinator=true
discovery-server.enabled=true
discovery.uri=http://<Presto Server>:8080
http-server.http.port=8080
node-scheduler.include-coordinator=false
query.max-memory-per-node=2.5GB
query.max-memory=148GB
task.concurrency=8

Now is there any way we can overwrite this 148 GB value for the BTEQ session? If yes, the  how? That's what we are trying to find out.

 

Thanks & Regards,

Arpan.