Db2 The Performant Database
On June 30, IBM is releasing Db2 11.5.4, the latest version of Db2 database. This new release includes lots of enhancements in the areas of Enterprise Readiness, Cost Savings, Multi-Cloud support, Graph Database, Spatial Analytics, Machine Learning, Security, Availability, and enhanced Developers Support.
This blog will focus on performance enhancements in Enterprise Readiness, which comprise the following topics:
· Faster Database Activation
· Federation Parallelism
· Columnar Query Speed-up
Faster Database Activation
Database activation time, among other factors, depends heavily on the time it takes to allocate the database buffer pools in memory. When you activate a database, the database infrastructure and background threads are started, and the memory for all buffer pools is allocated. If the total size of the buffer pools is large, this can substantially delay the completion of the activation. This manifests as a delay in activation or a temporarily “hung” connection from an end user perspective, and can be particularly problematic if occurring during a recovery scenario. The delay can last dozens of minutes depending on the amount of memory requested, and on memory fragmentation. This is especially relevant on larger systems that have large buffer pools and/or multiple buffer pools.
To improve the overall performance of the database, it is recommended to allocate large buffer pools to be used by the database. With large buffer pools you can fit the working set size in memory, and thereby reduce the number of I/O operations to disks. Even if you are using Solid State Disks or SSDs for storage, memory is still thousand times faster than accessing data on those disks. Another strategy to improve database performance is to create multiple buffer pools based on your data access pattern. You can configure separate buffer pools that either keep data in the buffer cache longer, or make the buffers available for new data soon after accessing the data blocks. This is relevant since memory is managed with a least recently used (LRU) algorithm. For example, you might want to put tables and indexes that are queried and updated frequently into a large buffer pool, while keeping a separate buffer pool for those blocks that are infrequently accessed. In addition, creating multiple buffer pools is needed when using tablespaces with different page sizes.
The Faster Database Activation feature allows the buffer pools to be allocated asynchronously in the background rather than synchronously upon database startup, allowing the database to be available for usage much more quickly. In internal benchmarks we have measured as much as a 30x reduction in activation time for configurations with large buffer pools. The high-level processing is illustrated below in Figure 1.
Federation Parallelism
This enhancement applies to Db2 DPF (Database Partitioning Feature). A partitioned database environment is a database installation that supports the distribution of data across multiple database partitions. All partitions are completely independent of each other with a shared nothing architecture. A database partition includes its own data, indexes, configuration files, and transaction logs. Because data is distributed across database partitions, you can use the power of multiple processors on multiple physical machines to satisfy requests for information. Tables can be located in one or more database partitions. When a database table is divided into multiple database partitions, some of its rows are stored in one database partition, and other rows are stored in other database partitions.
In a partitioned database environment, user interaction occurs through one database partition, known as the coordinator partition for that user. When an application issues a query, the coordinator node compiles the query, then sends parts of these database requests to subagents at the other partition nodes. All results from the other partitions are consolidated at the coordinator node before being sent back to the application. Federation allows you to access heterogeneous data source outside of Db2. When an application issues a query, the coordinator node compiles the query into a serialized plan, which gets executed only in the coordinator node.
The enhancement in this release introduces federation inter-partition parallelism. With inter-partition parallelism, federation will be enabled on all active nodes instead of only at the coordinator node, and the process of fetching data from remote data sources is distributed among all the nodes, making the fetching of data much faster. This is made possible by enhancing the Query Compiler to generate a parallelized federated access plan, and splitting the remote data to be fetched between all the different partitions. Every partition receives the access plan and executes it, as shown in figure 2 below.
Columnar Query Speed-up
This enhancement is specific to Db2 BLU for queries accessing column-organized tables, and targeted to removing duplicate rows in a database. It introduces new ways to handle duplicate rows early on, and performs aggregation (“group by”) processing earlier when executing a query in the columnar engine. By addressing this issue earlier during query processing, it averts much bigger work later on, this tends to improve the query performance and memory utilization.
This enhancement uses three strategies to tackle the above problem, and the best part is that the query optimizer determines the best methods to use depending on the specific case, based on the estimated cost. To best explain this enhancement, let us use the example shown in figure 3 below. Suppose we have 2 partitions in an DPF or partitioned database, and a table defined across both partitions with distribution key C1. All rows with the same value for C1 reside in the same partition. Query processing occurs in parallel in both partitions, in a shared-nothing architecture. The problem we are trying to solve is how to remove duplicate rows, if the table is spread over multiple different machines?
Example: “select distinct C2”. In the figure below, we have C2 =2, and C2 = 3 in both partitions.
The current strategy for partitioned databases redistributes the rows on the fly, by sending the rows from one partition to the other. The goal is to consolidate all the 2’s in one partition, and all the 3’s in the other partition then remove the duplicates as shown in figure 4. This strategy could be expensive, as it involves moving a lot of rows between partitions.
Now can fully remove the duplicates:
An alternative to the above approach called “MPP partial-final distinct”, is to first remove the duplicates locally…
then redistribute a lot fewer rows…
then remove the duplicates.
The optimizer will model both methods, and chooses the one estimated to be cheaper i.e. uses the least amount of resources.
Another strategy called “Full Early Distinct” or FED uses a divide and conquer approach. The duplicate removal happens early on, and in smaller steps, instead of one huge step later which may overwhelm the memory. The query compiler analysis marks the spots where duplicates can optionally be removed while still maintaining correctness. The optimizer creates multiple plan candidates, some with the early duplicate removal, and some without. The winning plan is the one using the least amount of resources.
With the last strategy called “Full Early Aggregation” or FEA, the query compiler analysis marks the spots where early aggregation can optionally be performed while still maintaining correctness. This strategy is identified during query compilation at the query rewrite phase, and determines whether or not to use it during the optimization phase. The goal is to reduce the volume of data to be processed later by aggregating it earlier, particularly before expanding joins. This can reduce the overall effort and memory use. The optimizer tries to determine under which operations, and under which joins it is advantageous to place the early aggregation. The optimizer creates multiple plan candidates, some with the early aggregation, and some without. The winning plan is based on the estimated cost.
Conclusion
This latest version of Db2 11.5.4 release introduces several enhancements in the areas of Enterprise Readiness related to performance in terms of Faster Database Activation, Federation Parallelism, and Columnar Query Speed-up. These enhancements apply to OLTP, partitioned, and columnar databases, making Db2 more responsive to activation and answering queries. You can find out more information about the IBM Db2 database here.
