MySQL Bugs: #55323: add an option to limit the write rate to slow query log

Bug #55323	add an option to limit the write rate to slow query log
Submitted:	16 Jul 2010 16:12	Modified:	13 Dec 2011 19:20
Reporter:	Axel Schwenke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Geir Høydalsvik	CPU Architecture:	Any

Description:
On a busy server the slow query log might be growing fast, especially when log_queries_not_using_indexes is turned on. To avoid bad surprises like a full disk, it would be nice if one limit the write rate. Typically the slow query log contains the same queries over and over again, so by limiting the write rate one would not lose information.

How to repeat:
n/a

Suggested fix:
Typical implementations use two configuration variables:

burst_size: that many events will be written before rate limiting kicks in.
rate_limit: a limit of events per second. If too many events flow in some will be dropped in order to keep below the rate limit.

I would think that initially having a simple rate limit (which is by default 0, not limited) which shows the first x queries not using indexes per unit time would be good. The peak values seems probably an unnecessary extra knob.

Also the limit is probably best set as a rate per minute as a per second limit is possibly not going to be useful unless the value can take non-integer numbers.

The log_queries_not_using_indexes can really make the slowlog file size grow extremely quickly on busy servers, and most of the useful information can be obtained by the selected queries.

What might be helpful if this is applied is to count the number of queries and total executed seconds, and total rows read in the queries and print these missing numbers out prior to the next "valid" slow log entry.

This means that at least some idea of how much data is missing and how slow these queries are will be kept even if the queries themselves are not.

If this is the typical output for a single query:

# Query_time: 0.005764  Lock_time: 0.000129 Rows_sent: 0  Rows_examined: 20802

then add a not shown list as follows:

# Not shown (due to rate limiting): Queries: 999 Query_time: 3.5764  Lock_time: 2.0129 Rows_sent: 100  Rows_examined: 223080

Noted in 5.6.5 changelog.

If the log_queries_not_using_indexes system variable is enabled, slow
queries that do no use indexes are written to the slow query log. In
this case, it is now possible to put a logging rate limit on these
queries by setting the new log_throttle_queries_not_using_indexes 
system variable, so that the slow query log does not grow too
quickly. By default, this variable is 0, which means there is no
limit. Positive values impose a per-minute limit on logging of
queries that do not use indexes. The first such query opens a 
60-second window within which queries up to the given limit are
logged. Additional queries within that window are suppressed. When
the window ends, a summary is logged that indicates the number of 
suppressed queries and the aggregate time spent in them. The next
60-second window begins when the next query that does not use indexes
is logged.