HOW TO Optimize SRM mysql table to return free disk space when "purge" is not clearing disk

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

Using Archive or Purge options for SRM retention policy does not appear to be reclaiming disk space. How do I get that disk space back? 

Cause

This is because of how MySQL handles disk space and how it allocates it. The issue is that once MySQL has allocated disk space it will remain that size until the table is optimized.

Mysql databases hold onto free space, even after we delete data from the database e.g. we reduced the data retention period of the event table of the reporting database, (via archive expert) but we do not see the size of the database reduce when purged, as it should.  If left un-addressed, the database will continue to grow until it fills the disk, which will most likely cause corruption of the database and a corrupt mysql database, which on a full hard drive, is complicated to fix.

This is a documented requirement to optimize mySQL dbs after deleting or purging data to reclaim free disk space. 

On the reporting db, it is documented as a manual mysql command here in the How to Manually Purge Reporting Data from the Reporting Database documentation topic.

On the DDMdb side we have a Spectrum script that does so here in the Database Maintenance and Optimization documentation topic.

This requirement is more important now, that we are using innodb file per table, as we see disk usage increased, as each table can only use the free space of that table and not of any other in the db. 

http://dev.mysql.com/doc/refman/5.7/en/innodb-file-space.html 

For more information on mysqlcheck see:

http://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html

On the optimize table syntax:

http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

Resolution

NOTE: In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

Schedule table optimization of the mySQL database, as a periodic maintenance, to avoid the above scenario. This is done using the mysqlcheck program, adding the optimize table option as below.  Scheduling options will depend on your platform and network administrators preferences.

NOTE: There needs to be free space available, 1.5 times the size of the table that mysql is attempting to optimize or 1.5 times the largest table in the database, if we run this on the entire database.

1. Log into the Spectrum Report Manager (SRM) system as the user that owns the Spectrum installation

2. Shutdown the tomcat process

3. In on Windows, start a bash shell by running "bash -login"

4. cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql to optimize the entire reporting database

./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> --optimize reporting

or on a per table basis

./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> --optimize reporting event

NOTE: Let this run to completion. Note that if the Reporting database is large (over 100GB) then this process can take some time. Best to run the optimization on downtime, such as overnight or over a weekend. 

5. After the mysqlcheck has run, restart tomcat

If you see any errors like this: "The storage engine for the table doesn't support optimize", it can be safely ignored. When complete, verify disk space has been regained.