Section 16.5. Configuring Your Databases

16.5. Configuring Your Databases

The database configuration parameters affect all aspects of your database performance and all applications that access the database. This section examines the parameters that have the biggest impact on system performance and provides suggestions on how to choose the right value for the parameter.

Table 16.3 lists the parameters with the biggest impact on system performance.

Table 16.3. Parameters with the Most Impact on System Performance for Configuring Your Database

Parameter Name	Description
AVG_APPLS	Average number of active applications.
MINCOMMIT	Number of commits to group.
LOGBUFSZ	Size of the log buffer.
SORTHEAP	Memory available for sorts.
SHEAPTHRES_SHR	Shared sort heap threshold.
LOCKLIST	Memory available to store lock information.
MAXLOCKS	Percent of lock list per application.
NUM_IOSERVERS	Number of I/O servers.
NUM_IOCLEANERS	Number of asynchronous page cleaners.
CHNGPGS_THRESH	Changed pages threshold before soft checkpoint.
SOFTMAX	Percent of log file before soft checkpoint.

The database buffer pools also have a very big impact on the performance of the database and its applications. You can choose the size of your buffer pools based on the default buffer pool size (BUFFPAGE), or you can size each buffer pool individually using the create bufferpool or alter bufferpool statements. (See section 8.5, Buffer Pools, for a detailed discussion of buffer pool usage.)

16.5.1. Average Number of Active Applications

The DB2 optimizer uses the value you set for the average number of active applications when it is determining the optimal access plans for statements in your applications. This number is used to determine the percentage of total system resources that each application can use.

Examine your current workload to determine the best setting for the average number of active applications. You can do this by taking database monitor snapshots at various times throughout the day and averaging the Average number of concurrently executing applications snapshot element. You can get this information for the sample database using either of the following commands:

 GET SNAPSHOT FOR DATABASE ON sample | grep -i 'Appls. executing in db manager currently' 

or

 SELECT appls_in_db2 FROM table(snapshot_database('sample', -1)) as snapshot_database 

NOTE

The Snapshot Monitor functions are discussed in detail in section 16.7, Snapshot Monitoring.

You can then set the average number of active applications for the database sample based on the snapshot information as follows:

 update db cfg for sample using avg_appls 10 

16.5.2. Database Logging

The log buffer is an area of memory that helps speed up the database logging process. DB2 writes information about all transactions to the log buffer and then flushes this buffer to disk periodically. This improves database performance since DB2 does not have to write every change to disk immediately. before making the changes in the database. This process is known as write-ahead logging. Remember that writing log records to disk is different than writing database changes to disk. Database changes are written (flushed) to disk either during buffer pool cleaning or when the buffer pool is full.

To ensure the integrity of your database, DB2 writes the log buffers to disk when:

• One or more transactions commit

• The log buffer is full

• One second has elapsed since the last log buffer flush

By default the log buffer is flushed to disk after every commit statement. However, for a database with a lot of concurrent applications, you can tell DB2 to wait to flush the log buffer until a specific number of commits occur. This number is known as the number of commits to group and is controlled by the MINCOMMIT database configuration parameter. When you set this parameter to a value greater than one, the applications that issue a commit may not return immediately because DB2 must ensure that the log buffer is written to disk before returning to the application. If you have many applications running very small, short transactions, you may see a slow down in the applications since they may wait up to one second for the commits to return. You can increase the number of commits to group for the sample database to a value of 5 using the command:

 update db cfg for sample using mincommit 5 

Since the log buffer is also flushed to disk when it becomes full, it is important to have a log buffer that is large enough that it is not constantly being written to disk. To increase the size of the log buffer for the sample database to 250 pages, use the command:

 update db cfg for sample using logbufsz 250 

16.5.3. Sorting

There are two database configuration parameters that affect sorting in your database applications. The database sort heap (SORTHEAP) specifies the maximum amount of memory that each individual sort can use.

You will not be able to have a large enough sort heap so that all sorts can occur in memory, especially for a large data warehouse. Therefore, you need to estimate the sort heap requirements for your system based on the EXPLAIN information for the queries being executed. The EXPLAIN output tells you if the optimizer has chosen to perform a sort to build the result set for your query. If there is a sort in the access plan, then in the sort portion of the access plan, there are two pieces of information:

• The average row size

• The estimated number of rows to be sorted

You can multiply these two values together to get a rough estimate of the memory required for the sort operation.

NOTE

Each row that is being sorted uses some extra space in the sort heap, so there is some overhead required to perform the sort.

You can set the sort heap threshold for the sample database as follows:

 update db cfg for sample using sortheap 6400 

As discussed earlier, private and shared sorts use memory from two different memory areas. The maximum amount of memory available for shared sorts (the shared sort heap threshold, SHEAPTHRES_SHR) within a database is allocated when the database is activated or when your first application connects to the database. When any shared sort occurs within the database it uses memory within this area. If there are already a number of shared sorts in process, and your application attempts to perform another shared sort, DB2 checks to make sure there is enough memory within this shared sort area. If there is enough memory available, the sort will be done as normal; if there is not enough memory available, the sort will be overflowed and DB2 creates a temporary table to perform the sort.

NOTE

Unlike the sort heap threshold, the shared sort heap threshold is a hard limit.

You can set the shared sort heap threshold for the sample database using the following command:

 update db cfg for sample using sheapthres_shr 120000 

16.5.4. Locking

When you are accessing data in a database, DB2 acquires locks as it is reading the data. The mode of the lock and the object that the lock is obtained on are determined by the isolation level of the application. No matter on which object the lock is held, DB2 needs to store and manage the information about all locks in the database. This information is stored in the database lock list. The lock list size (LOCKLIST) specifies the amount of memory that is available to store the lock information for the database. You can set the size of the lock list for the sample database using the command:

 update db cfg for sample using locklist 2048 

If the lock list becomes full, DB2 will perform lock escalation: the process where DB2 replaces a number of row-level locks with a single table lock. This can drastically reduce the amount of free space in the lock list for future lock requests.

Lock escalation can also occur when the number of locks held by a single application reaches or exceeds the maximum percent of lock list before escalation (MAXLOCKS). When an application reaches or exceeds this value, DB2 performs lock escalation by:

1. Examining the lock list for all locks held by the application to determine which database object has the most row-level locks held on it by this application,

2. Requesting a table-level lock on this table, and

3. Releasing the row-level locks once the table lock is granted.

The default setting for the maximum percent of lock list before escalation is 10 percent on UNIX and 22 percent on Windows and Linux. You may want to increase this, especially for databases with few applications, as this can cause premature escalation. To increase the maximum percent of lock list before escalation to 35 percent, use the command:

 update db cfg for sample using maxlocks 35 

16.5.5. Buffer Pool Prefetching and Cleaning

When DB2 agents are working to service your applications, they need to read and manipulate data within the database buffer pools. If the page that DB2 needs is not already in the buffer pool, it has to be read from disk and placed into the buffer pool before DB2 can scan or update the page.

DB2 can detect when your applications are reading pages and can read them into the buffer pool before your applications need them, saving the time the applications must wait to get its result set from DB2. This is known as prefetching. You can control the amount of prefetching that your system can perform by using the number of I/O servers (NUM_IOSERVERS) configuration parameter.

In the previous example the buffer pool is full, so when DB2 tries to read a page from disk into the buffer pool, DB2 must choose a spot in the buffer pool to place the new page. If there is a page already in this spot, DB2 will check the page to see if it has changed since it was placed into the buffer pool. If it has not changed, DB2 can simply replace this page with the new page. If it has changed, DB2 must first write the page back to disk since it cannot lose these changes. Only after the page has been written to disk can the new page replace it in the buffer pool.

To try to eliminate these wait conditions, DB2 periodicallyand asynchronouslywrites changed pages in the buffer pool back to disk. This is known as buffer pool page cleaning. You can control the amount of buffer pool page cleaning that your system can perform using the number of asynchronous page cleaners (NUM_IOCLEANERS) configuration parameter.

Prefetching is most efficient if your table spaces have more than one container, allowing DB2 to take advantage of parallel I/O. Base the number of prefetchers on the number of physical disks on which you have created table spaces for your database. If your database named sample has table space containers on 32 different physical disks, you can set the number of prefetchers for the database to 32 using the command:

 update db cfg for sample using num_ioservers 32 

If your applications perform a lot of update operations, increasing the number of asynchronous page cleaners will help to improve the overall performance of your database applications. If a system crashes due to a power failure or some other reason, this also helps to reduce the database recovery time because it keeps writing changes to disk instead of having a buffer pool full of a large number of changes.

Since all the page cleaners are triggered at the same time, having too many can overwhelm your system. As a general rule of thumb, do not configure more asynchronous page cleaners than there are CPUs in your DB2 server. For an eight-way SMP server, you can set the number of asynchronous page cleaners for the sample database to eight using the command:

 update db cfg for sample using num_iocleaners 8 

There are two different configuration parameters that let you control how frequently the page cleaners are triggered.

• The changed page threshold (CHNGPGS_THRESH) tells DB2 to trigger the page cleaners after any of the database's buffer pools is this percent used by pages that have changed since they were read into the buffer pool.

• The percent of the log file reclaimed before the soft checkpoint database configuration parameter (SOFTMAX) tells DB2 to trigger the page cleaners after this percent of any of the database log files has been filled by your transactions.

NOTE

DB2 also triggers the page cleaners if a DB2 agent is attempting to place a page into the buffer pool and needs to write a changed page to disk before it can place the page in the buffer pool.