A load test gives the database administrator quite a lot of valuable information and may make the difference between poor and acceptable application performance. Here are some proactive tips to make your IBM DB2 production implementation a success.
Many software development projects
produce software that moves into the production environment without a formal
load test. This is unfortunate! A load test gives the database administrator
quite a lot of valuable information. It may make the difference between poor
and acceptable application performance. Here are some proactive tips to make
your production implementation a success.
How is Load Testing Used?
The term load testing is one
encompassing several different types of pre-production test scenarios. Depending
upon your enterprise standards and the type or size of project, this may
include one or more of the following:
-
Integration
testing. Ensure
application modules join together well. -
Systems
testing. Confirm the
application works well with other applications. -
Recovery
testing. Can the
application recover correctly and within service level agreements after a failure? -
Security
testing. Verify that
users have necessary and sufficient security to perform functions. -
Stress testing. Tests various combinations of
transactions loads, including number of concurrent users, volume of input data,
and transaction load on the database. In some cases, it is necessary to
determine at what load point the application or database will fail (sometimes
called test-to-failure).
Usually, the application team and the
release manager have discussed and decided upon the testing schedule and level
of testing to be performed prior to releasing the application to production.
Reasons for executing various test scenarios may depend upon various factors
including staff availability and budget, project deliver date, and availability
of a suitable database environment for testing.
In some cases, the reasons for doing
load testing are deemed insufficient and this process is bypassed on the way to
production. This is unfortunate: the database administrator (DBA) gains quite
a lot of valuable information during load testing.
In point of fact, the DBA should insist
upon a load test, and do whatever is necessary to assist developers and release
managers with this process. Here are the most important items the DBA looks
for, and how they should be addressed.
-
Deadlocks and
timeouts. These
symptoms may be caused by one or more separate issues. The most common in load
testing is the “initialization problem”. Since the application is processing
for the first time, many transactional tables may be empty (e.g., customers,
current orders, account activity). Since table and index I/O is done at the
block level (usually by the 4K page), many active table rows and index entries
will be stored on the same page. Simultaneous insert and update activity for
different rows on the same page may cause deadlock or timeout issues. As the tables
and indexes grow this issue tends to disappear. Later, the occurrence of these
symptoms is most likely due to poor application design, transactions that are
too long (i.e., do far too many database updates per unit of work), or
poor database design. I will cover these issues in a later article. -
Hot spots. These are a common occurrence in
transactional systems that assign keys to new entities or transactions. Typical
examples are an order entry system where new orders are assigned sequentially
ascending keys or a financial management system where money transactions are
keyed by the date/time of their entry (again, an ascending key). Tables and
indexes may be structured to support application functionality and performance
for such key assignment. One common method is to store new table rows at the
physical end of the table. While this may help performance by clustering the
data in key sequence, the table pages at the end of the table will be very
frequently referenced. The same is true for indexes that support ascending
keys. -
SQL capture for
access path analysis.
DBAs prefer to design database table and index structures to support data
retrieval. Knowing the SQL that will access the data is key to performance
tuning. The DBA can use EXPLAINs of the SQL to note access paths and to make
proper index choices and other structural decisions prior to production
implementation. -
Monitor growth
patterns. As the
application matures, performance may suffer due to table and index size. DBAs
consider a pre-defined purge process to be a critical factor to prevent this.
However, many times the application development team does not define or create
a purge process. The usual reason: “We won’t be purging for a year, so there’s
plenty of time to get it defined.” Regrettably, in many cases, the purge
process is not defined, and the DBA is left to deal with a combination of huge
tables and application performance problems. Far better to deal with this
before production implementation, and the load test provides a good place for
the DBA to demonstrate how data volume can affect performance. -
Confirm object
definitions. The
moment of production implementation is not the time to discover that
tables, indexes, or associated structures are not defined correctly. A
successfully completed load test gives everyone confidence that all table
columns have the correct attributes and that all SQL statements update and
retrieve the proper data.
During the Load Test
In preparation for the load test, the
DBA needs to have several processes ready. Here are the most common.
-
Reorgs and
RunStats. DB2
chooses access paths for SQL statements based on object statistics. For the
initial execution of the load test, many tables will be empty and their
statistics will reflect that. However, as the tables and indexes grow the
statistics become outdated and possibly detrimental to performance. A typical
example: For a simple table access, DB2 may choose a tablespace scan access
path (after all, the statistics show that the table is empty!). To avoid this
issue (the “initialization problem” discussed earlier), the DBA should be
prepared to run index and tablespace reorgs and runstats during the beginning
stages of the load test. -
Full Backups
after Initial Testing. Since
the initial state of the application (many empty tables) will not be a normal state,
performance measures during the load test may not be valid. The usual fix for
this is to begin load testing and exercise the application for a predetermined
period. At this point, the DBA would run reorgs and runstats as described
previously, and then execute full image copies of all tables involved in the
test. This provides a recovery point that can be used in subsequent rounds of
testing, thus bypassing the need for any priming transactions and repeated
reorgs and runstats. -
Metrics for
Resource Constraints. The
most common resource constraints encountered in load testing by mainframe DB2
applications are I/Os, application throughput, and waits due to object locking.
Decide ahead of time how these will be measured and what criteria will be used
for database service levels.
Tips for a Successful Load Test
-
Achieve Agreement
on Purge Processes. Initialization
issues provide a good lesson for developers and management. Application staff
now have a direct experience where data volume and distribution patterns lead
to performance problems. Future growth may cause problems too; so, they need to
make sure that all purge processes are defined and implemented prior to the
move to production. -
Capture and
save SQL. In addition
to executing EXPLAINs on captured SQL, the DBA can store the captured
statements for future documentation or analysis purposes. This can be of great
assistance when introducing a new DBA to the application, or when analyzing a
production performance issue. -
Full Backups after
Seeding. After
initialization is complete, execute image copies for all tables. This provides
a recovery point for later testing, speeding up the process. -
Address Production
Issues before Production. DBAs
prefer to be proactive and deal with potential issues before they become
production nightmares. In reacting to production performance problems, the DBAs
commonly use tools in the following list. Read it, and imagine being able to
apply the correct fixes during load testing rather than during a production
crisis. - Adding new indexes
-
Implement
row-level locking -
Tune SQL
statements -
Partition or
re-partition tables -
Implement an
emergency purge process
-
Reset invalid or
missing security
Additional Resources
IBM Information Management Software for z/OS Solutions Information Center
