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

Re: sql-bench

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: yoav x <yoav112003(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: sql-bench
Date: 2006-09-14 11:33:34
Message-ID: 82C2E57E-666A-4021-A070-07901F72E5E3@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-performance
Have you tuned postgresql ?

You still haven't told us what the machine is, or the tuning  
parameters. If you follow Merlin's links you will find his properly  
tuned postgres out performs mysql in every case.

--dc--
On 14-Sep-06, at 2:55 AM, yoav x wrote:

> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> 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
>>
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>        subscribe-nomail command to majordomo(at)postgresql(dot)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
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


In response to

Responses

pgsql-performance by date

Next:From: Jérôme BENOISDate: 2006-09-14 13:08:59
Subject: High CPU Load
Previous:From: Markus SchaberDate: 2006-09-14 07:05:34
Subject: Re: sql-bench

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