Skip to content

# Self throttling MySQL queries

MySQL Hack MyISAM Performance SQL Stored routines November 1, 2011November 1, 2011

Recap on the problem:

- A query takes a long time to complete.
- During this time it makes for a lot of I/O.
- Query’s I/O overloads the db, making for other queries run slow.

I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the query does not perform I/O at that time, which then means other queries can have their chance to execute.

I present two approaches:

- The naive approach: for every 1,000 rows, the query sleep for 1 second
- The factor approach: for every 1,000 rows, the query sleeps for the amount of time it took to iterate those 1,000 rows (effectively doubling the total runtime of the query).

#### Sample query

We use a simple, single-table scan. No aggregates (which complicate the solution considerably).

>     SELECT
>       rental_id,
>       TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
>     FROM
>       sakila.rental
>     ;

#### The naive solution

We need to know every 1,000 rows. So we need to count the rows. We do that by using a counter, as follows:

>     SELECT
>       rental_id,
>       TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days,
>       @row_counter := @row_counter + 1
>     FROM
>       sakila.rental,
>       (SELECT @row_counter := 0) sel_row_counter
>     ;

A thing that bothers me, is that I wasn’t asking for an additional column. I would like the result set to remain as it were; same result structure. We also want to sleep for 1 second for each 1,000 rows. So we merge the two together along with one of the existing columns, like this:

>     SELECT
>       rental_id +
>         IF(
>           (@row_counter := @row_counter + 1) % 1000 = 0,
>           SLEEP(1), 0
>         ) AS rental_id,
>       TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
>     FROM
>       sakila.rental,
>       (SELECT @row_counter := 0) sel_row_counter
>     ;

To remain faithful to my slides, I rewrite as follows, and this is the naive solution:

>     SELECT
>       rental_id +
>         CASE
>           WHEN (@row_counter := @row_counter + 1) % 1000 = 0 THEN SLEEP(1)
>           ELSE 0
>         END AS rental_id,
>       TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
>     FROM
>       sakila.rental,
>       (SELECT @row_counter := 0) sel_row_counter
>     ;

The WHEN clause always returns 0, so it does not affect the value of rental_id.

#### The factor approach

In the factor approach we wish to keep record of query execution, every 1,000 rows. I introduce a nested WHEN statement which updates time records. I rely on SYSDATE() to return the true time, and on NOW() to return query execution start time.

>     SELECT
>       rental_id +
>         CASE
>           WHEN (@row_counter := @row_counter + 1) IS NULL THEN NULL
>           WHEN @row_counter % 1000 = 0 THEN
>             CASE
>               WHEN (@time_now := SYSDATE()) IS NULL THEN NULL
>               WHEN (@time_diff := (TIMESTAMPDIFF(SECOND, @chunk_start_time, @time_now))) IS NULL THEN NULL
>               WHEN SLEEP(@time_diff) IS NULL THEN NULL
>               WHEN (@chunk_start_time := SYSDATE()) IS NULL THEN NULL
>               ELSE 0
>             END
>           ELSE 0
>         END AS rental_id,
>       TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
>     FROM
>       sakila.rental,
>       (SELECT @row_counter := 0) sel_row_counter,
>       (SELECT @chunk_start_time := NOW()) sel_chunk_start_time
>     ;

#### Proof

How can we prove that the queries do indeed work?

We can see if the total runtime sums up to the number of sleep calls, in seconds; but how do we know that sleeps do occur at the correct times?

A solution I offer is to use a stored routines which logs to a MyISAM table (a non transactional table) the exact time (using SYSDATE()) and value per row. The following constructs are introduced:

>     CREATE TABLE test.proof(
>       id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>       dt DATETIME NOT NULL,
>       msg VARCHAR(255)
>     ) ENGINE=MyISAM;

>     DELIMITER $$
>     CREATE FUNCTION test.prove_it(message VARCHAR(255)) RETURNS TINYINT
>     DETERMINISTIC
>     MODIFIES SQL DATA
>     BEGIN
>       INSERT INTO test.proof (dt, msg) VALUES (SYSDATE(), message); RETURN 0;
>     END $$
>     DELIMITER ;

The prove_it() function records the immediate time and a message into the MyISAM table, which immediately accepts the write, being non-transactional. It returns with 0, so we will now embed it within the query. Of course, the function itself incurs some overhead, but it will nevertheless convince you that SLEEP()s do occur at the right time!

>     SELECT
>       rental_id +
>         CASE
>           WHEN (@row_counter := @row_counter + 1) IS NULL THEN NULL
>           WHEN @row_counter % 1000 = 0 THEN
>             CASE
>               WHEN (@time_now := SYSDATE()) IS NULL THEN NULL
>               WHEN (@time_diff := (TIMESTAMPDIFF(SECOND, @chunk_start_time, @time_now))) IS NULL THEN NULL
>               WHEN SLEEP(@time_diff) + test.prove_it(CONCAT('will sleep for ', @time_diff, ' seconds')) IS NULL THEN NULL
>               WHEN (@chunk_start_time := SYSDATE()) IS NULL THEN NULL
>               ELSE 0
>             END
>           ELSE 0
>         END AS rental_id,
>       TIMESTAMPDIFF(DAY, rental_date, return_date) AS rental_days
>     FROM
>       sakila.rental,
>       (SELECT @row_counter := 0) sel_row_counter,
>       (SELECT @chunk_start_time := NOW()) sel_chunk_start_time
>     ;

>     mysql> SELECT * FROM test.proof;
>     +----+---------------------+--------------------------+
>     | id | dt                  | msg                      |
>     +----+---------------------+--------------------------+
>     |  1 | 2011-11-01 09:22:36 | will sleep for 1 seconds |
>     |  2 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
>     |  3 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
>     |  4 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
>     |  5 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
>     |  6 | 2011-11-01 09:22:36 | will sleep for 0 seconds |
>     |  7 | 2011-11-01 09:22:38 | will sleep for 1 seconds |
>     |  8 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
>     |  9 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
>     | 10 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
>     | 11 | 2011-11-01 09:22:38 | will sleep for 0 seconds |
>     | 12 | 2011-11-01 09:22:40 | will sleep for 1 seconds |
>     | 13 | 2011-11-01 09:22:40 | will sleep for 0 seconds |
>     | 14 | 2011-11-01 09:22:40 | will sleep for 0 seconds |
>     | 15 | 2011-11-01 09:22:40 | will sleep for 0 seconds |
>     +----+---------------------+--------------------------+

The above query is actually very fast. Try adding BENCHMARK(1000,ENCODE(‘hello’,’goodbye’)) to rental_id so as to make it slower, or just use it on a really large table, see what happens (this is what I actually used to make the query run for several seconds in the example above).

Observant reads will note that the “will sleep…” message actually gets written after the SLEEP() call. I leave this as it is.

Another very nice treat of the code is that you don’t need sub-second resolution for it to work. If you look at the above, we don’t actually go to sleep every 1,000 rows (1,000 is just too quick in the query — perhaps I should have used 10,000 seconds). But we do make it once a second has elapsed. Which means it works correctly on average. Of course, the entire discussion is only of interest when a query executes for a substantial number of seconds, so this is just an anecdote.

#### And the winner is…

Wow, this contest was anything but popular. Marc Alff is the obvious winner: he is the only one to suggest a solution 🙂

But Marc uses a very nice trick: he reads the PERFORMANCE_SCHEMA. Now, I’m not sure how the PERFORMANCE_SCHEMA gets updated. I know that the INFORMATION_SCHEMA.GLOBAL_STATUS table does not get updated by a query until the query completes (so you cannot expect a change in innodb_rows_read throughout the execution of the query). I just didn’t test it (homework, anyone?). If it does get updated, then we can throttle the query based on InnoDB page reads using a simple query. Otherwise, an access to /proc/diskstats is possible, assuming no apparmor or SELinux are blocking us.

Marc also uses a stored function, which is the clean way of doing it; however I distrust the overhead incurred by s stored routine and prefer my solution (which is, admittedly, not a pretty SQL sight!).

Happy throttling!

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
5
Leave a Reply

5 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
sbester

i got stuck on the potential sub-second requirement quite a bit.. only way i thought it possible was getting it from the uuid() but it got too messy and i gave up 🙂

Author
shlomi

@sbester,

Technically, you can use:
TIMESTAMPDIFF(MICROSECOND, start, end)/1000000.0
The syntax is accepted in MySQL.

In MariaDB this should actually work (I never tried).

You will get a letter of condolences for not winning the Glory award.

Contest for Glory: write a self throttling MySQL query | code.openark.org

[…] Self throttling MySQL queries […]

Guest
Rick James

Hmmm… I may want to incorporate you technique in my web page on how to do big deletes:
http://mysql.rjweb.org/doc.php/deletebig

You discussed SELECT, but in applying this to write operations, be aware issues with SYSDATE() in a replication situation:
http://bugs.mysql.com/bug.php?id=15101
(I have not tested.)

As for MICROSECONDs, most people have to wait for SYSDATE() (or whatever) to provide microseconds. The manual (even for 5.0) claims that SYSDATE() “Returns … YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context”, but it does not work on 5.1.

Author
shlomi

@Rick,
for DELETEs I provide with oak-chunk-update, as part of the openark-kit.

I’m also going to integrate this into QueryScript.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.