How to reduce Reporting database size and improve performance

Document ID : KB000074561
Last Modified Date : 23/03/2018
Show Technical Document Details
Introduction:
The massive Reporting database size can slow down the synchronization between Landscape and SRM database, it could 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. 

 
Instructions:
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; 
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 'custom' 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/events/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.