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 (OCA) 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
Triage: Needs Triage: D5 (Feature request)

[3 Sep 2009 1:58] Roel Van de Paar
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.
[3 Sep 2009 2:01] Roel Van de Paar
Verifying as D5
[11 Sep 2009 19:31] Enterprise Tools JIRA Robot
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).
[21 Jan 2010 14:29] MC Brown
We already had some information about reclaiming row space, but I've added a note about dramatic changes to the purging period.