Implement automatic query kill functionality

Bug #671227 reported by Peter Zaitsev on 2010-11-05
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Invalid
Wishlist
Unassigned

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.

Related branches

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) wrote :

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: +1 888 401 3401 ext 501 Skype: peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

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

Baron Schwartz (baron-xaprb) wrote :

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.

Percona (percona-team) on 2010-11-25
Changed in percona-server:
status: New → Confirmed
importance: Undecided → Wishlist
Stewart Smith (stewart) on 2011-05-25
Changed in percona-server:
status: Confirmed → Triaged
Changed in percona-server:
status: Triaged → Confirmed
Stewart Smith (stewart) on 2012-06-25
Changed in percona-server:
status: Confirmed → Triaged
Alexey Kopytov (akopytov) wrote :

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

Changed in percona-server:
status: Triaged → Invalid

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers