query_script_throttle
throttle: regulate execution of script by sleep-suspend
SYNOPSIS
while (expression)
{
statement;
throttle <number>;
}
while (expression)
{
statement;
throttle $throttle_ratio;
}
DESCRIPTION
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.
Consider the following code as example:
while (delete from world.City limit 10)
{
throttle 1;
}
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.
EXAMPLES
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;
}