throttle: regulate execution of script by sleep-suspend


while (expression)
  throttle <number>;
while (expression)
  throttle $throttle_ratio;


throttle is a QueryScript statement which, when invoked, may suspend execution by invoking SLEEP().

throttle takes a number as a parameter, which is the ratio of throttling. The number can be an integer or a floating point, and it is interpreted as follows:
  • 0 or less: no throttling is done. It makes no sense to provide such values
  • x, a positive number: assuming an unthrottled operation would take s seconds to run, a throttled operation is expected to run s*(1 + x) seconds.

throttle also accepts a local variable or a MySQL user defined variable as argument. Such argument is cast to a number and handled as specified above.

Consider the following code as example:

while (delete from world.City limit 10)
  throttle 1;
The above deletes all rows from a table, 10 rows at a time. The throttle value of 1 means doubling the runtime: if the original deletion loop would take 7 seconds to complete, the throttle statements adds one measure to that, resulting with 14 seconds.

A throttle value of 2 would make the code run 3 times as long it it would without throttling. A throttle value of 0.3 would make the code run 1.3 times as long it it would without throttling.

Combined with loop iteration, throttling makes for heavy duty query put less load on the system. That is, CPU or disk resources, which may be extensively used during normal query or queries iteration, are given breathing space during throttle suspension period. Throttling makes for a longer total runtime, while making pauses in between operations. Throttling is also a useful technique in avoiding replication lag: standard MySQL replication (5.1, 5.5) is single threaded. Throttling allows the slave to execute other queries, interleaved with script statements.

While this statement is valid anywhere throughout the script, it mostly makes sense when executed from within a loop, such that the iterations of the loop are being throttled.


Duplicate a table: copy rows in small chunks, throttle:

create table world.City2 like world.City;
split (insert into world.City2 select * from world.City)
  throttle 2;


Execution, Flow control, sleep


Shlomi Noach
common_schema documentation