The BUFFERPOOL configuration parameter and memory utilization

The BUFFERPOOL configuration parameter specifies the properties of buffer pools. The information that you define in the BUFFERPOOL configuration parameter fields affects memory use.

You can have multiple buffer pools if you have dbspaces that use different page sizes. The onconfig configuration file contains a BUFFERPOOL line for each page size. For example, on a computer with a 2 KB page size, the onconfig file can contain up to nine lines, including the default specification. When you create a dbspace with a different page size, a buffer pool for that page size is created automatically, if it does not exist. A BUFFERPOOL entry for the page size is added to the onconfig file. The values of the BUFFERPOOL configuration parameter fields are the same as the default specification.

The BUFFERPOOL configuration parameter controls the number of data buffers available to the database server. These buffers are in the buffer pool portion of shared memory and are used to cache database data pages in memory.These buffers are in the resident portion of shared memory (buffer pool) and are used to cache database data pages in memory.

Increasing the number of buffers increases the likelihood that a needed data page might already be in memory as the result of a previous request. However, allocating too many buffers can affect the memory-management system and lead to excess operating system paging activity. To take advantage of the large memory available on 64-bit addressing machines, you can increase the size of the buffer pool.

The size of the buffer pool has a significant effect on database I/O and transaction throughput.

The size of the buffer pool has a significant effect on database I/O and transaction throughput. You can ensure that the buffer pool has enough buffers by making the buffer pool extendable. When the buffer pool is extendable, the database server expands the buffer pool as needed to improve performance.

The size of the buffer pool is equal to the number of buffers multiplied by the page size. The percentage of physical memory that you need for buffer space depends on the amount of memory that you have available on your system and the amount that is used for other applications. For systems with a large amount of available physical memory (4 GB or more), buffer space might be as much as 90 percent of physical memory. For systems with smaller amounts of available physical memory, buffer space might range from 20 to 25 percent of physical memory.

For example, suppose that your system has a page size of 2 KB and 100 MB of physical memory. You can set the value in the buffers field to 10,000 - 12,500, which allocates 20 - 25 MB of memory.

Calculate all other shared-memory parameters after you specify the size of the buffer pool.

Note: If you use non-default page sizes, you might need to increase the size of your physical log. If you frequently update non-default pages, you might need a 150 - 200 percent increase of the physical log size. Some experimentation might be needed to tune the physical log. You can adjust the size of the physical log as necessary according to how frequently the filling of the physical log triggers checkpoints.

You can use onstat -g buf to monitor buffer pool statistics, including the read-cache rate of the buffer pool. This rate represents the percentage of database pages that are already present in a shared-memory buffer when a query requests a page. (If a page is not already present, the database server must copy it into memory from disk.) If the database server finds the page in the buffer pool, it spends less time on disk I/O. Therefore, you want a high read-cache rate for good performance. For OLTP applications where many users read small sets of data, the goal is to achieve a read cache rate of 95 percent or better. If the buffer pool is extendable, you can specify the read cache hit ratio below which the database server extends the buffer pool.

If the read-cache rate is low, you can repeatedly increase buffers and restart the database server. As you increase the BUFFERPOOL 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 your operating-system shared-memory allocation.

Use the memory-management monitor utility in your operating system (such as vmstat or sar on UNIX™) to note the level of page scans and paging-out activity. If these levels rise suddenly or rise to unacceptable levels during peak database activity, reduce the size of the buffer pool.

Smart large objects and buffers

Depending upon your situation, you can take one of the following actions to achieve better performance for applications that use smart large objects:

If your applications frequently access smart large objects that are 2 KB or 4 KB in size, use the buffer pool to keep them in memory longer. Use the following formula to increase the value of the buffers field:
```
Additional_buffers = numcur_open_lo *
                     (lo_userdata / pagesize)
```
In this formula:
- numcur_open_lo is the number of concurrently opened smart large objects that you can obtain from the onstat -g smb fdd command.
- lo_userdata is the number of bytes of smart-large-object data that you want to buffer.
- pagesize is the default page size in bytes for the computer.
As a rule, try to have enough buffers to hold two smart-large-object pages for each concurrently open smart large object. The additional page is available for read-ahead purposes.
Use lightweight I/O buffers in the virtual portion of shared memory.
Use lightweight I/O buffers only when you read or write smart large objects in operations greater than 8000 bytes and seldom access them. That is, if the read or write function calls read large amounts of data in a single-function invocation, use lightweight I/O buffers.

When you use lightweight I/O buffers, you can prevent the flood of smart large objects into the buffer pool and leave more buffers available for other data pages that multiple users frequently access.