cognos性能调优.doc

文档存盘名称目录Cognos ReportNet性能调优方案1目录2第一章 软件环境调优51 HTTP SERVER（WEB SERVER）配置文件修改51.1 Timeout51.2 MaxKeepAliveRequests51.3 KeepAliveTimeout52 APPLICATION SERVER（ReportNet自带tomcat）配置文件修改52.1 maxProcessors62.2 AcceptCount62.3 ConnectionTimeout62.4 session-timeout63 Cognos ReportNet配置文件修改63.1 warpproperties.xml63.1.1 VirtualMemoryDiagnostics73.1.2 MaxCachedQueries73.2 reportservice.xml73.2.1 max_process83.2.2 linger_process83.2.3 max_non_affine_connections_per_process83.2.4 idle_process_check_interval_ms93.2.5 idle_process_max_idle_ticks93.2.6 queue_time_limit_ms93.2.7 async_wait_timeout_ms103.3 batchreportservice.xml103.3.1 max_process103.3.2 linger_process113.3.3 max_non_affine_connections_per_process113.3.4 idle_process_check_interval_ms123.3.5 idle_process_max_idle_ticks123.3.6 queue_time_limit_ms123.3.7 async_wait_timeout_ms133.4 CQEConfig.xml133.4.1 Timeout133.4.2 PoolSize133.4.3 queryReuse144 Cognos ReportNet服务器管理设置144.1 Content Store数据库连接池设定144.2 开启审计功能154.2.1 Audit Native Query164.2.2 Audit logging level164.2.3 Audit run-time usage logging level164.2.4 Audit administration logging level164.2.5 Audit other logging level164.2.6 审核等级说明165 更改网关 URI176 更改ReportNet启动服务资源配置187 调整Sort buffer size198 分布式部署199 应用服务器性能评估20第二章 报表设计调优211 Report Stuido设计调优211.1 手写SQL定制报表查询211.2 报表函数的使用231.3 过滤条件顺序调整231.4 查询字段、查询表顺序调整241.5 聚合前后设置过滤条件241.6 自动分组汇总设置251.7 字段排序设置251.8 自动排序设置261.9 报表Processing设置271.10 外连接设置281.11 向量乘积设置281.12 使用With子句291.13 报表服务器本地缓存设置301.14 汇总项设置301.15 数据库查询的策略设置（First Rows设置）312 Framework Manager设计调优312.1 手写SQL定制查询主题312.2 函数列表指定312.3 表关联设定322.4 Edit Governors查询性能设置342.4.1 Report table limits设置342.4.2 Data retrieval limits设置352.4.3 Query execution time limits设置352.4.4 Large text items limit设置36附录37第一章 软件环境调优1 HTTP SERVER（WEB SERVER）配置文件修改以apache为例说明，可修改apache配置文件apacheconfhttpd.conf。1.1 Timeout用户连接超时设置，从用户提交请求到从web server得到相应的最大时间间隔。默认值 300秒。1.2 MaxKeepAliveRequestsWeb server保持的最大活动请求数，允许持续连接的最大请求数，增大该值可一定程度提高性能，可将其设置为0（对最大连接请求数不做限制）。默认值为100。1.3 KeepAliveTimeout从同一个客户端的同一个连接发过来的两个请求之间的允许的最大时间间隔，运行reportnet的浏览器会每隔一个时间间隔向服务器发出一个同步请求，如果这个值太小的话，可能会出现找不到请求的错误。默认值为15。2 APPLICATION SERVER（ReportNet自带tomcat）配置文件修改以TOMCAT为例说明，可修改TOMCAT配置文件CRN_ROOTtomcat*.*confserver.xml。其参数集中在行：<Connector className="org.apache.coyote.tomcat4.CoyoteConnector" port="9300" minProcessors="5" maxProcessors="75" enableLookups="true" redirectPort="9443" acceptCount="100" debug="0" connectionTimeout="60000" useURIValidationHack="false" disableUploadTimeout="true"/>2.1 maxProcessorstomcat在运行的时候允许的最大处理请求（多线程）。在服务器资源允许的情况下，提高该值可以提高对资源的利用和处理速度。默认值 75。2.2 AcceptCountTomcat允许的最大连接数。在服务器资源允许的情况下，可提高该值。默认值为100。2.3 ConnectionTimeoutTomcat连接的超时设置。因为reportnet的同步连结处理http server传递给tomcat，所以该值需要和http server中KeepAliveTimeout值匹配。默认值60000秒。2.4 session-timeout文件路径：CRN_ROOTtomcat*.*confweb.xml<session-config><session-timeout>30</session-timeout></session-config>Session超时设置，默认值为30，单位分钟。3 Cognos ReportNet配置文件修改3.1 warpproperties.xml文件路径：CRN_ROOTconfiguration warpproperties.xml.sample注：将warpproperties.xml.sample文件名修改为warpproperties.xml后，重启服务后配置生效。3.1.1 VirtualMemoryDiagnostics<structure><!- Setting VirtualMemoryDiagnostics. Value can beeDiagNone=0No diagnosticseDiagPageMap=1Dump the paging map to a file (look for rsvpvdiag* in your temp directory)eDiagNoPaging=2Turns off paging to disk, all objects will be held in memoryValues can be or'd together. -><property>VirtualMemoryDiagnostics</property><value type="long">2</value> </structure>监视CRN_ROOTtemp目录，如果有cclvpage*.tmp文件生成，将eDiagNoPaging=2可以对性能有所提升。3.1.2 MaxCachedQueries<structure><!- MaxCachedQueries is the maximum number of queries the Prompt Manager will attempt to cache.Valid values are:0=OFF-1=No Limit (default)n=Cache up to 'n' queries -><property>MaxCachedQueries</property><value type="long">-1</value></structure>对报表提示页的缓存进行必要设置，可以提高报表的运行性能。3.2 reportservice.xml文件路径：CRN_ROOT webappsp2pdWEB-INFservices reportservice.xml注：修改文件后，重启服务后配置生效。3.2.1 max_process<init-param><!- The maximum number of processes that can be running at one time -><param-name>max_process</param-name><param-value>2</param-value></init-param>ReportNet服务交互式报表处理所占进程数。默认值为2，可根据CPU数情况进行调整，通常每个CPU可支持2个处理。注：该值需要和ReportNet ReportService服务中的Maximum number of interactive report service processes值匹配。3.2.2 linger_process<init-param><!- The minimum number of processes to keep in memory once they're started -><param-name>linger_process</param-name><param-value>1</param-value></init-param>ReportNet服务交互式报表处理初始化进程数。默认值为1，可根据CPU数情况进行调整，通常每个CPU可设置1个处理。注：提高该属性设置，可以提高服务器在非高峰使用时段的处理能力。3.2.3 max_non_affine_connections_per_process<init-param><!- The maximum number of non-affine connections per process -><param-name>max_non_affine_connections_per_process</param-name><param-value>4</param-value></init-param>ReportNet服务交互式报表处理所占线程数。默认值为4，可根据CPU数情况进行调整，通常每个CPU最大可支持4个处理，Windows环境建议设置为2个处理。注：该值需要和ReportNet ReportService服务中的Number of low affinity connections for the interactive report service值匹配。3.2.4 idle_process_check_interval_ms<init-param><!- Time period between checks for idle or expired processes (in ms) .Everytime we check an idle process we bump its tick count. Every time we usea process the count is reset to 0. If the count ever reaches the valueof idle_process_max_idle_ticks (below) the process is destroyed.-><param-name>idle_process_check_interval_ms</param-name><param-value>30000</param-value></init-param>空闲进程检测时间间隔，默认值为30000，单位为毫秒。由于进程的频繁启动退出会消耗服务器资源，可根据并发使用情况，减小时间间隔，以提高进程的使用效率。3.2.5 idle_process_max_idle_ticks<init-param><!- number of idle check ticks before an idle process is killed. -><param-name>idle_process_max_idle_ticks</param-name><param-value>10</param-value></init-param>空闲进程检测标记，默认值为10。由于进程的频繁启动退出会消耗服务器资源，可根据并发使用情况，增大设置，以提高进程的使用效率。3.2.6 queue_time_limit_ms<init-param><!- The maximum time a request is queued before it gives up (in ms) -><param-name>queue_time_limit_ms</param-name><param-value>30000</param-value></init-param>报表服务排队时间限制，默认值为30000，单位毫秒。可根据并发查询情况，增大时间限制，以避免请求被取消。注：该值需要和ReportNet ReportService服务中的Queue time limit of report service in seconds值匹配。3.2.7 async_wait_timeout_ms<init-param><!- The amount of time the dispatcher will wait for a async request before it cancels it (in ms) -><param-name>async_wait_timeout_ms</param-name><param-value>30000</param-value></init-param>Dispatcher请求等待同步时间，默认值为30000，单位毫秒。可根据并发查询情况，增大时间限制，以避免请求被取消。注：该值需要和tomcat以及apache中的连结超时相匹配。3.3 batchreportservice.xml文件路径：CRN_ROOT webappsp2pdWEB-INFservices batchreportservice.xml注：修改文件后，重启服务后配置生效。3.3.1 max_process<init-param><!- The maximum number of processes that can be running at one time -><param-name>max_process</param-name><param-value>2</param-value></init-param>ReportNet服务批量报表处理所占进程数。默认值为2，可根据CPU数情况进行调整，通常每个CPU可支持2个处理。注：该值需要和ReportNet ReportService服务中的Maximum number of batch report service processes值匹配。3.3.2 linger_process<init-param><!- The minimum number of processes to keep in memory once they're started -><param-name>linger_process</param-name><param-value>1</param-value></init-param>ReportNet服务批量报表处理初始化进程数。默认值为1，可根据CPU数情况进行调整，通常每个CPU可设置1个处理。注：提高该属性设置，可以提高服务器在非高峰使用时段的处理能力。3.3.3 max_non_affine_connections_per_process<init-param><!- The maximum number of non-affine connections per process -><param-name>max_non_affine_connections_per_process</param-name><param-value>4</param-value></init-param>ReportNet服务批量报表处理所占线程数。默认值为4，可根据CPU数情况进行调整，通常每个CPU最大可支持4个处理，Windows环境建议设置为2个处理。注：该值需要和ReportNet ReportService服务中的Number of low affinity connections for the batch report service值匹配。3.3.4 idle_process_check_interval_ms<init-param><!- Time period between checks for idle or expired processes (in ms) .Everytime we check an idle process we bump its tick count. Every time we usea process the count is reset to 0. If the count ever reaches the valueof idle_process_max_idle_ticks (below) the process is destroyed.-><param-name>idle_process_check_interval_ms</param-name><param-value>30000</param-value></init-param>空闲进程检测时间间隔，默认值为30000，单位为毫秒。由于进程的频繁启动退出会消耗服务器资源，可根据并发使用情况，减小时间间隔，以提高进程的使用效率。3.3.5 idle_process_max_idle_ticks<init-param><!- number of idle check ticks before an idle process is killed. -><param-name>idle_process_max_idle_ticks</param-name><param-value>10</param-value></init-param>空闲进程检测标记，默认值为10。由于进程的频繁启动退出会消耗服务器资源，可根据并发使用情况，增大设置，以提高进程的使用效率。3.3.6 queue_time_limit_ms<init-param><!- The maximum time a request is queued before it gives up (in ms) -><param-name>queue_time_limit_ms</param-name><!- 4 hours in msec. 0 (block forever) is very dangerous -><param-value>14400000</param-value></init-param>批量报表处理排队时间限制，默认值为14400000，单位毫秒。可根据并发查询情况，增大时间限制，以避免请求被取消。3.3.7 async_wait_timeout_ms<init-param><!- The amount of time the dispatcher will wait for a async request before it cancels it (in ms) -><param-name>async_wait_timeout_ms</param-name><param-value>30000</param-value></init-param>Dispatcher请求等待同步时间，默认值为30000，单位毫秒。可根据并发查询情况，增大时间限制，以避免请求被取消。注：该值需要和tomcat以及apache中的连结超时相匹配。3.4 CQEConfig.xml文件路径：CRN_ROOTconfiguration CQEConfig.xml.sample注：将CQEConfig.xml.sample文件名修改为CQEConfig.xml后，重启服务后配置生效。3.4.1 Timeout<!- Description: Database connection timeout. Default is 900 seconds (15 minutes) -><entry name="Timeout" value="900"/>应用数据库连接超时设置。默认值为900，单位秒。3.4.2 PoolSize<!- Description: Database connection pool size. -><!- Maximum number of connections managed by the report server. Default=20 -><entry name="PoolSize" value="20"/>应用数据库连接池最大连接数设置。默认值为20。3.4.3 queryReuse<section name="QueryEngine"><!- Description:Controls the queryReuse feature. The entry must be uncommented. -><!- A value of 0 will disable queryReuse. -><!- The default value of 5 means that 5 result sets are reused. The maximum is set to 10. -><!- entry name="queryReuse" value="5" /-><!- -></section>查询缓冲设置。默认值为5，最大值为10，设置为0将关闭查询缓冲。注：如要设置该属性需要删除将该属性前的注释标记。4 Cognos ReportNet服务器管理设置4.1 Content Store数据库连接池设定在浏览器内输入Cognos ReportNet门户登陆地址(http:/localhost/crn/)，在登入门户后点击Tools下拉菜单下的服务器管理（Server Administration），在监控（Monitor）的标签页下，选择类型（Type）服务（Services），在下方列出的所有服务中，选择ContentManagerService的属性，点击设置（Settings），在列出的环境（Enviorment）后点击编辑（Edit），将“改写从父条目获取的设置”(Override the settings acquired from the parent entry)选中，在出现的下方列表中可以设置Content Store数据库连接池的几项属性。下表是上图中四个参数的属性说明Parameter Purpose Default DbConnectPoolMax The maximum number of concurrent database connections that are allowed to the content store. Valid settings are -1, or 5 to 2147483647 or the database setting, whichever is less. -1 (unlimited) DbConnectPoolTimeout Maximum time in milliseconds that a thread waits for a connection to be available from the pool. Valid settings are -1 to 2147483627. A setting of 0 specifies that threads never wait for a connection if one is not available immediately. -1 (unlimited) DbConnectPoolIldleTime Minimum time in milliseconds that a connection stays idle in the pool. Only valid if the value of DbConnectPoolCleanUpPeriod setting is positive. Valid settings are -1 to 2147483647. A setting of 0 or -1 specifies that idle connections are closed upon content manager restart. 300000 (5 minutes) DbConnectPoolCleanUpPeriod Time in milliseconds between invocations of a cleanup thread that closes idle connections in the pool that exceed the setting of DbConnectPoolIdleTime. Valid settings are -1 to 2147483647. A setting of 0 or -1 specifies no clean up thread. 300000 (5 minutes) 注：详情请参见安装配置手册Cognos ReportNet(TM) Administration and Security Guide中的Manage Content Manager Database Connection Pool Settings章节。4.2 开启审计功能在浏览器内输入Cognos ReportNet门户登陆地址(http:/localhost/crn/)，在登入门户后点击Tools下拉菜单下的服务器管理（Server Administration），选择服务器的属性后，点击设置(Settings)，在下方的列表中可以对审核等级等属性进行设置。4.2.1 Audit Native Query将该设置选择为Yes后，才能看到详细的日志信息。可以通过对日志的查看达到服务器检测的效果，并根据检测结果适当调节服务器设置，使之达到最优。4.2.2 Audit logging level参见4.2.6章节4.2.3 Audit run-time usage logging level参见4.2.6章节4.2.4 Audit administration logging level参见4.2.6章节4.2.5 Audit other logging level参见4.2.6章节4.2.6 审核等级说明Category Description Audit Logs events for management and control purposes Audit administration Logs actions that involve configuration, Cognos groups and roles, data sources, distribution lists, packages, models, or deployment objects Audit run time usage Logs actions that deliver or generate report output or actions that manipulate a report or job Audit other Logs actions other than run-time usage and administration The following table indicates the details that each level logs.  1 2 3 4 5 System and service startup and shutdown      Messages at user logon      Use requests      Service requests and responses      All requests to all components along with their parameter values      Third-party queries to ReportNet components (native query)      注：详情请参见安装配置手册Cognos ReportNet(TM) Administration and Security Guide中的Log Messages章节。5 更改网关 URI 1. 启动 Cognos Configuration。2. 点击 Environment。3. 在右侧属性栏的 Gateway URI 属性中，替换网关URI中的cognos.cgi 部分: · 如果使用Windows的IIS，输入 cognosisapi.dll。· 如果使用Apache Web Server，输入： http:/computer_name:port/crn/cgi-bin/module module 为以下内容: Operating system Apache 1.3 module Apache 2.0 module Windows mod_cognos.dll mod2_cognos.dll Solaris and AIX mod_cognos.so mod2_cognos.so HPUX mod_cognos.sl mod2_cognos.sl · 如果使用的servlet gateway：https:/computer_name:port/contextName/servlet/Gateway contextName是已部署ServletGateway Web application的名称。注: 确认配置的Web server支持servlet gateway。将Gateway URI 中的主机名（computer name）更换为localhost或是服务器IP地址或机器名。 5. 保存配置，重启服务。6. 将目录CRN_ROOTwebcontent下的default.htm和index.html中含有网关cognos.cgi的URI部分替换为更换的Web Server网关。注：如果使用Apache Web Server，详情请参见安装配置手册Cognos ReportNet(TM) Installation and Configuration Guide中的Change the ReportNet Gateway章节。6 更改ReportNet启动服务资源配置1. 启动 Cognos Configuration。2. 点击 ReportNet service结点下的Cognos ReportNet服务，右键点击删除。3. 新建服务，命名为Cognos ReportNet。（可根据服务器资源情况分别采用三种不同类型服务，三种类型服务之间的主要区别为内存分配大小。）4. 在右侧属性栏的Dispatcher maximum