Online exclusive (July 2003 version 1.1 update): Dig deeper into the
eWEEK Labs/PC Labs database benchmark by downloading our version 1.1 database configuration and tuning scripts,
JSP code, ASP.Net code and spreadsheets containing expanded benchmark
results (582 KB .zip file). This file was updated in July 2003 to add a
more detailed readme.txt, include PC Magazine's ASP.Net code, add some
optimizations to the code and include the originally missing MySQL
configuration file. We are also making available the raw import data (981 MB .zip file) we used to
construct our databases.
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 we've 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 IBM's DB2 7.2 with FixPack 5, Microsoft Corp.'s SQL
Server 2000 Enterprise Edition with Service Pack 2, MySQL AB's MySQL
4.0.1 Max, Oracle Corp.'s Oracle9i Enterprise Edition 184.108.40.206.1 and
Sybase Inc.'s ASE (Adaptive Server Enterprise) 220.127.116.11.
Overall, Oracle9i and MySQL had the best performance and scalability (see charts, images 1 and 2 ),
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, ASE's performance leveled off at 500 pages
per second, about 100 pages per second less than Oracle9i's and MySQL's
leveling-off point of about 600 pages per second. DB2's performance
dropped substantially, leveling off at 200 pages per second under high
Due to its significant JDBC (Java Database Connectivity)
driver problems, SQL Server was limited to about 200 pages per second
for the entire test.
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
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, Oracle9i's data and query plan
caches had to be smaller than those of the other databases because of
memory taken by user connections.
MySQL's 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, MySQL's 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
All the bookstore order tables (which needed to support
transactions as per our requirements specification) were configured to
use MySQL's 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 MySQL's
lighter-weight, nontransactional MyISAM engine.
MySQL 4.0.1's 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
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
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).
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).
We invited each database vendor to have staff on-site when their
products were tested at PC Magazine's New York lab facility. MySQL and
Sybase both accepted and had staffers tune their own databases as they
wished. IBM didn't 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. DB2's JDBC driver doesn't 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. IBM's driver then also made it through
our 8-hour stability test.
With Sybase's 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
didn't run for 8 hours, hanging every application server.
To get around this, we rewrote our application's 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 ASE's
overall performance more than doubled with this change. With only
forward-scrolling cursors, we could run the benchmark successfully all
night. ASE's client-side record caching could well pay off for
client/server applications, but for application server use, it's a poor
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
Out of all the drivers we used, Microsoft's new JDBC driver
had the most problems. It's still a beta driver in the form distributed
on Microsoft's Web site, but it's not a new product per se, because
it's based on code licensed from DataDirect Technologies Inc., which
has had the leading third-party SQL Server JDBC driver for some years
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 WebLogic's 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
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.
Links to other stories in this package