Skip site navigation (1) Skip section navigation (2)

Re: sql-bench

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: yoav x <yoav112003(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: sql-bench
Date: 2006-09-13 15:32:48
Message-ID: 12097.1158161568@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
yoav x <yoav112003(at)yahoo(dot)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

In response to

  • sql-bench at 2006-09-13 12:24:14 from yoav x

Responses

pgsql-performance by date

Next:From: Jeff DavisDate: 2006-09-13 16:07:34
Subject: Re: Performance With Joins on Large Tables
Previous:From: Joshua MarshDate: 2006-09-13 14:49:24
Subject: Re: Performance With Joins on Large Tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group