Tivoli Storage Manager for Sun Solaris Administrator's Guide


Optimizing the Performance of the Database and Recovery Log

Periodically the database size and internal organization can progress to where it is no longer internally efficient. To improve database performance, the database can be unloaded and reloaded in an optimal manner that will:

The database and recovery log buffer pool sizes can also affect TSM performance. A larger database buffer pool can improve performance, and 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.

Dynamically Adjusting the Database Buffer Pool Size

TSM can dynamically adjust the size of the database buffer pool, or you can do this procedure yourself.

The SELFTUNEBUFPOOLSIZE option has two values: YES or NO. NO is the default. If YES is specified, TSM will dynamically adjust the database buffer pool.

If the SELFTUNEBUFPOOLSIZE option is specified as YES in the server options file, buffer pool cache hit ratio statistics will be reset at the beginning of expiration. After expiration processing completes, the BUFPOOLSIZE will be 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

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 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

Reorganizing the Database

Over time, database volumes become fragmented. You can restore the efficiency of the database by unloading and reloading it. First unload the database in key order. Then reload the database, which will, in effect, compress and reorganize it. When the server is offline, use the DSMSERV UNLOADDB utility. After unloading the database, use the DSMSERV FORMAT utility (which initializes the database) or the DSMSERV LOADFORMAT utility (which does not initialize the database) followed by the DSMSERV LOADDB utility. After using the DSMSERV UNLOADDB, the DSMSERV FORMAT, or DSMSERV LOADFORMAT, and the DSMSERV LOADDB utilities, the DSMSERV AUDITDB utility may be required to locate and correct any database inconsistencies. Here is an example of reorganizing the database, where the database is unloaded to tape:

  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 (see Saving the Device Configuration File for details).
  2. Estimate the database size:
    1. Use the size of your existing database volumes if you cannot bring up the server and must estimate the size of your database.
    2. When the server is online, issue the following command:
      query db
      
    3. From the query output, multiply the Used Pages by the Page Size. Use the result to estimate the number of tapes of a specific device class that you will need to unload the database.

    This estimate is usually larger than necessary. The space required will likely be less than your estimate.

  3. When the server is offline, issue the DSMSERV UNLOADDB utility to unload the database to tape. For example:
    dsmserv unloaddb devclass=tapeclass scratch=yes
    
  4. To reload the database, mount the volumes in the same order in which they were mounted for the unload operation. This order information is recorded in the volume history file (see Saving the Volume History File for details). If you do not have a volume history file, label the volumes and record the order in which they are mounted.
  5. Stop the server:
    halt
    
  6. Reinitialize the database and recovery log:
    dsmserv format 1 log1 2 db1 db2
    
  7. Reload the database:
    dsmserv loaddb devclass=tapeclass volumenames=db001,db002,db003
    


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