Bookmark and Share
RSS

Recent Posts


DB2 12 In-Memory Table Optimization

October 4, 2016

Following up on last week's post on real memory and in-memory index optimization, here's a little more about the upcoming DB2 12. The new release will support in-memory tables that use in-memory contiguous buffer pools.

In DB2 11 the PGSTEAL(NONE) buffer pool parameter was introduced as a way to tell DB2 when a table should have all pages read from disk and stored in memory so that a GETPAGE request will have no physical I/O occurring. The DB2 optimizer recognizes this and calculates this information into the cost optimization when defining the access path. DB2 12 has made the memory management even more efficient with the new memory contiguous buffer pools, which provide a direct row access in-memory that's designed to greatly reduce GETPAGE overhead. The lab has seen up to 8 percent CPU reduction measured in OLTP workloads.

Some improvements to the PGSTEAL(NONE) algorithm allow you to avoid the least recently used algorithm (LRU) and its associated hash chain management overhead. During the allocation of memory, 10 percent of the buffer pool is reserved for overflow if an object doesn't fit completely in memory. This area is auto managed by DB2 using LRU. This overflow area is only backed by real memory when it's used.

Real memory has become very important. It's critical that customers understand the changing requirements for configuring z/OS with more real memory to reduce the overall cost of running today's complex applications. My hope is that DB2 and z/OS systems programmers work together with the goal of allocating sufficient amounts of real memory to run a high-performing DB2 database with a minimum amount of CPU, thus reducing the overall cost of running the application.

Posted October 4, 2016| Permalink

comments powered by Disqus
-->