A buffer pool is an area of storage into which database pages (containing table rows or index entries) are temporarily read and changed. The purpose of the buffer pool is to improve database system performance. Data can be accessed much faster from memory than from a disk. Therefore, the fewer times the database manager needs to read from or write to a disk, the better the performance.
The configuration of one or more buffer pools is the single most important tuning area, since it is here that most of the data manipulation takes place for applications connected to the database (excluding large objects and long field data).
When an application accesses a row of a table for the first time, the database manager places the page containing that row in the buffer pool. The next time any application requests data, the buffer pool is checked first. If the requested data is found on pages kept in the buffer pool, the database manager does not need to go out to disk storage to retrieve the requested data. Avoiding the need to retrieve data from disk storage results in faster performance.
Pages stay in the buffer pool until the database is shut down, or until the space occupied by a page is required for another page. The space chosen in the buffer pool to bring in another page is selected using criteria such as the following:
| Note: | After changed pages are written out to disk, they are not removed from the buffer pool unless the space they occupy is needed for other pages. Until they are overwritten, they can be accessed again if their data is needed. |
When creating a buffer pool, by default the page size is 4 KB. You can choose to have the page size set at either 4 KB or 8 KB when creating the buffer pool. If buffer pools are created using an 8 KB page size, only table spaces created using an 8 KB page size can then be associated with them. You cannot alter the page size of the buffer pool following its creation.
Pages in the buffer pool can have different attributes:
Pages can be written from the buffer pool to disk when the percentage of space occupied by changed pages in the buffer pool has exceeded the value specified by the chngpgs_thresh configuration parameter. You also may need to configure the database to include more than one page-cleaner agent. These agents write out changed pages to disk so that the database agents can find usable space in the buffer pool.
Page cleaner agents perform I/O that would otherwise have to be performed by the database agents. As a result, your applications can run faster, because transactions are not forced to wait while their database agents write pages to disk. (Page-cleaner agents are sometimes referred to as asynchronous page cleaners or asynchronous buffer writers because they can run in parallel with the database agents.)
To change the number of page-cleaner agents, use the num_iocleaners configuration parameter (the default is to create one page-cleaner agent). For information, see "Number of Asynchronous Page Cleaners (num_iocleaners)".
Writing pages to disk also allows for faster recovery of the database should a system crash occur, because the database manager is able to rebuild more of the buffer pool from disk rather than having to use the database log files. As a result, page cleaning is requested if the size of the log that would need to be read during recovery exceeds the following maximum:
logfilsiz * softmax
where:
For example, if the value of softmax is 250, then 2.5 log files will contain the changes that need to be recovered if a crash occurs.
You may use the database system monitor to help you track the number of times that page cleaning is requested to minimize log read time during recovery. For more information see the pool_lsn_gap_clns (buffer pool log space cleaners triggered) monitor element description in the System Monitor Guide and Reference manual.
The size of the log that would need to be read during recovery is the difference between the location of the following in the log:
The following figure illustrates how the work of managing the buffer pool can be shared between page-cleaner agents and database agents, compared to the database agents performing all of the I/O.
Figure 55. Asynchronous Page Cleaner. "Dirty" pages are written out to disk.