Proposed change to MAX_STATEMENT_TIME patch

Showing 1-10 of 10 messages
Proposed change to MAX_STATEMENT_TIME patch Chip Turner 4/7/14 2:01 PM
It seems Oracle is taking the MAX_STATEMENT_TIME patch for 5.7, which is awesome, but I have a proposal for changing the syntax before it becomes baked into the upstream version.  For context, currently you can't set a time limit on the time the server will spend executing a query.  This patch allows it, via either a session variable (MAX_STATEMENT_TIME) or as part of a query.  Specifically, you can do:

SELECT MAX_STATEMENT_TIME = 90 * FROM foo;

and it sets MAX_STATEMENT_TIME to 90, just for that query.

When I first saw the syntax, I was confused by the '90 * FROM' -- it didn't feel right.  The rest of the syntax just didn't feel natural to me overall, so after thinking more about it, and discussing with other mysql people here, a consensus emerged that we might want to change it before it's baked into upstream.  I can see why it isn't a hint (it's reasonable to want a hard failure if a timeout can't be enforced, say, because the server isn't configured)

These discussions brought two ideas:

Add a RESOURCE LIMIT clause to SELECT, allowing something like:

SELECT * FROM Foo WITH RESOURCE LIMIT MAX_STATEMENT_TIME = 90;

or, more general, add "for this statement only" a session variable:

SELECT * FROM Foo WITH SESSION MAX_STATEMENT_TIME =90;

Currently, I believe "WITH" only is used for "WITH ROLLUP" and shouldn't cause any grammar problems.  This gives us a more extensible syntax for future uses while being easier to understand when reading a query.

I also think the name would be clearer as "MAX_STATEMENT_MILLISECONDS" -- we've found, very often, it's much simpler when configuration options (names in config files, command line parameters, etc) have the unit they expect as part of the name.  It eliminates all ambiguity, especially since MySQL itself tends to prefer seconds for most units.

Thoughts?

--
Chip Turner - ctu...@pattern.net
Re: Proposed change to MAX_STATEMENT_TIME patch Laurynas Biveinis 4/7/14 2:32 PM
FWIW Percona Server went with the option of a "for this statement
only" variable support the way it was suggested in
http://dev.mysql.com/worklog/task/?id=681:

SET STATEMENT max_statement_time=1000 FOR SELECT name FROM name ORDER BY name;
> --
> You received this message because you are subscribed to the Google Groups
> "webscalesql" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to webscalesql...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
Laurynas
Re: Proposed change to MAX_STATEMENT_TIME patch pivanof 4/7/14 7:10 PM
My personal favorite would be

SELECT * FROM Foo WITH SESSION MAX_STATEMENT_TIME_MS = 90;

I'm not sure whether we need to go with Percona's syntax or not.

Pavel
Re: Proposed change to MAX_STATEMENT_TIME patch Colin Charles 4/8/14 2:50 AM
Hi!

On 8 Apr 2014, at 10:10, Pavel Ivanov <piv...@google.com> wrote:

> My personal favorite would be
>
> SELECT * FROM Foo WITH SESSION MAX_STATEMENT_TIME_MS = 90;
>

I'm in agreement with Chip and Pavel here, much nicer syntax compared to choice in WL#681

> I'm not sure whether we need to go with Percona's syntax or not.
>

Now a quick question: Percona Server 5.6 is already shipping with this based on WL#681. Does WebScaleSQL look at other branches or is compatibility from a user/DBA standpoint only in focus with upstream (i.e. Oracle's MySQL branch)?

Thanks

> Pavel
>
> On Mon, Apr 7, 2014 at 2:32 PM, Laurynas Biveinis
> <laurynas...@gmail.com> wrote:
>> FWIW Percona Server went with the option of a "for this statement
>> only" variable support the way it was suggested in
>> http://dev.mysql.com/worklog/task/?id=681:
>>
>> SET STATEMENT max_statement_time=1000 FOR SELECT name FROM name ORDER BY name;

<snipped>
--
Colin Charles, http://bytebot.net/blog/
twitter: @bytebot | skype: colincharles
"First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

Re: Proposed change to MAX_STATEMENT_TIME patch Davi Arnaut 4/8/14 10:57 AM
Hi Chip,

With respect to the use of WITH, it might be worth checking it won't conflict with its use for common table expressions (in case MySQL ever implements it). Additionally, might be worth simply eliminating the option for select statements.

As for the option name and value ambiguity, it would be more consistent with other MySQL options to make it a fractional type that takes seconds.

- Davi


On Mon, Apr 7, 2014 at 1:59 PM, Chip Turner <ctu...@pattern.net> wrote:
It seems Oracle is taking the MAX_STATEMENT_TIME patch for 5.7, which is awesome, but I have a proposal for changing the syntax before it becomes baked into the upstream version.  For context, currently you can't set a time limit on the time the server will spend executing a query.  This patch allows it, via either a session variable (MAX_STATEMENT_TIME) or as part of a query.  Specifically, you can do:

SELECT MAX_STATEMENT_TIME = 90 * FROM foo;

and it sets MAX_STATEMENT_TIME to 90, just for that query.

When I first saw the syntax, I was confused by the '90 * FROM' -- it didn't feel right.  The rest of the syntax just didn't feel natural to me overall, so after thinking more about it, and discussing with other mysql people here, a consensus emerged that we might want to change it before it's baked into upstream.  I can see why it isn't a hint (it's reasonable to want a hard failure if a timeout can't be enforced, say, because the server isn't configured)

These discussions brought two ideas:

Add a RESOURCE LIMIT clause to SELECT, allowing something like:

SELECT * FROM Foo WITH RESOURCE LIMIT MAX_STATEMENT_TIME = 90;

or, more general, add "for this statement only" a session variable:

SELECT * FROM Foo WITH SESSION MAX_STATEMENT_TIME =90;

Currently, I believe "WITH" only is used for "WITH ROLLUP" and shouldn't cause any grammar problems.  This gives us a more extensible syntax for future uses while being easier to understand when reading a query.

I also think the name would be clearer as "MAX_STATEMENT_MILLISECONDS" -- we've found, very often, it's much simpler when configuration options (names in config files, command line parameters, etc) have the unit they expect as part of the name.  It eliminates all ambiguity, especially since MySQL itself tends to prefer seconds for most units.

Thoughts?

Chip


--
Chip Turner - ctu...@pattern.net

Re: Proposed change to MAX_STATEMENT_TIME patch steaphan 4/8/14 11:45 AM
On Tue, Apr 8, 2014 at 2:50 AM, Colin Charles <ccha...@gmail.com> wrote:

Now a quick question: Percona Server 5.6 is already shipping with this based on WL#681. Does WebScaleSQL look at other branches or is compatibility from a user/DBA standpoint only in focus with upstream (i.e. Oracle's MySQL branch)?

In general, this is only a focus with upstream mysql - but, even then, if something is broken/bad/confusing, and we all agree on the fix, we'll still fix it.

However, I would say that if something were in wide use in other branches, it is a worthy argument to reduce the friction of others by not changing it from what is already in use, without a compelling reason.

Personally, in this specific case, I'm with Chip.  I think the confusion in the current proposed upstream syntax is a compelling reason to fix this - and the sooner we do, the less friction fixing this will cause.  But, that's just my personal opinion.
Re: Proposed change to MAX_STATEMENT_TIME patch Morgan Tocker 5/5/14 9:31 AM
Hi Chip,

On Monday, April 7, 2014 2:01:07 PM UTC-7, Chip Turner wrote:
It seems Oracle is taking the MAX_STATEMENT_TIME patch for 5.7, which is awesome, but I have a proposal for changing the syntax before it becomes baked into the upstream version.  For context, currently you can't set a time limit on the time the server will spend executing a query.  This patch allows it, via either a session variable (MAX_STATEMENT_TIME) or as part of a query.  Specifically, you can do:

SELECT MAX_STATEMENT_TIME = 90 * FROM foo;

and it sets MAX_STATEMENT_TIME to 90, just for that query.

When I first saw the syntax, I was confused by the '90 * FROM' -- it didn't feel right.  The rest of the syntax just didn't feel natural to me overall, so after thinking more about it, and discussing with other mysql people here, a consensus emerged that we might want to change it before it's baked into upstream.  I can see why it isn't a hint (it's reasonable to want a hard failure if a timeout can't be enforced, say, because the server isn't configured)

Sorry for the late reply to the thread.

One of the goals of the DMRs it to collect early feedback before any GA release.  I think it's worthwhile filing a bug report/feature request, and providing upstream an opportunity to re-evaluate the syntax.

May I do so on your behalf?

- Morgan
Re: Proposed change to MAX_STATEMENT_TIME patch Chip Turner 5/5/14 10:53 AM
Sure, that'd be totally fine.  I think it would be excellent to get
the syntax improved before a GA release.
> --
> You received this message because you are subscribed to the Google Groups
> "webscalesql" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to webscalesql...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



Re: Proposed change to MAX_STATEMENT_TIME patch Morgan Tocker 5/5/14 11:52 AM
Filed as http://bugs.mysql.com/bug.php?id=72540

For additional discussion, the bugs system showed me two other related FRs:

http://bugs.mysql.com/bug.php?id=72240 - Overly simplistic
implementation of MAX_STATEMENT_TIME with UNION
http://bugs.mysql.com/bug.php?id=72243 - Queries where execution time
exceeds 'MAX_STATEMENT_TIME' should go to slow log

- Morgan
--
Morgan Tocker
http://www.tocker.ca/
Re: Proposed change to MAX_STATEMENT_TIME patch Morgan Tocker 6/18/15 10:41 AM
Hi Chip,

On Monday, May 5, 2014 at 2:52:06 PM UTC-4, Morgan Tocker wrote:
Filed as http://bugs.mysql.com/bug.php?id=72540

Sorry to circle back on an old thread: 

 The statement timeout functionality is now also available using the new query hint syntax.  For example:

SELECT /*+ MAX_EXECUTION_TIME(5) */ * FROM t1 INNER JOIN t2 WHERE ...


Does this help address the concerns you raised regarding the syntax?

- Morgan