MySQL Bugs: #54719: Add options to control statement

Bug #54719	Add options to control statement_examples table growth
Submitted:	23 Jun 2010 3:27
Reporter:	Shannon Wade	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S3 (Non-critical)
Version:	2.2.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	windmill

Description:
This table holds example queries (currently unpartitioned longtext field), my understanding is that the agent has 5 'slowest' slots / canonical query / minute.

On a busy server sending lots of repetitive queries with different params it's possible to fill the 5 slots / query each time (per minute) . In these cases the table can grow quite fast. Previously this information was stored in the dc_string table but it's growth is more apparent in 2.2 after being split into a separate table.

If disk space is an issue from this table, ince there is not a separate purge setting for this there are two choices, reduce the quan historical purge period which removes all quan information for that period. Or use a manual purge script to delete from this table more frequently than quan purge settings.  (also it's not partitioned so one must run optimize as well periodically)

How to repeat:
Use mem + quan on a very busy server with repetitive queries with different where conditions,etc.

Suggested fix:
It would be good to have:

1) Partitioning of this table
2) A separate purge setting/period for example queries.

Also one or both of the following:

3) Configurable number of slots / minute (or if 4 per time frame).
4) If possible configurable time frame but I'm not sure if this can be implemented it would seem the agent would need to store these then get the X number of slowest in X time range.