How to reclaim unused space from InnoDB reporting.event table?

Article Id: 17210
Status: Published
Updated On: 11-09-2019 07:17
Legacy Id: TEC1715354
Products:
CA Spectrum , CA Spectrum , CA Spectrum , CA Spectrum
Issue/Introduction:

The CA Spectrum Report Manager was configured to retain 45 days of historical data from the partitioned event table, but the MySQL InnoDB is not releasing disk space after deleting data rows from the table.



How to reclaim unused space on InnoDB partitioned reporting.event table?

Environment:

Release:
Component:

Resolution:

Out Of the Box (OOB) CA Spectrum Report Manager will not shrink the reporting database.

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table. 

 

1) Open a bash shell (bash -login) 

 

2) Go to $SPECROOT/mysql/bin directory 

 

3) Run: 

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting (Linux/Solaris) 

 

./mysql -uroot -proot reporting (Windows) 

 

4) Run: 

ALTER TABLE event ENGINE=INNODB;

 

 In the following image you can see the files before and after defragmentation:


event_defrag.png

Additional Information:

You can also run this MySQL syntax from a script to run it in a weekly basis:

cd $SPECROOT/mysql/bin

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting -e"ALTER TABLE event ENGINE=InnoDB;"