Dual CPU Database Server Comparison

The benchmark

Our client application that fires off SQL requests is called DBconn, written in VB.NET. As I am no .NET expert, DBconn was written by one of my best students, Jo Neve.

DBconn is similar to AnandTech's SQL Loader, with a few differences. DBconn increases the workload progressively by augmenting the concurrency level. In other words, the client simulates that more and more users request data from the database system.

You can see the simplified interface of DBconn above. DBconn can also connect to different databases, and at the moment, DB2, MySQL and MS SQL server are supported. The precision of the tests can be increased in two ways. We can specify the number of repetitions of one test, and the total number of requests. A request can be described as a "package" of SQL statements sent to the Database. Every request is done by one thread.

The databases are not accessed via ODBC, but by the following drivers:

• Microsoft SQL server driver (for MS SQL testing, to be discussed later)
• IBM beta DB2 driver, .NET data provider (IBM.Data.DB2.dll 15/06/2004)
• MYSQL beta4 byteFX driver (ByteFX.MySqlClient.dll 15/06/2004)

When you start so many threads, it is easy to get wrong results. Here is a small excerpt of the original code:

First, we used this code for making threads. The "join" method in this VB.Net code should normally wait until the last thread is finished. However, we found out by experiment that sometimes previous threads are still running. To avoid this, a special method in DBconn checks if all threads are indeed done before firing off a new load of threads.

The application also performs randomly in two ways: variables in SQL statements are randomized to avoid getting the same results over and over again. Queries are also called in a randomized order.

DBconn proved to be pretty accurate and results were repeatable. After weeks of tuning the application, we measured an error margin of +/- 2% to 3% between runs in DB2 8.1.3 and MS SQL server 2000.

The database was about 1 GB and has been extracted out of a database of about 7 GB of data. We cannot go into much detail, but the database is the backend of an e-commerce site, which provides a vast number of products from many suppliers. Extensive descriptions of products exist in the form of articles and newsflashes about the latest developments, so the database is smaller, but similar to Anandtech with its news and articles.

The following is a typical and very common SQL statement in DBconn:

SELECT table.x ..... table.z,  function(table.y)
			FROM table3
				LEFT JOIN othertable ON matching keys
				LEFT JOIN yetanothertable ON matching keys

			WHERE othertable.y IN condition AND condition1 AND condition2
			GROUP BY table.xyz
			ORDER BY othertable.zyx;

There are queries to request the newspage, to request an overview of certain category of products ordered in different ways, and so on. The mix of queries is based on the real world use of the database.