From owner-freebsd-threads@FreeBSD.ORG Mon May 24 08:03:52 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 2E31516A4CE for ; Mon, 24 May 2004 08:03:52 -0700 (PDT) Received: from clueful.shagged.org (clueful.shagged.org [212.13.201.101]) by mx1.FreeBSD.org (Postfix) with ESMTP id EF44F43D1D for ; Mon, 24 May 2004 08:03:51 -0700 (PDT) (envelope-from chris@clueful.shagged.org) Received: from chris by clueful.shagged.org with local (Exim 4.30; FreeBSD) id 1BSGz3-0003lF-Qq; Mon, 24 May 2004 16:03:21 +0100 Date: Mon, 24 May 2004 16:03:21 +0100 From: Chris Elsworth To: Daniel Eischen Message-ID: <20040524150321.GA13862@shagged.org> References: <40B19F2E.8010804@he.iki.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.6i Sender: Chris Elsworth X-SA-Exim-Mail-From: chris@clueful.shagged.org X-SA-Exim-Scanned: No; SAEximRunCond expanded to false cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 15:03:52 -0000 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