In this article, Vikram Vaswani discusses the ways in which SQL plays an important role in the computer market today, and what may be in store for this database language in the future. This excerpt comes from chapter 26 of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).
As SQL-based relational databases have moved into the mainstream of enterprise data processing, database performance has become a critical factor in DBMS selection. User focus on database performance, coupled with the DBMS vendors’ interest in selling high-priced, high-margin, high-end DBMS configurations, has produced a series of benchmark wars among DBMS vendors. Virtually all of the DBMS vendors have joined the fray at some point over the last decade. Some have focused on maximum absolute database performance. Others emphasize price/performance and the cost-effectiveness of their DBMS solution. Still others emphasize performance for specific types of database processing, such as OLTP or OLAP. In every case, the vendors tout benchmarks that show the superior performance of their products while trying to discredit the benchmarks of competitors.
The early benchmark claims focused on vendor-proprietary tests, and then on two early vendor-independent benchmarks that emerged. The Debit/Credit benchmark simulated simple accounting transactions. The TP1 benchmark, first defined by Tandem, measured basic OLTP performance. These simple standardized benchmarks were still easy for the vendors to manipulate to produce results that cast them in the most favorable light.
In an attempt to bring more stability and meaning to the benchmark data, several vendors and database consultants banded together to produce standardized database benchmarks that would allow meaningful comparisons among various DBMS products. This group, called the Transaction Processing Council, defined a series of official OLTP benchmarks, known as TPC-A, TPC-B, and TPC-C. The Council has also assumed a role as a clearinghouse for validating and publishing the results of benchmarks run on various brands of DBMS and computer systems. The results of TPC benchmarks are usually expressed in transactions per minute (e.g., tpmC), but it’s common to hear the results referred to simply by the benchmark name (e.g., “DBMS Brand X on hardware Y delivered 10,000 TPC-Cs”).
The most recent TPC OLTP benchmark, TPC-C, attempts to measure not just raw database server performance, but the overall performance of a client/server configuration. Modern multiprocessor workgroup-level servers are delivering thousands or tens of thousands of transactions per minute on the TPC-C test. Enterprise-class UNIX-based SMP servers are delivering multiple tens of thousands of tpmC. The maximum results on typical commercially available systems (a multimillion-dollar 64-bit Alpha processor cluster) exceed 100,000 tpmC.
The Transaction Processing Council has branched out beyond OLTP to develop benchmarks for other areas of database performance. The TPC-D benchmark focuses on data warehousing applications. The suite of tests that comprise TPC-D are based on a database schema typical of warehousing environments, and they include more complex data analysis queries, rather than the simple database operations more typical of OLTP environments. Interestingly, the TPC benchmarks specify that the size of the database must increase as the claimed number of transactions per minute goes up. A TPC benchmark result of 5000 tpmC may reflect results on a database of hundreds of megabytes of data, for example, while a result of 20,000 tpmC on the same benchmark may reflect a test on a multigigabyte database. This provision of the TPC benchmarks is designed to add more realism to the benchmark results since the size of database and computer system needed to support an application with demands in the 5000 tpm range is typically much smaller than the scale required to support an application with 20,000 tpm demands.
In addition to raw performance, the TPC benchmarks also measure database price/ performance. The price used in the calculation is specified by the council as the five-year ownership cost of the database solution, including the purchase price of the computer system, the purchase price of the database software, five years of maintenance and support costs, and so on. The price/performance measure is expressed in dollar-per-TPC (e.g., “Oracle on a Dell four-way server broke through the $500-per- TPC-C barrier”). While higher numbers are better for transactions-per-minute results, lower numbers are better for price/performance measures.
Over the last several years, vendor emphasis on TPC benchmark results have waxed and waned. The existence of the TPC benchmarks, and the requirement that published TPC results be audited, have added a level of integrity and stability to benchmark claims. It appears that benchmarking and performance testing will be part of the database market environment for some time to come. In general, benchmark results can help with matching database and hardware configurations to the rough requirements of an application. On an absolute basis, small advantages in benchmark performance for one DBMS over another will probably be masked by other factors.
Remember: this is chapter 26 of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. Buy this book now.