Measure the impact of DB2 with BLU Acceleration using IBM InfoSphere Optim Workload Replay

Validate performance estimates using production workloads

In this article, learn to use IBM® InfoSphere® Workload Replay to validate the performance improvement of InfoSphere Optim™ Query Workload Tuner (OQWT) driven implementation of DB2® with BLU Acceleration on your production databases. The validation is done by measuring the actual runtime change of production workloads that are replayed in an isolated pre-production environment.

Share:

Patrick Titzler (ptitzler@us.ibm.com), Technical Enablement Specialist, IBM

Photo of Patrick TitzlerPatrick currently leads technical enablement for InfoSphere Workload Replay for DB2 for z/OS and DB2 for Linux, UNIX and Windows. He guides customers in their product evaluation efforts and supports best practice deployments.



Hassi Norlén (hnorlen@us.ibm.com), Information Developer, IBM

Photo of Hassi NorlénHassi Norlén leads the information development for InfoSphere Workload Replay and specializes in up-and-running documentation and user interface development using the progressive disclosure methodology. Hassi currently works on InfoSphere Optim Data Management Solutions and is based in Washington, D.C.



08 May 2014

Also available in Chinese Russian

Overview

IBM DB2 with BLU Acceleration, a feature introduced in DB2 version 10.5, can dramatically improve the performance of complex analytical queries. This article builds on a previous article, "Use Optim Data Tools to get the most out of BLU Acceleration", which outlines how to use IBM InfoSphere Optim Query Workload Tuner with BLU Acceleration to improve the performance of your analytical workloads. (There are many articles about this exciting new technology, how it works, and how you can adopt it in your environment. Resources has links to more articles about BLU Acceleration.)

To determine if your existing row-based tables might benefit from conversion to column-based tables, you can use OQWT, which is part of the DB2 Advanced Editions. OQWT version 4.1 introduces new features that use a cost-based approach to determine which existing row-based tables should be converted to improve performance.

You can use InfoSphere Workload Replay to measure and analyze the runtime impact that DB2 BLU Acceleration has on the performance of your analytical workloads. Use the tool to first capture a workload in your production environment and then replay it in a pre-production environment where BLU Acceleration is enabled, as shown in Figure 1.

Figure 1. Using InfoSphere Workload Replay
Use InfoSphere Workload Replay to analyze the performance impact that DB2 BLU Acceleration has on runtime

InfoSphere Workload Replay is different from other test tools because it lets you capture actual production SQL workloads and replay them in your test environment without having to set up a complex application infrastructure to drive the SQL activity. InfoSphere Workload Replay complements your existing test strategy by letting you assess the impact on the behavior of your critical applications from hardware, software, database, or configuration changes on the database server.

The general workload replay workflow is divided into four steps:

  1. Capture a workload.
  2. Prepare to replay the workload.
  3. Replay the workload.
  4. Compare and analyze the workloads.

This article outlines how you can use these steps to assess the DB2 BLU Acceleration impact on the runtime performance of your workloads. To learn more about the general workload replay workflow and its tasks and steps, try our new e-learning course (in Resources).


Establish a baseline workload

To compare performance before and after DB2 BLU Acceleration, you must first establish a baseline workload.

Start by capturing an analytical workload in the source database environment, as in Figure 2. Replay the workload in the target environment without enabling BLU Acceleration. Then, analyze whether the workload execution behavior in the target sufficiently approximates the behavior in the source environment.

Figure 2. Establish a baseline workload on DB2 v10.5
Establishing a baseline

Figure 3 shows an example of the InfoSphere Workload Replay web console with the stages and accompanying workloads of a typical workload lifecycle. The steps are:

  1. Capture a representative workload sample in the pre-DB2 BLU production environment.
  2. Create a Capture report to validate the workload.
  3. Prepare the workload and the DB2 V10.5 target database for initially replaying the workload without BLU Acceleration enabled. This step includes creating the required database objects, loading data, and granting privileges to execute the SQL.
  4. Replay the workload on the target database.
  5. Create a Comparison report to identify potential accuracy issues such as SQL return code, row count differences, and performance differences.
Figure 3. Example completed baseline workflow
Example of a completed baseline workflow in the InfoSphere Workload Replay web console

The elapsed time-based performance reports highlight SQL performance differences between two workload executions, making it easy to identify regressions or improvements at the workload, transaction, or SQL level. Figure 4 shows an example.

Figure 4. Analyze workload replay differences with performance reports
Analyze workload replay differences with performance reports

Any significant issues detected during the workload replay should be addressed. The workflow steps can then be repeated until the behavior of the replayed workload is sufficiently similar to the behavior of the original workload. You now have a baseline workload that can be used for further testing.


Analyze BLU Acceleration impact on workload performance

After a baseline workload has been established, you can export the captured SQL for use by OQWT and use the cost estimation advisors in OQWT to determine whether the captured workload might benefit from BLU Acceleration. If suitable candidate tables are identified by the advisor:

  1. Enable BLU Acceleration for some (or all) tables in the target DB2 V10.5 database environment.
  2. Replay the baseline workload.
  3. Analyze the result for performance differences.
  4. Repeat this process until no more suitable conversion candidates are identified or until your objectives have been met.

Figure 5 outlines the process, which is described in more detail in the following sections.

Figure 5. Analyze BLU Acceleration impact
Analyze BLU Acceleration impact

Import the captured SQL into OQWT

You can export SQL data in an OQWT-compatible format from Performance or Accuracy reports in the InfoSphere Workload Replay web console. Depending on which report you initiate the export operation from, you can choose to export all SQL or just a subset, as in Figure 6.

Figure 6. Export SQL from the Performance report
TODO

For each unique SQL statement, the exported metadata includes execution count as well as total execution time, which was collected during the replay.

With OQWT, you can import SQL workloads from a variety of sources such as the dynamic statement cache, database performance monitors, and files. Figure 7 shows an example. An exported workload replay workload file can serve as an OQWT source for BLU Acceleration analysis.

Figure 7. Import captured SQL into OQWT
TODO

Identify BLU Acceleration candidate tables

Version 4.1 of OQWT introduces two major DB2 BLU specific features: the Table Organization Advisor and the Test Candidate Table Organization feature.

The Table Organization Advisor first collects cost estimations from the DB2 optimizer for each of the SQL statements that use the existing non-BLU tables. It then identifies tables that are referenced by the workload and ignores those that don't meet BLU prerequisites. The remaining qualifying tables are converted virtually to column-organized tables, and query cost estimations are collected again for the entire workload. Using the pre- and post-conversion cost estimations, and taking into account any user-defined minimum benefit threshold, OQWT recommends which tables should be converted. Figure 8 shows an example.

Figure 8. Review table organization recommendation results in OQWT
Review table organization recommendation results in OQWT

For tables that do not qualify as good candidates, OQWT states the reasons. For tables that are considered good candidates, as in Figure 9, additional information is provided such as which SQL will be impacted and how much performance is expected to change.

Figure 9. Expected impact of conversion to column-based storage on your SQL
Review the expected impact that conversion to column-based storage has on your SQL

The Test Candidate Table Organization feature lets you manually select tables that should be converted providing what-if analysis capability.

If one or more conversion candidates were identified by OQWT, you can use IBM Data Studio to convert those tables from row-organized to column-organized in the target database.

Validate performance impact estimates

After the candidate tables have been converted to use BLU Acceleration and the database has been prepared for replay, you can replay the baseline workload and then create a Comparison report to analyze the performance changes. In the report, listed in Figure 10, you compare the original baseline workload replay on DB2 without BLU Acceleration with this workload replay on a DB2 with BLU Acceleration.

Figure 10. Completed BLU Acceleration analysis workflow in the Workload Replay web console
TODO

The BLU Acceleration impact on performance of the workload is easily seen in the Performance reports, as in Figure 11.

Figure 11. Analyze DB2 BLU Acceleration impact on workload performance
TODO

In this report view, a lower cumulative statement response time during replay indicates that overall it took DB2 less time to process the SQL statements that returned the same results. The workload performance statistics identify how many of the workload SQL statements improved or regressed by at least the threshold value that was set for your report configuration. In the previous example, the measured performance improvement exceeds the estimation that OQWT provided.

The SQL Improvements drill-down report identifies statements for which the response time was reduced, as in Figure 12. Sort the report by any of the elapsed time metrics to review the SQL statements that were most affected by BLU Acceleration.

Figure 12. Review SQL performance improvements
Reviewing query performanc eimprovements in the Workload Replay performance report

You can identify outliers by drilling into the SQL Details report for each of the unique statements. Figure 13 shows an example.

Figure 13. SQL performance details for individual SQL executions
Reviewing SQL performance details for individual SQL executions

If the workload replay results don't yet meet your requirements:

  1. Reset the database.
  2. Reanalyze the workload using OQWT with different settings; for example, by choosing a subset of the tables using the Test Candidate Table Organization feature.
  3. Replay the workload again.

You can also evaluate the impact that the proposed BLU Acceleration implementation has on other workloads by replaying them and analyzing differences from earlier workload replays that you might have performed.


Conclusion

With InfoSphere Workload Replay, you can streamline how you assess DB2 with BLU Acceleration impact on your analytical workloads before you make the changes in your production environment. By replaying production workloads in your pre-production database environment, you can easily confirm the accuracy of InfoSphere OQWT estimates without setting up a workload-specific application infrastructure.

Resources has more information about InfoSphere Optim Query Workload Tuner and InfoSphere Workload Replay.


Acknowledgement

The authors greatly thank Leif Pedersen for his assistance in writing this article.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=970729
ArticleTitle=Measure the impact of DB2 with BLU Acceleration using IBM InfoSphere Optim Workload Replay
publish-date=05082014