BUFFERPOOL configuration parameter

Use the BUFFERPOOL configuration parameter to configure how many data pages are cached in shared memory and how often those pages are flushed to disk between checkpoints. The default values of the BUFFERPOOL configuration parameter are adequate for many systems. However, you can change the values to tune the performance of your system.

onconfig.std values

Operating systems with 2 KB default page size:

BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50.00,
lru_max_dirty=60.50
BUFFERPOOL size=2k,buffers=50000,lrus=8,lru_min_dirty=50,
lru_max_dirty=60

Operating systems with 4 KB default page size:

BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50.00,
lru_max_dirty=60.50
BUFFERPOOL size=4k,buffers=10000,lrus=8,lru_min_dirty=50,
lru_max_dirty=60

default value if you created a server during installation

BUFFERPOOL default,memory='auto'
BUFFERPOOL size=page_size,memory=memory_size

The page_size value is the default page size. The initial size of the buffer pool is 32 MB. The maximum size, which is specified by the value of the memory field as either auto or the memory_size value, depends on the value of the AUTO_TUNE_SERVER_SIZE configuration parameter.

values

See the Usage section.

separators

Separate fields with a comma.

takes effect

After you edit your onconfig file and restart the database server.

When you add an entry dynamically in your onconfig file by running the onparams -b command.

When you add an entry dynamically by adding a dbspace with a different page size by running the onspaces -c -d command.

After you add an entry dynamically in your onconfig file by running the SQL administration API task() or admin() function with the add bufferpool argument.

Usage

Cached data pages are held in buffers. Buffers are contained in buffer pools. You need a buffer pool for each page size that you use for storage spaces. When the database server moves new data pages into shared memory, data pages that are the least-recently used are moved out of shared memory. The BUFFERPOOL configuration parameter controls the size of the buffer pool and how frequently data pages are flushed to disk.

The BUFFERPOOL configuration parameter has two entries in the onconfig.std file or in the onconfig file that was generated if you created a server during installation:

The first entry specifies the default values for a buffer pool for a dbspace with a non-default page size.
The second entry specifies the default values for a buffer pool that is based on the default page size of the system.

The BUFFERPOOL configuration parameter entries that include the size field take precedence over the entry that includes the default field.

The BUFFERPOOL configuration parameter has two formats:

Use the BUFFERPOOL configuration parameter with the memory field if you want to specify the size of your buffer pool in units of memory like MB or GB.
Use the BUFFERPOOL configuration parameter with the buffers field if you want to specify the size of your buffer pool in units of pages, or to retain settings from a previous release.

You can use either format to enable the database server to expand the size of the buffer pool as needed to improve performance.

Restriction: You cannot combine formats in the onconfig file. All entries for the BUFFERPOOL configuration parameter in the onconfig file must have the same format or the database server does not start and the following error shows:

ERROR: Cannot mix buffer arguments with memory arguments.  (BUFFERPOOL)

The fields in the BUFFERPOOL entries are not case-sensitive and the fields can be listed in any order.

Figure 1. Syntax with the memory field

Figure 2. Syntax with the buffers field

extendable options

Table 1. Options for the BUFFERPOOL configuration parameter value
Field	Values
buffers	Default is 1000. The `number_buffers` value is an integer >= 1000 that specifies the maximum number of shared-memory buffers. The maximum allowed number of buffers depends on the operating system, the bit size, and the page size: UNIX™, 32-bit, with a 2 KB page size: 1000 - 1843200 UNIX™, 32-bit, with a 4 KB page size: 1000 - 921600 Windows™, 32-bit: 100 - 524288 64-bit: 100 - (2³¹-1). For the actual value for your 64-bit platform, see your machine notes. For example, the maximum number of buffers on the Solaris platform is 536,870,912. Set the value of the buffers field to at least four buffers per user. If your system handles more than 500 concurrent users, specify at least 2000 buffers. Each buffer is the size of the operating system page. Therefore, the number of buffers that the database server requires depends on the amount of physical memory and how much memory is used by applications. For example, if the database server accesses 15 percent of the application data 90 percent of the time, allocate enough buffers to hold 15 percent of the data. Increasing the number of buffers can improve system performance. The number of buffers can have a significant affect on performance and use a large percentage of physical memory. For more information, see The BUFFERPOOL configuration parameter and memory utilization.
cache_hit_ratio	Default is 90. The `ratio` value is an integer 0 - 100 that represents the threshold below which the buffer pool is extended. When the average read cache hit ratio remains below the value of `ratio` for approximately five minutes, the database server extends the buffer pool. The cache_hit_ratio field is valid only if extendable=1 is set.
extendable	Default is 1 if the memory field is set. Default is 0 if the buffers field is set. Whether the database server can extend the size of the buffer pool: 0 = Disabled. The buffer pool cannot grow. 1 = Enabled. The buffer pool can grow.
lru_max_dirty	Default is 60.00. The `max_percentage` value is a decimal number 0 - 100.00 that sets the percentage of modified pages in the LRU queues at which the queue is cleaned. This value is updated automatically as needed if the AUTO_LRU_TUNING configuration parameter is enabled.
lru_min_dirty	Default is 50.00. The `min_percentage` value is a decimal number 0 - 100.00 that sets the percentage of modified pages in the LRU queues at which page cleaning is no longer mandatory. Page cleaners might continue cleaning beyond the specified percentage under some circumstances. This value is updated automatically as needed if the AUTO_LRU_TUNING configuration parameter is enabled.
lrus	Default is 8. If the MULTIPROCESSOR configuration parameter is enabled, the default is the greater of 8 or the number of CPU VPs. The `number_lrus` value is a positive integer that specifies the number of LRU (least recently used) queues in the buffer pool. The range of values depends on the bit size of the operating system: 32-bit platforms: 8 - 128 64-bit platforms: 8 - 512 The more LRU queues that you specify, the more page cleaners work in parallel. However, setting the value of lrus field too high might result in excessive page-cleaner activity. The value of lrus field, in combination with the lru_min_dirty and lru_max_dirty fields control how frequently the shared-memory buffers are flushed to disk. For more information, see BUFFERPOOL and its effect on page cleaning.
max_extends	Default is 8. The `extends` value represents the maximum number of times that the database server can extend the buffer pool. The value of `extends` is 0 through the maximum number of segments, which depends on the operating system and bit size: 32 bit = 16 UNIX™ 64 bit = 24 Windows™ 64 bit = 8 The max_extends field is valid only if buffers and extendable=1 are set.
memory	Default is auto. The `max_size` value represents the maximum size of the buffer pool. The range of values for `max_size` is: An integer that represents 32 MB - 4 TB. You can specify the size units of KB, MB, or GB. If you do not specify units, the default units are KB. auto = The database server determines the maximum amount of shared memory to allocate to the buffer pool. The value of the AUTO_TUNE_SERVER_SIZE configuration parameter, if it is set, controls the maximum size of the buffer pool.
next_buffers	Default is 1000. The `number_buffers` value is an integer >= 1000 that specifies the number of shared-memory buffers by which the database server extends the buffer pool. The maximum value of `number_buffers` is limited by the amount of virtual shared memory. The `number_buffers` value is doubled every four extensions. The next_buffers field is valid only if buffers and extendable=1 are set.
size	The `page_size` value specifies the page size for buffers, in KB. The page size must be 2 - 16 KB and must be a multiple of the default page size. For example, if the default page size is 2 KB, the page size can be 2, 4, 6, 8, 10, 12, 14, or 16. If the default page size is 4 KB, the page size can be 4, 8, 12, or 16. The default value depends on the system default page size: 2 KB default page size: `size=2k` 4 KB default page size: `size=4k` The `k` is optional.
start_memory	Default is 32 MB. The `start_size` value represents the initial size of the buffer pool when the database server starts: An integer that represents 32 MB through the maximum amount of shared memory that is available. You can specify the size units of KB, MB, or GB. If you do not specify units, the default units are KB. The initial size of the buffer pool might be larger than the value of `start_size` because the size must be a multiple of the size of a shared memory segment. auto = The database server determines the initial amount of shared memory to allocate to the buffer pool. If you do not set the start_memory field, the initial size of the buffer pool is equal to the value of the memory field. The start_memory field is valid only if the memory field is set.

The size of the buffer pool with the memory format

If you use the memory format, by default the buffer pool grows in size as needed. Shared memory segments are added to the buffer pool when the average cache read hit ratio is under the threshold. You can set the initial and maximum size of the buffer pool or allow the database server to determine the optimal sizes.

If the extendable field is set to 0, the buffer pool does not grow. The size is equal to the value of the start_memory field, if it is set, otherwise, the value of the memory field.

When you restart the server, the size of the buffer pool is reset to the value of the start_memory field.

The size of the buffer pool with the buffers format

If you use the buffers format, by default the buffer pool does not grow in size. The size is equal to the value of the buffers field.

If you set the extendable field to 1, shared memory segments are added to the buffer pool when the average cache read hit ratio is under the threshold. You must set the initial number of buffers in the buffers field. You can optionally set the number of buffers by which to extend the buffer pool, and the maximum number of times that the buffer pool can be extended, and the cache hit ratio. The number of buffers that are added to the buffer pool doubles every fourth extension.

Example: Adding a BUFFERPOOL entry with the memory field

The following entry creates a buffer pool that has a 10 KB page size:

BUFFERPOOL size=10k,start_memory=auto,memory=4gb

The buffer pool is extendable up to 4 GB. The database server determines the initial size of the buffer pool and the sizes of extensions to the buffer pool.

Example: Adding a BUFFERPOOL entry with the buffers field

The following entry creates a buffer pool that has a 2 KB page size:

BUFFERPOOL size=2k,extendable=1,buffers=1000,next_buffers=2000,max_extends=8

The buffer pool is extendable eight times. The buffer pool starts with 1000 buffers. The first three extensions to the buffer pool add 2000 buffers. The fourth through seventh extensions add 4000 buffers. The eighth extension adds 8000 buffers.

Example: Adding a BUFFERPOOL entry by adding a dbspace with a different page size

When you add a dbspace with a different page size with the onspaces utility, or when you add a buffer pool with the onparams utility, a BUFFERPOOL configuration parameter entry is added in the onconfig file. The following example shows a third entry:

BUFFERPOOL default,buffers=10000,lrus=8,lru_min_dirty=50.00,lru_max_dirty=60.50
BUFFERPOOL size=2k,buffers=10000,lrus=8,lru_min_dirty=50,lru_max_dirty=60
BUFFERPOOL size=6k

When you create a dbspace with a non-default page size, the database server uses the existing BUFFERPOOL entry for that page size, if that entry exists. Otherwise, the database server uses the values from the BUFFERPOOL default line.