Earlier this month, IBM announced
support for Power11.
That announcement reminded me of a Db2 enhancement tied to the IBM Power10
portfolio which hasn’t been widely promoted. With Power10, the processors
include an on-chip unit that supports hardware-based compression and
decompression.
The Db2
for i development team decided to leverage this high-speed compression
capability with its Plan Cache support. When a query running on Power10 (&
Power11) servers produces a large query plan, the Db2 optimizer will store a
compressed version of the query access plan in its Plan Cache. Compressed query
plans means that Db2 can store more plans in the Plan Cache without increasing
its overall size. More query plans increase the chances of Db2 reusing a query plans which contributes to
faster query performance since building a new query plan takes time and system
resources.
As you can see in the following figure, Db2 uses a couple of new Plan Cache
properties in the ACS SQL Performance Center to highlight the temporary storage
savings of query plan compression on newer Power servers. In this example,
query plan compression is saving over half a gigabyte of temporary storage
usage.
With
this compression capability available, clients running with Power10 or Power11
may also want to review their Plan Cache configuration setting for the Plan
Cache auto-sizer. That setting is also visible on the SQL Performance Center Plan
Cache properties display as shown below.
Combining
plan compression with auto-sizing enables clients to get a bigger bang for their buck when it comes to the temporary storage footprint
used by Db2 for query workloads. That’s because the size of the Plan Cache not
only controls the amount of temporary storage allocated for the Plan Cache, but
it also impacts how much temporary storage is used to cache temporary runtime data structures to speed up future executions of the same
query.
If the
Plan Cache uses auto-sizing, Db2 will keep the same number of plans and cached
temporary structures as it did before Power 10 hardware, but it will do so with
a smaller temporary storage footprint. That’s due to the fact that the hit
ratio drives the number of plans in the cache instead of the fixed size of the
cache. A comparison of the two sample
Plan Cache configuration should make it clearer why auto-sizing can have an
advantage when it comes to overall temporary storage usage by Db2 for query
workloads.
First,
let’s start with a Plan Cache using auto-sizing. On older hardware, assume that
twenty thousand query plans require 2 GB of temporary storage in the auto-sized
Plan Cache. With Power10 servers, we’ll assume that twenty thousand query plans
could be stored in 1.5 GB of storage.
In
contrast, consider a Plan Cache manually set to a fixed size of 2 GB. On older
hardware, it makes sense that twenty thousand query plans can also be stored in
that fixed-size cache. Imagine that Power10 compression enables twenty-three
thousand plans to be stored in the fixed-size 2 GB cache. While it’s great the
fixed-size plan cache can store three thousand more plans with compression,
keep in mind that the number of query plans dictates how much temporary
storage Db2 allocates for the caching of temporary runtime structures mentioned
earlier. The higher the number of query plans, the higher the number of temporary
runtime structures that can be cached by Db2 for i.
With
twenty-three thousand plans, let’s assume Db2 allocates 2 GB for cached
temporary runtime structures. That means the total temporary storage footprint
for the fixed-size cache is 4 GB – 2 GB for the fixed size Plan Cache and 2 GB
for the cached temporary structures. In
comparison, assume that the twenty thousand plans in the auto-sized Plan Cache
results in Db2 allocating 1.75 GB of storage for cached temporary runtime
structures. That results in total temp storage footprint for the auto-sized
Plan Cache being 3.25 GB. Almost 20%
less storage than the fixed-size cache temporary storage footprint.
The next
logical question that pops to mind is why are not all clients using Plan Cache
auto-sizing? One of the primary reasons
for this choice is that Db2’s auto-sizing algorithm prior to the IBM i 7.4
release was too conservative in its usage of temporary storage for the Plan
Cache. This conservative nature often caused the auto-sizer to not allocate
more temporary storage to store more query plans in the Plan Cache. Some customers’
query workloads were not able to meet their target Plan Cache Hit Ratio due to
this behavior. As a result, some clients turned off the auto-sizer and manually
increased the size of the Plan Cache to achieve a higher hit ratio.
So, if you’re using newer Power hardware (Power10 or Power11), it’s a good time to review your Plan Cache configuration to ensure that your query workloads are getting the most out of the temporary storage and hardware compression used by Db2 for your query workloads. If you’re not yet using newer Power hardware, then maybe it’s a good time to put a bug in the ear of your management.
