[teiid-issues] [JBoss JIRA] Reopened: (TEIID-1596) Document queryThresholdInSecs and allow functionality to ensure user queries take no longer than a specified interval

[Graeme Gillies (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1596?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Graeme Gillies reopened TEIID-1596:
-----------------------------------



Hi,

I'd like to re-open this issue, having thinking more about it I have a better set of requirements.

At the moment, we need to ensure that for any user querying teiid, their query cannot run longer than 180 seconds. This is to keep load low on our database server slaves (which teiid points to) and ensure any big queries don't stall replication.

How we achieve this at the moment is, in each datasource we setup in jboss to point to real databases, we set
{code}
<query-timeout>180</query-timeout>
{code}
In the datasource xml. This works, but is kind of ugly, as it closes the connection out from under teiid (which throws a big exception, which isn't exactly clear to anyone what exactly happened), and it seems to cause some problems as well with the connection not being returned to the connection pool cleanly (though this may be another issue altogether).

What we would like, is some sort of configuration option like maxSourceRows where we can set in teiid itself, the maximum amount of time a query is allowed to execute for before killing it cleanly in teiid, and throwing back a teiid exception with a message like "Query took longer than X seconds to execute" or similar.

Let me know if you require any further information or clarification.

Regards,

Graeme

> Document queryThresholdInSecs and allow functionality to ensure user queries take no longer than a specified interval
> ---------------------------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-1596
>                 URL: https://issues.jboss.org/browse/TEIID-1596
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Server
>    Affects Versions: 7.4
>            Reporter: Graeme Gillies
>            Assignee: Steven Hawkins
>
> In the teiid documentation there is no mention (from what I can find) of exactly what the option queryThresholdInSecs does. The xml file states "Long running query threshold, after which a alert can be generated by tooling if configured", what tooling is this exactly? Does it alert and stop the query, or just alert?
> On the same token, there is the configuration item maxSourceRows which is configured per instance. Unfortunately this isn't flexible enough sometimes, and it would be better if this option could be configured per VDB and per data role as well (some people can get more data than others).
> I would expect the determination of what maxSourceRows is for a query should be as follows
> 1) If maxSourceRows is set for the data role the user has, use that (in the case of two data roles with different values, use the largest value)
> 2) If maxSourceRows is not set on any of the users data role, but set on the vdb, use that value
> 3) If maxSourceRows is not set on the vdb or any data roles the user has, use the instance setting.
> Likewise, the setting maxSourceRows should not only be configurable per data role, vdb, instance, but also the setting queryThresholdInSecs, and either that option or another option should be put in place that allows us to hard limit the length of time a query can run. This is because in teiid we want to be able to say users cannot write a big huge query that will take more than X amount of seconds to execute, to stop someone in teiid giving it a huge query and hammering all the backend databases. Configuring this option by vdb and data role (as some people are more trusted and can run longer queries) would be fantastic.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira