A significant
trend in the
database world these days is moving more data
management and processing into memory. If you can bypass disk I/O you can
achieve tremendous performance gains. There are many reasons for
this, but of course, the most important reason is that disk access is much
slower than memory access.
It is orders of magnitude more efficient to access data
from computer memory than it is to read it from disk. Memory access is usually
measured in microseconds, whereas disk access is measured in milliseconds (1
millisecond equals 1000 microseconds).
So, it makes sense to process as much data as we can in
memory. This is the reason that most popular RDBMS use buffer pools to cache
data in memory. But buffer pools are not always sufficient for caching
everything that is needed. Just take a look at IBM Db2 12 for z/OS and the
index FTB feature, which caches unique index structures in memory – not in any
buffer pool.
FTBs, or Fast Traverse Blocks, provide in-memory index
optimization. FTBs are stored in a separate area outside of your buffer pools.
You must allocate additional real memory for it in Db2 12. This memory is an
optimized structure for fast index lookups that provide random index access.
The FTB contains the non-leaf structure of the index.
QuickSelect
for Db2
Of course, FTBs are just one technique to optimize your
memory usage. Another technique you might consider is to deploy QuickSelect for Db2, an in-memory
optimization product from Log-On Software
(an Israeli company that has been providing tools and services to the mainframe
market for decades). QuickSelect for Db2 caches query results in memory. This
is a different concept than buffer pools which cache table and index space
pages or in-memory tables which cache entire tables.
By caching the results of frequently run queries in memory
with QuickSelect for Db2, you can optimize the SQL statements that recur
frequently in your environment. QuickSelect runs as a started task and it
automatically caches repetitive query results in self-managed memory above the
bar (64-bit).
QuickSelect returns the same data DB2 would – but from its
cache. QuickSelect will identify and cache only frequently used data via a
threshold parameter. This saves both CPU and I/O, and therefore can reduce
elapsed time and improve application response times. All other requests are
satisfied as usual by Db2.
Which leads to the question: which queries should be
cached? QuickSelect helps you to identify ideal queries with its Survey mode
which automatically finds SQL queries suitable for caching.
The results cached by QuickSelect are the actual data retrieved
by Db2 queries. So the next time that same query runs (with the same host variable
values) the results are quickly returned from the QuickSelect cache instead of
invoking process-heavy disk I/O operations. Think of all the repetitive SQL
that you are running in your shop every day – and all of the redundant I/O that
is used to read that data over and over again.
But, you may be asking, what happens if the data changes?
Well, QuickSelect is aware of all changes that are made, whether by a Db2
utility like Load, Reorg and Recover, or by your applications issuing Insert,
Update, Delete, and Truncate statements. Data changes on the tables that are
cached are detected by QuickSelect in real-time using various techniques. QuickSelect
invalidates the cached data for the updated tables automatically, thus data
integrity is preserved. Further queries will cause QuickSelect to cache the
updated data, thereby continuing to save. QuickSelect runs in a multiple LPAR
environment and supports update sensitivity by using XCF functions.
In other words, QuickSelect returns the same answer as Db2
does… only faster. And, of course, that is the most important thing.
If you were paying attention as you read through this so
far you will have noticed that there are no application changes required
whatsoever to enable QuickSelect. And you do not have to Rebind your programs
to take advantage of QuickSelect, either!
What happens if you shut down QuickSelect? There should be
no impact on your application programs; processing reverts entirely back to
Db2. The data will no longer be returned from the QuickSelect cache, instead it
will again be retrieved by Db2 just like before you used QuickSelect.
QuickSelect:
The Latest Features
Log-On Software is not resting on its laurels; new
features are added to QuickSelect on a regular basis. For example, in Febrauary
2018 auto-configuration was added to QuickSelect, thereby easing configuration
in a Data Sharing environment.
With auto-configuration the QuickSelect group
configuration is dynamically determined. It is no longer required to
pre-configure QuickSelect groups. QuickSelect now automatically detects the
active members in the Db2 group, if any, and automatically establishes a
connection between all QuickSelect servers that correlate to the current Db2
configuration. This is much simpler than the previous requirement of
hard-coding QuickSelect grouping using pre-defined parameters.
The
Bottom Line
QuickSelect for Db2 offers a different approach to
in-memory caching by focusing on query results. QuickSelect for Db2 customers
have been able to significantly reduce CPU during peak times – the times set by the rolling four-hour average that impact your monthly IBM software bill.
For example, one customer, a large European commercial
Bank was able to turn off an entire CPU after installing QuickSelect in production
due to the CPU savings they achieved. In this case, the customer was saving
more than 10 billion SQL statements during a typical 12-hour window.
The bottom line is that in-memory processing can save CPU,
improve performance and save money. Wise organizations will look into multiple
ways of exploiting memory to achieve results, including potentially looking at
novel software solutions like QuickSelect for
Db2.
