Performing buffer pool simulation

You perform buffer pool simulation by enabling simulated buffer pools, and examining the effect of various simulated buffer pool sizes.

Follow these steps to perform buffer pool simulation:

1. Start a statistics trace for IFCID 0002 to capture trace records with the simulation results. For example:

   -START TRACE(STAT) IFCID(2)

   Alternatively, you can obtain simulation information by issuing the DISPLAY BUFFERPOOL command after you do a simulation, as shown in step 5.

2. Issue the ALTER BUFFERPOOL command for a buffer pool that you want to simulate. You need to set the buffer pool name and the SPSIZE option. You might also need to set the SPSEQT option. Although the PGSTEAL option and the VPSIZE option are not directly related to simulation, you might need to change them to make the simulation work.

   Follow these guidelines for setting SPSIZE and SPSEQT when you issue ALTER BUFFERPOOL:

   SPSIZE value

   You use SPSIZE to simulate the behavior of the buffer pool when you increase the buffer pool size by a certain amount. To choose a value for SPSIZE, find the VPSIZE value for the real buffer pool with the highest synchronous read I/O. Start by setting an SPSIZE value that increases the buffer pool size by a small amount, such as 20%. For example, if VPSIZE is currently 5000 buffers, set SPSIZE to 1000 to see what happens if you increase the buffer pool size by 20%.

   If you start with an SPSIZE value that is too small, you might underestimate the benefit of increasing the buffer pool size, because pages in the simulated buffer pool might be stolen before they are referenced again.

   Gradually increasing the SPSIZE value over multiple simulations generally works better than choosing a very large value immediately. Although the Db2 database manager does not allocate buffers for simulated buffer pools, storage is still needed to track the pages in the simulated buffer pools. The approximate amount of storage that is used by simulated buffer pools is 2% of SPSIZE*4KB. For example, if SPSIZE is 6553600, approximately 500MB of storage is needed. In addition, the following system limits on buffer pools include the amount of storage that is used for simulated buffer pools:

   • A maximum of twice the amount of real storage
   • A maximum of 1TB

   As with VPSIZE, you need to ensure that the amount of storage that you specify with SPSIZE is backed by real storage, to avoid paging and additional CPU overhead.

   SPSEQT value

   For the first simulation, if you are unsure about a value to set, do not specify SPSEQT. If you specify a non-zero value for SPSIZE, but you do not set the SPSEQT value, the SPSEQT value is the same as the VPSEQT value. For subsequent simulations in which you increase the SPSIZE value, set SPSEQT to a lower value if you do not want the number of buffers for sequentially accessed pages to be increased proportionately with the increased buffer pool size. If you specify a SPSEQT value of 0, sequentially accessed pages are not moved into the simulated buffer pool.

   The following settings are also required for VPSIZE and PGSTEAL:

   VPSIZE value

   Do not set VPSIZE to 0 while you are doing buffer pool simulation. Setting VPSIZE to 0 deallocates real and simulated buffer pools, even if the SPSIZE value is greater than 0.

   PGSTEAL value

   The PGSTEAL value must be LRU. Buffer pool simulation works only with a least-recently-used page stealing algorithm. Perform simulations only for buffer pools that already use the least-recently-used page stealing algorithm.

3. Optional: Issue ALTER BUFFERPOOL to start simulations for other buffer pools.

   Start no more than three simulations at the same time. Buffer pool simulation requires CPU overhead, which varies depending on the number of simulated buffer pools and the size of each simulated buffer pool. The additional CPU overhead is a result of moving information about stolen pages in the virtual buffer pools to the simulated buffer pools.

4. Run a workload that results in a typical amount of activity for the buffer pool that you are simulating.
5. Take one of the following actions to obtain the simulation results:
   • Issue the DISPLAY BUFFERPOOL command to obtain the results. For example:

     -DISPLAY BUFFERPOOL(BP0) DETAIL

   • Format the trace records from the statistics trace that you started in step 1.
6. Analyze the results. See Buffer pool simulation results for more information.
7. If the results indicate that you can save read I/O operations by increasing the buffer pool size, free the allocated storage for simulated buffer pools by issuing ALTER BUFFERPOOL with SPSIZE(0). Then start with step 2, specifying a larger buffer pool size and a larger or smaller sequential steal threshold. If you stopped the trace to obtain trace records in step 5, start with step 1.