Linux filesystems and MySQL 3.23 vs 4.1

[Bas Meijer]

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