There are certain prerequisites that must be satisfied
before performance benchmark testing can be initiated.
Before you start performance benchmark testing:
- Complete both the logical and physical design of the database
against which your application will run
- Create tables, views, and indexes
- Normalize tables, bind application packages, and populate tables
with realistic data; ensure that appropriate statistics are available
- Plan to run against a production-size database, so that the application
can test representative memory requirements; if this is not possible,
try to ensure that the proportions of available system resources to
data in the test and production systems are the same (for example,
if the test system has 10% of the data, use 10% of the processor time
and 10% of the memory that is available to the production system)
- Place database objects in their final disk locations, size log
files, determine the location of work files and backup images, and
test backup procedures
- Check packages to ensure that performance options, such as row
blocking, are enabled when possible
Although the practical limits of an application might be revealed
during benchmark testing, the purpose of the benchmark is to measure
performance, not to detect defects.
Your benchmark testing program should run in an accurate representation
of the final production environment. Ideally, it should run on the
same server model with the same memory and disk configurations. This
is especially important if the application will ultimately serve large
numbers of users and process large amounts of data. The operating
system and any communications or storage facilities used directly
by the benchmark testing program should also have been tuned previously.
SQL statements to be benchmark tested should be either representative
SQL or worst-case SQL, as described in the following list.
- Representative SQL
- Representative SQL includes those statements that are executed
during typical operations of the application that is being benchmark
tested. Which statements are selected depends on the nature of the
application. For example, a data-entry application might test an INSERT
statement, whereas a banking transaction might test a FETCH, an UPDATE,
and several INSERT statements.
- Worst-case SQL
- Statements falling under this category include:
- Statements that are executed frequently
- Statements that are processing high volumes of data
- Statements that are time-critical. For example, statements in
an application that runs to retrieve and update customer information
while the customer is waiting on the telephone.
- Statements with a large number of joins, or the most complex statements
in the application. For example, statements in a banking application
that produces summaries of monthly activity for all of a customer's
accounts. A common table might list the customer's address and account
numbers; however, several other tables must be joined to process and
integrate all of the necessary account transaction information.
- Statements that have a poor access path, such as one that is not
supported by an available index
- Statements that have a long execution time
- Statements that are executed only at application initialization
time, but that have disproportionately large resource requirements.
For example, statements in an application that generates a list of
account work that must be processed during the day. When the application
starts, the first major SQL statement causes a seven-way join, which
creates a very large list of all the accounts for which this application
user is responsible. This statement might only run a few times each
day, but it takes several minutes to run if it has not been tuned
properly.
DB2 10.5 for Linux, UNIX, and Windows