Server Databases Clash

Finding solid performance data to help choose among competing technologies is as tough as creating the data in the first place. This is particularly true in the database space, where database vendors routinely use no-benchmarking clauses in their license agreements to block publication of benchmarks of which they do not approve.

Still, this is data that customers should have to make informed purchases, and, as weve found again and again at eWEEK Labs, benchmarking is an unmatched technique for flushing out unexpected technical strengths and failings that can make or break a project.

For four weeks last month and early this month, eWEEK and sister publication PC Magazine carried out a comprehensive benchmark of the latest available versions of five server databases. These tests showed us on a level playing field which database performed best when used with a Java-based application server. We also were able to evaluate different approaches to database server tuning that can help every one of these products perform better.

To our knowledge, this is the first time a computer publication has published database benchmark results tested on the same hardware since PC Magazine did so in October 1993.

We tested IBMs DB2 7.2 with FixPack 5, Microsoft Corp.s SQL Server 2000 Enterprise Edition with Service Pack 2, MySQL ABs MySQL 4.0.1 Max, Oracle Corp.s Oracle9i Enterprise Edition 9.0.1.1.1 and Sybase Inc.s ASE (Adaptive Server Enterprise) 12.5.0.1.

Overall, Oracle9i and MySQL had the best performance and scalability (see charts, images 1 and 2 in slideshow), with Oracle9i just very slightly ahead of MySQL for most of the run. ASE, DB2, Oracle9i and MySQL finished in a dead heat up to about 550 Web users. At this point, ASEs performance leveled off at 500 pages per second, about 100 pages per second less than Oracle9is and MySQLs leveling-off point of about 600 pages per second. DB2s performance dropped substantially, leveling off at 200 pages per second under high loads.

Due to its significant JDBC (Java Database Connectivity) driver problems, SQL Server was limited to about 200 pages per second for the entire test.

Key challenges

Drivers, memory tuning and database design issues were the three factors that had the most impact on performance in our tests. Manual tuning makes a huge difference with databases—in general, our final measured throughput was twice as fast as our initial out-of-the-box test runs.

The Oracle and MySQL drivers had the best combination of a complete JDBC feature set and stability. (MySQL staff chose to use the MySQL JDBC driver written by Mark Matthews because the company does not have its own JDBC driver.)

Finding the best-performing memory configuration for each database (in terms of how much memory to assign to the various subsystems used by each database) was a significant challenge, and we spent many days on this issue.

SQL Server and MySQL were the easiest to tune, and Oracle9i was the most difficult because it has so many separate memory caches that can be adjusted. This issue was even more nettlesome with Oracle9i because it required the most memory per concurrent connection to the database (about 400KB of RAM). By comparison, DB2 required 177KB of RAM per connection, and SQL Server, MySQL and ASE all required about 50KB of RAM per connection. As a result, Oracle9is data and query plan caches had to be smaller than those of the other databases because of memory taken by user connections.

MySQLs great performance was due mostly to our use of an in-memory query results cache that is new in MySQL 4.0.1. When we tested without this cache, MySQLs performance fell by two-thirds.

MySQL staff took advantage of a feature unique to MySQL among databases tested—the ability to use different database engines on a table-by-table basis.

All the bookstore order tables (which needed to support transactions as per our requirements specification) were configured to use MySQLs InnoDB database engine (which supports transactions, row-level locking and a multiversioning concurrency design also used by Oracle9i). The catalog and user tables did not require transaction support, so MySQL staff configured these tables to use MySQLs lighter-weight, nontransactional MyISAM engine.

MySQL 4.0.1s new, extremely fast query cache is also quite notable, as no other database we tested had this feature. If the text of an incoming query has a byte-for-byte match with a cached query, MySQL can retrieve the results directly from the cache without compiling the query, getting locks or doing index accesses. This query caching will be effective only for tables with few updates because any table updates that clear the cache to guarantee correct results are always returned.

Finally, adjusting the database design itself—by adding extra indexes and arranging table rows in the best physical order for our query set—provided measurable performance gains, although these were smaller than the effects of the driver and database memory tuning (see chart).

Making the numbers count

Direct comparability was a major goal of the benchmark. All databases were tested on the same hardware platform (Hewlett-Packard Co. provided HP NetServer LT 6000r servers with four 700MHz Xeon CPUs, 2GB of RAM and 24 10,000-rpm 9.1GB Ultra3 SCSI hard drives used for database storage) and the same operating system (Windows 2000 Advanced Server with Service Pack 2).

We used a Web-based bookstore application called Nile to generate database load and stress-tested Nile using Empirix Inc.s e-Test Suite 6.0 load testing tool, with loads from 50 to 1,000 concurrent Web users.

We selected BEA Systems Inc.s WebLogic 6.1 with Service Pack 1 as our application server platform (see related story) and wrote the Nile application in JavaServer Pages.

Each test ran for 1 hour and generated about 50,000 orders and 150,000 to 200,000 associated line items. We got the best application server scalability running six instances of WebLogic on two six-way HP NetServer LT 6000r servers, each with 4GB of RAM and Gigabit Ethernet network cards. HTTP traffic was load-balanced evenly across all six WebLogic instances.

As an extra data point, we also rewrote the benchmark in ASP .Net and, due to time constraints, tested just SQL Server on this platform. We stress that the results of this test are not comparable to the Java benchmark results because the ASP .Net test used a different Web server (Internet Information Services 5.0), different application engine (ASP .Net) and different database driver (OLE DB).

However, our results do provide evidence that this all-Microsoft software stack can produce excellent performance, peaking at just under 870 pages per second (see charts, images 3 and 4 in slideshow).

We invited each database vendor to have staff on-site when their products were tested at PC Magazines New York lab facility. MySQL and Sybase both accepted and had staffers tune their own databases as they wished. IBM didnt send personnel, but we exchanged several rounds of e-mail with IBM engineers to get tuning advice. Microsoft and Oracle both declined to be involved in the test—with their database servers, we did all tuning ourselves with no vendor input.

Drivers the untold story

To our surprise, database connectivity drivers proved to be the biggest source of problems.

Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems. DB2s JDBC driver doesnt support updatable result sets (a JDBC 2.0 feature), so we had to open all result sets using the CONCUR_READ_ONLY attribute (the only attribute the IBM driver would accept) and do updates using SQL update statements. With this change, we could run the application. IBMs driver then also made it through our 8-hour stability test.

With Sybases JConnect 5.5 driver, we discovered that when applications request result sets that have bidirectional cursors, JConnect stores the entire result set in client memory to speed subsequent cursor repositioning commands. (We were using bidirectional cursors to let users page forward and back through the list of books that matched their search criteria.)

This behavior worked fine at low loads, but when we got into loads of hundreds of users, we found it was consuming hundreds of megabytes of memory per minute on the application server.

As a result, performance topped out at less than 200 pages per second because each of our six application servers was spending so much time allocating and then freeing memory (doing garbage collection). In addition, this memory-thrashing configuration proved unstable and didnt run for 8 hours, hanging every application server.

To get around this, we rewrote our applications browse logic to use only forward-scrolling cursors (which are not cached in client memory by JConnect). Because the application requires that the number of books found be displayed before the list of titles themselves, we had to run the same query twice: once to get the number of books and a second time to retrieve the book data. This is an inefficient design for this frequently called page and cost ASE some performance vis-à-vis the other products.

However, this was definitely the lesser of two evils, as ASEs overall performance more than doubled with this change. With only forward-scrolling cursors, we could run the benchmark successfully all night. ASEs client-side record caching could well pay off for client/server applications, but for application server use, its a poor choice.

With the exception of the forward-scrolling cursor changes (which we thought would hurt performance elsewhere), we retested all the databases whenever we made an application code change to maintain comparability.

Out of all the drivers we used, Microsofts new JDBC driver had the most problems. Its still a beta driver in the form distributed on Microsofts Web site, but its not a new product per se, because its based on code licensed from DataDirect Technologies Inc., which has had the leading third-party SQL Server JDBC driver for some years now.

Providing and supporting its own JDBC driver is a very welcome move, and Microsoft officials informed us last month that they had 70,000 downloads of the driver so far, so there is considerable customer interest in it. However, the driver, in both Beta 1 and Beta 2 forms (we tested both), has serious performance and stability problems.

Using the driver, we were unable to get more than about 200-page-per-second throughput, and the problem was clearly the driver—the database was only at about 15 percent to 20 percent CPU utilization at this load. The driver also has memory leaks: We could see on WebLogics administration console that less memory was freed each time the Java virtual machine did a garbage collection. Because of these leaks, the Microsoft JDBC driver was unable to run for 8 hours straight.

West Coast Technical Director Timothy Dyck has been testing and reviewing SQL database servers for eWeek Labs for the past six years. During this time, he carried out several database benchmarks using the ANSI SQL Standard Scalable and Portable benchmark, the Nile benchmark and other benchmarks.

Resource Library:

Links to other stories in this package

Java Performance Tuning

Reader Comments: Server Databases Clash >>> Be the FIRST to comment on this article!

>>> More Database Articles          >>> More By Timothy Dyck