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 5 lists recommended settings for these parameters.
For information on estimating the size of the resident portion of shared memory, see Resident Portion. This calculation includes figuring the size of the buffer pool, logical-log buffer, physical-log buffer, and lock table.
You can use the BUFFERPOOL configuration parameter to define a buffer pool for pages corresponding to each unique page size in use by all of the dbspaces. You can create buffer pools with page sizes that are not currently in use by any dbspace. It is recommended that you create the buffer pool before you create a standard or temporary dbspace with a non-default page size.
You cannot create multiple buffer pools with the same page size.
If you create a dbspace with a page size that does not have a corresponding buffer pool, Dynamic Server automatically creates a buffer pool using the default parameters defined in the ONCONFIG configuration file.
Information that was specified with the BUFFERS, LRUS, LRU_MAX_DIRTY, and LRU_MIN_DIRTY configuration parameters prior to Version 10.0 is also now specified using the BUFFERPOOL configuration parameter. When you create a buffer pool, you define information about the buffer pool including its size, the number of buffers in the buffer pool, the number of LRUs in the buffer pool, and lru_min_dirty and lru_max_dirty values.
If you do not specify a page size for a new buffer pool, Dynamic Server uses the operating system default page size (4 kilobytes on Windows and 2 kilobytes on most UNIX platforms) as the default page size for the buffer pool.
The following example shows information for a buffer pool that is defined on a UNIX operating system and has a default page size:
BUFFERPOOL default,lrus=8,buffers=5000,lru_min_dirty=50,lru_max_dirty=60 BUFFERPOOL size=2K,buffers=5000,lrus=8,lru_min_dirty=50,lru_max_dirty=60
The ONCONFIG configuration file can contain one or more BUFFERPOOL lines. For example, on a computer with a two-kilobyte page size, the ONCONFIG file can contain up to nine lines, including the default specification.
The buffers field in the BUFFERPOOL configuration parameter specifies the number of data buffers available to the database server. These buffers reside in the resident portion of shared memory and are used to cache database data pages in memory.
If the value of buffers is zero (0) or if the value of buffers is missing in the BUFFERPOOL configuration parameter, Dynamic Server will not create the buffer pool of the specified page size.
When the database server is in online, quiescent, or single-user mode, you can use the onparams utility to add a new buffer pool of a different size. When you use the onparams utility, the information that you specify about a buffer pool is automatically transferred to the ONCONFIG file.
When you use the onparams utility, you specify information as follows:
onparams -b -g <size of buffer page in Kbytes> -n <number of buffers> -r <number of LRUs> -x <max dirty (fractional value allowed)> -m <minimum dirty (fractional value allowed)>
For example:
onparams -b -g 6 -n 3000 -r 2 -x 2.0 -m 1.0
This adds 3000 buffers of size 6K bytes each with 2 LRUS with lru_max_dirty set at 2 percent and lru_min_dirty set at 1 percent.
The BUFFERPOOL configuration parameter has a significant effect on database I/O and transaction throughput. The more buffers that are available, the more likely it is that a needed data page might already reside 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.
The database server uses the following formula to calculate the amount of memory to allocate for this data buffer pool:
bufferpoolsize = BUFFERS * page_size
onstat -b ... Buffers address userthread flgs pagenum memaddr nslots pgflgs xflgs owner waitlist 2 modified, 0 resident, 200 total, 256 hash buckets, 4096 buffer size
On Windows, the page size is always 4 kilobytes (4096).
It is recommended that in the BUFFERPOOL configuration parameter you set the value of buffers to between 20 and 25 percent of the number of megabytes in physical memory. For example, if your system has a page size of 2 kilobytes and 100 megabytes of physical memory, you can set the value in the buffers field to between 10,000 and 12,500, which allocates between 20 megabytes and 25 megabytes of memory.
For information on creating a dbspace with a non-default page size and more information on defining buffer pools, see the IBM Informix: Dynamic Server Administrator's Guide. For more information on the BUFFERPOOL configuration parameter and information on how to use the onparams utility, see the IBM Informix: Dynamic Server Administrator's Reference.
To take advantage of the very large memory available on 64-bit addressing machines, increase the number of buffers in the buffer pool. This larger buffer pool increases the likelihood that a needed data page might already reside in memory.
By default, the database server reads smart large objects into the buffers in the resident portion of shared memory (also known as the buffer pool).
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 kilobytes or 4 kilobytes in size, use the buffer pool to keep them in memory longer.
Use the following formula to increase the value of buffers:
Additional_BUFFERS = numcur_open_lo * (lo_userdata / pagesize)
As a general 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 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. For more information, see Lightweight I/O for Smart Large Objects.
You can monitor buffers and buffer-pool activity using the following options of onstat:
You can also use onstat -p to monitor 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 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 BUFFERPOOL value of buffers.
The DS_TOTAL_MEMORY parameter places a ceiling on the amount of shared memory that a query can obtain. You can use this parameter to limit the performance impact of large, memory-intensive queries. The higher you set this parameter, the more memory a large query can use, and the less memory is available for processing other queries and transactions.
For OLTP applications, set DS_TOTAL_MEMORY to between 20 and 50 percent of the value of SHMTOTAL, in kilobytes. For applications that involve large decision-support (DSS) queries, increase the value of DS_TOTAL_MEMORY to between 50 and 80 percent of SHMTOTAL. If you use your database server instance exclusively for DSS queries, set this parameter to 90 percent of SHMTOTAL.
A quantum unit is the minimum increment of memory allocated to a query. The Memory Grant Manager (MGM) allocates memory to queries in quantum units. The database server uses the value of DS_MAX_QUERIES with the value of DS_TOTAL_MEMORY to calculate a quantum of memory, according to the following formula:
quantum = DS_TOTAL_MEMORY / DS_MAX_QUERIES
To allow for more simultaneous queries with smaller quanta each, it is suggested that you increase DS_MAX_QUERIES. For more information on DS_MAX_QUERIES, see DS_MAX_QUERIES. For more information on the MGM, see Memory Grant Manager.
The database server derives a value for DS_TOTAL_MEMORY if you do not set DS_TOTAL_MEMORY or if you set it to an inappropriate value. Whenever the database server changes the value that you assigned to DS_TOTAL_MEMORY, it sends the following message to your console:
DS_TOTAL_MEMORY recalculated and changed from old_value Kb to new_value Kb
The variable old_value represents the value that you assigned to DS_TOTAL_MEMORY in your configuration file. The variable new_value represents the value that the database server derived.
When you receive the preceding message, you can use the algorithm to investigate what values the database server considers inappropriate. You can then take corrective action based on your investigation.
The following sections document the algorithm that the database server uses to derive the new value for DS_TOTAL_MEMORY.
In the first part of the algorithm, the database server establishes a minimum for decision-support memory. When you assign a value to the configuration parameter DS_MAX_QUERIES, the database server sets the minimum amount of decision-support memory according to the following formula:
min_ds_total_memory = DS_MAX_QUERIES * 128 kilobytes
When you do not assign a value to DS_MAX_QUERIES, the database server uses the following formula instead, which is based on the value of VPCLASS cpu or NUMCPUVPS:
min_ds_total_memory = NUMCPUVPS * 2 * 128 kilobytes
In the second part of the algorithm, the database server establishes a working value for the amount of decision-support memory. The database server verifies this amount in the third and final part of the algorithm.
The database server first checks whether SHMTOTAL is set. When SHMTOTAL is set, the database server uses the following formula to calculate the amount of decision-support memory:
IF DS_TOTAL_MEMORY <= SHMTOTAL - nondecision_support_memory THEN
decision_support_memory = DS_TOTAL_MEMORY
ELSE
decision_support_memory = SHMTOTAL -
nondecision_support_memory
This algorithm effectively prevents you from setting DS_TOTAL_MEMORY to values that the database server cannot possibly allocate to decision-support memory.
When SHMTOTAL is not set, the database server sets decision-support memory equal to the value that you specified in DS_TOTAL_MEMORY.
When you do not set DS_TOTAL_MEMORY, the database server proceeds as follows. First, the database server checks whether you have set SHMTOTAL. When SHMTOTAL is set, the database server uses the following formula to calculate the amount of decision-support memory:
decision_support_memory = SHMTOTAL -
nondecision_support_memory
When the database server finds that you did not set SHMTOTAL, it sets decision-support memory as in the following example:
decision_support_memory = min_ds_total_memory
For a description of the variable min_ds_total_memory, see Deriving a Minimum for Decision-Support Memory.
The final part of the algorithm verifies that the amount of shared memory is greater than min_ds_total_memory and less than the maximum possible memory space for your computer. When the database server finds that the derived value for decision-support memory is less than the value of the min_ds_total_memory variable, it sets decision-support memory equal to the value of min_ds_total_memory.
When the database server finds that the derived value for decision-support memory is greater than the maximum possible memory space for your computer, it sets decision-support memory equal to the maximum possible memory space.
The LOGBUFF parameter determines the amount of shared memory that is reserved for each of the three buffers that hold the logical-log records until they are flushed to the logical-log file on disk. The size of a buffer determines how often it fills and therefore how often it must be flushed to the logical-log file on disk.
If you log smart large objects, increase the size of the logical-log buffers to prevent frequent flushing to the logical-log file on disk.
The PHYSBUFF parameter determines the amount of shared memory that is reserved for each of the two buffers that serve as temporary storage space for data pages that are about to be modified. The size of a buffer determines how often it fills and therefore how often it must be flushed to the physical log on disk. Choose a value for PHYSBUFF that is an even increment of the system page size.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]