|
|
Intelligent Enterprise
November 1999
On-The-Fly On The Up And Up
Richard Winter and Pekka Kostamaa
Vendors are now publishing the latest Transaction Processing Council (TPC) benchmark results, which shed new light on the performance of leading database engines for data warehousing. Although the benchmarks attracted remarkably little attention when the TPC announced them earlier this year, they deserve notice. It's still not a good idea to take these new measures at face value, but they represent a few steps in the right direction for the TPC and the data warehousing industry.
The Sinking of the TPC-D
The change began when the controversial and widely discussed TPC-D benchmark went down almost as fast as the Titanic, ripped open at its point of vulnerability by the issue of stored aggregates. (See the September 14 Scalable Systems, "Be Aggregate Aware.") And we have seen only the tip of that proverbial iceberg so far.The first rip in the hull of the TPC-D came about two years ago, when NCR introduced the join index for Teradata, which yields the same sort of query speedup as a stored-aggregate feature. Stored aggregates are for automatic use by an optimizer to accelerate queries through precalculation of joins and sums, counts, averages, and the like. By late 1998, DB2 5.2 for Unix and NT had automatic summary tables (ASTs) and Oracle8i featured materialized views.
The TPC created its TPC-D to be an ad hoc query benchmark. However, because the TPC-D tests only 17 types of queries, vendors could use stored aggregates and other indexes to engineer the database design to those specific queries. And they did. In the later runs of the TPC-D, system resources were going significantly more into database building and updating than into query processing.
The TPC-D was no longer measuring what it was intended to measure, and people were concerned that its relevance to realistic data warehousing needs was declining. Matters had gotten out of hand.
At the same time, an unexpected benefit arose: The aggregation and indexing techniques vendors were developing gained importance for the real-life data warehouse. Therefore, the TPC realized, it would be desirable to continue measuring those techniques' performance.
Introduction of the TPC-H and the TPC-R
The TPC solved the dilemma by replacing the TPC-D with two new benchmarks: the TPC-R for "reporting" and the TPC-H for "ad hoc query." Both of these new benchmarks in fact are closely related to the TPC-D, using even the same database as their predecessor.The TPC-R judges predictable data retrieval and extraction, such as periodic reporting requires (culling weekly sales figures, for example). It is the intended showcase for performance capabilities such as stored aggregates and indexes. And this benchmark makes sense: it is realistic to use such capabilities for your periodic reporting requirements; you know in advance what data you'll need. Why not build indexes, summary tables, and the like?
The TPC-H, however, is aimed at unpredictable query needs. The test designers' reasoning is this: If you don't know what the query is going to be, you can't build a summary table or an index for it. The TPC-H, therefore, forbids stored aggregates and allows indexing only on primary keys. This benchmark was designed to measure the database engine's ability to cope with queries unknown in advance.
There are some other changes as well. Query 13 of the TPC-D was removed. The new benchmarks use a new query 13 and add new queries 18 through 22. The other queries, including the two update queries, are the same as in the original benchmark. Also significant is a new execution rule that requires part of the benchmark to run with multiple concurrent query streams. This rule closes an important loophole in the old benchmark that allowed publishable results based on single-user query performance alone.
Finally, the new benchmarks use a single metric, "Composite Queries per Hour," as the primary performance measure. It equals the square root of the product of the Power and Throughput metrics. The metric itself is not new, but its elevation to the role of primary metric is. Not only that, but it is more meaningful now that multiple query streams are required.
Assessment
These new benchmarks are definitely an improvement over the TPC-D. Having two separate measures, one for ad hoc query fitness and one for index and summary table efficacy, is better than having only one measure that no longer gives reliable information about either performance aspect. Testing more types of queries, and, especially, requiring multiple query streams are also valuable improvements.In addition, the introduction of the new benchmarks demonstrates that the TPC is capable of responding productively to technological advances, changing its measurement concepts to fit.
Unfortunately, some of the TPC-D's shortcomings remain. The database is still unskewed. The schema is still overly simple. The database doesn't contain enough columns for you to create realistic predicates and thus fully reveal the differences in indexing techniques and optimizers.
Finally, neither the TPC-H nor the TPC-R not even the combination of the two quite presents the performance challenge of a real data warehouse application. There are still relatively few distinct queries in the TPC-H, and because they are known far in advance, engineers can tune optimizers to artificially boost results. And of course, real data warehouses aren't subject to the TPC-H limitations. They do have indexes and summary tables. What real data warehouses don't have is database designers who know exactly which queries to expect.
Nor are real-life ad hoc queries totally unpredictable; database designers can guess some of their characteristics. For example, designers know that merchandisers often query for product sales by store and day. Although a great variety of queries will be ones the designer can't possibly predict, access patterns over time can help the designer decide on some additional summary tables to build. Similarly, a designer may know that users often select sets of customers for promotions according to age, income, profession, and hobbies. Again, though unable to foresee specific queries, the designer can use this knowledge to build indexes.
In production databases, many of the right indexes and aggregates are in place. The optimizer's challenge is most often in deciding how best to use those indexes and aggregates to handle a wide range of queries, many of which are unpredictable. Database engines in real life rarely have to handle a totally unexpected query that will not benefit from any existing aggregate or index structure.
Although the new TPC-H will help you analyze some aspects of performance, neither it nor the TPC-R quite presents the gist of the real-world challenge. Still, these new benchmarks are welcome improvements and the progress of the TPC deserves applause. Analyzing these benchmark results will tell us a great deal more than we know now about the performance of leading products in data warehousing. The trove of information created, especially now that the full dis- closure reports are available online, will be considerable.
However, it's important for consumers of this information especially users or application developers making decisions on the basis of it to understand something: These benchmarks don't present "the answer." The primary metric for the TPC-H is not really going to give a full picture of the ad hoc query performance for a database product or platform. It's going to give one, rather specific measure of it. The same is true for the TPC-R and evaluation of reporting. What you need to do is look beyond the single metrics, analyze the results, and understand what they are saying about your requirements. And with two benchmarks, you'll get more grist for analysis than before, but evaluating it will be a bigger job than ever.
Richard Winter (Ricahrd.Winter@wintercorp.com, fax: 617-338-4499) is a specialist in large database technology and implementation, and president of Waltham, Mass.-based Winter Corp (www.wintercorp.com).
Pekka Kostamaa (p.kostamaa@worldnet.att.net) is a database consultant focusing on database performance, performance measurement, and architecture.