Optimize mysql table to return free disk space.

Products

• CA Spectrum

Issue/Introduction

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.

Additional Information: 

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 

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/spectrum/10-4/installing-and-upgrading/install-report-manager/maintenance-and-troubleshooting/how-to-manually-purge-reporting-data-from-the-reporting-database.html

On the DDMdb side we have a Spectrum script that does so here: 

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/spectrum/10-4/administrating/database-management/ddm-database-maintenance/database-maintenance-and-optimization.html

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

and on the optimize table syntax:

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

       

Environment

Release:
Component:

Resolution

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 -proot --optimize reporting

or on a per table basis

./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -proot --optimize reporting event

5. After the mysqlcheck has run, restart tomcat