Date: Mon, 24 May 2004 16:03:21 +0100 From: Chris Elsworth <chris@shagged.org> To: Daniel Eischen <eischen@vigrid.com> Cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? Message-ID: <20040524150321.GA13862@shagged.org> In-Reply-To: <Pine.GSO.4.10.10405241010540.1932-100000@pcnet5.pcnet.com> References: <40B19F2E.8010804@he.iki.fi> <Pine.GSO.4.10.10405241010540.1932-100000@pcnet5.pcnet.com>
next in thread | previous in thread | raw e-mail | index | archive | help
On Mon, May 24, 2004 at 10:13:36AM -0400, Daniel Eischen wrote: > On Mon, 24 May 2004, Petri Helenius wrote: > > > Thomas Hurst wrote: > > > > > > > >Only if you're only using one table. While this is true in this > > >benchmark, it isn't really relevent because we're only testing selects, > > >which are pure reads. There should be no table locking getting in the > > >way; Linux's performance would seem to confirm this. > > > > > > > I changed the test to use InnoDB table type and saw 50% performance > > improvement. > > Are you able to run the test using InnoDB under Linux also > (just to see if Linux also gets a ~50% improvement)? I have to aplogise unreservedly. Despite thoroughly testing the different table types for the last half an hour, I've only just realised that I had skip-innodb in my.cnf and thus the InnoDB tables were actually being created as MyISAM tables. Please consider all the InnoDB results just posted as void and replaced with the following: root@cluestick:~/super-smack-1.2# src/super-smack smacks/select-key_i.smack 4 100000 Query Barrel Report for client smacker1 connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 800000 0 0 22509.05 This one got a bit more interesting. Since InnoDB is transactional, it maintains a log of updates. Every update incurs a log write. With innodb_flush_log_at_trx_commit =1 .. root@cluestick:~/super-smack-1.2# src/super-smack smacks/update-select_i.smack 4 100000 Query Barrel Report for client smacker connect: max=1ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 400000 0 0 2517.41 update_index 400000 1 0 2517.41 However, with innodb_flush_log_at_trx_commit = 0, meaning InnoDB does *not* have to flush the log after every update: root@cluestick:~/super-smack-1.2# src/super-smack smacks/update-select_i.smack 4 100000 Query Barrel Report for client smacker connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 400000 0 0 8473.54 update_index 400000 0 0 8473.54 Obviously, the first update test is thoroughly disk bound; 2500 transactions per second to disk, at a rate of 13MB/s. Select performance is unchanged as to the setting of innodb_flush_log_at_trx_commit, as you might expect. -- Chris
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20040524150321.GA13862>