Thread: sql-bench
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
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 >
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
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 >
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
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
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
"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
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.)
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
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
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
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
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 >
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
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/