How to reduce the Spectrum Report Manager (SRM) reporting database size and improve performance
Article ID: 74561
Updated On:
Products
CA Spectrum
DX NetOps
Issue/Introduction
The massive reporting database size can slow down the synchronization between Landscape and SRM database. It can also cause many performance issues on CABI server as well as SRM server especially when you run the reports. You might be thinking if there is any way that I could optimize the reporting database, reduce the size of the database without reducing the Data Retention Period.
Environment
Release: Any
Component: SPCRPT - Spectrum Report Manager
Resolution
The Event tables usually have the the most data to store in the SRM database.
Please navigate to $SPECROOT/mysql/bin directory on the SpectroSERVER host and run the following commands.
1. Login to MYSQL database
./mysql -uroot -proot ddmdb
2. To get the Top 10 events most commonly generated:
SELECT hex(type), COUNT(*) as cnt FROM event GROUP BY type
ORDER BY cnt DESC LIMIT 10;
1. Login to MYSQL database
./mysql -uroot -proot ddmdb
2. To get the Top 10 events most commonly generated:
SELECT hex(type), COUNT(*) as cnt FROM event GROUP BY type
ORDER BY cnt DESC LIMIT 10;
3. To get the Top 10 models with the most events:
SELECT hex(e.model_h), m.model_name, COUNT(*) as cnt
FROM event e, model m WHERE e.model_h=m.model_h
GROUP BY e.model_h
ORDER BY cnt DESC LIMIT 10;
4. To get the Top 10 high-volume days for events:
SELECT date(from_unixtime(utime)) as x, count(*) as cnt
FROM event GROUP BY x
ORDER BY cnt DESC LIMIT 10;
5. To get the last 10 days volume of events:
SELECT date(from_unixtime(utime)) as x, count(*) as cnt
FROM event GROUP BY x
ORDER BY x DESC LIMIT 10;
Once you have collected these data and understand what are the top 10 Events and the associated models in your DDMDB, please discuss with your team and pick up those unnecessary events and associated models that you don't need them to be stored in SRM database.
Now please go to OneClick server to exclude these Event types in SRM database in order to reduce the SRM database size.
1. Copy the event-processing-filter.xml and event-processing-filter-schema.xsd files to the '$SPECROOT/custom/repmgr/config' directory. For example, see the following syntax:
cp
<$SPECROOT>/tomcat/webapps/spectrum/WEB-INF/repmgr/config/event-processing-filter-schema.xsd
<$SPECROOT>/custom/repmgr/config/
cp
<$SPECROOT>/tomcat/webapps/spectrum/WEB-INF/repmgr/config/event-processing-filter.xml
<$SPECROOT>/custom/repmgr/config/
2.Edit the event-processing-filter.xml to reflect your selected filtering strategy. For example, see the following syntax:
<ignore> <event-type>0x1245</event-type> <event-type>0xffa0004</event-type> <model>0x00d40010</model> <model>0xff0100d1</model> </ignore>
Note: You can only ignore events that are associated with specific models or event types.
3. Restart Tomcat.
The specified event processing filters are now in effect.
Feedback
Yes
No
Powered by
