pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsql-bench
16+ messages / 9 participants
[nested] [flat]
* sql-bench
@ 2006-09-13 12:24 yoav x <yoav112003@yahoo.com>
2006-09-13 12:27 ` Re: sql-bench Dave Cramer <pg@fastcrypt.com>
2006-09-13 14:44 ` Re: sql-bench Mark Lewis <mark.lewis@mir3.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 16+ messages in thread
From: yoav x @ 2006-09-13 12:24 UTC (permalink / raw)
To: pgsql-performance
Hi
I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux.
Some of the insert tests seems to be ver slow
For example: select_join_in
Are there any tuning parameters that can be changed to speed these queries? Or are these queries
especially tuned to show MySQL's stgrenths?
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
@ 2006-09-13 12:27 ` Dave Cramer <pg@fastcrypt.com>
2006-09-13 12:50 ` Re: sql-bench yoav x <yoav112003@yahoo.com>
2 siblings, 1 reply; 16+ messages in thread
From: Dave Cramer @ 2006-09-13 12:27 UTC (permalink / raw)
To: yoav x <yoav112003@yahoo.com>; +Cc: pgsql-performance
All of the tuning parameters would affect all queries
shared buffers, wal buffers, effective cache, to name a few
--dc--
On 13-Sep-06, at 8:24 AM, yoav x wrote:
> Hi
>
> I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux.
> Some of the insert tests seems to be ver slow
>
> For example: select_join_in
>
> Are there any tuning parameters that can be changed to speed these
> queries? Or are these queries
> especially tuned to show MySQL's stgrenths?
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 12:27 ` Re: sql-bench Dave Cramer <pg@fastcrypt.com>
@ 2006-09-13 12:50 ` yoav x <yoav112003@yahoo.com>
2006-09-13 14:03 ` Re: sql-bench Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 16+ messages in thread
From: yoav x @ 2006-09-13 12:50 UTC (permalink / raw)
To: pgsql-performance
So why are these queries so slow in PG?
--- Dave Cramer <pg@fastcrypt.com> wrote:
> All of the tuning parameters would affect all queries
>
> shared buffers, wal buffers, effective cache, to name a few
>
> --dc--
> On 13-Sep-06, at 8:24 AM, yoav x wrote:
>
> > Hi
> >
> > I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux.
> > Some of the insert tests seems to be ver slow
> >
> > For example: select_join_in
> >
> > Are there any tuning parameters that can be changed to speed these
> > queries? Or are these queries
> > especially tuned to show MySQL's stgrenths?
> >
> >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
>
>
> ---------------------------(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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 12:27 ` Re: sql-bench Dave Cramer <pg@fastcrypt.com>
2006-09-13 12:50 ` Re: sql-bench yoav x <yoav112003@yahoo.com>
@ 2006-09-13 14:03 ` Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 16+ messages in thread
From: Dave Cramer @ 2006-09-13 14:03 UTC (permalink / raw)
To: yoav x <yoav112003@yahoo.com>; +Cc: pgsql-performance
First of all you are going to have to show use what these queries are
exactly, what the machine is you are running on (CPU, memory, and
disk) , and how you have tuned it.
slow is a relative term.. we need information to determine what
"slow" means.
Dave
On 13-Sep-06, at 8:50 AM, yoav x wrote:
> So why are these queries so slow in PG?
>
>
> --- Dave Cramer <pg@fastcrypt.com> wrote:
>
>> All of the tuning parameters would affect all queries
>>
>> shared buffers, wal buffers, effective cache, to name a few
>>
>> --dc--
>> On 13-Sep-06, at 8:24 AM, yoav x wrote:
>>
>>> Hi
>>>
>>> I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux.
>>> Some of the insert tests seems to be ver slow
>>>
>>> For example: select_join_in
>>>
>>> Are there any tuning parameters that can be changed to speed these
>>> queries? Or are these queries
>>> especially tuned to show MySQL's stgrenths?
>>>
>>>
>>>
>>>
>>> __________________________________________________
>>> Do You Yahoo!?
>>> Tired of spam? Yahoo! Mail has the best spam protection around
>>> http://mail.yahoo.com
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>> choose an index scan if your joining column's datatypes do
>>> not
>>> match
>>>
>>
>>
>> ---------------------------(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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
@ 2006-09-13 14:44 ` Mark Lewis <mark.lewis@mir3.com>
2 siblings, 0 replies; 16+ messages in thread
From: Mark Lewis @ 2006-09-13 14:44 UTC (permalink / raw)
To: yoav x <yoav112003@yahoo.com>; +Cc: pgsql-performance
The last I checked (years ago), sql-bench was very synthetic (i.e.
reflecting no realistic use case). It's the sort of test suite that's
useful for database developers when testing the effects of a particular
code change or optimization, but not so applicable to real-world uses.
Historically the test was also bad for PG because it did nasty things
like 10,000 inserts each in separate transactions because the test was
written for MySQL which at the time didn't support transactions. Not
sure if that's been fixed yet or not.
Can you provide details about the schema and the queries that are slow?
-- Mark
On Wed, 2006-09-13 at 05:24 -0700, yoav x wrote:
> Hi
>
> I am trying to run sql-bench against PostgreSQL 8.1.4 on Linux.
> Some of the insert tests seems to be ver slow
>
> For example: select_join_in
>
> Are there any tuning parameters that can be changed to speed these queries? Or are these queries
> especially tuned to show MySQL's stgrenths?
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
@ 2006-09-13 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 19:36 ` Re: sql-bench Merlin Moncure <mmoncure@gmail.com>
2006-09-14 06:55 ` Re: sql-bench yoav x <yoav112003@yahoo.com>
2 siblings, 2 replies; 16+ messages in thread
From: Tom Lane @ 2006-09-13 15:32 UTC (permalink / raw)
To: yoav x <yoav112003@yahoo.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-13 19:36 ` Merlin Moncure <mmoncure@gmail.com>
2006-09-13 20:51 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 21:10 ` Re: sql-bench Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 2 replies; 16+ messages in thread
From: Merlin Moncure @ 2006-09-13 19:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: yoav x <yoav112003@yahoo.com>; pgsql-performance
On 9/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
if postgresql uses prepared statements for such queries, it will
roughly tie mysql/myisam in raw query output on this type of load
which also happens to be very easy to prepare...afaik mysql gets zero
performance benefit from preparing statements This is extremely
trivial to test&confirm even on a shell script. [aside: will this
still be the case if peter e's planner changes become reality?]
another cheater trick benchmarkers do to disparage postgresql is to
not run analyze intentionally. Basically all production postgresql
systems of any size will run analyze on cron.
another small aside, I caught the sqlite people actually *detuning*
postgresql for performance by turning stats_command_string=on in
postgresql.conf. The way it was portrayed it almost looked like
cheating. I busted them on it (go to
http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison and look for the
remarks right below the results)
merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 19:36 ` Re: sql-bench Merlin Moncure <mmoncure@gmail.com>
@ 2006-09-13 20:51 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 16+ messages in thread
From: Tom Lane @ 2006-09-13 20:51 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: yoav x <yoav112003@yahoo.com>; pgsql-performance
"Merlin Moncure" <mmoncure@gmail.com> writes:
> another small aside, I caught the sqlite people actually *detuning*
> postgresql for performance by turning stats_command_string=on in
> postgresql.conf.
Hm, well, that's not unreasonable if a comparable facility is enabled
in the other databases they're testing ... but it'll hardly matter in
8.2 anyway ;-)
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 19:36 ` Re: sql-bench Merlin Moncure <mmoncure@gmail.com>
@ 2006-09-13 21:10 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-09-13 21:31 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 23:42 ` Re: sql-bench Merlin Moncure <mmoncure@gmail.com>
1 sibling, 2 replies; 16+ messages in thread
From: Scott Marlowe @ 2006-09-13 21:10 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; yoav x <yoav112003@yahoo.com>; pgsql-performance
On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote:
> On 9/13/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 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.
>
> if postgresql uses prepared statements for such queries, it will
> roughly tie mysql/myisam in raw query output on this type of load
> which also happens to be very easy to prepare...afaik mysql gets zero
> performance benefit from preparing statements This is extremely
> trivial to test&confirm even on a shell script. [aside: will this
> still be the case if peter e's planner changes become reality?]
>
> another cheater trick benchmarkers do to disparage postgresql is to
> not run analyze intentionally. Basically all production postgresql
> systems of any size will run analyze on cron.
>
> another small aside, I caught the sqlite people actually *detuning*
> postgresql for performance by turning stats_command_string=on in
> postgresql.conf. The way it was portrayed it almost looked like
> cheating. I busted them on it (go to
> http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison and look for the
> remarks right below the results)
They're running autovacuum, which requires that, doesn't it?
I'd rather them be running autovacuum than not vacuuming / analyzing at
all. And autovacuum is a pretty realistic setting for most databases (I
use it on my production machines.)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 19:36 ` Re: sql-bench Merlin Moncure <mmoncure@gmail.com>
2006-09-13 21:10 ` Re: sql-bench Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-09-13 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 16+ messages in thread
From: Tom Lane @ 2006-09-13 21:31 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; yoav x <yoav112003@yahoo.com>; pgsql-performance
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote:
>> another small aside, I caught the sqlite people actually *detuning*
>> postgresql for performance by turning stats_command_string=on in
>> postgresql.conf.
> They're running autovacuum, which requires that, doesn't it?
No, you're thinking of stats_row_level.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 19:36 ` Re: sql-bench Merlin Moncure <mmoncure@gmail.com>
2006-09-13 21:10 ` Re: sql-bench Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-09-13 23:42 ` Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Merlin Moncure @ 2006-09-13 23:42 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; yoav x <yoav112003@yahoo.com>; pgsql-performance
On 9/14/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote:
> > another small aside, I caught the sqlite people actually *detuning*
> > postgresql for performance by turning stats_command_string=on in
> > postgresql.conf. The way it was portrayed it almost looked like
> > cheating. I busted them on it (go to
> > http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison and look for the
> > remarks right below the results)
>
> They're running autovacuum, which requires that, doesn't it?
actually, you are right, it was row_level, not command_string (i got
it right on their wiki, just not in the email here)...rmy bad on that.
still, they did not disclose it.
merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-14 06:55 ` yoav x <yoav112003@yahoo.com>
2006-09-14 07:05 ` Re: sql-bench Markus Schaber <schabi@logix-tt.com>
2006-09-14 11:33 ` Re: sql-bench Dave Cramer <pg@fastcrypt.com>
1 sibling, 2 replies; 16+ messages in thread
From: yoav x @ 2006-09-14 06:55 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-14 06:55 ` Re: sql-bench yoav x <yoav112003@yahoo.com>
@ 2006-09-14 07:05 ` Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 16+ messages in thread
From: Markus Schaber @ 2006-09-14 07:05 UTC (permalink / raw)
To: yoav x <yoav112003@yahoo.com>; +Cc: pgsql-performance
Hi, Yoav X,
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.
As far as I've seen, they include the CREATE TABLE command in their
benchmarks.
Realistic in-production workloads don't have so much create table
commands, I think.
Wondering,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-14 06:55 ` Re: sql-bench yoav x <yoav112003@yahoo.com>
@ 2006-09-14 11:33 ` Dave Cramer <pg@fastcrypt.com>
2006-09-15 00:11 ` Re: sql-bench Grega Bremec <gregab@p0f.net>
1 sibling, 1 reply; 16+ messages in thread
From: Dave Cramer @ 2006-09-14 11:33 UTC (permalink / raw)
To: yoav x <yoav112003@yahoo.com>; +Cc: 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@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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-14 06:55 ` Re: sql-bench yoav x <yoav112003@yahoo.com>
2006-09-14 11:33 ` Re: sql-bench Dave Cramer <pg@fastcrypt.com>
@ 2006-09-15 00:11 ` Grega Bremec <gregab@p0f.net>
2006-09-15 00:32 ` Re: sql-bench Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 16+ messages in thread
From: Grega Bremec @ 2006-09-15 00:11 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
> >> 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.)
Just a point (I've taught some MySQL courses before, sorry 'bout that;
if you're not, I am, sort of :)) - the crash-proof version of
transactional tables in MySQL was supposed to be the Berkeley ones, but
(oh, the irony) they're still beta. InnoDB were just supposed to be
optimized to perform well with loads of data and a mediocre amount of
clients, and *finally* support referential integrity and the rest of the
lot.
Anyways... with Oracle buying off all that stuff, don't even know if it
still matters: the incantation is to either add the ENGINE= or TYPE=
clause after each CREATE TABLE statement, which would look like
CREATE TABLE foo (
...
) ENGINE=InnoDB;
or specify the --default-storage-engine or --default-table-type server
startup option (or, alternatively, set the default-storage-engine or
default-table-type option in my.cnf).
The trick being, mysqldump will be quite explicit in CREATE TABLE
statements, so a vi(1) and a regular expression will probably be needed.
Kind regards,
--
Grega Bremec
gregab at p0f dot net
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: sql-bench
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 15:32 ` Re: sql-bench Tom Lane <tgl@sss.pgh.pa.us>
2006-09-14 06:55 ` Re: sql-bench yoav x <yoav112003@yahoo.com>
2006-09-14 11:33 ` Re: sql-bench Dave Cramer <pg@fastcrypt.com>
2006-09-15 00:11 ` Re: sql-bench Grega Bremec <gregab@p0f.net>
@ 2006-09-15 00:32 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 0 replies; 16+ messages in thread
From: Steinar H. Gunderson @ 2006-09-15 00:32 UTC (permalink / raw)
To: pgsql-performance
On Fri, Sep 15, 2006 at 02:11:23AM +0200, Grega Bremec wrote:
> Just a point (I've taught some MySQL courses before, sorry 'bout that;
> if you're not, I am, sort of :)) - the crash-proof version of
> transactional tables in MySQL was supposed to be the Berkeley ones, but
> (oh, the irony) they're still beta.
They are being dropped in 5.1.12 (yes, across a minor revision). From
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html:
Incompatible change: Support for the BerkeleyDB (BDB) engine has been
dropped from this release. Any existing tables that are in BDB format will
not be readable from within MySQL from 5.1.12 or newer. You should convert
your tables to another storage engine before upgrading to 5.1.12.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2006-09-15 00:32 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-13 12:24 sql-bench yoav x <yoav112003@yahoo.com>
2006-09-13 12:27 ` Dave Cramer <pg@fastcrypt.com>
2006-09-13 12:50 ` yoav x <yoav112003@yahoo.com>
2006-09-13 14:03 ` Dave Cramer <pg@fastcrypt.com>
2006-09-13 14:44 ` Mark Lewis <mark.lewis@mir3.com>
2006-09-13 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 19:36 ` Merlin Moncure <mmoncure@gmail.com>
2006-09-13 20:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 21:10 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-09-13 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 23:42 ` Merlin Moncure <mmoncure@gmail.com>
2006-09-14 06:55 ` yoav x <yoav112003@yahoo.com>
2006-09-14 07:05 ` Markus Schaber <schabi@logix-tt.com>
2006-09-14 11:33 ` Dave Cramer <pg@fastcrypt.com>
2006-09-15 00:11 ` Grega Bremec <gregab@p0f.net>
2006-09-15 00:32 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
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