Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 19, 2010

Why IBM DB2 DBAs Love Load Testing

By Lockwood Lyon

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

» See All Articles by Columnist Lockwood Lyon

DB2 Archives