Re: Linux filesystems and MySQL 3.23 vs 4.1

[Peter Zaitsev]

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.

Bas,

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.
> 
> <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
-- 
Peter Zaitsev, Senior Performance Engineer
Come to hear my talk at MySQL UC 2005  http://www.mysqluc.com/
MySQL AB, www.mysql.com