Filesystems comparison using sql-bench 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

Prerequisite

Needed packages on Fedora Core 3 are:

1	libdbi-dbd-mysql-0.6.5-9
2	mysql-3.23.58-14
3	perl-DBD-MySQL-2.9003-5
4	mysql-server-3.23.58-16.FC3.1
5	mysql-bench-3.23.58-16.FC3.1

Scripts

Script used to benchmark filesystems using sql-bench is sqlbench_run.

sql-bench 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:

mount /dev/sdc1 /var/lib/mysql
chmod 0755 /var/lib/mysql
chown mysql:mysql /var/lib/mysql
mysql_install_db

service mysqld start

cd /usr/share/sql-bench ; perl run-all-tests

service mysqld stop

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

This sequence has been made 10 times for each filesystem and an average value has been computed (with sqlbench_average).
Graphical results compare filesystems on the elapsed real time value (graphics are generated with sqlbench_compare,sql_bench_functions).

Tests

run-all-tests executes following scripts (found in /usr/share/sql-bench):

SQL-bench script	Aim
test-alter-table	Test of alter table
test-big-tables	Test of extreme tables
test-create	This test is for testing how long it takes to create tables, make a count(*) from them and finally drop the tables.
test-select	Test of selecting on keys that consist of many parts
test-ATIS	Creation of 29 tables and a lot of selects on them.
test-connect	This test is for testing the speed of connections and sending data to the client.
test-insert	Test of creating a simple table and inserting $record_count records in it, $opt_loop_count rows in order, $opt_loop_count rows in reverse order and $opt_loop_count rows in random order. (opt_loop_count is 100000)
test-wisconsin	This is a port of the PostgreSQL version of this benchmark (create and insert table from Wisconsin directory)

Filesystems comparison using sql-bench and mySQL

Test bed:

Prerequisite

Scripts

Tests

Results

InnoDB table type

MyISAM table type