DB2: DPSI Do, or DPSI Don't?
One of the more interesting features delivered in recent years for DB2 for z/OS is the data-partitioned secondary index, or DPSI (pronounced "DIP-see"). Introduced with DB2 for z/OS Version 8, DPSIs are defined on table-controlled partitioned tablespaces (i.e., tablespaces for which the partitioning scheme is controlled via a table-level -- as opposed to an index-level -- specification), and are a) non-partitioning (meaning that the leading column or columns of the index key are not the same as the table's partitioning key), and b) physically partitioned along the lines of the table's partitions (meaning that partition 1 of a DPSI on partitioned table XYZ will contain entries for all rows in partition 1 of the underlying table -- and only for those rows).
[For more information about partitioned and partitioning indexes on table-controlled partitioned tablespaces, see my blog entry on the topic, posted a few weeks ago.]
Here's what makes DPSIs really interesting to me:
Isaac Yassin, a consultant and noted DB2 expert, was quick to respond to the question with a warning that the NPIs would have a very negative impact on the performance of the partition-level LOAD REPLACE operation. Isaac's answer was right on the money. It's true that the concept of a logical partition in an NPI (i.e., the index entries associated with rows located in a partition of the underlying table) makes a partition-level LOAD REPLACE compatible, from a concurrency perspective, with application access to other partitions in the target tablespace (a LOAD REPLACE job operating on partition X of table Y will get a so-called drain lock on logical partition X of each NPI defined on table Y). Still, technically feasible doesn't necessarily mean advisable. For a partitioned tablespace with no NPIs, a LOAD REPLACE with an empty input data set is indeed a very CPU- and time-efficient means of removing data from a partition, because it works at a data set level: the tablespace partition's data set (and the data set of the corresponding physical partition of each partitioned index) is either deleted and redefined (if its is a DB2-managed data set) or reset to empty (if it is a user-managed data set, or if it is DB2-managed and REUSE was specified on the utility control statement), and -- thanks to the empty input data set -- you have a purged partition at very little cost in terms of CPU consumption.
Put NPIs in that picture, and the CPU and elapsed time story changes for the worse. Because the NPIs are not physically partitioned, the index-related action of the partition-level LOAD REPLACE job (the deletion of index entries associated with rows in the target table partition) is a page-level operation. That means lots of GETPAGEs (CPU time!), potentially lots of I/Os (elapsed time!), and lots of index leaf page latch requests (potential contention issue with respect to application processes concurrently accessing other partitions, especially in a data sharing environment). Nick Cianci, an IBMer in Australia, suggested changing the NPIs to DPSIs, and that's what the DBA who started the DB2-L thread ended up doing (as I confirmed later -- he's a friend of mine). With the secondary indexes physically partitioned along the lines of the table partitions, the partition-level LOAD REPLACE with the empty input data set will be what the DBA wanted: a very fast, very efficient means of removing a partition's worth of data from the table without impacting application access to other partitions.
DPSI don't: there's a flip side to the partition-level utility benefits that can be achieved through the use of data partitioned (versus non-partitioned) secondary indexes, and it has to do with query performance. When one or more of a query's predicates match the columns of the key of a non-partitioned index, DB2 can quickly zero in on qualifying rows. If that same index is data-partitioned, and if none of the query's predicates reference the partitioning key of the underlying table, DB2 might not be able to determine that a partition of the table contains any qualifying rows without checking the corresponding partition of the DPSI (in other words, DB2 might not be able to utilize page range screening to limit the number of partitions that have to be searched for qualifying rows). If a table has just a few partitions, this may not be such a big deal. But if the table has hundreds or thousands of partitions, it could be a very big deal (imagine a situation in which DB2 has to search 1000 partitions of a DPSI in order to determine that the rows qualified by the query are located in just a few of the table's partitions -- maybe just one). In that case, degraded query performance might be too high of a price to pay for enhanced partition-level utility operations, and NPIs might be a better choice than DPSIs.
That's exactly what happened at a large regional retailer in the USA. A DBA from that organization was in a DB2 for z/OS database administration class that I taught recently, and he told me that his company, on migrating a while back to DB2 V8, went with DPSIs for one of their partitioned tablespaces in order to avoid the BUILD2 phase of partition-level online REORG. [With NPIs, an online REORG of a partition of a tablespace necessitates BUILD2, during which the row IDs of entries in the logical partition (corresponding to the target tablespace partition) of each NPI are corrected to reflect the new physical location of each row in the partition. For a very large partition containing tens of millions of rows, BUILD2 can take quite some time to complete, and during that time the logical partitions of the NPIs are unavailable to applications. This has the effect of making the corresponding tablespace partition unavailable for insert and delete activity. Updates of NPI index key values are also not possible during BUILD2.]
The DPSIs did indeed eliminate the need for BUILD2 during partition-level online REORGs of the tablespace (this because the physical DPSI partitions are reorganized in the same manner as the target tablespace partition), but they also caused response times for a number of queries that access the table to increase significantly -- this because the queries did not contain predicates that referenced the table's partitioning key, so DB2 could not narrow the search for qualifying rows to just one or a few of a DPSI's partitions. The query performance problems were such that the retailer decided to go back to NPIs on the partitioned table. To avoid the BUILD2-related data availability issue described above, the company REORGs the tablespace in its entirety (versus REORGing a single partition or a subset of partitions). [By the way, DPSI-related query performance problems were not an issue for the DBA who initiated the DB2-L thread referenced in the "DPSI do" part of this blog entry, because 1) the table in question is accessed almost exclusively for inserts, with only the occasional data-retrieval operation; and 2) the few queries that do target the table contain predicates that reference the table's partitioning key, so DB2 can use page-range screening to avoid searching DPSI partitions in which entries for qualifying rows cannot be present.]
DPSIs and DB2 9: the question as to whether or not you should use DPSIs is an interesting one, and it's made more so by a change introduced with DB2 9 for z/OS: the BUILD2 phase of partition-level online REORG has been eliminated (BUILD2 is no longer needed because the DB2 9 REORG TABLESPACE utility will reorganize NPIs in their entirety as part of a partition-level online REORG operation). Since some DB2 V8-using organizations went with DPSIs largely to avoid the data unavailability situation associated with BUILD2, they might opt to redefine DPSIs as NPIs after migrating to DB2 9, if DPSI usage has led to some degradation in query performance (as described above in the "DPSI don't" part of this entry). Of course BUILD2 avoidance (in a DB2 V8 system) is just one justification for DPSI usage. Even with DB2 9, using DPSIs (versus NPIs) is important if you need a partition-level LOAD REPLACE job to operate efficiently (see "DPSI do" above).
Whether you have a DB2 V8 or a DB2 V9 environment, DPSIs may or may not be a good choice for your company (and it may be that DPSIs could be used advantageously for some of your partitioned tables, while NPIs would be more appropriate for other tables). Understand the technology, understand your organization's requirements (and again, these could vary by table), and make the choice that's right in light of your situation.
[For more information about partitioned and partitioning indexes on table-controlled partitioned tablespaces, see my blog entry on the topic, posted a few weeks ago.]
Here's what makes DPSIs really interesting to me:
- They are a great idea in some situations.
- They are a bad idea in other situations.
- You may change the way you think about them in a DB2 9 context, versus a DB2 V8 environment.
Isaac Yassin, a consultant and noted DB2 expert, was quick to respond to the question with a warning that the NPIs would have a very negative impact on the performance of the partition-level LOAD REPLACE operation. Isaac's answer was right on the money. It's true that the concept of a logical partition in an NPI (i.e., the index entries associated with rows located in a partition of the underlying table) makes a partition-level LOAD REPLACE compatible, from a concurrency perspective, with application access to other partitions in the target tablespace (a LOAD REPLACE job operating on partition X of table Y will get a so-called drain lock on logical partition X of each NPI defined on table Y). Still, technically feasible doesn't necessarily mean advisable. For a partitioned tablespace with no NPIs, a LOAD REPLACE with an empty input data set is indeed a very CPU- and time-efficient means of removing data from a partition, because it works at a data set level: the tablespace partition's data set (and the data set of the corresponding physical partition of each partitioned index) is either deleted and redefined (if its is a DB2-managed data set) or reset to empty (if it is a user-managed data set, or if it is DB2-managed and REUSE was specified on the utility control statement), and -- thanks to the empty input data set -- you have a purged partition at very little cost in terms of CPU consumption.
Put NPIs in that picture, and the CPU and elapsed time story changes for the worse. Because the NPIs are not physically partitioned, the index-related action of the partition-level LOAD REPLACE job (the deletion of index entries associated with rows in the target table partition) is a page-level operation. That means lots of GETPAGEs (CPU time!), potentially lots of I/Os (elapsed time!), and lots of index leaf page latch requests (potential contention issue with respect to application processes concurrently accessing other partitions, especially in a data sharing environment). Nick Cianci, an IBMer in Australia, suggested changing the NPIs to DPSIs, and that's what the DBA who started the DB2-L thread ended up doing (as I confirmed later -- he's a friend of mine). With the secondary indexes physically partitioned along the lines of the table partitions, the partition-level LOAD REPLACE with the empty input data set will be what the DBA wanted: a very fast, very efficient means of removing a partition's worth of data from the table without impacting application access to other partitions.
DPSI don't: there's a flip side to the partition-level utility benefits that can be achieved through the use of data partitioned (versus non-partitioned) secondary indexes, and it has to do with query performance. When one or more of a query's predicates match the columns of the key of a non-partitioned index, DB2 can quickly zero in on qualifying rows. If that same index is data-partitioned, and if none of the query's predicates reference the partitioning key of the underlying table, DB2 might not be able to determine that a partition of the table contains any qualifying rows without checking the corresponding partition of the DPSI (in other words, DB2 might not be able to utilize page range screening to limit the number of partitions that have to be searched for qualifying rows). If a table has just a few partitions, this may not be such a big deal. But if the table has hundreds or thousands of partitions, it could be a very big deal (imagine a situation in which DB2 has to search 1000 partitions of a DPSI in order to determine that the rows qualified by the query are located in just a few of the table's partitions -- maybe just one). In that case, degraded query performance might be too high of a price to pay for enhanced partition-level utility operations, and NPIs might be a better choice than DPSIs.
That's exactly what happened at a large regional retailer in the USA. A DBA from that organization was in a DB2 for z/OS database administration class that I taught recently, and he told me that his company, on migrating a while back to DB2 V8, went with DPSIs for one of their partitioned tablespaces in order to avoid the BUILD2 phase of partition-level online REORG. [With NPIs, an online REORG of a partition of a tablespace necessitates BUILD2, during which the row IDs of entries in the logical partition (corresponding to the target tablespace partition) of each NPI are corrected to reflect the new physical location of each row in the partition. For a very large partition containing tens of millions of rows, BUILD2 can take quite some time to complete, and during that time the logical partitions of the NPIs are unavailable to applications. This has the effect of making the corresponding tablespace partition unavailable for insert and delete activity. Updates of NPI index key values are also not possible during BUILD2.]
The DPSIs did indeed eliminate the need for BUILD2 during partition-level online REORGs of the tablespace (this because the physical DPSI partitions are reorganized in the same manner as the target tablespace partition), but they also caused response times for a number of queries that access the table to increase significantly -- this because the queries did not contain predicates that referenced the table's partitioning key, so DB2 could not narrow the search for qualifying rows to just one or a few of a DPSI's partitions. The query performance problems were such that the retailer decided to go back to NPIs on the partitioned table. To avoid the BUILD2-related data availability issue described above, the company REORGs the tablespace in its entirety (versus REORGing a single partition or a subset of partitions). [By the way, DPSI-related query performance problems were not an issue for the DBA who initiated the DB2-L thread referenced in the "DPSI do" part of this blog entry, because 1) the table in question is accessed almost exclusively for inserts, with only the occasional data-retrieval operation; and 2) the few queries that do target the table contain predicates that reference the table's partitioning key, so DB2 can use page-range screening to avoid searching DPSI partitions in which entries for qualifying rows cannot be present.]
DPSIs and DB2 9: the question as to whether or not you should use DPSIs is an interesting one, and it's made more so by a change introduced with DB2 9 for z/OS: the BUILD2 phase of partition-level online REORG has been eliminated (BUILD2 is no longer needed because the DB2 9 REORG TABLESPACE utility will reorganize NPIs in their entirety as part of a partition-level online REORG operation). Since some DB2 V8-using organizations went with DPSIs largely to avoid the data unavailability situation associated with BUILD2, they might opt to redefine DPSIs as NPIs after migrating to DB2 9, if DPSI usage has led to some degradation in query performance (as described above in the "DPSI don't" part of this entry). Of course BUILD2 avoidance (in a DB2 V8 system) is just one justification for DPSI usage. Even with DB2 9, using DPSIs (versus NPIs) is important if you need a partition-level LOAD REPLACE job to operate efficiently (see "DPSI do" above).
Whether you have a DB2 V8 or a DB2 V9 environment, DPSIs may or may not be a good choice for your company (and it may be that DPSIs could be used advantageously for some of your partitioned tables, while NPIs would be more appropriate for other tables). Understand the technology, understand your organization's requirements (and again, these could vary by table), and make the choice that's right in light of your situation.
posted by Robert Catterall at
8:13 PM
4 Comments:
Will not the performance of Load Replace per partition still be better than using SQLs to delete records to overcome the increased number of get pages because of NPIs?
The point it-NPIs get pages is going to happen either way be it through Load Replace utitility or SQL delete statement when deleting records from the table.
I received an e-mail notification that someone had posted this comment (though for some reason I couldn't see the comment under the blog entry):
"Will not the performance of Load Replace per partition still be better than using SQLs to delete records to overcome the increased number of get pages because of NPIs?
The point is: NPIs get pages is going to happen either way be it through Load Replace utility or SQL delete statement when deleting records from the table."
I was not suggesting that SQL DELETEs will remove rows from a table partition more quickly and efficiently than would a partition-level LOAD with the REPLACE option if there are non-partitioned indexes defined on the table. I was just pointing out that a partition-level LOAD REPLACE process will run more slowly and consume more CPU if there are non-partitioned indexes defined on the target table, relative to the partition-level LOAD REPLACE performance you'd see if those indexes were DPSIs (data-partitioned secondary indexes).
Nice research. I just converted 28 tables to have all partitioned indexes. I partitioned on a column that is specified in 98+% of all the SQLs that access the table. So far, we've seen nothing slow down and most things speed up. We weren't looking for performance improvements. We did this to allow all 28 tables to have a single partition recovered. The performance improvments are a bonus. And we reorg by partition, so not having the NPIs sped that up, too. Based on this, we're likely going to convert the rest of the tables in this database and move on to others.
Sorry about the delay in responding, Bill.
I can see how DPSIs might improve the performance of some queries that reference a partitioned table's partitioning key. For such a query, the leaf pages of a DPSI would be "richer" than those of an NPI, as they would contain entries for key values that appear only in the corresponding table partition, versus all over the table. Given that the query's result set will come from a restricted number of partitions anyway (because of the aforementioned predicate referencing the partitioning key), the "richer" index leaf pages might translate into fewer GETPAGEs at the index level and correspondingly better performance.
Robert
Post a Comment
Subscribe to Post Comments [Atom]
<< Home