Page 1 of 1
SetTimout to terminate the sql execution if takes too long
Posted: Sat Sep 01, 2012 3:42 am
by naidug
We are trying to find how to set timeout to the connection/Statement to prevent sql taking minutes/hours.
It looks like Vertica driver doesn't support setTimeout (sec), what is the alternate? Anyone solved the problem?
Vertica takes minutes/hours occassionally to execute a SQL causing to accumulate too many sqls in the queue causing outage to the tomcat.
We want to kill the execution if it takes more than 60 seconds.
We are using Vertica Analytic Database v5.0.4-0
We are using Vertica JDBC driver V4.1
Thanks in advance,
Naidug
Re: SetTimout to terminate the sql execution if takes too lo
Posted: Sat Sep 01, 2012 10:47 am
by JimKnicely
Hi naidug,
Welcome to the forums!
First thing I want to ask is do you really have SQL that is running for "minutes/hours"?!?! I have yet to experience a query that runs more than a minute in Vertica. However, we're running Cognos queries and those appear to take a long time to run because it takes forever to transfer millions of rows across the network from our Vertica cluster to the Cognos cube environment. If I run the queries locally in vsql they're super fast.
Anyway, check out the RUNTIMECAP parameter. Using it you can limit the amount of time a query can run...
For instance:
SET SESSION RUNTIMECAP '1 minute';
Re: SetTimout to terminate the sql execution if takes too lo
Posted: Sat Sep 01, 2012 8:36 pm
by scutter
Does a single SQL statement really block all other statements from executing? If yes - is that single SQL taking all of the memory in the resource pool, file handles, threads…
--Sharon
Re: SetTimout to terminate the sql execution if takes too lo
Posted: Fri Sep 07, 2012 9:07 pm
by naidug
knicely87 wrote:Hi naidug,
Welcome to the forums!
First thing I want to ask is do you really have SQL that is running for "minutes/hours"?!?! I have yet to experience a query that runs more than a minute in Vertica. However, we're running Cognos queries and those appear to take a long time to run because it takes forever to transfer millions of rows across the network from our Vertica cluster to the Cognos cube environment. If I run the queries locally in vsql they're super fast.
Anyway, check out the RUNTIMECAP parameter. Using it you can limit the amount of time a query can run...
For instance:
SET SESSION RUNTIMECAP '1 minute';
Thanks so much Jim, it worked perfect and able to use it.