Setting the size of the buffer pool, logical-log buffer, and physical-log buffer

The values that you specify for the BUFFERPOOL, DS_TOTAL_MEMORY, LOGBUFF, and PHYSBUFF configuration parameters depend on the type of applications that you are using (OLTP or DSS) and the page size.

Table 1 lists suggested settings for these parameters or guidelines for setting the parameters.

For information about estimating the size of the resident portion of shared memory, see Estimating the size of the resident portion of shared memory. This calculation includes figuring the size of the buffer pool, logical-log buffer, physical-log buffer, and lock table.
Table 1. Guidelines for OLTP and DSS applications
Configuration Parameter OLTP Applications DSS Applications
BUFFERPOOL The percentage of physical memory that you need for buffer space depends on the amount of memory that is available on your system and the amount of memory that is used for other applications. Set to a small buffer value and increase the DS_TOTAL_MEMORY value for light scans, queries, and sorts.

For operations such as index builds that read data through the buffer pool, configure a larger number of buffers.

DS_TOTAL_MEMORY Set to a value from 20 to 50 percent of the value of SHMTOTAL, in kilobytes. Set to a value from 50 to 90 percent of SHMTOTAL.
LOGBUFF The default value for the logical log buffer size is 64 KB.

If you decide to use a smaller value, the database server generates a message a message that indicates that optimal performance might not be obtained. Using a logical log buffer smaller than 64 KB, impacts performance, not transaction integrity.

If the database or application is defined to use buffered logging, increasing the LOGBUFF size beyond 64 KB improves performance.

Because database or table logging is usually turned off for DSS applications, you can set LOGBUFF to 32 KB.
PHYSBUFF The default value for the physical log buffer size is 128 KB.

If the RTO_SERVER_RESTART configuration parameter is enabled, use the 512 kilobyte default value for PHYSBUFF.

If you decide to use a value that is smaller than the default value, the database server generates a message that indicates that optimal performance might not be obtained. Using a physical log buffer that is smaller than the default size impacts performance, not transaction integrity.

Because most DSS applications do not physically log, you can set PHYSBUFF to 32 KB.

Copyright© 2018 HCL Technologies Limited