Tivoli Common Reporting Cognos Tuning Parameters

Kalarith Sisowath IT Architect - Lab Services Business Analytics Tel: +33-1-5875-5016 E-mail: [email protected]

Document Control

Revision

Date

Author

Summary of Changes

1

02/04/14

Kalarith Sisowath

First Version

Reviewers Name

Title

Andrew Bruckshaw

IT Specialist, Cloud and Smarter Infrastructure

Introduction This document outlines the changes that should made to the default Cognos parameters that have an impact on performance. The default settings are not suitable for a production environment. In this example case each server is to host the Gateway, Application Tier and Content Manager components and have been sized to support approximately 120 concurrent users running interactive and batch type queries of 'intermediate' complexity. The hardware platform is assumed to be IBM System P with Power7 processors running AIX. Each server is allocated 23 CPU cores and 55 GB RAM. Thanks to Kalarith Sisowath, IT Architect - Lab Services, Business Analytics for providing the information.

Cognos Parameter Settings The following section details the recommended Cognos parameter settings based on the above assumptions for sizing. Open IBM Cognos Configuration: Change initial heap size to 1150 (this is for Cognos BI 8.4.27.78 – 32bit)

In folder /configuration, save and edit files : CQEConfig.xml:

LocalCacheOutputSize 50 SessionCacheSize 30 VirtualMemoryDiagnostics 2 Add manually this entry : BurstThreadPoolSize 50 In folder /tomcat4.1.7/conf, save and edit file : server.xml : In folder /webapps/p2pd/WEB-INF/, save and edit file: p2pd_deploy_defaults.properties : # Content Manager Connection Pool size dispatcher.cm_poolsize=80 # Size of the request cache of the report server (default = 10) wp_cache_size=80 In folder /webapps/p2pd/WEB-INF/, save and edit files: batchreportservice.xml and reportservice.xml: max_process 6 Go to the administration Cognos portal, then modify setting to increase number of process for the BatchReportService and the ReportService :

Advanced settings : Go to IBM Cognos Administration tab :

Then in “Settings” and “Advanced settings” > “Edit…”

CM.GENERATEUNIQUENAMES

TRUE

CM.CAMSearchDeferredLocking

TRUE

CM.MAXPATHLOCK

100000

CM.DATACACHEMAXK

2147483647

CM.POLICYCACHESIZE

100000

RSVP.QUERY.OPTIMIZEQRD

TRUE

RSVP.VPICACHE.ENABLED

TRUE

RSVP.VIRTUAL.MAPPINGMODE

PAGEDINDEX

RSVP.PROMPT.RECONCILIATION

CHUNKED

RSVP.PROMPT.RECONCILIATION.CHUNKSIZE

7

RSVP.CONCURRENTQUERY.ENABLEDFORINTERACTIVEOUTPUT

TRUE

RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT

8

RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS

24

RSVP.FILE.EXTENSION.XLS

TRUE

CM.DbConnectPoolIdleTime

30000

CM.DbConnectPoolCleanUpPeriod

30000

Explanations:

CM.GENERATEUNIQUENAMES

Under certain operations, the CM checks the uniqueness of name of his internal objects. That operation maintains a lock on the Content Store for the duration of the checking. Default: FALSE, Changed to TRUE Effect: allow to bypassing checking name uniqueness when updating internal defaultName with identical value, thus no unnecessary locks are issued.

CM.POLICYCACHESIZE

Size of the CM internal cache where the policy data (users policies correlated to the LDAP) are stored. The CM Lock Manager maintains a lock until the entire user policies are fetched from the CS. Default: 1000 , Changed to 100000 Effect: Increasing the CM.POLICYCACHESIZE setting will reduce the number of times Content Manager fetches policy data directly from the Content Store database, reducing the lock duration of the lock manager subcomponent and speeding the portal browsing/navigation.

CM.CAMSearchDeferredLocking

Parameter which instructs the CM Lock Manager how to manage the locks on LDAP objects during the analysis of a CAMID searchPath. Default: FALSE, Changed to TRUE Effect: When CAM deferred locking is enabled, the CM LockManager will NOT lock any objects (i.e. the entire namespace) during the initial lock analysis of a CAMID searchPath. Instead, CM will defer the locking until CM is iterating the results of the AAA.Search. During iteration, CM will release all previous locks and request a new lock ONLY for the current CAMID proxy being processed

CM.DATACACHEMAXK

CM manages the size of the temp file cache based on the advanced setting CM.DATACACHEMAXK, which by default is set to 1G. The temp file cache is implemented as a Least Recently Used cache (LRU cache), where files are removed either 15 minutes since their last access (and the next time the cache is accessed), or the directory reaches the configured maximum. Default: 1000000, Changed to 2147483647 Effect: To increase the size limit on CM temporary files. The default is set to ´1000000´, the units are in KB so this is roughly 1GB. The maximum possible value is

INT_MAX or 2147483647 (10 digits, take care!). CM temp files are cleaned automatically after 15 minutes of non-use and at shutdown, so the size of CM temp files won’t grow infinitely large. CM.MAXPATHLOCK

When doing move/copy/paste operations on Portal folders, CM maintain a lock on source folders for the duration of the move operation Default: 1000, Changed to 100000 Effect: increasing that value has an effect that no locks are maintain during the duration of fetching objects by blocks, thus allowing a very short locking time.

CM.DbConnectPoolIdleTime

Minimum time in milliseconds that a connection stays idle in the Content Manager pool. Default: 300000 (5 minutes), Changed to 30000 ( 30 seconds) Effect: reducing to 30sec the duration of idle connection before cleaning them, after the 15mn of inactivity timeout has occurred.

CM.DbConnectPoolCleanUpPeriod

Time in milliseconds between invocations of a cleanup thread that closes idle connections in the Content Manager pool that exceed the setting of DbConnectPoolIdleTime. Default: 300000 (5 minutes), Changed to 30000 ( 30 seconds) Effect: each 30sec, the cleanup thread will be activated in order to delete the idle connection, reducing to a maximum of 59sec the time a idle connection stays alive before deletion

RSVP.PROMPT.RECONCILIATION COMPLETE or 0 All queries are sent at once. This is the slowest, most accurate form of reconciliation. This is the default setting. GROUPED or 1 Queries are sent by priority group. This setting works best for reports that have many unfiltered queries and few filtered queries. It provides medium speed and high reconciliation accuracy. CHUNKED GROUPED or 2 Queries are sent by priority group with a maximum number per request. The queries do not span groups. This setting works best on reports that have many queries with similar filter expressions. It provides maximum speed and low reconciliation accuracy. CHUNKED or 3 Queries are sent by priority group with a maximum number per request. The queries can span groups. RSVP.CONCURRENTQUERY.NUM Enables concurrent query execution and sets the maximum number of query HELPERSPERPROCESS execution helpers for each report service or batch report service process. Each query execution helper results in an additional data source connection. For example, a report service has four processes with two high affinity connections and two low affinity connections. If the maximum number of query execution helpers is set to 0 (disabled), the maximum number of data source connections created by the report service is 16 (two low affinity connections plus two high affinity connections plus zero query execution helpers times four processes). If the maximum number of query execution helpers is set to 2, the maximum number of data source connections created by the report service is 24 (two low affinity connections plus two high affinity connections plus two query execution helpers times four processes). The calculated maximum number of data source connections applies to each report service or batch report service process. Default: 0 (concurrent query execution disabled) RSVP.CONCURRENTQUERY.ENA BLEDFORINTERACTIVEOUTPUT

Enables concurrent query execution when the report service is producing interactive output. For interactive reports, if concurrent query execution is enabled, some queries may be unnecessarily executed because the results are not used. For example, all the queries for a multi-page report may execute with at least one query on each page, but the user may view only the first page. If you do not want to use resources for results that are not used in interactive reports, disable this parameter. Authored prompt pages are not interactive output and are not affected by this setting. Default: false (disabled)

RSVP.VPICACHE.ENABLED

optimize the management of the virtual memory by enabling a internal cache for objects manipulated by the Report Server.

RSVP.VIRTUAL.MAPPINGMODE

optimize the mapping mode of the virtual memory allowed to the Report Server.

Peak period and non peak period: We can play with 2 periods to spread more processor to Report or Batch report service for a specific period.

Max processes and JVM memory variations depending on system specification The information below indicates how the values for MaxProcessess and JVM heap size should be set to make best use of different machine specifications.