Implement automatic query kill functionality

[Peter Zaitsev]

Implement some functionality to limit query execution time, it could be

SELECT ... SQL_TIMEOUT=60 .... in which query will be killed in 60 seconds.

It would be also helpful to have max_query_time or similar variable so queries can be killed automatically.
This can be set as per session for interactive web sessions which know they should not be doing long queries.

We may consider implementing this as user privilege to so we can restrict users from doing very long queries,
this would be good for hosting providers which have to prevent users from running very long queries and killing
system.

[Sasha Pachev (sasha-pachev)]

On Thu, Nov 4, 2010 at 6:51 PM, Peter Zaitsev <email address hidden> wrote:
> Public bug reported:
>
> Implement some functionality to limit query execution time, it could be
>
> SELECT ... SQL_TIMEOUT=60  ....  in which query will be killed in 60
> seconds.
>
> It would be also helpful to have    max_query_time or similar variable so queries can be killed automatically.
> This can be set as per session for interactive web sessions which know they should not be doing long queries.
>
> We may consider implementing this as user privilege to so we can restrict users from doing very long queries,
> this would be good for hosting providers which have to prevent users from running very long queries and killing
> system.

Peter:

Is there any benefit to SELECT ... SQL_TIMEOUT vs SET max_query_time
60 ; SELECT ; SET max_query_time = 0 ?

If we skip SQL_TIMEOUT=60 we avoid have to maintain a parser patch.

[Peter Zaitsev (pz-percona)]

Sasha,

Yes. I think it is critical. It is easy to modify one query but it is hard
to inject couple of more
also (unless you use multi statement execution API) you're looking at
increasing number of round trips
which is very bad.

On Mon, Nov 8, 2010 at 10:47 AM, Sasha Pachev <email address hidden>wrote:

> On Thu, Nov 4, 2010 at 6:51 PM, Peter Zaitsev <email address hidden> wrote:
> > Public bug reported:
> >
> > Implement some functionality to limit query execution time, it could be
> >
> > SELECT ... SQL_TIMEOUT=60 .... in which query will be killed in 60
> > seconds.
> >
> > It would be also helpful to have max_query_time or similar variable so
> queries can be killed automatically.
> > This can be set as per session for interactive web sessions which know
> they should not be doing long queries.
> >
> > We may consider implementing this as user privilege to so we can restrict
> users from doing very long queries,
> > this would be good for hosting providers which have to prevent users from
> running very long queries and killing
> > system.
>
> Peter:
>
> Is there any benefit to SELECT ... SQL_TIMEOUT vs SET max_query_time
> 60 ; SELECT ; SET max_query_time = 0 ?
>
> If we skip SQL_TIMEOUT=60 we avoid have to maintain a parser patch.
>
> --
> Implement automatic query kill functionality
> https://bugs.launchpad.net/bugs/671227
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in Percona Server with XtraDB: New
>
> Bug description:
> Implement some functionality to limit query execution time, it could be
>
> SELECT ... SQL_TIMEOUT=60 .... in which query will be killed in 60
> seconds.
>
> It would be also helpful to have max_query_time or similar variable so
> queries can be killed automatically.
> This can be set as per session for interactive web sessions which know they
> should not be doing long queries.
>
> We may consider implementing this as user privilege to so we can restrict
> users from doing very long queries,
> this would be good for hosting providers which have to prevent users from
> running very long queries and killing
> system.
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/percona-server/+bug/671227/+subscribe
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: [phone] ext 501 Skype: peter_zaitsev
24/7 Emergency Line [phone] ext 911

Percona Training Workshops
http://www.percona.com/training/

[Baron Schwartz (baron-xaprb)]

Maybe we can solve this in a way that will solve future problems too.
Instead of adding specific syntax for this, we might do a generic hint
mechanism, like a magic comment:

SELECT /*? key="timeout" val="60" */ col1, col2.............;

Then the key-value pairs could be available in a hash in the THD, and
anything in the server could look at them as desired. Then instead of
a syntax change to extend it further in the future, it's just a matter
of a convention that has to be documented.

I hate things like this, but I like it a lot better than adding more
and more stuff to the parser, configuration variables, SQL_MODE (ugh),
and so on.

[Alexey Kopytov (akopytov)]

Closing the bug as invalid as there's a blueprint covering this feature request: https://blueprints.launchpad.net/percona-server/+spec/port-max-statement-time

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2329