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
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:
- Capture a workload.
- Prepare to replay the workload.
- Replay the workload.
- 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
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:
- Capture a representative workload sample in the pre-DB2 BLU production environment.
- Create a Capture report to validate the workload.
- 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.
- Replay the workload on the target database.
- 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
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
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:
- Enable BLU Acceleration for some (or all) tables in the target DB2 V10.5 database environment.
- Replay the baseline workload.
- Analyze the result for performance differences.
- 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
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
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
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
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
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
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
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
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
If the workload replay results don't yet meet your requirements:
- Reset the database.
- Reanalyze the workload using OQWT with different settings; for example, by choosing a subset of the tables using the Test Candidate Table Organization feature.
- 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.
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.
The authors greatly thank Leif Pedersen for his assistance in writing this article.
- "Super Analytics, Super Easy: Introducing IBM DB2 10.5 with BLU Acceleration" (IBM data magazine, 2013): Read about reducing costs and improving time-to-value by making complex analytics faster, easier, and more resource-friendly.
- "DB2 with BLU Acceleration: A rapid adoption guide" (developerWorks, 2013): A few scenarios that show the key setup requirements to start leveraging BLU technology for your workload.
- "Use Optim Data Tools to get the most out of BLU Acceleration" (developerWorks, 2013): Three scenarios that illustrate how to use Data Studio and IBM InfoSphere Optim Query Workload Tuner (OQWT) with the BLU Acceleration feature.
- InfoSphere Workload Replay for DB2: Capturing, replaying, and analyzing workloads: An e-learning course for using the InfoSphere Workload Replay web console to capture workloads, prepare them for replay, and then compare and analyze the execution results.
- InfoSphere Workload Replay community on DeveloperWorks: A central location for users and business partners to find and share information about using IBM InfoSphere Workload Replay.
- InfoSphere Workload Replay Information Roadmap: Resources for planning, installing, evaluating, using, and troubleshooting.