Administrator's Guide

Optimizing the Performance of the Database or Recovery Log

The size of the database and recovery log buffer pools can affect performance at the cost of greater memory. For example, a large database buffer pool can improve performance, and a large recovery log buffer pool reduces how often the server forces records to the recovery log.

Adjusting the Database Buffer Pool

You can adjust the size of the database buffer pool by updating the server option for it.

Step 1: Resetting Database Buffer Pool Utilization Statistics

To gather statistics on database use, reset the buffer pool statistics on a regular basis and chart the results. Initially, you might want to monitor the database twice a day. Later, when most client nodes have been registered to the server, you can reset statistics each week.

To reset the database buffer pool, enter:

reset bufpool

Step 2: Requesting Information about the Database Buffer Pool

To see if the database buffer pool is adequate for database performance, enter:

query db format=detailed

The server displays a report, like this:

+--------------------------------------------------------------------------------+
|  Available Space (MB): 196                                                     |
|Assigned Capacity (MB): 196                                                     |
|Maximum Extension (MB): 0                                                       |
|Maximum Reduction (MB): 176                                                     |
|     Page Size (bytes): 4,096                                                   |
|           Total Pages: 50,176                                                  |
|            Used Pages: 4,755                                                   |
|                 %Util: 9.5                                                     |
|            Max. %Util: 9.5                                                     |
|      Physical Volumes: 5                                                       |
|     Buffer Pool Pages: 128                                                     |
| Total Buffer Requests: 1,193,212                                               |
|        Cache Hit Pct.: 99.73                                                   |
|       Cache Wait Pct.: 0.00                                                    |
+--------------------------------------------------------------------------------+

Use the following fields to evaluate your current use of the database buffer pool:

Buffer Pool Pages

The number of pages in the database buffer pool. This value is determined by the server option for the size of the database buffer pool. At installation, the database buffer pool is set to 512KB, which equals 128 database pages.

Total Buffer Requests

The number of requests for database pages since the server was last started or the buffer pool was last reset. If you regularly reset the buffer pool, you can see trends over time.

Cache Hit Pct

The percentage of requests for cached database pages in the database buffer pool that were not read from disk.

A high cache hit percentage indicates that the size of your database buffer pool is adequate. If the cache hit percentage is below 90%, consider increasing the size of the database buffer pool.

Cache Wait Pct

The percentage of requests for database pages that had to wait for a buffer to become available in the database buffer pool.

When the cache wait percentage is greater than 0, increase the size of the database buffer pool.

Step 3: Set the Size of the Database Buffer Pool

You can set the size of the database buffer pool by setting the buffer pool size option (BUFPOOLSIZE). You can set options through the ADSM Server Utilities or by editing the server options file (see ADSM Administrator's Reference).

Adjusting the Recovery Log Buffer Pool

You can adjust the size of the recovery log buffer pool by updating the server option for it.

Step 1: Requesting Information about the Recovery Log Buffer Pool

To see how the buffer pool size affects recovery log performance, enter:

query log format=detailed

The server displays a report, like this:

+--------------------------------------------------------------------------------+
|  Available Space (MB): 12                                                      |
|Assigned Capacity (MB): 12                                                      |
|Maximum Extension (MB): 0                                                       |
|Maximum Reduction (MB): 8                                                       |
|     Page Size (bytes): 4,096                                                   |
|           Total Pages: 3,072                                                   |
|            Used Pages: 227                                                     |
|                 %Util: 7.4                                                     |
|            Max. %Util: 69.6                                                    |
|      Physical Volumes: 1                                                       |
|        Log Pool Pages: 32                                                      |
|    Log Pool Pct. Util: 6.25                                                    |
|    Log Pool Pct. Wait: 0.00                                                    |
|                                                                                |
+--------------------------------------------------------------------------------+

Use the following fields to optimize the log buffer pool size for your installation:

Log Pool Pages

The number of pages in the recovery log buffer pool. This value is set by the server option for the size of the recovery log buffer pool. At installation, the default setting is 128KB, which equals 32 recovery log pages.

Log Pool Pct. Util

The percentage of pages used to write changes to the recovery log after a transaction is committed.

A low value (under 10%) indicates that the size of your recovery log buffer pool is adequate. As this number increases, consider increasing the size of the recovery log buffer pool.

Log Pool Pct. Wait

The percentage of requests for a page that is not available because all pages are waiting to write to the recovery log.

If the log pool percentage wait value is greater than 0, increase the size of the recovery log buffer pool.

Step 2: Setting the Size of the Recovery Log Buffer Pool

You can set the size of the recovery log buffer pool by setting the buffer pool size option (LOGPOOLSIZE). You can set options through the ADSM Server Utilities or by editing the server options file (see ADSM Administrator's Reference).