From: Tom Lane <tgl@sss.pgh.pa.us>
To: yoav x <yoav112003@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: sql-bench
Date: Wed, 13 Sep 2006 11:32:48 -0400
Message-ID: <12097.1158161568@sss.pgh.pa.us> (raw)
In-Reply-To: <20060913122414.16995.qmail@web35811.mail.mud.yahoo.com>
References: <20060913122414.16995.qmail@web35811.mail.mud.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, yoav112003@yahoo.com
Subject: Re: sql-bench
In-Reply-To: <12097.1158161568@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox