Tivoli Header

Administrator's Guide


Optimizing Database and Recovery Log Performance

Over time, the database size and organization can change to the point that performance is degraded. Unloading and reloading the database can have the following benefits:

The database and recovery log buffer pool sizes can also affect performance. A larger database buffer pool can improve performance. A larger recovery log buffer pool reduces how often the server forces records to the recovery log.

See Reorganizing the Database for more information about restoring database efficiency.

Adjusting the Database Buffer Pool Size

You can let Tivoli Storage Manager dynamically adjust the size of the database buffer pool or you can adjust it manually. If you specify YES for the SELFTUNEBUFPOOLSIZE server option, the database buffer pool is dynamically adjusted. The cache hit ratio statistics for the buffer pool are reset at the beginning of expiration. After expiration processing completes, the buffer pool size is adjusted dynamically.

Server expiration processing resets the database buffer pool before the next processing starts and examines if the database buffer pool cache hit ratio is above 98%. If the cache hit ratio is lower than 98%, the database buffer pool will be increased; if it is higher, the buffer pool size will not change. Increasing the database buffer pool will not be more than 10% of available real storage.

Manually Adjusting the Database Buffer Pool Size

Perform the following steps to track the database buffer pool statistics and adjust the buffer pool size:

Step 1: Reset Database Buffer Pool Utilization Statistics

Reset the buffer pool statistics. Initially, you might want to reset the statistics twice a day. Later, you can reset them less often. To reset, enter:

reset bufpool

Step 2: Monitor 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 2048KB, 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 value indicates that the size of your database buffer pool is adequate. If the value falls below 98%, consider increasing the size of the database buffer pool. For larger installations, performance could improve significantly if your cache hit percentage is greater than 99%.

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 this value is greater than 0, increase the size of the database buffer pool.

Step 3: Adjust the Database Buffer Pool

Use the BUFPOOLSIZE server option to set the size of the database buffer pool.

Adjusting the Recovery Log Buffer Pool Size

Do the following to adjust the size of the recovery log buffer pool:

Step 1: Monitor the Recovery Log Buffer Pool

To see how the recovery log 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 evaluate the log buffer pool size:

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 value below 10% means that the recovery log buffer pool size is adequate. If the percentage increases, consider increasing the recovery log buffer pool size.

Log Pool Pct. Wait
The percentage of requests for pages that are not available because all pages are waiting to write to the recovery log.

If this value is greater than 0, increase the recovery log buffer pool size.

Step 2: Adjust the Recovery Log Buffer Pool

Use the LOGPOOLSIZE server option to set the size of the recovery log buffer pool.

Reorganizing the Database

Over time, database volumes become fragmented. You can restore the efficiency of the database and improve database performance by reorganizing the database using database unload and reload processing. By reloading the database, you compress and reorganize it.

Procedure: Reorganizing the Database

Attention: Before you begin this procedure, perform a backup of your database. If an outage occurs while you are loading and reloading your database, you can use your backup copy for recovering the database.

The DSMSERV UNLOADDB operation assumes that the Tivoli Storage Manager database is usable and reads device information from the database, not from the device configuration file. A database dump operation (DSMSERV DUMPDB), on the other hand, does not assume a usable database and reads from the device configuration file.

To reorganize the database, follow these steps:

  1. Ensure that a current device configuration file exists. This file contains a copy of the device class, library, and drive definitions. These definitions are needed for the DSMSERV LOADDB utility. You must specify the name of the device configuration file by using the DEVCONFIG option in the server options file. See Saving the Device Configuration File. Also see Administrator's Reference for details on the option and the command.
  2. Before unloading the database, estimate how many tapes you will need:
  3. Halt the server if it is still running.
  4. With the server not running, issue the DSMSERV UNLOADDB utility to unload the database to tape. For example:
    dsmserv unloaddb devclass=tapeclass scratch=yes
    
    Note:
    Keep track of the order in which the tape volumes are written when the database is unloaded. You must specify the volume names in the same order when you reload the database using the DSMSERV LOADDB utility. For this task, you can either:
    • Review the output generated by the DSMSERV UNLOADDB utility and record the order of the volumes.
    • Manually view the volume history file to identify the tape volumes containing the unloaded database. The volumes have a volume type of DBDUMP. See Saving the Volume History File for details. (Do not restart the server and issue QUERY VOLHISTORY at this step.)
  5. Format the database and recovery log. For example:
    dsmserv loadformat 2 logvol1 logvol2 1 dbvol1
    

    This utility prepares the existing server database for the DSMSERV LOADDB utility.

  6. Reload the database using the volumes that contain the data from the unload operation. For example:
    dsmserv loaddb devclass=tapeclass volumenames=db001,db002,db003
    
    For the volume names, ensure that you do the following:


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]