DB2 version 9 offers new opportunities for buffer pool tuning. In version 9, you can increase the page size of indexes and compress these indexes. It is definitely not necessary, and is not even recommended to compress indexes with a page size greater than 4K. This paper describes the considerations of moving indexes into larger page sizes, how to identify candidates for page sizes of 8K, 16K or even 32K and the pros and cons of index compression with DB2 version 9.

Why moving indexes into page sizes greater than 4K? A big advantage of course is the expected reduction of index levels. A reduction of index levels may also result in:

• Reduction in get page
• Reduction in IO

An 8K page, of course, has more room for storing entries, than a 4K page. This results in a reduced number of pages being required. Fewer pages mean (hopefully) a reduction of index levels. We recommend to concentrate on some specific objects first and to choose 8K page sizes, rather than 16K or even 32K page sizes.

Reduced number of pages within the VSAM index space may result in reduced number of get pages. The less get pages being executed, the less overhead the DBM1 address space will have to manage. Less get pages means less release pages. Get pages are one of the main reasons for the overall DB2 CPU consumption. With larger page sizes, we have an excellent opportunity of overall CPU reduction!

What are the criteria for indexes in page sizes > 4K?

We recommend to first use page sizes of 8K. Larger page sizes might be helpful, but it is strongly recommended to do more testing before moving indexes in index pages with page sizes greater than 8K. The more random the access pattern is, the less benefit can be expected, or even a negative effect will occur.
Criteria 1: The index should have at least 4 levels.

It is not helpful to increase page size for all indexes just because it is possible. A reasonable selection is required to get most out of it. The more levels an index have, the better the chances to reduce the number of index levels and of course to reduce the number of get pages (or number of page splits in case of heavy insert activity).
Criteria 2: The closer the used pages are located, the better the saving will be.

One sample: Think about a scan on leaf page level of an index (typically SELECT COUNT(*) which can use an index for the select). This type of access will benefit significantly because there is a good chance to reduce the number of get pages by 50% when comparing a 4K index with an 8K index. The number of get pages is a good indicator for the workload and it is the main contributor, of course, for CPU consumption.

Heavy random access patterns don’t show a lot of benefit: It is just a replacement of a get page or even an IO on a 4K page by an 8K page. The negative side effect is an increased memory requirement because the space consumption is doubled in the buffer pool. (The worst case is one entry processed per page). This might lead to increased IO processing.

Conclusion: Sequential scans on leaf page level are perfect candidates for indexes in 8K Pages. This is because of dramatic reduction in get page and release page processing.

The next picture, produced by BPA4DB2, shows typical selection criteria for the type of indexes which are good candidates for index spaces with page sizes > 4K:

"Distinct Getpage", on column 7, means how many distinct page-numbers are processed within this workload. In the first line, we can find, that the number of distinct get pages (109.816) is very close to the number of overall get pages (117.683), "Getpage Count", on column 3. "Sequential Getpage", on column 5, is an indicator for heavy sequential scan activity on this index space because it is very close to the "Getpage Count".

Moving this object into an 8K Pool will reduce the number of Get- and Release Page requests for this object by nearly 50%!

Effect of Index compression

Indexes can be compressed as soon as they are built on page sizes greater than 4K. To increase page size indexes must be dropped and recreated. But it is not recommended to move all indexes in page sizes greater than 4K and to compress them.

Be aware of DBM1 SRB CPU consumption when using index compression!

You might see a significant improvement on SQL which is running scans on leaf page level of indexes (up to 20% were measured). Even the threads CPU consumption might be reduced.

But the drawback can be measured in the DBM1 address space. DBM1 is responsible for the compress/decompress routines. Monitor DBM1’s SRB CPU consumption carefully. You will find increased CPU consumption there!

Much more critical than the influence of index compression on application performance and CPU consumption is the result on Utility performance. You can expect:

• Increased utility runtime (e.g. Index rebuild)
• Increased utility CPU consumption
• AND of course significant increased DBM1 SRB CPU consumption.

Conclusion

Indexes in 8K Pages (uncompressed) are an excellent opportunity to improve application performance and to reduce CPU consumption (Reduced get page and release Page processing, less index splits.

Compressing indexes is not the first choice in terms of CPU cost reduction. The permanent compress – decompress on page level generates a significant risk of increased CPU consumption. The choice of the right indexes and the answer to the question "compress YES or NO" is substantial for the results you can reach by using this new feature.

To find out the best candidates for page sizes greater than 4K, a buffer pool analyzer is the best choice. A tool like BPA4DB2 helps to identify the objects where the criteria, as described in this paper, can be applied easily.