Re: [PERFORM] sql-bench
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 [EMAIL PROTECTED] wrote: yoav x [EMAIL PROTECTED] 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 = 1 work_mem = 10 maintenance_work_mem = 10 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 [EMAIL PROTECTED] 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: [PERFORM] sql-bench
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 TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] sql-bench
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 [EMAIL PROTECTED] wrote: yoav x [EMAIL PROTECTED] 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 = 1 work_mem = 10 maintenance_work_mem = 10 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 [EMAIL PROTECTED] 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 ---(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: [PERFORM] sql-bench
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 signature.asc Description: This is a digitally signed message part
Re: [PERFORM] sql-bench
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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] 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 ---(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: [PERFORM] sql-bench
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] sql-bench
So why are these queries so slow in PG? --- Dave Cramer [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: [PERFORM] sql-bench
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] sql-bench
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] sql-bench
yoav x [EMAIL PROTECTED] 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 = 1 work_mem = 10 maintenance_work_mem = 10 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] sql-bench
On 9/13/06, Tom Lane [EMAIL PROTECTED] 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 testconfirm 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] sql-bench
Merlin Moncure [EMAIL PROTECTED] 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 ---(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: [PERFORM] sql-bench
On Wed, 2006-09-13 at 14:36, Merlin Moncure wrote: On 9/13/06, Tom Lane [EMAIL PROTECTED] 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 testconfirm 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.) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] sql-bench
Scott Marlowe [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] sql-bench
On 9/14/06, Scott Marlowe [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
