I'm guessing that you already know that the SMP abbreviation in Db2 for i SMP actually stands for Symmetric Multiprocessing and not System May Plod. However, I'm hoping a title that highlights a different meaning - especially one that implies slow performance - gets people's attention because the usage of Db2 SMP can have negative impacts.
One definition of plod that I found online is: walk
doggedly and slowly with heavy steps. I'm not sure about you, but that's
not how I'd want the performance of my IBM Power server described! How could
slow ever be associated with the Db2 for i SMP feature? When one thinks of parallel processing, it's
easy to envision your queries receiving a performance boost like an F1 race car receives
from its turbochargers. While this type of performance improvement can occur
for your queries with Db2 SMP, it can also result in plodding system
performance when parallel processing is not properly used.
The improper usage of Db2 SMP occurs when it's enabled
systemwide instead of selectively applying parallel processing to long-running
queries and database operations in specific jobs. A couple of years ago, I
summarized parallel processing best practices for Db2 for i in an entry titled:
The ABCs of Effective Db2 SMP Usage. The "C" stands for Controlled
Usage - just another way of describing how parallel processing should only
be applied to a subset of your workloads. Despite this strong recommendation
from myself and other IBMers, too many IBM i clients are taking the easy road
of activating Db2 SMP systemwide looking for a quick fix for their query performance
challenges.
This "easy" approach of enabling database parallel
processing systemwide may enhance query performance, but it can be at the
expense of other workloads running on the system. Below you'll find an IBM iDoctor for IBM i graph of CPU utilization (green line) from a customer's
system. Notice how the CPU utilization is holding steady around a 60% rate.
That steady, healthy CPU usage rate is then interrupted by a sharp jump in CPU utilization
for several minutes in the 85-90% range.
When our IBM Technology Expert Labs team was engaged by the client to determine the root cause of these system performance spikes, they found that the high CPU usage peaks were a result of multiple queries on the system using parallel processing. The query users were probably happy, but it's easy to see how other users and workloads on the system were negatively impacted. This client's experience was not just a one off - my colleagues in IBM Support and Technology Expert Labs have shared several stories me about clients engaging them to explain their system slowdowns.
This poor practice has been compounded by the fact that
since June 2022, IBM i clients can try Db2 SMP without paying for a license. In
prior years, IBM i customers had to make a monetary investment before they
could take the easy road of enabling Db2 SMP systemwide. In fact, IBM Support's official stance is that clients should only
enable parallel processing systemwide on IBM i 7.5. This latest release of the IBM i contains two
new features, PARALLEL_MAX_SYSTEM_CPU and PARALLEL_MIN_TIME, which are very useful in preventing a system from being
overrun from systemwide usage of Db2 SMP.
Some clients that have taken the
easy path of enabling Db2 SMP for all queries run on the system have also
discovered that some of their high-level language programs are not thread safe.
Consider an RPG program which has been registered as an external user-defined
function (UDF) with the SQL CREATE FUNCTION statement. If parallel processing
is applied to a query that invokes this external UDF, then you need to remember
that multiple parallel threads can now be invoking the RPG program. If this RPG program is not thread safe, then
issues can arise. In this situation, the
developer can compile their high-level language programs to be thread-safe or
specify the DISALLOW PARALLEL option on the CREATE FUNCTION statement which
will prevent the database engine from using parallel processing on any query
that references the UDF. It's very easy to overlook program thread safety when
turning on parallel processing for all the queries on your system.
Systemwide activation of Db2 SMP is
also risky because it can mask the root cause of your query performance
problems. With Db2 SMP, you're essentially trading system resources for faster
response times. While your queries can return faster with Db2 SMP, sometimes there
are more efficient methods to improve query performance - if you've enabled
parallel processing systemwide, it can be harder to find the queries that would
benefit from these more efficient methods.
A couple of years ago, our
Technology Expert Labs team was hired to help a client experiencing degraded system
performance. As we investigated the problem, we discovered that Db2
SMP was enabled systemwide. This systemwide enablement of Db2 SMP was causing a
frequently run query to be implemented with a parallel degree of 38. This meant
that every time this query was run on the system - there were 38 threads
running in parallel consuming CPU, memory, and disk I/O resources. Further
analysis of this query and database identified that the creation of a new index
would enable the query to run in the same amount of time but with less system resources
(i.e., use a single thread). This client had used Db2 SMP systemwide to
"fix" their query performance problems with hardware instead of
addressing the root cause of a missing index - eventually they ran out of
hardware resources.
Hopefully,
you now have a better awareness of the risks of taking the easy road of
activating Db2 SMP systemwide. Putting in the extra effort to selectively use
parallel processing should result in a healthier system performance. I stumbled upon the following quote from Robert Kiyosaki and think it summarizes things
nicely: Remember the easy road often becomes hard, and the hard road
often becomes easy.
