Data Modeling: Benchmark Factory Tests File Systems: Part Two

Let's Rumble

OK, we've agreed on lots of disks, RAID and using a LVM - plus we saw that the standard Linux benchmarks are not entirely reliable - but which Linux file system works best for relational databases? Simple enough question, so onto a simple and clear answer.

I wanted to run two well-known and widely accepted database benchmarks:

• AS3AP - a scalable, portable ANSI SQL relational database benchmark. This benchmark provides a comprehensive set of tests for database processing power; has a built-in scalability and portability that tests a broad range of systems; minimizes human effort in implementing and running benchmark tests; and provides a uniform metric straight-forward interpretation of benchmark results.

• TPC-C - an online transaction processing (OLTP) benchmark. This benchmark involves a mix of five concurrent transactions of different types, and completely executes either online or queries for deferred execution. The database is comprised of nine types of tables having a wide range of record and population sizes. This benchmark is measured in transactions per minute.

But of course I don't know the DDL, DML or anything about these tests - other than their names and significance as benchmarks. I also don't want to pay lots of money to join some industry collation in order to have access to these benchmarks' SQL code.

So I used Quest Software's Benchmark Factory (shown below), as it permits me to easily select and run such tests in a matter of minutes. You just pick a database, pick an industry standard test, provide a few options regarding desired database size and concurrent user load, and that's it - really. Benchmark Factory even permits me to run concurrent user loads using many PC's on my network in the case that I do not like having a single PC simulate the load. You're wasting real time and money if you're doing benchmarks by writing code. Benchmark Factory let's me focus on the tuning instead of the benchmark.

And the Winner Is …

First let's look at the AS3AP test results. For database creation, loading and indexing, Figure 1 shows that the EXT3 file system wins hands down - and that RAW devices come in next to last place! How many saw that coming?

Moreover, the extremely popular Reiser file system completely choked on the index builds - taking over 2.6 times the average index creation time. Remember the Iozone benchmark above where Reiser was a winner? So see, file system benchmarks don't apply very well to databases.

For the transactional potion of the benchmark, the results in Table 1 were also surprising. All the file systems had roughly the same performance - except for RAW devices, which ran nearly twice as slow on the average time per transaction!

Based upon the AS3AP benchmark results, one should choose the EXT3 file system (a new and much improved version of EXT2 file system, that also offers journaling).

I personally had a very hard time with RAW devices doing so poorly. So for the TPC-C benchmarks, I added another benchmarking test scenario - giving the RAW device based database twice the memory allocation as its cooked file system counterparts. My thoughts were that maybe the Linux file system buffer cache was skewing my test results.

Well, lightning struck twice in the same spot - because the TPC-C results were nearly the same, relatively speaking, as the AS3AP results (see Figure 2 and Table 2, below)

Figure 2

Once again, the EXT3 file system is the clear winner for database creation, loading and indexing. Once again the Reiser file system came in last - choking on the index builds. And as with the AS3AP benchmark results, all the cooked file systems had roughly the same performance for the transactional portions of the TPC-C benchmark.

Even the RAW device scenario with twice the memory allocation could not compete. In fact, the improvement was so slight as to essentially scare me away from RAW devices on any Linux database project for the foreseeable future.

Based upon the TPC-C benchmark results, one should choose the EXT3 file system.

In closing, it's very reassuring when multiple and different natured, industry standard benchmarks yield similar results. In case you're wondering, I obtained similar results with Benchmark Factory's other half dozen or so database benchmarks. EXT3 it is.