MySQL causing high CPU on Spectrum Reporting Manager system

Products

• CA Spectrum

Issue/Introduction

We are seeing the MySQL process on the Spectrum Report Manager (SRM) system consistently taking up 100% CPU for extended periods of time.

Environment

Release : Any

Component : Spectrum Report Manager

Cause

 Some possible root causes:

- Running reports that will cause a large amount of data to be read and processed. For example, a monthly report

- Multiple reports running at the same time

- A large reporting database

- The reporting database is fragmented

Resolution

It is not unusual to see periodic high CPU on the SRM system when there are mysql queries running. If CPU is consistently high, you can try the following:

For reports that will cause a large amount of data to be read and processed:

Run these reports only when necessary and at off peak times using the scheduler.

For running multiple reports at the same time:

Run reports individually either manually or using the scheduler.

For a large reporting database:

Cut down on the number of days worth of data that is retained. By default, the reporting database is configured to retain 90 days of data. This can be modified by the user in the OneClick Administration -> Report Manager -> Preferences page.

Set the Data Retention Policy in the Preferences page to purge. The default is to keep all data.

Please reference the "Configure Data Retention" section of the Spectrum documentation and the "Spectrum Report Manager Data Retention Policy and Data Retention Period usage" knowledge article for more information on the "Data Retention Period" and "Data Retention Policy".

Use the $SPECROOT/bin/RpmgrInitializeLandscape.sh script to reinitialize the database. After running this command, the reporting database will be rebuilt from scratch using the current data in the Archive Manager (DDM) database on the SpectroSERVER systems you have configured for SRM to monitor. By default 45 days worth of data is kept in the DDM database.

Please reference the "Reporting Database Management" section of the Spectrum documentation and the "How to drop and rebuild the Spectrum Report Manager database without performing a re-installation?" knowledge article for more information on the RpmgrInitializeLandscape.sh script.

For reporting database that is fragmented:

If you have the Data Retention Policy set to purge, the old data is flagged to be purged but it still takes up space. In order to free up the space, you need to do one of the following:

Backup the reporting database using mysqldump and then restore using the backup file. The data that is flagged to be purged is not saved in the backup.

Run an optimize on the tables in the reporting database.

Please reference the "Reporting Database Management" section of the Spectrum documentation and the "How to free up purged records in the Spectrum Report Manager (SRM) reporting database" for more information.

Lastly, adding addition CPU and memory resources may address the issue.