Using debug properties to monitor and troubleshoot performance

Technote (FAQ)


This document applies only to the following language version(s):

Language Independent

Question

How can mxe.db.LogSQLTimeLimit, mxe.db.FetchResultLogLimit, MBOCount, mxe.db.logSQLPlan, and mxe.db.sqlTableScanExclude properties be set to troubleshoot or monitor performance and memory usage in Maximo and TPAE applications

Cause

Clarifying documentation

Answer

There are five helpful properties that can be used to troubleshoot and monitor Maximo performance. The implementation of these properties has changed over different versions of Maximo and all of the implementations are covered here. These properties are modified in the MXServer.properties in release 5, maximo.properties in release 6, and in the system properties application in release 7.

IBM has internal tools for analyzing logs. The results of these analysis are substantially improved when the properties described here are enabled. To have a log analyzed, submit the systemOut, maximo, or any TPAE appender log to a PMR and request an analysis using the tools. The resulting reports can be provided upon request.

Note: In Maximo 6, logging with these properties will only occur if the logging.properties is set properly for application logging. This means both the maximo.properties and the logging.properties must be checked for proper settings. At a minimum, the following properties must be set in the logging.properties.

Logging.properties
log4j.rootLogger=ERROR, A1, A2
log4j.logger.maximo=INFO


Note: Follow the procedures below to set proper logging for version 7 of Maximo and TPAE Applications.

Steps to enable logging:
1. Set the Root Logger in Logging Application to INFO and the appender to Rolling.



2. Also set the Rolling Appender to a file size of 20 and backup index of 24.




3. Apply the settings


4. Set the following properties in the System Properties Application



a) mxe.db.logSQLTimeLimit=1000



b) mxe.mbocount=1


c) mxe.db.fetchResultLogLimit=200
Note: The default value for this is 1000 but in cases where memory is being exhausted by MBO in memory, 1000 may not be sufficient to capture the process responsible for loading the objects. See the description below.


5. After setting the above 3 properties, choose Select Action / Live Refresh


Note: Using the FetchResultLogLimit, LogSQLTimeLimit, and MBOCount properties have minimal to no impact on performance. It is recommended that these three settings remain turned on at all times to assist with debugging in the event a problem arises that may need troubleshooting. The logsqlplan, and sqltablescanexclude properties (Oracle only) can impact performance and should only be used on a temporary basis or on a separate logging server.

The five properties are:

mxe.db.fetchResultLogLimit (5.2 P02A and above)
mxe.db.logSQLTimeLimit (5.2 P02A and above)
mbocount (5.2 P05 and above - different implementations)
mxe.db.logSQLPlan (6.0 P01 and above)
mxe.db.sqlTableScanExclude (6.0 P01 and above)

Each of these settings are set in the properties file (maximo.properties for Maximo 6 and MXServer.properties for maximo 5). If the property does not exist, it can be simply be added. If a property is added that is not supported in a specific version of Maximo, the property will be ignored.

mxe.db.fetchResultLogLimit – This property is used to capture the number of objects an MBO loads while it is processing. It is common for an MBO to load several thousand objects temporarily and then release them; however, loading 60,000 or more objects may indicate a problem. The number associated with this property is the number of objects that should be loaded before a log entry is made. If this is set to 1000, log entries will be made as 1000, 2000, 3000, etc objects are loaded into any single MBO set. For troubleshooting performance and memory problems, this value should be set to 200. When performance and memory are not an issue, this value should be set to 1000. This property does not impact performance but may impact log readability.
Note: IBM has internal tools for analyzing large logs to extract the results of these debug properties. The amount of data in the log should not be a concern when troubleshooting performance and stability problems; however, there is a limit. Setting root loggers to DEBUG level could have an impact and is not generally recommended unless specifically requested by IBM support. Setting root loggers to DEBUG should not be confused with setting these specific DEBUG properties. These three properties should be left on at all times in all environments.

mxe.db.logSQLTimeLimit – This property is used to capture SQL statements that take longer than the specified amount of time to return. Databases should be able to return most SQL statements in under one second. When there is a perceived performance problem, setting this property can capture long running SQL and help determine if database tuning is required. The number associated with this property is milliseconds. 1000 is equal to 1 second. This property does not impact performance but the smaller the value the more SQL Statements will appear in the logs. This can affect log readability.

mxe.cronTask.psdi.server.MboCountCronTask or mxe.mbocount – This property is used to capture snapshot intervals of memory usage. In Maximo 5.2 this is a cron task that runs at the interval specified by the associated value. In Maximo 6.x this is a property and will run every one minute. The mbocount is useful in identifying trends of memory usage and release. Typically a log would show objects growing and shrinking as usage differed on the server. If objects never get released or continually grow over time, this is an indicator of a memory leak. Many things including bad data, custom code, incorrect environment configuration, and bugs can cause memory leaks. The most important thing about fixing a memory leak is identifying it. This property does not impact performance.

Note: The MBOCount property controls minute by minute logging of user counts, users per JVM, MBOs in memory, memory utilization, crontask execution and more. This is a critical property to analyze the status of a JVM.
Note: The MBOCount function has changed from a cron task in 5.2 to a property in 6.0.
Note: In V6 releases prior to 6.2.2 the mbo count report will not generate if mxe.crontask.donotrun=ALL is set. A hotfix is available to address this issue for versions prior to 6.2.2.
Note: Due to language support in Maximo 7, all boolean values such as YES/NO and TRUE/FALSE have been changed to 1/0 where 1=YES or TRUE and 0=NO or FALSE. The mbocount property should be set to 1 in Maximo 7 where it was set to YES in V6.

mxe.db.logSQLPlan – This property is only available in environments where Oracle is used as the Maximo database. This property logs the execution plan for all SQL statements which contains a full table scan. The mxe.db.logSQLPlan property is dependant on the mxe.db.sqlTableScanExclude property. This property can affect performance and should only be used on a temporary basis or on a separate logging server.

mxe.db.sqlTableScanExclude - This property is only available in environments where Oracle is used as the Maximo database. This property provides exclude criteria for the mxe.db.logSQLPlan property. Tables which do not have indexes and are intended to use a full table scans may be excluded from logging using this property. Table names are separated by commas and must be entered in upper case. This property can affect performance and should only be used on a temporary basis or on a separate logging server.

These properties can be found or added to the MXServer.properties file in 5.2 P02A and later

mxe.db.fetchResultLogLimit=200
mxe.db.logSQLTimeLimit=1000

These properties can be found or added to the MXServer.properties file in 5.2 P05 and later

mxe.db.fetchResultLogLimit=200
mxe.db.logSQLTimeLimit=1000
mxe.cronTask.psdi.server.MboCountCronTask=1m

These properties can be found or added to the maximo.properties file in 6.0 and later

mxe.db.fetchResultLogLimit=200
mxe.db.logSQLTimeLimit=1000
mxe.mbocount=YES

These properties can be found or added to the maximo.properties file in 6.0 P01 and later

mxe.db.fetchResultLogLimit=200
mxe.db.logSQLTimeLimit=1000
mxe.mbocount=YES
// NOTE: Oracle Only below - Can impact performance
mxe.db.logSQLPlan=true
mxe.db.sqlTableScanExclude=ACTION,MAXROLE,SCCONFIG,MAXUSER

These properties can be found or added to the system properties in 7.1 and later

mxe.db.fetchResultLogLimit=200
mxe.db.logSQLTimeLimit=1000
mxe.mbocount=1
// NOTE: Oracle Only below - Can impact performance
mxe.db.logSQLPlan=true
mxe.db.sqlTableScanExclude=ACTION,MAXROLE,SCCONFIG,MAXUSER



*********************************************************************************
*********************************************************************************

Below is an example of log output for the mxe.db.fetchResultLogLimit property. Note that it appears as an exception but it is a purposefully generated exception:

01 Feb 2007 05:18:48:774 [INFO] -------FetchResultLogLimit logging starts------
01 Feb 2007 05:18:48:774 [INFO] User Name : MAXADMIN
01 Feb 2007 05:18:48:774 [INFO] Query : select * from applicationauth
where groupname in ( 'MAXADMIN' ) order by app, groupname
01 Feb 2007 05:18:48:774 [INFO] MboSet Name : APPLICATIONAUTH
Reference = psdi.app.signature.ApplicationAuthSet[RemoteStub [ref:
[endpoint:[164.84.200.77:2694](local),objID:[2966855]]]]
01 Feb 2007 05:18:48:774 [INFO] MboSet Size : 1000
01 Feb 2007 05:18:48:774 [INFO] Fetch count so far : 1000
01 Feb 2007 05:18:48:774 [INFO] Printing StackTrace:
java.lang.Exception
at psdi.mbo.MboSet.fetchMbos(Unknown Source)
at psdi.mbo.MboSet.getMbo(Unknown Source)
at psdi.security.Profile.constructAppAuthOneApp(Unknown Source)
at psdi.security.Profile.constructAppAuthOneApp(Unknown Source)
at psdi.security.Profile.constructAppAuthAll(Unknown Source)

*********************************************************************************
*********************************************************************************
Below is an example of log output for the mxe.db.logSQLTimeLimit property.

21 Dec 2004 11:55:26:281 :select * from maxapps where app in
('LABOR','TOOL','DPAMSWS','FINCNTRL','INBXCONFIG','WFDESIGN')
(execution took 1100 milliseconds)

*********************************************************************************
*********************************************************************************
Below is an example of log output for the mxe.mbocount property.

13 Dec 2007 17:53:37:671 [INFO] CRONTASKDEF: mbosets (4), mbos (8)
13 Dec 2007 17:53:37:671 [INFO] CRONTASKINSTANCE: mbosets (5), mbos(9)
13 Dec 2007 17:53:37:671 [INFO] CRONTASKPARAM: mbosets (4), mbos (4)
13 Dec 2007 17:53:37:671 [INFO] Total = 266469376 Free = 230165096

See document 1326774 for troubleshooting memory leaks.

http://www.ibm.com/support/docview.wss?rs=3214&uid=swg21326774

*********************************************************************************
*********************************************************************************
Below is an example of log output for the mxe.db.logSQLPlan property. This property shows tables that are accessed using a full table scan in Oracle.

Explan plan: Operation, Options, Object Name
==========================================
SELECT STATEMENT, null, null
TABLE ACCESS, FULL, WORKORDER


Cross reference information
Segment Product Component Platform Version Edition
Systems and Asset Management Tivoli Asset Management for IT All
Systems and Asset Management IBM Maximo Asset Management Essentials All
Systems and Asset Management Tivoli Asset Management
Systems and Asset Management Tivoli Change and Configuration Management Database
Systems and Asset Management Tivoli Service Request Manager
Systems and Asset Management Tivoli Service Automation Manager

Rate this page:

(0 users)Average rating

Document information


More support for:

IBM Maximo Asset Management
System Related

Software version:

5.2, 6.0, 6.1, 6.2, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 7.1, 7.1.1, 7.2, 7.2.1, 7.5

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1291250

Modified date:

2010-07-17

Translate my page

Machine Translation

Content navigation