Rational ClearQuest Performance Improvement Rational Software Whitepaper

Rational Software Whitepaper
Rational ClearQuest Performance
Improvement
Jim Tykal, SCM Specialist – Worldwide Technical Marketing
Revision 1.3
26-Sept-2003
Table of Contents
OVERVIEW...................................................................................................1
RATIONAL CLEARQUEST PERFORMANCE OPTIMIZATION ..................1
SCHEMA DESIGN CONSIDERATIONS ......................................................1
Rational ClearQuest Hooks – Performance Considerations ...........................................2
Additional Performance Implications of using PERL .......................................................2
Additional Schema Design Guidelines...............................................................................2
SERVER CONFIGURATION ........................................................................4
Database Server ...................................................................................................................4
Oracle-Specific Recommendations .......................................................................... 4
SQL Server-Specific Recommendations .................................................................. 4
IIS Web Server ......................................................................................................................5
NETWORK CONFIGURATION ....................................................................5
DIAGNOSING PERFORMANCE PROBLEMS.............................................6
ADDITIONAL RESOURCES ........................................................................8
Overview
The intent of this whitepaper is to consolidate much of the available information regarding Rational ClearQuest
Performance Improvement into one navigable resource. It contains a number of suggestions – as well as hyperlinks
to more detailed information – for experienced Rational ClearQuest administrators and schema designers seeking to
optimize the performance of the Rational ClearQuest applications. First-time schema designers would be best served
using examples from the RDN ClearQuest Hooks Index, and these sample hooks may help avoid some of the
performance issues addressed in this paper.
Rational ClearQuest Performance Optimization
Regardless of how you will be deploying Rational ClearQuest – Windows or Unix clients, a Web Server, or a
combination of several interfaces – your level of application performance will depend largely on how you address
the following three key areas:
1.
Schema Design Considerations
2.
Server Configuration (Database and IIS Servers)
3.
Network Configuration
We’ll discuss each of these in the following sections.
Schema Design Considerations
This is probably the one area of your Rational ClearQuest deployment where you have the most control over how
the application is going to perform. While Rational ClearQuest is a flexible and configurable change management
system, it is not intended to be a complete program development environment. It is designed for customization of
the “out of the box” schemas, not for complete re-implementation of the underlying database application. If you find
yourself writing thousands of lines of hook code in ClearQuest, you should probably look to simplify your business
use cases instead of trying to capture and implement these complex rules in your hooks. The result will be a system
that is much easier for you to deploy and maintain, and much more usable for your team members who are using the
application to track defects and change requests. As a schema designer, you will have many choices on how you
implement the desired functionality in your schema. In most cases, there is more than one way to achieve a
particular result, and understanding the performance implications of using various constructs in Rational ClearQuest
can help you make good decisions that will provide your users with the functionality they need with minimal impact
to overall performance.
Rational ClearQuest provides several “out of the box” schemas upon which a Rational ClearQuest Schema Designer
can base his/her implementation. While many schema designers have requirements that are largely met by the
functionality provided in these schemas, others have more complex needs such as:
•
Multi-level parent/child relationships between change requests and/or other records
•
Need to track entire history of all changes made (governing agencies sometimes require this)
•
Role-based process controls requiring extensive hook scripting
•
Complex requirements for change notification, resulting in numerous e-mail rules
•
Dependent fields, where the list of valid choice list values for one field depend on the value(s) selected
in one or more other fields
The more of these requirements your Rational ClearQuest implementation must meet, the more diligent you will
need to be in optimizing the performance of your environment.
Rational ClearQuest Hooks – Performance Considerations
Particular attention needs to be paid to the use of hook code within the schema – particularly if you hooks are
implemented in perl as opposed to VBScript. You generally have a choice on whether to use perl or VBScript for
your Rational ClearQuest hooks. There are limitations, however, which may force you to use one or the other. For
example, if your environment needs to support users of the native Unix client for Rational ClearQuest, then you
must write your hooks in perl, as VBScript is not supported in the Unix environment.
Regardless of your chosen scripting language, keep in mind that when a user logs in to Rational ClearQuest –
whether it be through a native Windows or Unix client or through the web interface – all hook code defined in the
schema is loaded into memory on the CQ client (or CQWeb Server). More hook code results in longer login times
and more memory consumption.
Also, ALL Record Scripts (for the current Record Type) and ALL Global Scripts are passed to the interpreter and
compiled – EACH AND EVERY TIME an “action” is performed or a base action hook executes. It doesn’t matter if
the executing hook actually uses any of the Record Scripts or Global Scripts – they are still passed to the interpreter.
This can have performance implications, particularly if your schema contains significant amounts of code in Record
Scripts and/or Global scripts. To minimize the amount of code sent to the interpreter, consider using PERL modules
or VBScript COM objects – which can be loaded on an “as-needed” basis at execution time – to encapsulate your
global scripts.
Additional Performance Implications of using PERL
If you are planning to provide user access to your Rational ClearQuest application via CQWeb, it is important to
note that CQWeb’s execution of perl code is SINGLE-THREADED. This will limit the scalability of CQWeb
implementations using perl as a scripting language for your Rational ClearQuest hooks. Unless your deployment
also includes – or is likely to include – native Unix/Linux clients, it is recommended that VBScript be used instead
of perl in CQWeb deployments, particularly if your implementation requires extensive hook code.
Additional Schema Design Guidelines
The rest of this section references a number of other resources that provide detailed information on the performance
impact of using certain Rational ClearQuest constructs in your schema. There are trade-offs to be made between
performance and functionality, and the better you – as a schema designer – understand these trade-offs, the more
likely you will be to deploy a successful Rational ClearQuest application.
•
Avoid using “AdminSession” to access data from the schema repository.
User information is stored in the user database, so there is no need to go to the schema repository for data
such as the user’s full name, etc. If you must retrieve data from the schema repository, store it in a Session
Variable for reuse. (see example of using session variables below)
•
Avoid using “Recalculate Choice List” in your field properties.
Doing so will force either a round-trip to the database or a hook script to run every time any field in the
record is changed during an edit. Instead, write a “VALUE_CHANGED” hook in the “parent” field to
force an update on the “child” choicelist values using the “InvalidateFieldChoiceList” and
“SetFieldChoiceList” methods.
•
Static/Dynamic Choice Lists should have <100 entries.
This is because of cache limits in the Rational ClearQuest client. If you need more than 100 choices, use a
REFERENCE_LIST.
•
Limit number of REFERENCE or REFERENCE_LIST fields displayed on a form.
Rational ClearQuest must perform a “join” of all record types referenced by a form in order to render that
form onscreen. Multi-table joins are expensive operations -- the time it takes to display a form will increase
dramatically as the number of REFERENCE or REFERENCE_LIST fields to be displayed increases. A
good rule of thumb is to have no more than 10 such fields on a single form.
•
Avoid using FIELD_VALIDATION hooks.
Field validation hooks are executed each time ANY field value changes during the course of an action. (It
also increases the amount of hook code to be loaded into memory at login time.) Instead, validate
individual field values in the ACTION_VALIDATION hook, as this is run only once when the user attempt
to “Apply” (or commit) the changes made.
•
Minimize the number of database queries executed by your hook code.
Whenever possible, store the returned value of commonly used queries in a session variable. During that
session, you can simply read the value instead of re-executing the query each time a hook is run. Do this
only for queries that return fairly static data, such as user information, EntityType data, etc. If you use a
session variable to store the return data from a query such as “all open defects owned by <user>”, you run
the risk of that data being out of date on subsequent uses of the session variable. See below for an example
of using a session variable to avoid a database query:
$sessionObj = $entity->GetSession();
if ($sessionObj->HasValue(“variable_name”)) {
$result = $sessionObj->GetNameValue(“variable_name”);
}
else {
# do whatever query is necessary to determine value…
$session->SetNameValue(“variable_name”, $result);
}
•
Avoid returning MULTILINE_TEXT fields in queries.
With no multiline text fields, CQ will batch select 250 rows at a time from the database. So, a query
returning N records will require roughly N/250 logical round-trips to the database. With multiline fields in
the query result, CQ will fetch all non-multiline fields for ONE ROW, then do a separate fetch for each
MULTILINE text field in that row. Then, it fetches the next row in the same way. So, a query returning N
rows with M number of multiline fields in the result set will require (1+M)*N logical round-trips to the
database. NOTE: A fix to address this issue is planned as a patch to v2003.06.00. We recommend avoiding
the use of multiline text fields in your query result sets until this patch is installed.
•
Use E-Mail Rules judiciously.
Each e-mail rule generally contains a filter, and each filter is associated with a Query. Each time an e-mail
rule is run, the associated query is executed. E-mail rules are loaded and run with every action. As an
alternative to e-mail rules, you could use notification hooks (where you would need to write your own code
to construct and send messages) which fire only on the appropriate actions. Try to minimize or combine email rules to reduce the amount of query code executed. This may mean sending “generic” notifications to
a wider audience instead of targeted e-mails to specific users or groups, but the performance trade-off may
be worth it.
•
Don’t retrieve an entire record when a field or two will do.
Retrieving an entire CQ record from the database is an expensive operation. If you only need one or two
fields from a record or a collection of records, using a query to extract a result set containing just those
fields is much more efficient than retrieving the record(s) and looping through those records to choose the
desired field(s).
Similarly, you can obtain the value of a field contained in a referenced record without retrieving the entire
record first. For example, suppose the current entity has a field named “Project” that is a REFERENCE to
another record type, which contains a field called “Name”. You can obtain the name of the referenced
project using:
my $projname = $entity->GetFieldValue("Project.Name")->GetValue();
This will be more efficient than:
my $project = $entity->GetFieldValue(“Project”)->GetValue();
my $projname = $project->GetFieldValue(“Name”)->GetValue();
Server Configuration
First and foremost, it is critical that you separate the tasks of your database server and your IIS server (if you are
using CQWeb) onto separate machines. This will not only improve performance by distributing load, but also
greatly improve the stability of your Rational ClearQuest environment.
Database Server
Regardless of the back-end database vendor selected for your Rational ClearQuest implementation, there are some
general configuration tasks that will help ensure maximum performance. Periodic tuning and maintenance of an
enterprise-level database requires the expertise of a DBA, and is vital to the long-range performance and scalability
of your Rational ClearQuest application.
ClearQuest creates indexes on the ID field of stateful record types and on the primary key fields in stateless records.
These indexes should not be removed or otherwise tampered with or many of the normal operations on the database
system will be impaired. One of the keys to improving the performance of the Rational ClearQuest database is the
creation of additional indexes on the table(s) that are commonly accessed. Knowing where and how to apply indexes
requires DBA-level knowledge of the backend database in use, and an understanding of what will be helpful and
what won’t based on the typical queries being performed. Although not a trivial task, it would be extremely
beneficial to have a DBA review the SQL queries being executed on the database, and construct indexes designed to
optimize the speed at which the requested data is accessed. Because different schemas and different usage patterns
require different indexes to optimize performance, it is far beyond the scope of this whitepaper to provide specifics
on how to create indexes that would benefit all Rational ClearQuest implementations. Please refer to the following
solutions available for examples of creating indexes on the History and Attachments tables in a Rational ClearQuest
database:
•
Solution ID: 25610 – Create an INDEX on the History Table
•
Solution ID: 140585615 – Create an INDEX on the Attachments Table
Indexes need to be recreated periodically -- they are overwritten by database upgrades, for example – so be sure to
document the procedure.
Also, it is not uncommon to turn on SQL tracing at the ODBC level when diagnosing Rational ClearQuest
performance issues. However, to ensure maximum performance, make sure SQL Tracing is not activated while your
database is “live”:
•
Solution ID: 129591114 – WINDOWS: SQL tracing cause any problems? How do I start or stop SQL
tracing?
Oracle-Specific Recommendations
•
Solution ID: 18912 – Oracle Patch information for Rational ClearQuest Web performance/reliability issues
•
“ANALYZE” the database to optimize performance based on the size of the tables and the presence of
indexes
o To be run on both schema and user databases
o The Optimizer_mode should probably be set to CHOOSE.
o The Optimizer_mode is a dynamic parameter, which can be set anytime via:
ALTER SESSION SET OPTIMIZER_MODE='CHOOSE'
o Example:
sqlplus sys/<Password>@<TNS-Alias>
SQL> exec DBMS_UTILITY.ANALYZE_SCHEMA
('<schema-name1>','COMPUTE');
SQL> exec DBMS_UTILITY.ANALYZE_SCHEMA
('<schema-name2>','COMPUTE');
•
Oracle archive log should be periodically cleared
SQL Server-Specific Recommendations
• SQL Server Configuration Tips
o
Solution ID: 13900 – WINDOWS: How to create an empty SQL_SERVER 7/2000 database
o
Solution ID: 16187 – WINDOWS: SQL Server Performance Tuning during setup
o
Solution ID: 16188 – WINDOWS: SQL Server Performance Tuning Hardware Bottlenecks
o
Solution ID: 16422 – WINDOWS: SQL Server Performance Tuning on Windows NT 4.0
IIS Web Server
•
Ensure that IIS is configured according to the instructions in the Rational ClearQuest Installation Guide.
•
Make sure users are logging out before closing their browsers and/or set the Login Timeout to a shorter
period
o With Superuser privileges, select “Edit Web Settings” from the Operations menu in CQWeb
o Change the timeout judiciously – you don’t want users losing their edits if they step away from
their work for just a few minutes…
•
Periodically restart the IIS Web Publishing Service (due to inetinfo.exe memory leaks)
o use “bounceiis” from the CQWeb SEKit, available from your IBM Rational tech rep or support
•
Periodically delete files in the “<RATIONAL CLEARQUEST_HOME>/WWW/cache” directory
o Remove all files except readme.htm
•
Periodically delete entries in the Windows Event Viewer
o Open “Control Panel -> Administrative Tools -> Event Viewer”
o Select “Application” Log, then do “Action -> Clear All Events”
Network Configuration
To understand the importance of network configuration in a Rational ClearQuest environment, one needs to
understand the underlying architecture of a Rational ClearQuest deployment, and the communication that needs to
take place between the various components.
CQ Designer
(Windows)
Windows/UNIX
Client
Web
Client
Client
Easy to use
CQ Core
CQ Core
ODBC
http
IIS Web Server
CQ Core
Windows / Unix
Database
Rational ClearQuest Architecture
Server
Scalable
The key here is that the IIS Web Server and each of the Rational ClearQuest native clients communicate to the
database server through ODBC messages. This is a very “fat” protocol, which is definitely not suitable for Wide
Area Network traffic. If you are using CQWeb, your IIS Web Server and your Rational ClearQuest database server
should be on the same subnet in your network, preferably with a very high speed connection. If you are using native
CQ clients instead of or in addition to your IIS Web Server, these clients also need to communicate with the
database server as quickly as possible. Therefore, it’s important to minimize the number of “hops” between your
Rational ClearQuest clients and the database server.
Diagnosing Performance Problems
Rational ClearQuest is one of the most flexible and easily configurable defect and change tracking products
available commercially today, but along with this power comes the ability to make good as well as bad choices.
One common result of this is poor performance. If you are experiencing delays of more than several seconds for
performing nominal operations – such as invoking an action on a record or committing changes (querying for all
records and generating lengthy reports not included) – then you may want to investigate whether the delay is due to
client processing (CQ core execution or your hook code), database server processing, or network delays.
To determine where time is being consumed in your Rational ClearQuest environment, utilize the trace mechanism
(discussed below). Select API and SQL (at a minimum), and include a timestamp. By logging this information, you
should be able to determine where the bulk of the time is being spent, and focus on improving that. If it is in the
SQL, investigate the response time the database is giving you. Some potential causes of poor SQL response times
include:
•
•
•
•
•
inadequate network bandwidth/contention
long distances (WAN operation is not recommended or supported)
domain partitioning (minimize number of network “hops” between SQL client and server)
insufficient database platform hardware
database tables (particularly History) not properly indexed
If the tracing implicates certain hooks, you should weigh the trade-offs between the functionality provided by the
hook against the performance impact. If you see lots of traffic to the database server and/or excessive client
processing time when displaying a form, you should take a closer look at your use of REFERENCE-type fields,
dependent choice lists, and/or your use of “Recalculate Choice List” to see if you can make your implementation
more efficient – keeping in mind the guidelines presented earlier in this paper. For example, there are several ways
to implement Dependent Choice Lists – from hard-coded choice list hooks to a hierarchy of dependencies in
stateless records. Each of these implementation choices have their own functional vs. performance trade-offs. See
“Implementing Dependent Choice Lists in Rational ClearQuest” for more details.
To aid in your debugging efforts, the session object supports a method named “OutputDebugString” which takes a
string parameter. In the Rational ClearQuest installation directory, there is a utility called dbwin32. When dbwin32
is active, it will display all the messages generated by OutputDebugString. This is available from VB and Perl
hooks and external scripts, on Windows, Unix and the Web (run it on the web server machine as a Windows
administrator). Running dbwin32 and adding calls to “OutputDebugString” strategically in your hooks provides
adequate debugging capabilities. When dbwin32 is not active, the messages simply go into the bit bucket. (Note that
in Windows2000, you must be running ClearQuest as a member of the Administrators group or the output is not
displayed. See http://support.microsoft.com/default.aspx?scid=kb;en-us;274559 for more information.
Beyond this, there is a very powerful tracing feature that permits a user to monitor a rich set of activities, such as
database interactions, license interactions, API calls, email processing, etc. This tracing can be controlled through
the registry (under [HKEY_CURRENT_USER\Software\Rational Software\Rational ClearQuest\Diagnostic]) or
through environment variables. The following registry keys illustrate example settings, with all common settings
and definitions provided below each key (all keys and values are case insensitive):
"Trace"="LICENSE;SESSION;SQL"
•LICENSE
- licensing operations
•DB_CONNECT
- db connections
•SESSION
- session create/destroy
•SQL
- SQL messages
•EMAIL;EMAIL_VB
- email processing
•SYSTEM_UPGRADE
- upgrade operations
•METADATA_INIT
- initialization
•API
- API calls, parameter and return values
•others available too (some unimplemented)
"Behavior"=""
- (leave this blank)
"Name"=""
- (unused)
"Report"="MESSAGE_INFO=0x0400;DIAG_FLAGS=-1”
•MESSAGE_INFO
- controls prefix of each line
0x0001:
- message number
0x0002:
- PID
0x0004:
- PPID on Unix, unused on Win32
0x0008:
- PGID on Unix, Thread ID on Win32
0x0010:
- machine name (Unix only)
0x0100:
- time (in sortable format)
0x0200:
- date (in sortable format)
0x0400:
- seconds since initial debug message (secs)
0x0800:
- seconds since previous debug message (ticks)
0x1000:
- include label for each item in prefix
•DIAG_FLAGS=-1
- displays current trace settings at top of trace,
used to see all possible settings
"Output"="ODS"
•nil
•ods
•out
•err
•cout
•cerr
•C:\…
-
output
output
output
output
output
output
output
to
to
to
to
to
to
to
bit bucket (same as empty)
OutputDebugString (i.e. dbwin32)
stdout
stderr
C++ cout stream
C++ cerr stream
specified file
"EMailSendVB"="ODS”
•nil
•ods
•out
•err
•cout
•cerr
•C:\…
-
output
output
output
output
output
output
output
to
to
to
to
to
to
to
bit bucket (same as empty)
OutputDebugString (i.e. dbwin32)
stdout
stderr
C++ cout stream
C++ cerr stream
specified file
The equivalent using environment variables is shown below, and supports all the same options described above.
In the case where both EVs and Registry keys are used, the EVs take precedence.
Environment Variable
CQ_Diag_Trace
CQ_Diag_Behavior
CQ_Diag_Name
CQ_Diag_Report
CQ_Diag_Output
CQ_Diag_EMailSendVB
Value
"LICENSE;SESSION;SQL"
"" - (leave this blank)
"" - (unused)
"MESSAGE_INFO=0x40B;DIAG_FLAGS=-1”
"ODS"
"ODS”
Additional Resources
RUC 2001 Presentations
o CM24: Scaling Rational ClearQuest Web Servers (Hahn)
o CMA15: X-treme Rational ClearQuest (Exum)
RUC 2002 Presentations
o SCM13: Rational ClearQuest Performance Tuning (Kamath)
o SCMA12: X-treme Rational ClearQuest: Tips and Tricks (Mellott, McEwen)
Rational Developer Network
o Rational ClearQuest Customization Best Practices (Exum)
o Troubleshooting Rational ClearQuest – Problems You May Encounter (Rational)
Tech Notes / Solutions
o Search using keywords such as “Rational ClearQuest” and “performance” at:
www.ibm.com/software/rational/support/
Rational Software
IBM Software Group
Dual Headquarters:
Rational Software
18880 Homestead Road
Cupertino, CA 95014
Tel: (408) 863-9900
Rational Software
20 Maguire Road
Lexington, MA 02421
Tel: (781) 676-2400
Toll-free: (800) 728-1212
E-mail: [email protected]
Web: www.rational.com
International Locations: www.rational.com/worldwide
Rational and the Rational logo, among others, are trademarks or registered trademarks of Rational Software Corporation in the
United States and/or other countries. References to other companies and their products use trademarks owned by the respective
companies and are for reference purposes only.
 Copyright 2003 IBM, Rational Software Corporation
Subject to change without notice. All rights reserved.