Troubleshoot Database Performance issues

TIP: Write Rules to watch events

Write rules to watch for events with message IDs: CCTR135I or CCTR138I. Writing rules saves having to look at the IBM® Sterling Control Center Monitor engine log files to find out when these messages were logged. This allows you to be proactive on performance issues.

Every 6 hours, a new set of metrics are output that contains valuable database related metrics for that time frame.

11 Jan 2019 22:33:36,577 280851610 [LicenseChecker] INFO  SCCAgentLicenseCheck - 
--------Java Heap Memory Details------------ 
Max Heap Size = 4194304 KB 
Current Max Heap Size = 2090560 KB 
Free  Memory = 2334493 KB 
Used  Memory = 1859810 KB 
---------------------------------------------- 
11 Jan 2019 22:33:36,577 280851610 [LicenseChecker] INFO  EventProcessing - 
<PRE>--------------------- Event processing Timings ----------------------

TOTAL_EVENTS_PROCESSED ..................: 5762716
TOTAL_TIME_FOR_GETTING_RULE_SESSION .....: 2065
TOTAL_TIME_FOR_RULE_MATCH ...............: 155706
TOTAL_TIME_FOR_RULE_QUERY ...............: 5629
TOTAL_TIME_FOR_ACTION_PROCESSING ........: 814011
TOTAL_TIME_FOR_ATTRIBUTE_SETTING ........: 235
TOTAL_TIME_FOR_RE_PROCESSING ............: 978645
Average Time for Rule Processing ........: 0.169824
Average Time for Rule Match .............: 0.027020

-----------Monitoring monitoring-------------
Average Time to check on polling status .: 0.036744

-----------Action Processing Details---------------
TOTAL_TIME_FOR_ACTION_PROCESSING ........: 814011
TOTAL_TIME_FOR_BROADCASTING .............: 4089

-----------Database Access Timings for CD_STATS_LOG Table
TOTAL_CDSTATS_INSERTED             ........: 14968
TOTAL_CDSTAT_INSERTS               ........: 3929
TOTAL_TIME_FOR_CDSTAT_INSERTS .............: 10828
Average Time for CD_STATS_LOG table Inserts: 2.755918
Average Time for CD_STATS_LOG Insert ......: 0.723410

-----------Database Access Timings for EVENTS Table 
TOTAL_EVENTS_INSERTED             ........: 212042
TOTAL_EVENT_INSERTS               ........: 128874
TOTAL_TIME_FOR_EVENT_INSERTS .............: 605630
Average Time for EVENTS table Inserts  ...: 4.699396
Average Time for EVENT insert          ...: 2.856179

TOTAL_EVENT_UPDATES               ........: 4244
TOTAL_TIME_FOR_EVENT_UPDATES .............: 58135
Average Time for EVENTS table Updates  ...: 13.698162
</PRE>

TIP: Insufficient Heap memory allocated

Performance of IBM Sterling Control Center Monitor may also be impacted by the amount of heap memory allocated to it. When too little heap memory is allocated to IBM Sterling Control Center Monitor, it can cause the Java garbage collection logic to run more often, which has a negative impact on performance of IBM Sterling Control Center Monitor. The Java Heap Memory Details logged help you ascertain if more memory for the Java heap could help IBM Sterling Control Center Monitor performance. It is advised to not allocate less than 4GB for heap.

Check the metrics data to see how well the database performed in the last 6 hours. Note that the values displayed are all reset after they’re logged. In the metrics output, time displayed for inserts and updates for the EVENTS and CD_STATS_LOG, tables are in milliseconds and should not be too large. In general, double digit values for these numbers can be considered as too large.

What are TOTAL_CDSTATS_INSERTED, TOTAL_CDSTAT_INSERTS, TOTAL_EVENTS_INSERTED, and TOTAL_EVENT_INSERTS?

Control Center Monitor typically inserts multiple rows at a time that is, Control Center Monitor strives for batch inserts, instead of inserting one row at a time, since batch inserts are more efficient. TOTAL_CDSTATS_INSERTED and TOTAL_EVENTS_INSERTED are the number of rows inserted for the previous 6 hours, while TOTAL_CDSTAT_INSERTS and TOTAL_EVENT_INSERTS are the number of inserts performed to insert those rows.

TIP: Use Performance gathering and reporting tools

Databases have performance gathering and reporting tools that may be used to help ferret out performance issues. For example, Oracle Automatic Workload Repository (AWR). A database administrator should be able to use such tool(s) to help find, recommend changes, and resolve many problems causing performance issues.