throttle
NAME
throttle(): Throttle current query by periodically sleeping throughout its execution.TYPE
FunctionDESCRIPTION
This function sleeps an amount of time proportional to the time the query executes, on a per-lap basis. That is, time is measured between two invocations of this function, and that time is multiplied by throttle_ratio to conclude the extent of throttling.
The throttle() function is introduced as an easy means to alleviate the load of a heavy-weight query, by injecting sleep time periods into the query's execution; periods where query is not consuming CPU nor performing I/O operations. During such sleep periods, other queries can more easily compete for such resources.
The function essentially increases the total runtime of the query.
Due to the fact throttling is done within the query itself, some resources taken by query's execution are not released throughout the sleep periods. Namely, no locks nor memory are released for the entire duration of the query.
Whether the function should in fact throttle depends on current query execution time, and, so as to alleviate the overhead of this function itself, only computed once in a 1,000 runs.
throttle() returns the number of seconds spent sleeping on this call of the function. The number may be 0 if no throttling took place (either the like event of not being a one in a 1,000 execution, or the case where query lap time is too small to consider throttling).
SYNOPSIS
throttle(throttle_ratio DOUBLE) RETURNS DOUBLE
Input:
-
throttle_ratio: ratio by which to throttle, or extend total query time.
Query time is extended by multiplying given arguemnt with original query time.
For example, throttle_ratio value of 1 will double the total execution time, since it adds one unit of query execution time.
throttle_ratio of 0.3 will make the query execute for 30% more time, to the total of 130% the original time.
EXAMPLES
Compare query runtime with and without throttling. Roughly double the query's execution time by providing with a throttle_ratio value of 1.
mysql> SELECT Id, Name, sleep(0.001) from world.City ORDER BY Population DESC; +------+------------------------------------+--------------+ | Id | Name | sleep(0.001) | +------+------------------------------------+--------------+ | 1024 | Mumbai (Bombay) | 0 | | 2331 | Seoul | 0 | | 206 | São Paulo | 0 | | 1890 | Shanghai | 0 | | 939 | Jakarta | 0 | ... | 2316 | Bantam | 0 | | 3538 | Città del Vaticano | 0 | | 3333 | Fakaofo | 0 | | 2317 | West Island | 0 | | 2912 | Adamstown | 0 | +------+------------------------------------+--------------+ 4079 rows in set (4.53 sec)
mysql> SELECT Id, Name, sleep(0.001), throttle(1) from world.City ORDER BY Population DESC; +------+------------------------------------+--------------+-------------+ | Id | Name | sleep(0.001) | throttle(1) | +------+------------------------------------+--------------+-------------+ | 1024 | Mumbai (Bombay) | 0 | 0 | | 2331 | Seoul | 0 | 0 | | 206 | São Paulo | 0 | 0 | | 1890 | Shanghai | 0 | 0 | | 939 | Jakarta | 0 | 0 | ... | 2316 | Bantam | 0 | 0 | | 3538 | Città del Vaticano | 0 | 0 | | 3333 | Fakaofo | 0 | 0 | | 2317 | West Island | 0 | 0 | | 2912 | Adamstown | 0 | 0 | +------+------------------------------------+--------------+-------------+ 4079 rows in set (8.69 sec)