Before a page of data can be used by the database manager, it must be located in its data page buffers. The buffers are two areas of storage in your database machine or partition, which are allocated when you start the database manager. One area called the directory buffer pool is reserved for pages from the DB2 Server for VSE & VM directory disk. The size of the pool is determined by the NDIRBUF initialization parameter. The other area called the local buffer pool is reserved for pages from the storage pools. Its size is determined by the NPAGBUF initialization parameter.
When the database manager needs a page, it looks for it in its buffer pool. If it does not find it there, it uses a service (IUCV *BLOCKIO or paging in VM, and VSAM in VSE) to read the page from DASD into a free space in its pool.
Since the buffer pools are part of a primary address space, the operating system treats them like part of the database manager code. If a buffer page is not referenced frequently, it may be moved out to system paging DASD by the VM or VSE paging system. In VM the page may also be moved out to expanded storage if it is available. (Refer to Auxiliary Storage.)
Figure 12. The Standard DB2 Server for VM DASD I/O System. The database manager explicitly directs the operating system to move pages to and from DASD. Once database machine pages are in main storage, they may be moved out to system paging DASD by the paging system. In VSE, pages are moved by VSAM and the database machine is a database partition.
|
When the database manager needs a buffer for another page, it overwrites the "oldest" unmodified page in the pool with a new page. This is referred to as releasing a page or stealing a buffer.
While a page is in the buffer pool, the database manager may modify it. To ensure the integrity of your data, a modified page will not be released until it has been written back to DASD. If the database manager needs a buffer occupied by a modified page, it first writes the page to DASD, then loads the buffer with a new page.
The sizes of these buffer pools are among the more important factors determining performance. You can significantly improve performance by optimizing these values. Unless your system's main storage is extremely constrained, the default values are probably too low.
Buffer pool sizes are set by initialization parameters:
The optimal buffer pool size is governed by the trade-off between database I/O and system paging I/O (refer to Auxiliary Storage). In general, an increase in the buffer pool sizes improves performance only if the resulting increase in system paging is small. Stated another way, the buffer pools should be backed up by a corresponding amount of available main storage.
The database manager is designed to efficiently manage its buffer pools no matter how large they are. Very large buffer pools can be an excellent tuning choice if sufficient virtual and real storage is available.
At the other extreme, if your environment is characterized by limited real storage and a relatively high paging rate, consider using smaller buffer pool sizes. Avoid extremely small buffer pools: they increase the likelihood that work has to be backed out because of buffer pool contention. Twenty buffer pages per real agent (20*NCUSERS) is an absolute minimum, and is usually too low for most applications.
The performance information available through the COUNTER operator command is helpful in guiding the selection of buffer pool sizes. Two especially useful measurements are the local buffers effective use and the directory buffer effective use values. (Refer to COUNTER Operator Command.)
There are no fixed guidelines as to what constitutes a good or bad value, because this depends upon the availability of main storage to back up the buffer pools, as described above. Of more interest are their relative values under different conditions. For example, before and after observations can be used to find out how effective an increase in the buffer pool size was in reducing database I/O. A large decrease in I/O indicates that the change was effective, whereas a small increase would suggest that the change was not worthwhile. Alternatively, calculate your buffer hit ratios (see Measurements) before and after your change.
Because directory buffers are eight times smaller than the local buffer pages, you can afford to be much more generous with them. Consider increasing NDIRBUF enough to cause the directory read rate (DIRREAD/sampling interval) to be very low. On a well tuned system, the directory pool effective use tends to be much higher than the local buffer effective use.
Your internal dbspaces can use a virtual disk to improve their performance. Virtual Disk Support lets you use a data space as a virtual disk. A virtual disk is much faster than a conventional disk because it uses main storage instead of DASD. A virtual disk appears to any program or job as just another disk, only faster. Refer to Virtual Disk Support for VSE/ESA for Internal Dbspaces and "Virtual Disk Support for VM/ESA for Internal Dbspaces".
The package cache works much the same as the buffer pools, except that instead of storing data pages, the package cache stores packages. When a package is loaded into the database machine's virtual storage, users can use it consecutively without reloading it each time. Unfortunately, separate users cannot use the same package at the same time. If a package is already in use when a user requests it, an additional copy will be loaded.
You need to trade-off the advantage of reducing your DASD I/O by having a large cache capable of storing a large number of packages, against the storage the packages consume.
The package cache has a series of slots that contains information about the packages loaded into the database machine or partition. One slot is used for each package. The total number of slots available is determined at application server startup by two initialization parameters:
The number of slots in the package cache is calculated as follows:
NPACKAGE X NCUSERS
For example, if NPACKAGE is 10 and NCUSERS is 5, the number of slots in the cache is 50 (10X5). While NCUSERS is part of the calculation, do not use it to tune the size of the cache. Instead, increase or decrease NPACKAGE and set NCUSERS based on your requirements for real agents. (Refer to Agents.)
You can also set a package cache threshold that limits the number of packages that will remain in the cache. At the end of a logical unit of work (LUW), the database manager checks the number of packages in the cache. If that number exceeds the threshold, the database manager releases the package that has been in the cache the longest to make room for a new one.
The package cache threshold is determined at startup by an initialization parameter (NPACKPCT) and is calculated as follows:
NPACKPCT
NPACKAGE X NCUSERS X ----------
100
For example, if there are 50 slots in the cache and NPACKPCT is 80%, the package cache threshold is 40 packages (50X80/100). While NPACKAGE and NCUSERS appear in the calculation, do not tune the threshold with them, rather, use NPACKPCT.