View our accompaning on-demand webinar: Cognos 10.2.2 Performance Tuning. Todd Schuman, a senior architect at Senturus, took us through three broad areas that affect Cognos performance.
Summary
This on-demand webinar started with a quick poll of the audience: what are the current pain points around Cognos performance? Report performance came out as the top issue. Poor report performance is something business users really notice and it can be one of the most challenging problems to address.
- Hardware
The amount of processing power available has a direct impact on the speed at which Cognos can execute and serve up reports. CPU capacity is measured in the number of cores available, which determines how many processes and threads can run. A starting point for the amount of RAM required can be calculated, but it must be monitored and adjusted based on user activity.
Cognos is designed to run in a distributed environment, meaning that the processing workload should ideally be spread over several servers, with the number of servers increased during peak periods. The first dispatcher server receives the request and then passes it to an available server. If one server is busy or not on-line, one of the other servers performs the requested task.
Most data centers now run virtual servers, where several servers run on the same piece of hardware. This reduces the management cost of each individual server, allows for quick reallocation of resources, and the rapid adjustment of server horsepower, i.e., the number of processors and amount of memory available to the application.
- Cognos Application Tuning
Many settings are based on the number of concurrent users, which can be estimated with the 100:10:1 formula: for every 100 named or licensed users, one can assume that there are 10 active users and 1 concurrent user. So a 4000 user Cognos implementation would be tuned based on 40 concurrent users.
There are 93 different application parameters to adjust and fortunately the majority can be left to the default setting. An important one to look at is the maximum number of processes, which should be adjusted based on the number of processor cores available. Then, the number of low and high affinity connections should be balanced based on the type of user activity.
Another important configuration setting is the Maximum Memory for Websphere Liberty, which has a default setting of 768. If memory is available on the server, this allotment should be increased to 1152 or 1536. A side effect of increasing this memory allocation is that the Cognos service startup time increases, but that is not usually important to end users.
The web server can be tuned as well for better performance. A quick win here is to use ISAPI instead of CGI for the Gateway (for an IIS web server) or for Apache to use the mod2 Gateway file.
A quick win on the application server (dispatcher) is to turn off Dynamic Query Mode if it is not being used. By default the Query Serviced Enabled is set to TRUE, but it can be set to FALSE to disable the service and free up about 1GB of memory.
- Reports and Data Related
Servers now are 64-bit which, among other benefits, permits more memory to be accessed by applications, which in turn created the possibility of Dynamic Query Mode (DQM). DQM is a sophisticated data cache that can dramatically improve report performance. However, it does require more RAM and possibly some package redesign. DQM can be less forgiving than Compatible Query Mode (CQM) when a model does not adhere to a star schema. DQM works best with dimensionally modeled data with deep hierarchies. It is possible to run DQM in parallel with CQM so the transition can take place gradually, or CQM can be kept for the cases that do not justify the migration effort.
Even if DQM is not an option, using a star schema model reduces the number of joins required for reporting and thus favors better performance. Star schema data models are also easier to understand which facilitates troubleshooting problematic queries.
Another performance improvement tactic is to force more of the query processing to the database. While there are some restrictions, tables or views that are frequently accessed or are very large are good candidates for transformation into materialized views (Oracle), indexed views (SQL Server), or materialized query tables (DB2) to improve data retrieval times. The Cognos query engine is very good at aggregating data on the fly, but this too can be pushed to the database by using aggregate or summary tables for summarized data frequently accessed by the Cognos reports.
PowerPlay cubes are a good short-term solution as they can deliver superior performance and user interactivity via OLAP. However, there are three major downsides: the file size limitation (which limits the number of records), the time required to compile the cube file, and PowerPlay is a mature technology with no migration path. If OLAP is an option, Cognos Dynamic Cubes should be considered as it leverages DQM technology to allow fast access to huge sets of data.
This webinar touched on only a handful of performance considerations for Cognos. Performance is an issue that everyone faces at one time or another, which led to a flood of questions being asked during the presentation. Below are some answers to the most popular queries that were asked during the live Q&A. You can also view the complete questions log.
Q: In distributed environment, how many dispatchers can we add?
A: There is no limit to the number of dispatchers. Additional dispatchers distribute the workload so that reports are not waiting in a queue to be executed. However, if reports are never waiting, adding dispatchers will not help performance. You will be better off boosting the CPU or RAM on the servers.
Q: IBM recommends 4GB for the JVM. You are referencing 1536MB. Why?
A: This is a different setting. The 1536MB recommendation is for the maximum memory for Websphere Liberty Profile in Cognos Configuration. The IBM recommendation of 4GB is for the JVM heap size for the DQM service. Get more information from IBM about the Memory Guidelines for Dynamic Query Model.
Q: So does the Query Service not need to be enabled if I am not using Dynamic Query Mode?
A: No, the Query Service is used only for Dynamic Query Mode. By disabling the service, you can save up to 1GB of RAM that this service uses.
Q: How do I tune report output that contains graphics?
A: Image files should be placed on the same server as the Cognos Gateway. The files themselves should be reduced in size to as small as possible by reducing the resolution to the minimum requirement for displaying the image on the screen.
Q: Can you explain more about how summary tables work?
A: A summary table is a table, or view, that contains aggregate calculations. For example, if the lowest level of granularity is at the day level, then a summary table could include totals for the week or month level. This improves performance because Cognos is not doing these calculations when the report executes but instead when it is retrieving the pre-calculated numbers from the database.
Q: Do I need to configure Cognos any differently for summary tables? Is Cognos Framework Manager aware of summary tables?
A: There is no special tuning required to use summary tables. Framework Manager treats summary tables like any other query subject. Determinants can be used to accommodate the different level of granularity in a DMR model.
Q: What are stitched queries in Framework Manager?
A: Stitch queries are report queries that return data from multiple fact tables. This can cause performance problems because it forces Cognos to do an outer join on multiple sub-queries which requires extensive local processing. Get more info.
Q: I run Cognos on Linux, do you recommend cgi or mod2?
A: IBM does not recommend using the default cognos.cgi Gateway file for production environments. On Linux, you are likely using an Apache web server, so the mod2 gateway is recommended. For Windows servers running IIS, ISAPI is preferred.
Q: To run a report in DQM, do we have to convert all relational packages into DMR models? Will just publishing a relational package with DQM enabled work?
A: There is some confusion between DQM and DMR: DQM is Dynamic Query Mode, an intelligent cache mechanism introduced in Cognos BI v10.1. The packages must be converted or created in DQM mode. DMR is Dimensionally Modeled Relational and is a Framework Manager feature to take advantage of a star schema and provide OLAP type functionality, such as drill up/down in Cognos reports. This functionality existed since Cognos ReportNet, the predecessor to Cognos 8/10.
DQM does not require a package with DMR, but DQM does perform better with DMR or any star schema model with deep hierarchies. Simply publishing an existing package in DQM mode will work, but there will likely be unexpected results and the performance gains will be underwhelming.
Q: To tweak our server to run more interactive reports (have lots of unused memory) do we typically just increase the number of processes and leave the high and low affinity numbers as is?
A: For interactive reports, increase the number of processes and the high affinity setting. Carefully observe report performance and make adjustments as necessary.
Q: Have you experienced CPU spikes due to Java Garbage Collection when the JVM was running out of memory due to high concurrency DQM reporting?
A: The CPU spikes may or may not be due to Garbage Collection. There is a free tool available from Oracle, Java VisualVM that you can use to investigate this possibility.
If you do find a problem with the Java Garbage Collection, there are a couple of parameters you can tweak, read this article for more information.
Q: What can be done if I must report against OLTP?
A: Reporting against a transactional system is a challenge because the database is not optimized for the longer running queries generated by a reporting tool like Cognos. Keep the report designs as efficient as possible. A simple gauge of report complexity is to look at the SQL that is generated by the queries in Report Studio. If it is hard to understand then you can probably simplify the report. Also keep the number of queries and report elements (charts, pages, images) to a minimum.
Q: If I need to decide between using Active Report and Workspace Advanced for reporting, which is a better approach if my volume of data is a concern?
A: Active Report and Workspace Advanced serve two different purposes. Active Report is for offline distribution, often to tablets and smartphones. There is no hard limit as to the volume of data that can be included in an Active Report, but the files can become difficult to distribute and slow to open if too much data is included.
Cognos Workspace Advanced is a friendly version of Report Studio, offering more features than Query Studio for self-service reporting. Like any report developed in either studio, there is no data limit. However, if a high volume of data is expected, the reports should be designed with that in mind. For example, cross-tabs, master detail reports and complex layouts will degrade performance as the volume of data increases.
Q: Can core.dmp files be deleted periodically? I see lot of them occupying huge space on the server.
A: Yes, core.dmp files can take huge amounts of disk space. Core dumps are created when a process dies unexpectedly and the contents of memory at the time of the crash is written to disk. This can be helpful when debugging code.
However, if the core files are not being used for troubleshooting, they are safe to delete. This will not improve report performance, but it is a good practice to clean up unused files on the server as running low on disk space would be a major problem. If you see core dumps being created on a regular basis, then it might be a symptom of a more serious problem.
Thanks to David Currie for attending our webinar and summarizing it in this post. David is a long-time Cognos consultant and former Cognos employee. He also blogs about business intelligence and big data at davidpcurrie.com.
