Comments on: Contest for Glory: write a self throttling MySQL query

By: shlomi

shlomi — Thu, 24 Nov 2011 18:27:30 +0000

@harald, The solution has long been published. You should check on my site more often! Here's the link: Self throttling MySQL queries

By: harald

harald — Thu, 24 Nov 2011 16:28:56 +0000

how many days are “a couple of days”? 😉

thanks!

By: Self throttling MySQL queries | code.openark.org

Self throttling MySQL queries | code.openark.org — Tue, 01 Nov 2011 07:58:01 +0000

[…] Contest for Glory: write a self throttling MySQL query […]

By: shlomi

shlomi — Sat, 29 Oct 2011 04:38:15 +0000

@Baron,
This contest is so desolate! Apart from Marc no one has submitted anything…
I’ll give it a couple more days, then publish a solution. Do feel free to post your ideas.

By: Baron Schwartz

Baron Schwartz — Fri, 28 Oct 2011 19:44:21 +0000

I guess I can say that I figured it out in a conversation with Shlomi after recalling something funny about most SHOW commands… and maybe that is a hint. But it’s better than posting the solution and depriving others of the fun 🙂

By: shlomi

shlomi — Fri, 28 Oct 2011 05:38:27 +0000

@Marc,

very cool. Actually, I forgot to add an important thing: my talk suggested no stored routines are required … 😉

By: Marc Alff

Marc Alff — Thu, 27 Oct 2011 22:55:40 +0000

Proposed entry, which assumes mysql 5.6 and the performance schema:

— helper

create function tools.throttle(every_n integer, delay integer)
returns varchar(20)
begin
declare sent integer;
declare dummy integer;

if (every_n <= 0) then
signal sqlstate ‘HUMMM’ set message_text = “Very funny. Try harder.”,
mysql_errno = 50000;
end if;

set sent =
(select ROWS_SENT from performance_schema.events_statements_current
where THREAD_ID = @my_tid);

if ((sent > 0) and (sent % every_n = 0)) then
set dummy = (select sleep(delay));
end if;

return sysdate();
end
$$

— once in the session, do this:

set @my_tid= (select THREAD_ID from performance_schema.threads
where PROCESSLIST_ID = connection_id());

— before

select * from big_table;
select anything_you_want from big_table;

— after

select big_table.*, tools.throttle(1000, 2) from big_table;
select anything_you_want, tools.throttle(1000, 2) from big_table;

By: Marc Alff

Marc Alff — Thu, 27 Oct 2011 22:25:17 +0000

Correction (due to brackets tags lost in post)

select anything-you-want from big_table
becomes
select anything-you-want, tools.throttle(1000, 2) from big_table.

By: Marc Alff

Marc Alff — Thu, 27 Oct 2011 22:22:53 +0000

Sounds fun, so I take the bait 😉

This proposed solution assumes 5.6, with the performance schema.

create function tools.throttle(every_n integer, delay integer)
returns varchar(20)
begin
declare sent integer;
declare dummy integer;

if (every_n 0) and (sent % every_n = 0)) then
set dummy = (select sleep(delay));
end if;

return sysdate();
end

Execute this once in the current session:

set @my_tid= (select THREAD_ID from performance_schema.threads
where PROCESSLIST_ID = connection_id());

Then, rewrite queries like this:

select * from big_table;
select from big_table;

to the throttled version:

select big_table.*, tools.throttle(1000, 2) from big_table;
select , tools.throttle(1000, 2) from big_table;

To prove it works, the short answer is to look at the timestamps returned by tools.throttle.
The long version is to look at the performance schema waits in events_waits_history_long and see the sleeps there.

Regards,
— Marc