From owner-freebsd-threads@FreeBSD.ORG Mon May 24 07:37:09 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 861A316A4CE for ; Mon, 24 May 2004 07:37:09 -0700 (PDT) Received: from clueful.shagged.org (clueful.shagged.org [212.13.201.101]) by mx1.FreeBSD.org (Postfix) with ESMTP id 467B443D1F for ; Mon, 24 May 2004 07:37:09 -0700 (PDT) (envelope-from chris@clueful.shagged.org) Received: from chris by clueful.shagged.org with local (Exim 4.30; FreeBSD) id 1BSGYp-0003TA-FR; Mon, 24 May 2004 15:36:15 +0100 Date: Mon, 24 May 2004 15:36:15 +0100 From: Chris Elsworth To: Daniel Eischen Message-ID: <20040524143615.GA12869@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 14:37:09 -0000 On Mon, May 24, 2004 at 10:13:36AM -0400, Daniel Eischen wrote: > On Mon, 24 May 2004, Petri Helenius wrote: > > > 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've just done so. It doesn't make much difference. It should be noted, by the way, that the default super-smack tests use the query cache, which makes the numbers rather useless for testing different table types; all you're testing is how fast MySQL can fetch data from a cache. It won't be hitting the table at *all* after the first few queries, and thus tabletype will make *negligible* difference for the select-key test. I mean really, in the order of 0.0001 in the figures I quote below. One should also note that super-smack does not drop its test table after its done, so you need to drop it manually, or change the type, so that your tests are testing what you want. So, each of the smackfiles used below was changed to have sql_no_cache in the select. The _i smackfile simply has type=innodb in the create table statement. Here's the numbers: MyISAM: root@cluestick:~/super-smack-1.2# src/super-smack smacks/select-key.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 22063.58 InnoDB: 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 22464.32 MyISAM: root@cluestick:~/super-smack-1.2# src/super-smack smacks/update-select.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 8296.32 update_index 400000 0 0 8296.32 InnoDB: 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 8141.49 update_index 400000 0 0 8141.49 For what it's worth, the select-key test numbers raise to approximately 36,000 with the query cache enabled. -- Chris