Why is MySQL nearly twice as fast on Linux?

[Chris Elsworth]

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 at 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 at 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 at 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