Effective planning for Spectrum Reporting database growth, optimizations and repairs
Article ID: 50217
Updated On:
Products
Issue/Introduction
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.
Environment
Release: Any
Component: SPCRPT
Resolution
NOTE: Starting from DX NetOps Spectrum 21.2.4, the default root password for MySql is "MySqlR00t". For DX NetOps Spectrum versions prior to 21.2.4, the default root password is "root". In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.
Effective planning for Spectrum Reporting database growth, optimization and repairs
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 section of the documentation.
1. Log into the SRM system as the user that owns the Spectrum installation
2. If on Windows, start a bash shell by running "bash -login"
3. cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting
4. Run the following command to view the 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)
Feedback
