DB2 performance tuning using the DB2 Configuration Advisor
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
The syntax for the command is as follows:
db2 GET DATABASE | DB CONFIGURATION | CONFIG | CFG FOR DatabaseAlias
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:
db2 UPDATE DATABASE | DB CONFIGURATION | CONFIG | CFG FOR DatabaseAlias USING Parameter Value < IMMEDIATE | DEFERRED >
DatabaseAliasidentifies the DB2 database (by alias) to display configuration parameter values for.
Parameteridentifies 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.
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 no||Parameter||Value Range (default)||Description|
|1||avg_appls||1 — maxappls |
|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.|
|2||scatalogcache_sz||-1, 8 — 524,288 4k Pages |
|Specifies the amount of memory (in pages) that is to be used to cache system catalog information.|
|3||chngpgs_thresh||5 — 99% |
|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.|
|4||dft_degree||-1, 1 — 32,767 |
|Specifies the default value for the CURRENT DEGREE special register and the DEGREE bind option.|
|5||locklist||4 — 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.|
|6||logbufsz||4 — 4096 4kPages |
|Specifies the amount of memory (in pages) that is to be used to buffer log records before they are written to disk.|
|7||maxlocks||1 — 100% |
Default: 10 or 22
|Specifies the maximum amount (in percentage) of the lock list that any one application is allowed to use.|
|8||mincommit||1 — 25 |
|Specifies the number of |
|9||min_dec_div_3||YES, NO |
|Specifies whether or not the result of decimal division arithmetic operations is to always have a scale of at least 3.|
|10||num_iocleaners||0 — 255 |
|Specifies the number of asynchronous page cleaners that are to be used by the database.|
|11||dft_degree||1 — 255 |
|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.|
|12||pckcachesz||-1, 32 — 64,000 4kPages |
|Specifies the amount of application memory (in pages) that are used to cache packages.|
|13||seqdetect||YES, NO |
|Specifies whether or not sequential detection for the database is enabled.|
|14||dft_sheapthres_shrdegree||250 — 2,097,152 4k Pages |
|Specifies the maximum amount of memory (in pages) that is to be used at any one time to perform sort operations.|
|15||sortheap||16 — 524,288 4k Pages |
|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.
- Open the Control Center and browse to find the target database.
Figure 1. Control Center view
- Right Click on the database, and click the Configuration Advisor menu item.
Figure 2. Configuration Advisor menu item
- The Introduction screen shows the target database to be tuned. Click Next.
Figure 3. Introduction screen
- The Server screen detects, automatically, what percentage of the memory is currently allocated for DB2 database manager.
Figure 4. Server screen
- Assume that the machine is a dedicated OLTP server. Allocate 90 percent of the memory, and click Next.
Figure 5. Customize memory allocation
- 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
- 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
- The Administration priority screen allows you to choose the priority. If
Faster Transaction Performanceis chosen, the importance is given to the performance improvement over the data recovery. By default, the
Bothoption is selected. In this scenario, you would choose Faster Transaction Performance, and click Next.
Figure 8. Specify the priority
- 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
- 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
- This screen allows you to decide the isolation level for the transactions. For more information about isolation levels, refer to the Related topics section below. Choose Cursor stability for this scenario, and click Next.
Figure 11. Isolation level
- 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
- 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 onlyoption 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
- 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
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
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 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.
- 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 no||Parameter||Initial Value||Recommended Values OLTP OLAP|
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.
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.
- DB2 Viper Information Center: Your resource for information on the upcoming version of DB2.
- "Administration Made Easier: Scheduling and Automation in DB2 Universal Database" (IBM, January 2003): Learn more about scheduling and automation in DB2 UDB.
- "Best practices for tuning DB2 UDB v8.1 and its databases" (developerWorks: April 2004): Get optimal performance out of your DB2 UDB database and its applications.
- "A Quick Reference for Tuning DB2 Universal Database EEE" (developerWorks, May 2002): Learn techniques for tuning DB2 Extended Enterprise Edition (EEE), including memory usage, buffer pools, and tuning tools.
- "DB2 Tuning Tips for OLTP Applications" (developerWorks, January 2002): Learn tuning tips based on lessons learned from running Online Transaction Processing (OLTP)-type performance benchmarks.
- Learn more about Isolation levels.
- "Simplify performance management and tuning with DB2 Performance Expert" (developerWorks, September 2004): Be introduced to, and find out about the basics of, DB2 Performance Expert.
- Know how to schedule a task.
- "Top 10 performance tips" (developerWorks, March 2004): Read about troubleshooting tips to rapidly find the cause of performance problems, and develop remedial actions and tuning suggestions.
- developerWorks DB2 for Linux, UNIX, and Windows page: Expand your DB2 skills.
- 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.