MySQL Bugs: #47076: Enterprise Monitor Purge Settings do not remove unused space within tables

Bug #47076	Enterprise Monitor Purge Settings do not remove unused space within tables
Submitted:	3 Sep 2009 1:58	Modified:	21 Jan 2010 14:29
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Documentation	Severity:	S4 (Feature request)
Version:	2.0	OS:	Any
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
As a result of bug #1341, the MEM Purge Settings are somewhat 'purposeless' without manual intervention;

All the tables in the 'mem' database are InnoDB (and, innodb_file_per_table is being used). So, when some data has been gathered, the size of the .ibd files can only grow (not shrink).

Unless the purge settings have been fixed (since installation) to a certain # of weeks, or unless an optimize/rebuild table on large tables in the mem database is done, the purge settings have no effect. They may purge data internally, but externally, the same amount of disk space will be used.

How to repeat:
Before change of purge settings from 4 weeks to 1 week:

roel@roel-ubuntu-vm:/mem/monitor-2.1.0.1080/mysql/data/mem$ ls -l dc_ng_*_now.ibd
-rwx------ 1 roel roel 311296 2009-09-02 11:21 dc_ng_double_now.ibd
-rwx------ 1 roel roel 14680064 2009-09-02 11:21 dc_ng_long_now.ibd
-rwx------ 1 roel roel 9437184 2009-09-02 11:21 dc_ng_string_now.ibd

After change of purge settings from 4 weeks to 1 week: same result (i.e. though the data is gone from the files, the space is not automatically reclaimed)

After manual optimize/rebuild of the tables (using mysql client to mem db):

roel@roel-ubuntu-vm:/mem/monitor-2.1.0.1080/mysql/data/mem$ ls -l dc_ng_*_now.ibd
-rw-rw---- 1 roel roel 147456 2009-09-02 11:22 dc_ng_double_now.ibd
-rw-rw---- 1 roel roel 9437184 2009-09-02 11:22 dc_ng_long_now.ibd
-rw-rw---- 1 roel roel 524288 2009-09-02 11:22 dc_ng_string_now.ibd

Suggested fix:
Three possible fixes:

1. Resolve bug #1341 (been open since 2003).
2. Build into MEM an automatic optimize/rebuild table whenever purge settings are lowered/whenever deletes are done (remove server, change rules amount/frequency).
3. Change mem db tables to MyISAM.

Verifying as D5

Gary Whizin writes: 
Main docs should document that, after changing purge dramatically as described (either turned on for first time, or purge period is dramatically reduced), user could/should... (see suggested solution above).

We already had some information about reclaiming row space, but I've added a note about dramatic changes to the purging period.