After my previous experiments and tests I think I have found a path to
upgrade to 4.1 without sacrificing (write) performance: by using the xfs
or reiserfs filesystems instead of ext3 which is RedHat's standard. And by
using parameter tuning in my.cnf.
Primary testcase was a restore of a 87 table database of 670Mb measured
in seconds;
898.8 second is our benchmark, if this can be improved it is worthwhile.
I have run the standard multitreaded benchmark 'sysbench' as well,
relevant results below.
As the monospaced sheet shows the best restore time for 4.1 was 770
seconds, compare this to the 1167 seconds of the default installation of
4.1.10 on ext3, and our current 3.23.51 installation (898.8).
This confirms my previous conclusion that XFS rules, even if it has the
worst place on the disk (at the end).
But proper care should be given to MySQL 4.1, as with other databases you
do not get best performance out of the box. The tuning of parameters in
/etc/my.cnf has dramatic impact on the (write) performance.
<tt>
MySQL 3.23.51 4.1.10 4.1.10
default tuned
Filesystem
ext3: /dev/sda2 14 778 6G
restore time in sec 898.782 1167.432 1130.1
create 2mln myisam 59.3
sysbench rw/s 2761.68 1740.5
myisam tran/s 153.43 96.7
create 1mln myisam 25.624 32.5
create 1mln innodb 154.8
create 1mln bdb 31.8
myisam tran/s 155 122.1
innodb tran/s 61.6
bdb tran/s 149.21 120.3
xfs: /dev/sda6 1544 1798 2G
restore time in sec 726.739 902.997 770.1
create 2mln myisam 37.1
sysbench rw/s 4059.58 3415.0
myisam tran/s 225.53 189.7
create 1mln myisam 16.9
create 1mln innodb 147.3
create 1mln bdb 17.1
myisam tran/s 192.8
innodb tran/s 63.0
bdb tran/s 193.1
reiserfs: /dev/sda5 1289 1543 2G
restore time in sec 759.095 876.01 847.5
create 2mln myisam 35.7
sysbench rw/s 3457.4
myisam tran/s 192.1
create 1mln myisam 17.7
create 1mln innodb 142.8
create 1mln bdb 18.2
myisam tran/s 181.4
innodb tran/s 63.5
bdb tran/s 192.3
</tt>
Bas Meijer
