Dear Speedfreaks,
Today I re-ran the filesystem tests I did before (on Fedora3).
To get accurate results I went for "ceteris paribus":
same hardware, same disk sectors, same binaries, same config,
same order of steps. Everything the same except filesystem for database.
The testscript below was executed with the four filesystems available on
Suse 9 Enterprise server which was installed for this test. Each time a
newly
created filesystem was populated with a clean mysql database after that
a sysbench test was run (first result were noted to eliminate cache
effects).
CONCLUSION: XFS writes the fastest and is also the overall winner
(IMHO).
RESULTS unit ext3 reiserfs jfs xfs average
restore time secs 1241.2 868.1 927.6 785.8 955.7
create 2mln myisam secs 40.2 35.4 36.4 34.9 36.7
create 1mln myisam secs 19.2 17.2 17.2 17.0 17.6
create 1mln innodb secs 93.9 86.9 95.3 95.2 92.8
create 1mln bdb secs 20.0 18.0 17.7 17.7 18.3
overall write score 79.3% 109.3% 102.5% 117.9%
myisam tran/s n/s 145.9 206.1 207.4 203.5 190.7
innodb tran/s n/s 81.5 85.4 154.9 76.3 99.5
bdb tran/s n/s 142.9 203.5 204.7 199.5 187.6
sysbench rw/s n/s 2029.1 3672.6 3720.0 3612.9 3258.6
myisam tran/s n/s 112.7 204.0 206.7 200.7 181.0
overall trans. score 64.1% 111.6% 114.7% 109.6%
overall performance 71.7% 110.5% 108.6% 113.8%
NOTE
unit secs is time in seconds, less is better
unit n/s is number per second, more is better
percentages are compared to the average timing (100%).
TESTSCRIPT
mkfs -t xxx /dev/sda9
mount /dev/sda9 /test
cd /test
tar xf data.tar
mysql start
--mysql-table-type=myisam --oltp-table-size=2000000 prepare
--mysql-table-type=myisam --oltp-table-size=2000000 run
--mysql-table-type=myisam --oltp-table-size=2000000 cleanup
mysql restart
--mysql-table-type=myisam --oltp-table-size=1000000 prepare
--mysql-table-type=myisam --oltp-table-size=1000000 run
--mysql-table-type=myisam --oltp-table-size=1000000 cleanup
mysql restart
--mysql-table-type=bdb --oltp-table-size=1000000 prepare
--mysql-table-type=bdb --oltp-table-size=1000000 run
--mysql-table-type=bdb --oltp-table-size=1000000 cleanup
mysql restart
--mysql-table-type=innodb --oltp-table-size=1000000 prepare
--mysql-table-type=innodb --oltp-table-size=1000000 run
--mysql-table-type=innodb --oltp-table-size=1000000 cleanup
mysql benchmarks < mysql.dump
DETAILS
linux 2.6.5-7.97-smp on Suse Enterprise 9 evaluation
sysbench 0.3.1 + 670Mb sql-dump on ext3 partition
database on 15k mirrored scsi disk partition
start 16488.620 end 18543.779 logical
standard arguments for sysbench:
time sysbench --mysql-user=root --mysql-db=benchmarks --num-threads=50
--test=oltp
Bas Meijer
