Thread: sql-bench

sql-bench

From
yoav x
Date:
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

Re: sql-bench

From
Dave Cramer
Date:
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
>


Re: sql-bench

From
yoav x
Date:
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

Re: sql-bench

From
Dave Cramer
Date:
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
>


Re: sql-bench

From
Mark Lewis
Date:
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

Re: sql-bench

From
Tom Lane
Date:
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

Re: sql-bench

From
"Merlin Moncure"
Date:
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

Re: sql-bench

From
Tom Lane
Date:
"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

Re: sql-bench

From
Scott Marlowe
Date:
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.)

Re: sql-bench

From
Tom Lane
Date:
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

Re: sql-bench

From
"Merlin Moncure"
Date:
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

Re: sql-bench

From
yoav x
Date:
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

Re: sql-bench

From
Markus Schaber
Date:
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

Re: sql-bench

From
Dave Cramer
Date:
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
>


Re: sql-bench

From
Grega Bremec
Date:
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

Attachment

Re: sql-bench

From
"Steinar H. Gunderson"
Date:
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/