This is the third part in a series of articles about DB2 12 for z/OS, with a focus on insert performance. Read part one and part two.
Insert-intensive workloads are very common among IBM DB2 for z/OS customers and are often performance critical. However, achieving the performance levels required to meet service level agreements (SLAs) has historically been a challenge for some customers who need to insert very many data rows into the database at very high speeds.
The Insert Performance Challenge
Let’s consider a typical use case—an application characterized by a high rate of concurrent inserts into a journal or audit table. If the rows can’t be inserted fast enough, elapsed times are extended, transaction throughput is reduced and SLAs aren’t met. For prior releases of DB2, the space search algorithm used for the tablespace or partition can be a bottleneck for insert performance. A recommended way to deal with this problem is to use partitioned table spaces with the MEMBER CLUSTER and APPEND attributes, which forces DB2 to insert new rows at the end of the partition. This minimizes the impact of the space search algorithm.
This trade-off sustains high transaction rates by sacrificing the ability to store the data rows in the order of the clustering index. It’s not always necessary for the rows to be stored in the clustering index order, but some customers balance their need for fast insert processing with their need for efficient analytical processing by copying the data to other tables which are optimized for query performance. There is another advantage in doing this; if the number of indexes on the table used for insert processing is kept to a minimum, then the index maintenance overhead can be reduced, helping speed up inserts. Of course, if the table has no indexes, then index maintenance overhead is eliminated altogether.
Introducing Insert Algorithm 2
Using the MEMER CLUSTER and APPEND attributes to insert rows into the database more quickly proved a successful strategy. Despite this, some customers need even more improvement in insert throughput. DB2 12 tackles this challenge with a new insert algorithm that streamlines the free space search operation. The old insert algorithm is known as Algorithm 1, and the new one is known as Algorithm 2. Algorithm 2 avoids page contention—a significant cause of the space search bottleneck—and bulk formats new data pages for rows to be inserted into them. For each partition of each qualifying table space, the pages are written to a pipe (an in-memory queue) and an asynchronous task writes the pages to the table space.
This feature is specifically targeted at universal table spaces (UTS) with the MEMBER CLUSTER attribute because MEMBER CLUSTER is designed to avoid contention in data sharing. Note that the APPEND attribute isn’t required for a table space to qualify for Algorithm 2, widening the applicability of the new algorithm. This means that a UTS table space with MEMBER CLUSTER automatically qualifies for Algorithm 2.
Exploiting Algorithm 2
The first question on everyone’s mind when a new feature is introduced is “How do I exploit it?” or a more cautious “How do I turn it off?”
Before answering those questions, let’s clarify the requirements for the new algorithm. To add to what we already know, insert Algorithm 2 isn’t available until new function has been enabled in DB2 12. This is because the new function introduces new log record types that the algorithm requires. In summary, the requirements for the new insert algorithm are:
- Activating the new function
- Using the UTS table space type
- Defining the table space with Member Cluster
Going back to the above questions, Algorithm 2 can be turned on or off at the subsystem level using new DB2 12 system parameter DEFAULT_INSERT_ALGORITHM, and at the table space level using a new Data Definition Language (DDL) attribute INSERT ALGORITHM. The default setting for the new system parameter is to turn on Algorithm 2, but the DDL attribute can be used to override the system setting at a table space level.
If you want to exploit this new feature, plan for additional real memory for each qualifying table space partition and DB2 member. There’s an additional real memory requirement of 37 KB per partition per member. However, you should monitor Algorithm 2 APARs and PTFs to see if the memory requirement changes as DB2 maintenance is applied. In addition to the basic additional real memory requirement, monitor your buffer pools to see if they should be increased in size.
You should also be aware of any new messages and instrumentation (e.g., new message DSNI055I alerts you to errors when filling a pipe for a given partition). There are also new and modified IFCIDs that provide you with information about Algorithm 2 usage.
Note, by the way, that if DB2 is unable to use Algorithm 2 because of a pipe error, for example, then Algorithm 1 is used.
Algorithm 2 is intended to provide performance improvements, so let’s look at some figures produced during testing at IBM Silicon Valley Lab.
The chart above shows a higher rate of insert into a partition by range (PBR) UTS defined with MEMBER CLUSTER and APPEND in two-way data sharing in DB2 12 with new function enabled. The table space is group buffer pool dependent, with inserts being performed on both members. This is a special use case, as the table has no indexes but still demonstrates the benefits of Algorithm 2 when inserting into a table space without any of the side effects introduced by indexes. The workload consists of many short, fast insert processes running in parallel, each inserting a small number of rows. There are three things in particular to note with this chart:
1. The number of inserts per second increased from under 800,000 per second to over 1,000,000 per second—a 25 percent improvement
2. The Class 2 elapsed time per transaction with Algorithm 1 at 0.012 seconds per transaction was dramatically reduced to 0.002 seconds per transaction
3. The class 2 CPU time per transaction was reduced by about 20 percent
A more common scenario is outlined in the chart below, which is based on an application journal. Just as in the previous example, the experiments were performed in a two-way data sharing environment, with group buffer pool dependency. This is one of the use cases making up the high-insert performance workload test suite run by DB2 development on a continuous basis at the IBM Silicon Valley Laboratory.
There are three tables involved in this workload: one table with one index, the second with two indexes and the third with three indexes. The journal table—the target of the insert statements—is defined as a PBR UTS with row-level locking and Member Cluster. The index is based on a sequential key, and the rows arrive at the table in an order based on that key.
The chart compares insert throughput (inserts per second) and CPU per transaction in milliseconds on DB2 11 with insert Algorithm 1, and on DB2 12 with insert Algorithm 2. The CPU per transaction drops significantly from around 7.3 seconds per transaction to 6.2, which is approximately a 15 percent reduction. In addition, the insert throughput climbs dramatically, going from just over 300,000 per second to just over 400,000 per second—almost a 30 percent increase.
Bear in mind that these types of benefits will vary, and workloads that are constrained by lock/latch contention on the space map pages and on the data pages in the table space or table space partition are more likely to benefit from the new insert algorithm.
It’s very important to mention here that the new algorithm will not benefit cases where all the inserts are driven by a single batch process. It’s worth repeating that Algorithm 2 is targeted specifically at high-volume concurrent inserts.
Insert Algorithm 2 – Looking Ahead
This new feature is still a work in progress, and DB2 development is working on increased resilience, improved instrumentation and further performance improvements, as well as fixing any defects. You should monitor the DB2 12 APARs for any Algorithm 2 maintenance. Still open at the time of writing, for example, is APAR PI74870, which will provide improved information in message DSNI055I.
In the next article, we’ll look at a feature of great interest for many customers: DB2 12 access plan stability and access plan selection.
Gareth Z. Jones has worked in IT since 1985. Until 2000, he was an IBM client, with experience as a systems programmer and DBA. He now works in DB2 for z/OS development as a member of the SWAT Team, which is led by John Campbell. He has worked with many customers around the world to help them succeed in their use of DB2. Gareth has written several technical papers and presented at many conferences and group meetings. He can be contacted via email at firstname.lastname@example.org.