DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=CPmsWlSMula707oy84mTLUzejAJ1mL8wJeGBb2URsCneJLWHmRBScsHBzrl2k6ZtrK28JKH9Cva9EmLB+UD4GJaFCMk926e6EwzeU33PDU+DPRKn0aln4FesPe1T0hH7jsRvUPcuv+LIrNlkn4mEyVHKPBJmhkoFQN28oVs/id8=
	;
Message-ID: <20060914065522.23041.qmail@web35806.mail.mud.yahoo.com>
Date: Wed, 13 Sep 2006 23:55:22 -0700 (PDT)
From: yoav x <yoav112003@yahoo.com>
Subject: Re: sql-bench 
To: pgsql-performance@postgresql.org
In-Reply-To: <12097.1158161568@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

You can use the test with InnoDB by giving the --create-options=engine=innodb option in the
command line. Even with InnoDB, in some specific tests PG looks very bad compared to InnoDB.

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> yoav x <yoav112003@yahoo.com> writes:
> > Are there any tuning parameters that can be changed to speed these
> > queries? Or are these queries especially tuned to show MySQL's
> > stgrenths?
> 
> The latter.  I've ranted about this before --- there are both obvious
> and subtle biases in that benchmark.  The last time I spent any time
> with it, I ended up testing with these nondefault settings:
> 
> shared_buffers = 10000          
> work_mem = 100000               
> maintenance_work_mem = 100000   
> fsync = false                   
> checkpoint_segments = 30        
> max_locks_per_transaction = 128
> 
> (fsync = false is pretty bogus for production purposes, but if you're
> comparing to mysql using myisam tables, I think it's a reasonably fair
> basis for comparison, as myisam is certainly not crash-safe.  It'd be
> interesting to see what mysql's performance looks like on this test
> using innodb tables, which should be compared against fsync = true
> ... but I don't know how to change it to get all the tables to be
> innodb.)
> 
> Also, on some of the tests it makes a material difference whether you
> are using C locale or some other one --- C is faster.  And make sure you
> have a recent version of DBD::Pg --- a year or two back I recall seeing
> the perl test program eating more CPU than the backend in some of these
> tests, because of inefficiencies in DBD::Pg.
> 
> IIRC, with these settings PG 8.0 seemed to be about half the speed of
> mysql 5.0 w/myisam, which is probably somewhere in the ballpark of the
> truth for tests of this nature, ie, single query stream of fairly simple
> queries.  If you try concurrent-update scenarios or something that
> stresses planning ability you may arrive at different results though.
> I have not retested with more recent versions.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com