Effective planning for Spectrum Reporting database growth, optimizations and repairs in 9.2

Document ID : KB000050217
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

While running Spectrum Report Manager it is possible that over time the reporting database will grow to an extent where you may start to run out of space on the host where it resides.

Effective planning at the point of installing Spectrum Report Manager and creation of the MySQL database can help to ensure you avoid any space issues and the resulting problems this can cause, allowing for database growth and the running of any optimizations and repairs.

Solution:

Effective planning for Spectrum Reporting database growth, optimization and repairs in 9.2

While running Spectrum Report Manager it is possible that over time the Reporting database will grow to an extent you may start to run out of space on the host where it resides.

Effective planning at the point of installing Spectrum Report Manager and the creation of the MySQL database can help to ensure you avoid any space issues and the resulting problems this can cause, allowing for database growth and the running of any optimizations and repairs.

At the point of SRM install and the MySQL database creation allowing for three times expected database growth should provide sufficient space for its future growth and any related optimizations or repairs that may be required at a later date.

It is now not uncommon at the point of the publishing this knowledge base document to see reporting database sizes of around 100Gb. At the point of creating the initial database 300Gb would be the recommended amount to allow as free space on the system on which it resides. Please note that this will vary greatly from organization to organization depending upon the number and size of landscapes that are being monitored and also the amount of SPM tests that are being reported against.

In the event that you are required to run a MySQL repair on the Reporting database it is essential that you have sufficient free space on the host system to allow this repair to run correctly and prevent further possible database corruption as a result of the repair utility running out of space while running.

When running a MySQL repair as a minimum you should ensure that you have 1.5 times the largest table size in the Reporting database with recommendation to have at least 2 times free space to the largest table size.

During any MySQL repair the tables are repaired individually and for this to run successfully the above space requirements allow for table growth while the process is being carried out.

The largest event table is usually the event.MYI table.

Checking the reporting database table sizes at regular intervals will ensure that any potential space issues can be avoided and allow for planning of any disk space increases to be carried out in a scheduled preemptive manner before any repair scenarios may arise.

Event table sizes can be checked by viewing their size in a directory listing of the Reporting directory or alternatively by viewing the table sizes with the following MySQL command. If you are not familiar with how to run MySQL commands and logging into the MySQL reporting database, please consult the Spectrum Database Management Guide.

  1. Login to MySQL reporting database

  2. Run the following to view table sizes:
    SELECT CONCAT(table_schema, '.', table_name), 
    CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, 
    CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
    CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
    CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
    ROUND(index_length / data_length, 2) idxfrac
    FROM information_schema.TABLES 
    ORDER BY data_length + index_length DESC
    LIMIT 10;

Example output (notice total_size column):

+---------------------------------------+-------+-------+-------+------------+---------+
| CONCAT(table_schema, '.', table_name) | rows  | DATA  | idx   | total_size | idxfrac |
+---------------------------------------+-------+-------+-------+------------+---------+
| reporting.event                       | 0.03M | 0.06G | 0.00G | 0.07G      |    0.05 |
| reporting.ca_reportstrings            | 0.00M | 0.00G | 0.00G | 0.00G      |    0.01 |
| reporting.vendor                      | 0.03M | 0.00G | 0.00G | 0.00G      |    0.35 |
| reporting.eventdesc                   | 0.02M | 0.00G | 0.00G | 0.00G      |    0.25 |
| reporting.modeloutage                 | 0.00M | 0.00G | 0.00G | 0.00G      |    4.44 |
| reporting.time_dimension              | 0.01M | 0.00G | 0.00G | 0.00G      |    0.51 |
| reporting.bucketactivitylog           | 0.00M | 0.00G | 0.00G | 0.00G      |    0.71 |
| reporting.handleractivitylog          | 0.00M | 0.00G | 0.00G | 0.00G      |    0.69 |
| reporting.alarminfo                   | 0.00M | 0.00G | 0.00G | 0.00G      |    3.50 |
| reporting.model                       | 0.00M | 0.00G | 0.00G | 0.00G      |    2.00 |
+---------------------------------------+-------+-------+-------+------------+---------+
10 rows in set (0.22 sec)