Filesystems comparison using sysbench and mySQL

Test bed:

Kernel: 2.6.11 with Alex Tomas 's patches
RAM: 2 GB
Partition size: 68 GB (sdc1)

processor      : 2
vendor_id      : GenuineIntel
cpu family     : 15
model          : 2
model name     : Intel(R) Xeon(TM) CPU 2.80GHz
cpu MHz        : 2791.359
cache size     : 512 KB
bogomips       : 5505.02

# hdparm -t /dev/sdc1

/dev/sdc1:
Timing buffered disk reads: 202 MB in 3.02 seconds = 66.90 MB/sec

Filesystems and options are:

	filesystems	mount options
1	ext2	defaults
2	ext3	data=ordered
3	ext3	data=writeback
4	ext3	data=ordered,extents
5	ext3	data=ordered,mballoc,delalloc,extents
6	ext3	data=ordered,mballoc,extents
7	ext3	data=writeback,extents
8	ext3	data=writeback,mballoc,delalloc,extents
9	ext3	data=writeback,mballoc,extents
10	jfs	defaults
11	reiserfs	defaults
12	xfs	defaults

Scripts

Script used to benchmark filesystems using sysbench is sysbench_run.

sysbench is run for each filesystem we want to test. The partition to use is formated with
mkfs and mounted with appropriate options before copying tarball to it.

The test sequence is:

mysql_install_db
service mysqld start
mysqladmin create sbtest
sysbench --test=oltp --oltp-table-size=200000 --mysql-user=root prepare
sysbench --num-threads=16 --max-requests=2000000 --test=oltp --mysql-user=root run
sysbench --test=oltp --mysql-user=root cleanup
service mysqld stop

Of course, /dev/sdc1 is the device where we made a mkfs with the good filesystem previously.

Graphical results compare filesystems on the elapsed real time value (graphics are generated with sysbench_compare,sysbench_functions).

Tests

sysbench executes following operations (see http://sysbench.sourceforge.net):

Depending on the command line options, each transaction may contain the following statements:

Point queries:
SELECT c FROM sbtest WHERE id=N

Range queries:

SELECT c FROM sbtest WHERE id BETWEEN N AND M

Range SUM() queries:

SELECT SUM(c) FROM sbtest WHERE id BETWEEN N and M

Range ORDER BY queries:

SELECT c FROM sbtest WHERE id between N and M ORDER BY c

Range DISTINCT queries:

SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c

UPDATEs on index column:
UPDATE sbtest SET k=k+1 WHERE id=N
UPDATEs on non-index column:
UPDATE sbtest SET c=N WHERE id=M
DELETE queries:
DELETE FROM sbtest WHERE id=N
INSERT queries:
INSERT INTO sbtest VALUES (...)

Results

The result of sysbench is stored in a file in the following format:

sysbench v0.3.3:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 16

Doing OLTP test.
Using mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Maximum number of requests for OLTP test is limited to 2000000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            28003808
        write:                           8000763
        other:                           4000295
        total:                           40004866
    transactions:                        2000023 (341.16 per sec.)
    deadlocks:                           249    (0.04 per sec.)
    read/write requests:                 36004571 (6141.51 per sec.)
    other operations:                    4000295 (682.35 per sec.)

Test execution summary:
    total time:                          5862.4972s
    total number of events:              2000023
    total time taken by event execution: 93741.9585
    per-request statistics:
         min:                            0.0052s
         avg:                            0.0469s
         max:                            0.5604s
         approx.  95 percentile:         0.1432s

Threads fairness:
    distribution:                        99.31/99.71
    execution:                           99.31/99.71

The fields in red are the fields we study in following results, here.