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:
"[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,
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:
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,
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,
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
Please consult QueryGrid 2.0 documentation on how to change the session-level Presto properties from the Teradata side.
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,
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
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?
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.
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,