Managing Data Buffers

The BUFFERS configuration parameter specifies the number of data buffers available to the database server. These buffers reside in the resident portion of shared memory and cache active data pages. The BUFFERS parameter has no theoretical limit; it can be set to 2^31-1. Each buffer is the size of a single page, as specified by the PAGESIZE configuration parameter.

For OLTP applications, if more buffers are available, a required data page is more likely to be in memory as the result of a previous request. For example, if client applications access 15 percent of the data 90 percent of the time, set the BUFFERS parameter large enough to hold that 15 percent. Such a setting improves database I/O and transaction throughput. However, allocating too many buffers can affect the memory-management system and lead to excess paging activity.

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

It is suggested that you set BUFFERS to provide a buffer-space value between 20 and 25 percent of the number of megabytes in physical memory. It is recommended that you calculate all other shared-memory parameters after you set buffer space (BUFFERS multiplied by the system page size) to 20 percent of physical memory. Then, after you specify all shared memory parameters, increase the size of BUFFERS to the full 25 percent if you have enough shared memory. If you set the database server page size in the PAGESIZE configuration parameter, make sure that you use the specified page size in your calculation.

Tuning BUFFERS to Improve the Read-Cache Rate

Consider the simple example of a system that uses the default page size of 4 kilobytes and has 100 megabytes of physical memory. You might first set BUFFERS between 10,000 and 12,500 (40 to 50 megabytes of memory). Then use onstat -p to monitor the read-cache rate. The read-cache rate is the percent of database pages that are already present in a shared-memory buffer when a query requests them. Add this information to the data that you collect for your performance history.

A high read-cache rate improves performance for some applications because the database server requires time and resources to copy pages into memory from disk if they are not already present. If buffer reads are used by applications for scans of commonly used table fragments and to access tables through indexes, aim to achieve read-cache rates of at least 95 percent. To improve read-cache rates, increase the number of shared memory buffers (BUFFERS). For information about improving write-cache rates, see Specifying the Interval Between Checkpoints.

As you increase the value of BUFFERS, you reach a point at which increasing the value no longer produces significant gains in the read-cache rate, or you reach the upper limit of operating-system shared-memory allocation. Use an operating-system utility that monitors memory, such as vmstat or sar, to monitor the level of page scans and paging-out activity. If these levels suddenly rise, or rise to unacceptable levels during peak database activity, reduce the value of BUFFERS.