Home arrow MySQL arrow Page 4 - The Future of SQL

Hardware Performance Gains - MySQL

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).

  1. The Future of SQL
  2. Database Market Trends
  3. Market Diversity and Segmentation
  4. Hardware Performance Gains
  5. Benchmark Wars
  6. SQL Standardization
  7. SQL in the Next Decade
  8. Ultra-High-Performance Databases
  9. Object Integration
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 15
November 10, 2004

print this article



One of the most important contributors to the rise of SQL has been a dramatic increase in the performance of relational databases. Part of this performance increase was due to advances in database technology and query optimization. However, most of the DBMS performance improvement came from gains in the raw processing power of the underlying computer systems, and changed in the DBMS software designed to capitalize on those gains. While the performance of mainframe systems steadily increased, the most dramatic performance gains have been in the UNIX-based and Windows-based server markets, where processing power has doubled or more year by year.

Some of the most dramatic advances in server performance come from the growth of symmetric multiprocessing (SMP) systems, where two, four, eight, or even dozens of processors operate in parallel, sharing the processing workload. A multiprocessor architecture can be applied to OLTP applications, where the workload consists of many small, parallel database transactions. Traditional OLTP vendors, such as Tandem, have always used a multiprocessor architecture, and the largest mainframe systems have used multiprocessor designs for more than a decade. In the 1990s, multiprocessor systems became a mainstream part of the UNIX-based server market, and somewhat later, an important factor at the high end of the PC server market.

With Intel’s introduction of multiprocessor chipsets, SMP systems featuring two-way and four-way multiprocessing achieved near-commodity status in the LAN server market, and were available for well under $10,000. In the midrange of the UNIX-based server market, database servers from Sun, Hewlett-Packard, and IBM routinely have 8 or 16 processors and sell in the hundred-thousand-dollar price range. High-end UNIX servers today can be configured with more than 100 processors and tens of gigabytes of main memory. These systems, which rival the computing power of traditional mainframes, carry multimillion-dollar price tags.

SMP systems also provided performance benefits in decision support and data analysis applications. As SMP servers became more common, the DBMS vendors invested in parallel versions of their systems that were able to take the work of a single complex SQL query and split it into multiple, parallel paths of execution. When a DBMS with parallel query capabilities is installed on a four-way or eight-way SMP system, a query that might have taken two hours on a single-processor system can be completed in less than an hour. Companies are taking advantage of this hardware-based performance boost in two ways: either by obtaining business analysis results in a fraction of the time previously required or by leaving the timeframe constant and carrying out much more complex and sophisticated analysis.

Operating system support for new hardware features (such as multiprocessor architectures) has often lagged the availability of the hardware capabilities—often by several quarters or even years. This has posed a special dilemma for DBMS vendors, who need to decide whether to bypass the operating system in an attempt to improve database performance. The Sybase DBMS, for example, when originally introduced, operated as a single process and took responsibility for its own task management, event handling, and input/output—functions that are usually handled by an operating system such as UNIX or VMS. In the short term, this gave Sybase a major performance advantage over rival DBMS products with less parallel processing capability.  

But when operating system SMP support arrived, many of its benefits were automatically available to rival systems that had relied on the operating system for task management, while Sybase had the continuing burden of extending and enhancing its

low-level performance-oriented software. This cycle has played out for SMP designs, with major database vendors now relying on operating systems for thread support and SMP scaling. But the same trade-offs continue to apply to new hardware features as they appear and require explicit strategic decisions on the part of the DBMS vendors.

Today, the quest for higher and higher database performance certainly shows no signs of stopping. With today’s highest-performance servers featuring hundreds of multigigahertz processors, hardware advances have more than overcome the higher overhead of the relational data model, giving it performance equal to, or better than, the best nonrelational databases of the past. At the same time, of course, the demand for higher and higher transaction rates against larger and larger databases continues to grow. At the top end of the database market, it appears that one can never have too much database performance.

Database Server Appliances

Another hardware-based market trend in the 1980s and early 1990s was the emergence of companies that combined high-performance microprocessors, fast disk drives, and multiprocessor architectures to build dedicated systems that were optimized as database servers. These vendors argued that they could deliver much better database performance with a specially designed database engine than with a general-purpose computer system. In some cases, their systems included application-specific integrated circuits (ASICs) that implement some of the DBMS logic in hardware for maximum speed. Dedicated database systems from companies such as Teradata and Sharebase (formerly Britton-Lee) found some acceptance in applications that involve complex queries against very large databases. However, they have not become an important part of the mainstream database market, and these vendors eventually disappeared or were acquired by larger, general-purpose computer companies.

Interestingly, the notion of a packaged, all-in-one database server appliance was briefly rekindled at the end of the 1990s by Oracle Corporation and its CEO, Larry Ellison. Ellison argued that the Internet era had seen the success of other all-in-one products, such as networking equipment and web cache servers. Oracle announced partnerships with several server hardware vendors to build Oracle-based database appliances. Over time, however, these efforts had little market impact, and Oracle’s enthusiasm for database appliances faded from media attention.

Several venture-backed startups have recently embraced the idea of database server appliances once again, this time in the form of database caching servers that reside in a network between the application and an enterprise database. These startups point to the widespread success of web page caching within the Internet architecture, and posit a similar opportunity for data caching. Unlike web pages, however, database contents tend to have an inherent transactional character, which makes the synchronization of cache contents with the main database both much more important (to insure that requests satisfied by the database cache come up with the right response) and much more difficult. Whether the notion of a database caching appliance will catch on or not remains an open question as of this writing. 

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.

>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: