DB2 performance tuning using the DB2 Configuration Advisor

Tuning a database to get optimal performance can be an overwhelming task. DB2® configuration parameters play an important role in performance, as they affect the operating characteristics of a database or database manager. When you are tuning your database for performance, the worst possible approach is to change the value of many performance tuning parameters without having any idea of what is causing the performance problem. In such cases, DB2 Configuration Advisor wizard gives DBAs a good starting point with initial configuration parameter settings upon which they could make improvements if they want. This article familiarizes you with various database configuration parameters and the use of the Configuration Advisor wizard in performance tuning. To see how these principles apply, you examine two business case scenarios for online transaction processing (OLTP) and online analytical processing (OLAP) systems.

Suma C. Shastry (suma.chakrabarti@in.ibm.com), Staff Software Engineer, IBM

Suma C. ShastrySuma Shastry is a project lead working with IBM Software Labs, India for the Information Management team. She has six years of working experience in DB2. Her primary focus is DB2 tools development. She is a certified IBM DB2 DBA and has expertise in SVT, FVT, regression, and test automation.



Mohankumar S P (mohankumarsp@in.ibm.com), Software Engineer, IBM

Mohankumar S PMohan Saraswatipura works as a DB2 administrator at IBM Software Labs, India. He is a member of the Information Management System, and is certified as an IBM DB2 DBA. He has worked on DB2 in various areas including SVT, FVT, and samples development.



24 May 2004

Introduction

A database system is a complex data-processing environment that includes hardware resources, software components, and application programs. There are many different factors that can affect performance; including poor system design or poor application design. Tuning a database to get an optimal performance can be an overwhelming task.

DB2 provides a set of advisors and tools including the Configuration Advisor, the Design Advisor, the Memory Visualizer, the Event Analyser, and many more to help with tuning your DB2 database systems. DB2 configuration parameters play an important role in performance as they affect the operating characteristics of a database or database manager. When you carry out performance tuning, the worst possible approach is to change the value of many performance tuning parameters without having any idea of what is causing the performance problem. The Configuration Advisor makes recommendations on the initial settings for configuration parameters that can be easily adopted by inexperienced administrators or fine-tuned by more experienced administrators.

This article helps you become familiar with various database configuration parameters and the Configuration Advisor wizard. Two business case scenarios for OLTP and OLAP give you a fair idea of how to use the Configuration Advisor for DB2 performance tuning.


Factors which may affect the DB2 performance

  • Poor application design: When you experience performance problems, in many cases these stem from poor application design and inefficient programs. The database itself may not have any problems at all. For example, SQL statements written inappropriately can degrade overall performance, even though your database is well designed and tuned.
  • Poor system and database design: Poor design of your system or databases can also be a reason for performance problems. Inefficient disk storage layout, or failing to consider performance when designing and implementing your database, degrades performance, and can be very difficult to fix once your production system has been started.
  • System resource shortage: System resource shortages can cause bottlenecks in your database system.
  • CPU: Too many users running many applications on a CPU may cause system degradation.
  • Memory: Every process uses some physical memory. If you have insufficient memory, you may find that applications fail, or your system starts thrashing.
  • Disk I/O: I/O performance can play an important part in a database system. Too much activity on a single disk or I/O bus may cause performance problems.
  • Network: Unless you are in a standalone environment, the network plays an important role. If the network is too slow, then this may appear to the client as a performance problem at the database server.

Because of these issues, it becomes extremely important for the DB2 system to control how system and database resources are allocated and used. DB2 achieves this by means of configuration parameters. Be aware that the default values provided for many of these configuration parameters were produced with very simple systems in mind. As a result, you can usually improve overall system and database performance significantly with very little effort by changing the value of one or more configuration parameters. In fact, you should always modify DB2 configuration parameter values if the database environment contains one or more of the following:

  • Large databases
  • Databases that normally service large numbers of concurrent connections
  • One or more special applications that have high performance requirements
  • A special hardware configuration
  • Unique query or transaction loads
  • Unique query or transaction types

DB2 database configuration parameters

A database configuration file is created and initialized each time a new database is created. Each database configuration file comprises 82 different parameters. There are two ways to view the contents of a database configuration file:

  • By using the Database Configuration dialog, which is accessible from the DB2 Control Center. and
  • By executing the command line processor (CLP) command DATABASE CONFIGURATION.

The syntax for the command is as follows:

db2 GET DATABASE | DB CONFIGURATION | CONFIG | CFG FOR DatabaseAlias

Where DatabaseAlias identifies the DB2 database (by alias) for which you want to display configuration parameter values.

The value of one or more database configuration parameters can be changed using the Database Configuration dialog, or by executing the CLP command UPDATE DATABASE CONFIGURATION

The syntax for this command is as follows:

Click to see code listing

db2 UPDATE DATABASE | DB CONFIGURATION | CONFIG | CFG FOR DatabaseAlias USING Parameter Value < IMMEDIATE | DEFERRED >

Where:

  • DatabaseAlias identifies the DB2 database (by alias) to display configuration parameter values for.
  • Parameter identifies one or more database configuration parameters (by keyword) whose value is to be modified.
  • Value identifies the new value to assign to the database configuration parameters specified.

If the IMMEDIATE option is specified, all changes made take place immediately, if possible. If the DEFERRED option is used instead, changes would not become effective until all existing connections to the database have been terminated, and a new database connection is established. The IMMEDIATE option is the default.

For example, to change the value of the pckcachesz database configuration parameter for the SAMPLE database to 10000, you would execute the UPDATE statement as follows:

db2 UPDATE DB CFG FOR SAMPLE USING PCKCACHESZ 10000

This would set the parameter value immediately. You could specify the DEFFERED keyword for the value to take effect after instance restart.

Some of the parameters have a significant effect on performance, while others have little or no effect. The following table shows the database configuration parameters that are most likely to produce significant performance improvements.

Table 1. Database configuration parameters that affect DB2 performance
Sl noParameterValue Range (default)Description
1avg_appls1 — maxappls
Default: 1
Specifies the average number of active applications that normally access the database. The SQL optimizer uses this parameter to help estimate how much buffer pool memory is available for the chosen access plan at application run time.
2scatalogcache_sz-1, 8 — 524,288 4k Pages
Default: -1
Specifies the amount of memory (in pages) that is to be used to cache system catalog information.
3chngpgs_thresh5 — 99%
Default: 60
Specifies the percentage of pages stored in a buffer pool that must be modified before the asynchronous page cleaners used by DB2 are started, if they are not already active.
4dft_degree-1, 1 — 32,767
Default: 1
Specifies the default value for the CURRENT DEGREE special register and the DEGREE bind option.
5locklist4 — 60,000 4k Pages
Default: 25, 50, or 100
Specifies the maximum amount of memory (in pages) that is to be allocated and used to hold the lock list.
6logbufsz4 — 4096 4kPages
Default: 8
Specifies the amount of memory (in pages) that is to be used to buffer log records before they are written to disk.
7maxlocks1 — 100%
Default: 10 or 22
Specifies the maximum amount (in percentage) of the lock list that any one application is allowed to use.
8mincommit1 — 25
Default: 1
Specifies the number of COMMIT SQL statements that are to be processed before log records are written to disk.
9min_dec_div_3YES, NO
Default: NO
Specifies whether or not the result of decimal division arithmetic operations is to always have a scale of at least 3.
10num_iocleaners0 — 255
Default: 1
Specifies the number of asynchronous page cleaners that are to be used by the database.
11dft_degree1 — 255
Default: 3
Specifies the number of I/O servers that are to be used by the database. I/O servers are used on behalf of database agents to perform prefetch I/O and asynchronous I/O needed by utilities, such as backup and restore.
12pckcachesz-1, 32 — 64,000 4kPages
Default: -1
Specifies the amount of application memory (in pages) that are used to cache packages.
13seqdetectYES, NO
Default: YES
Specifies whether or not sequential detection for the database is enabled.
14dft_sheapthres_shrdegree250 — 2,097,152 4k Pages
Default: sortheap
Specifies the maximum amount of memory (in pages) that is to be used at any one time to perform sort operations.
15sortheap16 — 524,288 4k Pages
Default: 256
Specifies the amount of memory (in pages) that is to be available to perform sort operations.

When tuning a DB2 database for performance, it is always a good idea to start by making adjustments to the configuration parameters shown in Table 1.


Configuration Advisor wizard along with OLTP case study

OLTP is a class of application that facilitates and manages transaction-oriented applications; typically data-entry and data retrieval transactions. The industries that work on OLTP systems are banking application systems, airline systems, supermarkets, railway systems, and manufactures. Typically the OLTP workload consists of hundreds of concurrently running short transactions. Performance is one of the most important factor in any database system. This article focuses on a usage of Configuration Advisor for initial performance tuning.

This article takes an example of a credit card transaction system that has a typical OLTP workload. The system has the following specification:

  • System Configuration: Linux AMD64, 4 CPU's, 14 GB of RAM, 220 GB storage space
  • Connections:Fifteen-20 database connections from the application server
  • Transactions/min: 12000
  • Each transaction consists of about 25-35 SQL statements.
  • Each transaction updates two-four tables and creates new entries in the transaction history table before returning an approval or disapproval of the transaction.
  • Backup and logging are done on totally separate logical units.

With this background, take a tour of the Configuration Advisor wizard for this scenario.

  1. Open the Control Center and browse to find the target database.
    Figure 1. Control Center view
    Control Center View
  2. Right Click on the database, and click the Configuration Advisor menu item.
    Figure 2. Configuration Advisor menu item
    Configuration Advisor menu
  3. The Introduction screen shows the target database to be tuned. Click Next.
    Figure 3. Introduction screen
    Introduction screen
  4. The Server screen detects, automatically, what percentage of the memory is currently allocated for DB2 database manager.
    Figure 4. Server screen
    Server screen
  5. Assume that the machine is a dedicated OLTP server. Allocate 90 percent of the memory, and click Next.
    Figure 5. Customize memory allocation
    Customize memory allocation
  6. This screen allows you to decide the kind of workload you are running against the database. Since the system is an OLTP system, specify the workload as Transactions, and click Next.
    Figure 6. Workload screen
    Workload screen
  7. The transactions screen allows you to specify the kind of transactions that occurs against the database. It is also important to mention how many transactions per minute occur, so that the Configuration Advisor can decide the optimal configuration parameter values. In this scenario, 12000 transactions per minute is specified, and each transaction consists of 25-35 SQL statements. Click Next.
    Figure 7. Typical transaction type
    Typical transaction type
  8. The Administration priority screen allows you to choose the priority. If Faster Transaction Performance is chosen, the importance is given to the performance improvement over the data recovery. By default, the Both option is selected. In this scenario, you would choose Faster Transaction Performance, and click Next.
    Figure 8. Specify the priority
    Specify the priority
  9. It is assumed that the performance tuning is being performed on a production database that has the data populated. Choose Yes, and click Next.
    Figure 9. Data populated
    Data populated
  10. On this screen, you specify the average number of connected local and remote applications. In this scenario, you would specify 10 and 20 as local and remote application connections, and click Next.
    Figure 10. Number of connections
    Number of connections
  11. This screen allows you to decide the isolation level for the transactions. For more information about isolation levels, refer to the Resources section below. Choose Cursor stability for this scenario, and click Next.
    Figure 11. Isolation level
    Isolation level
  12. The Scheduler screen allows you to run the task. It also has an option for saving this task and schedule it to run at later time, provided scheduler is enabled on your system. Choose Run now without saving task history, and click Next.
    Figure 12. Scheduler screen
    Scheduler screen
  13. The Results screen shows the configuration parameters with the current values and the recommended values. Refer to Table 2 for a summary of recommended parameter values for both for OLTP and OLAP systems. This screen allows the user to save the script to a file, if the Save task only option was chosen on the Scheduler screen. This script file can then be run manually using the DB2 CLP. For more information, refer to the How to run the script using DB2 CLP section of this article. Click Finish.
    Figure 13. Results screen
    Results screen
  14. A DB20000 message dialog appears to indicate that all the changes to the configuration parameter values have been applied by the Configuration Advisor.
    Figure 14. DB2 Message Dialog
    DB2 Message Dialog

How to run the script using the DB2 CLP

It was discussed earlier that the DB2 Configuration Advisor has an option for saving the script to a file and running it using the DB2 CLP. Figure 15 shows what the Results screen with the Export Scripts option selected looks like. You can execute the script file using the following commands:

 1.db2 CONNECT TO OLTP USER <username> USING <passwd>
 2.db2 -tvf <filename>
Figure 15. Results Page - Export scripts to a file
Results Page - Export scripts to a file

Autoconfigure feature of DB2

Alternatively, experienced DB2 DBAs can make use of the autoconfigure feature of DB2 while creating a database. This can be done using the AUTOCONFIGURE option in CREATE DATABASE command. The following is an example of creating the autoconfigure database named "OLTP."

      db2 CREATE DB OLTP AUTOCONFIGURE USING ADMIN_PRIORITY performance MEM_PERCENT 90 
          WORKLOAD_TYPE complex NUM_STMTS 12000 NUM_LOCAL_APPS 20 ISOLATION CS
          BP_RESIZEABLE yes NUM_REMOTE_APPS 70 APPLY DB AND DBM"

OLAP systems

OLAP is an approach to quickly provide the answer to analytical queries that are dimensional in nature. It is part of the broader category of business intelligence that also includes extract, transform, and load (ETL), relational reporting, and data mining. The typical applications of OLAP are in business reporting for sales, marketing, management reporting, business performance management (BPM), budgeting and forecasting, financial reporting, and similar areas.

Databases configured for OLAP employ a multidimensional data model, allowing for complex analytical and ad-hoc queries with a rapid execution time.

Specification

  • System Configuration: Linux AMD64, 4 CPU's, 14 GB of RAM, 220 GB storage space
  • Connections: 12-15 database connections from the application server
  • Transactions/min: 1200
  • Each transaction consists of about 10-15 SQL statements

Table 2 gives the summary of recommended values for OLTP and OLAP systems, with the above mentioned specifications.

Table 2. Initial vs. recommended values for OLTP and OLAP systems
Sl noParameterInitial ValueRecommended Values OLTP                          OLAP
1catalogcache_sz-1260260
2chngpgs_thresh605059
3dft_degree111
4locklist100100100
5logbufsz8268203
6maxlocks105960
7mincommit111
8appgroup_mem_sz300001195912030
9num_iocleaners144
10num_ioservers344
10pckcachesz-1859859
11num_poolagents-1400400
12sheapthres_shr200002906129084
13sortheap256484581
14IBMDEFAULTBP1000268756268841

Performance summary

For the OLTP scenario discussed above, the performance gain was approximately three-fold compared to the initial performance. And in the case of the OLAP scenario, it was approximately four-fold.

Wind up

This should be enough to get you started with using the Configuration Advisor for the initial tuning of your database. You are encouraged to go through various articles on DB2 performance tuning to know about major DB2 configuration parameters that can affect OLAP and OLTP system performance and general performance tuning tips.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=124044
ArticleTitle=DB2 performance tuning using the DB2 Configuration Advisor
publish-date=05242004