Why some queries exceed time cap set?

Greg_Starosciak

We set parameter to limit execution time for queries to 30 seconds. In error_messages we can observe, that it works - message "Execution time exceeded run time cap of 00:00:30". But measuring execution time for queries shows that some of them exceed this given time. Some queries can last even few minutes. Why it is like this? How to limit it permanently?

Shobhit_Garg

Hi,

Did you mean that error_messages shows "Execution time exceeded run time cap of 00:00:30" but actually that query is running for more than 30 seconds cap?.
How the execution time of the queries being measured.

Also RUNTIMECAP parameter can be set at session level or user level or the resource pool. At which level you have set this parameter.
If it is at session level, then as soon as you exit/end the session, the RUNTIMECAP parameter value will be reset to the actual value for that user set by the super user.

Thanks

Greg_Starosciak

Time cap is set on user level to 30 seconds. In table v_monitor.query_requests I see that some queries are marked with error_count=1 and request_duration_ms > 30000 ms, success=False. Same event is logged in v_monitor.error_messages with message "Execution time exceeded run time cap of 00:00:30". I understand situation if execution time for killed queries is few ms longer than 30s - it is time which Vertica needs to kill this query. Usually it is few seconds, but we observed queries with limit 30s, which lasted even for 180 seconds (during heavy load).

colin_loghin

There are situations in which query run time cap is not effective. (1) if the server has already started returning results and the client is an application that manually scrolls or otherwise does not consume the result set, then the query would continue to run in blocking mode until client consumes the entire result set or cancels. (2) Unexplained cases in which the runtime cap is not honored. For that matter,we have put a regular crontab script in place that monitors sessions table statement_start parameter and performs an interrupt_statement/close_session operation to release resources and force the interval. For a 30 seconds cutoff this would be too much, but when used over something greater than 5 min, it is practical.

jmussom

Hello,

Taking into consideration the scenario (1) "if the server has already started returning results and the client is an application that manually scrolls or otherwise does not consume the result set, then the query would continue to run in blocking mode until client consumes the entire result set or cancels." It is still happening in version 9.1.x

is there any way to cancel automatically this queries taking so long? In spite of I defined the runtimecap for the resource pool to 30minutes, we are having few queries running for many days, for the moment I am canceling them manually...

SruthiA

Does the resource pool where you are running the query has a cascade to option set? Is the query cascading to secondary pool and running there? The reason I ask this is because when runtime cap exceeds and secondary pool is set using cascade option, then the query executes on that pool