On Mon, 2005-03-07 at 00:23, Bas Meijer wrote:
> 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.
Thanks for your data.
I'm very surprised to see such large difference in the 3.23 vs 4.1
benchmarks for transaction rates, as well as for such large difference
between file systems. Could you tell which hardware did you use as
well what parameters did you use for sysbench - so we could repeat it.
If we could get sources of other tests you've been running this also
would be great.
> 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.
> MySQL 3.23.51 4.1.10 4.1.10
> default tuned
> 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
> Bas Meijer
Peter Zaitsev, Senior Performance Engineer
Come to hear my talk at MySQL UC 2005 http://www.mysqluc.com/
MySQL AB, www.mysql.com