 |
| Special December 2016 - Understanding the database bufferpool |
|---|
| Category : Admin |
| Level : Advanced |
| Back to Overview |
Today we will have a look to the database bufferpool of cego. The bufferpool implements a memory cache, where all object data is accessed through. There is no direct access to the file and disk to any data except of the redo logs and the database xml configuration file. First, this enables the database to have fast access to the data and second to keep the data consistent on the disk.
 |
If a data page is requested, it will be read from the datafile ( if still not available in the pool ) and is mapped to an available slot in bufferpool. The page is claimed then by the calling database thread. We call this page fixed then. If the page is no more used, it will be unfixed. For further usage also by other database threads, it will stay in the bufferpool. If the page is modified, it is marked as dirty. Dirty pages are going to be written back to disk, if a database checkpoint has reached.
You can habe a look to the current bufferpool configuration by either using the cgclt or cgadm console program.
ws > show pool; +---------------------+---------------------+ | POOLINFO | POOLINFO | | PARAMETER | VALUE | +---------------------+---------------------+ | Page Size | 16384 | | Total Pages | 12000 | | Used Pages | 3744 | | Free Pages | 8256 | | Dirty Pages | 904 | | Fixed Pages | 0 | | Persistent Pages | 88 | | No Sync Pages | 0 | | Spread Rate | 1.549 | | ------------------- | ------------------- | | Stat Start | 18.12.2016 07:04:12 | | Hit Rate | 95.07% | | Cur Fix Count | 73305 | | Max Fix Count | 100000 | | Disk Reads | 3612 | | Disk Writes | 0 | | Read Delay | 0.11 msec | | Write Delay | 0.00 msec | | ------------------- | ------------------- | | Pool Uptime | 0d 0:14:44 | +---------------------+---------------------+ 20 tuples ok ( 0.075 s ) |
The bufferpool is used for all configured and online tablesets, so will get this view no matter what tableset you are currentyl using. Most bufferpool information is obvious as explained above.
Additionally, you can see information about persistent pages and no sync pages. Persistent pages are pages, which are kept in the bufferpool and will be never swapped out. System entry pages are persitent pages to keep fast access to the database dictionary. No sync pages are used for index objects which can be written to disk in an asynchronous way ( not via checkpoint ). This avoids checkpoint latencies in case of large index creation, if no btree cache is used. As a consequence, index objects might be inconsitenst during creation and just marked as valid after sucessful creation.
The spread indicates the balanced use of the bufferpool. A spread rate of zero means, all bufferpool slots are used with the same number of fixes, which is an optimal use. Practically, spread rates up to two indicate a well used bufferpool. If the spread rates increases, this means, bufferpool hot slots are occured.
In the lower part of the bufferpool information, statistics are printed, which have been collected during the last time interval. The hit rate indicates, how often a page request could be satisfied directly from the bufferpool, without requesting the page from disk. A hit rate greater than 90% is appropriate. Otherwise, the number of bufferpool should be increased. The read and write delay gives some information about the IO performance to the disk. These values should be less the 1msec, otherwise this is an indication for a slow IO system but also for a too small bufferpool configuration.
If you are using the admin console cgadm, you can print a dump of the complete bufferpool. Please note, that for large pool configuration this is a huge listing so it just should be used to explore small bufferpool configurations.
CGADM > list pool; +-----------+-----------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+--------------------------+ | PEL | PEL | PEL | PEL | PEL | PEL | PEL | PEL | PEL | PEL | | SEGMENT | POS | OCCSTATE | ISDIRTY | NUMFIXES | TABSETID | FILEID | PAGEID | FIXSTAT | NUMUSAGE | +-----------+-----------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+--------------------------+ | 0 | 0 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 1 | WRITEONSYNC | y | 0 | 2 | null | 6000 | 4 | 2 | | 0 | 2 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 3 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 4 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 5 | PERSISTENT | y | 0 | 2 | null | 4 | 132 | 100 | | 0 | 6 | WRITEONSYNC | y | 0 | 2 | null | 6004 | 4 | 2 | | 0 | 7 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 8 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 9 | PERSISTENT | y | 0 | 2 | null | 8 | 98 | 97 | | 0 | 10 | WRITEONSYNC | y | 0 | 2 | null | 6008 | 4 | 2 | | 0 | 11 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 12 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 13 | PERSISTENT | y | 0 | 2 | null | 12 | 5215 | 196 | | 0 | 14 | WRITEONSYNC | y | 0 | 2 | null | 6012 | 4 | 2 | | 0 | 15 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 16 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 17 | WRITEONSYNC | y | 0 | 2 | null | 6016 | 4 | 2 | | 0 | 18 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | | 0 | 19 | NOTOCCUPIED | n | 0 | 0 | null | 0 | 0 | 0 | +-----------+-----------+----------------+----------------+----------------+----------------+----------------+----------------+----------------+--------------------------+ ... |
Take care, merry christmas and a happy new year !