Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Fri, 2007-11-16 at 11:06 -0500, Jonah H. Harris wrote: On Nov 16, 2007 10:56 AM, Dave Dutcher [EMAIL PROTECTED] wrote: I don't know about that. There are times when it is the right plan: Agreed. IMHO, there's nothing wrong with nested-loop join as long as it's being used properly. I do agree also, but in some other cases, the usage of nested loops (esp when the number of rows estimated to be returned vs the actual number of rows being returned differs by up to 100x (or more) then it becomes a major issue. The example pointed out by Dave D shows the est rows = 1 and actual rows=1, then good performance of course. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Fri, 2007-11-09 at 16:41 +0100, Sebastian Hennebrueder wrote: If the queries are complex, this is understable. I had a performance review of a Hibernate project (Java Object Relation Mapping) using MySQL. ORM produces easily complex queries with joins and subqueries. MySQL uses nested loops for subqueries which lead to performance issues with growing database size. Even for Postgresql, nested loops are still evil and hampers performance. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
-Original Message- From: Ow Mun Heng Subject: Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD Even for Postgresql, nested loops are still evil and hampers performance. I don't know about that. There are times when it is the right plan: explain analyze select * from table1 t1 inner join table2 t2 on t1.f_id = t2.id where t1.id = 'xyzzy'; QUERY PLAN Nested Loop (cost=0.00..17.65 rows=1 width=344) (actual time=0.080..0.096 rows=1 loops=1) - Index Scan using table1_pkey on table1 t (cost=0.00..9.18 rows=1 width=238) (actual time=0.044..0.048 rows=1 loops=1) Index Cond: ((id)::text = 'xyzzy'::text) - Index Scan using table2_pkey on table2 i (cost=0.00..8.46 rows=1 width=106) (actual time=0.019..0.023 rows=1 loops=1) Index Cond: (t.f_id = i.id) Total runtime: 0.224 ms set enable_nestloop=off; SET explain analyze select * from table1 t1 inner join table2 t2 on t1.f_id = t2.id where t1.id = 'xyzzy'; QUERY PLAN Hash Join (cost=9.18..72250.79 rows=1 width=344) (actual time=13493.572..15583.049 rows=1 loops=1) Hash Cond: (i.id = t.f_id) - Seq Scan on table2 i (cost=0.00..61297.40 rows=2188840 width=106) (actual time=0.015..8278.347 rows=2188840 loops=1) - Hash (cost=9.18..9.18 rows=1 width=238) (actual time=0.056..0.056 rows=1 loops=1) - Index Scan using table1_pkey on table1 t (cost=0.00..9.18 rows=1 width=238) (actual time=0.040..0.045 rows=1 loops=1) Index Cond: ((id)::text = 'xyzzy'::text) Total runtime: 15583.212 ms (I changed the table names, but everything else is real.) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Fri, 16 Nov 2007 11:06:11 -0500 Jonah H. Harris [EMAIL PROTECTED] wrote: On Nov 16, 2007 10:56 AM, Dave Dutcher [EMAIL PROTECTED] wrote: I don't know about that. There are times when it is the right plan: Agreed. IMHO, there's nothing wrong with nested-loop join as long as it's being used properly. Can you explain further please? (I'm not disagreeing with you, just want to know when nested loops are not used properly - does the planner make mistakes that you have to watch out for?) Thx, Josh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Nov 16, 2007 10:56 AM, Dave Dutcher [EMAIL PROTECTED] wrote: I don't know about that. There are times when it is the right plan: Agreed. IMHO, there's nothing wrong with nested-loop join as long as it's being used properly. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(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] PostgreSQL vs MySQL, and FreeBSD
Dimitri wrote: Reading this article I'm just happy for them to see progress done on FreeBSD :-) As well to demonstrate OS parallelism it's not so impressive to see 4CPU server results rather 8CPU or 32threaded Niagara... Don't know why they did not present similar performance graphs for these platform, strange no?... Well, most of the results in the document (http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf) are for 8-CPU machines, which is about the most you can get with off the shelf hardware (2x4-core CPU, the document has both Xeon and Opteron results). Niagara support is unfinished, so there's nothing to report there. On the other hand, the document does compare between several versions of Linux, FreeBSD, NetBSD and DragonflyBSD, with both MySQL and PostgreSQL, so you can draw your conclusions (if any) from there. signature.asc Description: OpenPGP digital signature
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Nov 16, 2007 3:36 PM, Josh Trutwin [EMAIL PROTECTED] wrote: Agreed. IMHO, there's nothing wrong with nested-loop join as long as it's being used properly. Can you explain further please? (I'm not disagreeing with you, just want to know when nested loops are not used properly - does the planner make mistakes that you have to watch out for?) As long as statistics are updated properly, it's generally not an issue. You just don't want the system using a nested-loop join incorrectly (like when table sizes are equal, the outer table is larger than the inner table, or the inner table itself is overly large). -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
Steinar H. Gunderson wrote: On Sun, Nov 11, 2007 at 08:27:02PM +0100, Dimitri wrote: As well to demonstrate OS parallelism it's not so impressive to see 4CPU server results rather 8CPU or 32threaded Niagara... Don't know why they did not present similar performance graphs for these platform, strange no?... I guess it's because their Niagara support is still very raw, and besides, it's not a very common platform. /* Steinar */ Not sure how much coding would need to be done for Niagra chips but I would think that it is more likely a problem of getting the funds so they can have one to work on. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Nov 11, 2007, at 2:17 PM, Joshua D. Drake wrote: Dimitri wrote: Seems to me there is more thread model implementation problem on FreeBSD, and databases just reflecting it... Most of the test I done on Solaris show the same performance level on the same short READ- only queries for MySQL and PostgreSQL. And to be honest till the end, thread model should be far faster (context switching between threads is way faster vs processes), but - as I say usually - even a very good idea may be just wasted by a poor implementation... And in case of MySQL they have too much locking to manage concurrency between threads which kills all thread model benefits... Also, to compare apples to apples, they should run this test from remote client rather locally on the same host - however in this case the result for PostgreSQL will mostly depends on client implementation: if client implements reading via CURSOR (quite often), reading will generate 4x times more intensive network traffic than necessary and final PostgreSQL result will be worse... Reading this article I'm just happy for them to see progress done on FreeBSD :-) As well to demonstrate OS parallelism it's not so impressive to see 4CPU server results rather 8CPU or 32threaded Niagara... Don't know why they did not present similar performance graphs for these platform, strange no?... I don't find it strange. I would rather see benchmarks on what the majority of people running on the platform are going to run. Most people don't run 8core machines and they especially don't run 32thread Niagra boxes. Wait! So, what do you check you're email with? :) Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
Dimitri wrote: Seems to me there is more thread model implementation problem on FreeBSD, and databases just reflecting it... Most of the test I done on Solaris show the same performance level on the same short READ-only queries for MySQL and PostgreSQL. And to be honest till the end, thread model should be far faster (context switching between threads is way faster vs processes), but - as I say usually - even a very good idea may be just wasted by a poor implementation... And in case of MySQL they have too much locking to manage concurrency between threads which kills all thread model benefits... Also, to compare apples to apples, they should run this test from remote client rather locally on the same host - however in this case the result for PostgreSQL will mostly depends on client implementation: if client implements reading via CURSOR (quite often), reading will generate 4x times more intensive network traffic than necessary and final PostgreSQL result will be worse... Reading this article I'm just happy for them to see progress done on FreeBSD :-) As well to demonstrate OS parallelism it's not so impressive to see 4CPU server results rather 8CPU or 32threaded Niagara... Don't know why they did not present similar performance graphs for these platform, strange no?... I don't find it strange. I would rather see benchmarks on what the majority of people running on the platform are going to run. Most people don't run 8core machines and they especially don't run 32thread Niagra boxes. Joshua D. Drake Rgds, -Dimitri ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Sun, Nov 11, 2007 at 08:27:02PM +0100, Dimitri wrote: As well to demonstrate OS parallelism it's not so impressive to see 4CPU server results rather 8CPU or 32threaded Niagara... Don't know why they did not present similar performance graphs for these platform, strange no?... I guess it's because their Niagara support is still very raw, and besides, it's not a very common platform. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Nov 9, 2007 7:06 AM, Ivan Voras [EMAIL PROTECTED] wrote: I just read this document and thought I should share it with this list: http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf Nice presentation. Thanks for posting it on here. Among other things (FreeBSD advocacy, mostly :) ), it contains a direct comparison between MySQL and PostgreSQL on various platforms, with PostgreSQL winning! :) -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] PostgreSQL vs MySQL, and FreeBSD
Hi, I just read this document and thought I should share it with this list: http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf Among other things (FreeBSD advocacy, mostly :) ), it contains a direct comparison between MySQL and PostgreSQL on various platforms, with PostgreSQL winning! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
Among other things (FreeBSD advocacy, mostly :) ), it contains a direct comparison between MySQL and PostgreSQL on various platforms, with PostgreSQL winning! Hello, If the queries are complex, this is understable. I had a performance review of a Hibernate project (Java Object Relation Mapping) using MySQL. ORM produces easily complex queries with joins and subqueries. MySQL uses nested loops for subqueries which lead to performance issues with growing database size. They state in their documentation that for version 5.2 there are improvements planned regarding this kind of query. Best Regards Sebastian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Nov 9, 2007, at 6:06 AM, Ivan Voras wrote: Hi, I just read this document and thought I should share it with this list: http://people.freebsd.org/~kris/scaling/7.0%20Preview.pdf Among other things (FreeBSD advocacy, mostly :) ), it contains a direct comparison between MySQL and PostgreSQL on various platforms, with PostgreSQL winning! Which is typical for those who aren't in on the FUD :) Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Nov 9, 2007 9:41 AM, Sebastian Hennebrueder [EMAIL PROTECTED] wrote: If the queries are complex, this is understable. I had a performance review of a Hibernate project (Java Object Relation Mapping) using MySQL. ORM produces easily complex queries with joins and subqueries. MySQL uses nested loops for subqueries which lead to performance issues with growing database size. They state in their documentation that for version 5.2 there are improvements planned regarding this kind of query. So, MySQL 5.2 will be catching up to version 7.1 or 7.2 of PostgreSQL in that regard? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Fri, 9 Nov 2007, Sebastian Hennebrueder wrote: If the queries are complex, this is understable. The queries used for this comparison are trivial. There's only one table involved and there are no joins. It's testing very low-level aspects of performance. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
On Fri, 9 Nov 2007 11:11:18 -0500 (EST) Greg Smith [EMAIL PROTECTED] wrote: On Fri, 9 Nov 2007, Sebastian Hennebrueder wrote: If the queries are complex, this is understable. The queries used for this comparison are trivial. There's only one table involved and there are no joins. It's testing very low-level aspects of performance. Actually, what it's really showing is parallelism, and I've always expected PostgreSQL to come out on top in that arena. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL vs MySQL
David Griffiths wrote: This is a timely thread for myself, as I'm in the middle of testing both databases as an Oracle replacement. As of this moment, I know more about MySQL (tuning, setup, features) than I do about Postgres. Not because I like MySQL more, but because 1) the MySQL docs are better (sorry - I found them easier to read, and more comprehensive; I had an easier time finding the answers I needed) 2) there are more web pages devoted to MySQL (probably because it has a bit more market share) 3) there are more books on MySQL at the bookstore (I haven't had a chance to pick up Bruce's book yet; it might be all the book I'd ever need) 4) we looked at MySQL first (we needed replication, and eRServer had not been open-sourced when we started looking) With regards to #1, I'd like to specifically mention tuning - the docs at http://www.postgresql.org/docs/7.3/static/runtime-config.html give a basic explanation of the different options, but much more is needed for tuning. I'm running into a problem with an update statement (that uses a select in a sub-query) in Postgres - it's taking hours to run (the equiv, using a multi-table update statement in MySQL instead of a sub-query, takes all of 2 seconds). I'll be posting it later once I do more reading to make sure I've done as much as I can to solve it myself. David, I think you have valid observations. And the issue regarding replication has been quite a hot topic on occasion in the developer lists. I'm hoping at some point it would become part of the standard PostgreSQL package; but point in time recovery, PITR, is needed as a stepping stone to providing that functionality. Have you attempted the multi table update inside of a transaction for PostgreSQL yet and thus assuring the all of your updates are only visible after the commit? Depending on the design and the nature of the updates, their could be a race condition if the updates on one table are utilized by another process before the rest of the updates have completed. Sets of updates in a single transaction can improve performance as well. I really agree with this post: I guess my point is simply this: instead of saying: okay we use default settings that will run on _old_ hardware too we should go for a little script that creates a still save but much better config file. There's just no point in setting SHARED_BUFFERS to something like 16 (what's the current default?) if the PC has = 1 GB of RAM. Setting it to 8192 would still be save, but 512 times better... ;-) (IIRC 8192 would take 64 MB of RAM, which should be save if you leave the default MAX_CONNECTIONS.) It provides examples, and some real numbers to help someone new to the database take an initial crack at tuning. Remember, you're trying to compete with the big-guys (Oracle, etc), so providing info that an Oracle DBA needs is pretty critical. I'm currently at a complete loss for tuning Postgres (it seems to do things very differently than both Oracle and MySQL). I also have to admit a bit of irritation reading this thread; there is a fair number of incorrect statements on this thread that, while not wrong, definately aren't right: Speed depends on the nature of use and the complexity of queries. If you are doing updates of related tables, ACID is of vital importance and MySQL doesn't provide it. MySQL has ACID in InnoDB. I've found that MySQL is actually very fast on complex queries w/InnoDB (six tables, 1 million rows, two of the joins are outer-joins. In fact, I can get InnoDB to be almost as fast as MyISAM. Complex updates are also very very fast. We have not tried flooding either database with dozens of complex statements from multiple clients; that's coming soon, and from what I've read, MySQL won't do too well. using InnoDB tables (the only way to have foreign keys, transactions, and row level locking for MySQL) makes MySQL slower and adds complexity to tuning the database Adding this: innodb_flush_method=O_DSYNC to the my.cnf made InnoDB as fast as MyISAM in our tests. It doesn't turn off disk flushing; it's just a flush method that might work better with different kernels and drives; it's one of those play with this and see if it helps parameters; there are lots of those in Postgres, it seems. There are 10 variables for tuning InnoDB (and you don't have to tune for MyISAM, so it's actually a six-of-one, half-dozen-of-the-other). Setup between the two seems to be about the same. PostgreSQL supports constraints. MySQL doesn't; programmers need to take care of that from the client side Again, InnoDB supports constraints. Transactions: We've been here before. Suffice to say, MySQL+InnoDB is almost there. Plain ol' MySQL doesn't have it, which tells you something about their philosophy towards database design. InnoDB supports transactions very nicely, has the equivalent of WAL, and one thing I really like: a tablespace (comprised of data files
Re: [PERFORM] PostgreSQL vs. MySQL
On Wed, Oct 08, 2003 at 01:28:53PM -0400, Bruce Momjian wrote: Agreed. Text added to install docs: [c.] I think this is just right. It tells a user where to find the info needed, doesn't reproduce it all over the place, and still points out that this is something you'd better do. Combined with the new probe-to-set-shared-buffers bit at install time, I think the reports of 400 billion times worse performance than MySQL will probably diminish. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] PostgreSQL vs MySQL
This is a timely thread for myself, as I'm in the middle of testing both databases as an Oracle replacement. As of this moment, I know more about MySQL (tuning, setup, features) than I do about Postgres. Not because I like MySQL more, but because 1)the MySQL docs are better (sorry - I found them easier to read, and more comprehensive; I had an easier time finding the answers I needed) 2)there are more web pages devoted to MySQL (probably because it has a bit more market share) 3)there are more books on MySQL at the bookstore (I haven't had a chance to pick up Bruce's book yet; it might be all the book I'd ever need) 4)we looked at MySQL first (we needed replication, and eRServer had not been open-sourced when we started looking) With regards to #1, I'd like to specifically mention tuning - the docs at http://www.postgresql.org/docs/7.3/static/runtime-config.htmlgive a basic explanation of the different options, but much more is needed for tuning. I'm running into a problem with an update statement (that uses a select in a sub-query) in Postgres - it's taking hours to run (the equiv, using a multi-table update statement in MySQL instead of a sub-query, takes all of 2 seconds). I'll be posting it later once I do more reading to make sure I've done as much as I can to solve it myself. I really agree with this post: "I guess my point is simply this: instead of saying: "okay we use default settings that will run on _old_ hardware too" we should go for a little script that creates a "still save but much better" config file. There's just no point in setting SHARED_BUFFERS to something like 16 (what's the current default?) if the PC has = 1 GB of RAM. Setting it to 8192 would still be save, but 512 times better... ;-) (IIRC 8192 would take 64 MB of RAM, which should be save if you leave the default MAX_CONNECTIONS.)" It provides examples, and some real numbers to help someone new to the database take an initial crack at tuning. Remember, you're trying to compete with the big-guys (Oracle, etc), so providing info that an Oracle DBA needs is pretty critical. I'm currently at a complete loss for tuning Postgres (it seems to do things very differently than both Oracle and MySQL). I also have to admit a bit of irritation reading this thread; there is a fair number of incorrect statements on this thread that, while not wrong, definately aren't right: "Speed depends on the nature of use and the complexity of queries. If you are doing updates of related tables, ACID is of vital importance and MySQL doesn't provide it." MySQL has ACID in InnoDB. I've found that MySQL is actually very fast on complex queries w/InnoDB (six tables, 1 million rows, two of the joins are outer-joins. In fact, I can get InnoDB to be almost as fast as MyISAM. Complex updates are also very very fast. We have not tried flooding either database with dozens of complex statements from multiple clients; that's coming soon, and from what I've read, MySQL won't do too well. "using InnoDB tables (the only way to have foreign keys,transactions, and row level locking for MySQL) makes MySQL slower andadds complexity to tuning the database" Adding this: "innodb_flush_method=O_DSYNC" to the my.cnf made InnoDB as fast as MyISAM in our tests. It doesn't turn off disk flushing; it's just a flush method that might work better with different kernels and drives; it's one of those "play with this and see if it helps" parameters; there are lots of those in Postgres, it seems. There are 10 variables for tuning InnoDB (and you don't have to tune for MyISAM, so it's actually a six-of-one, half-dozen-of-the-other). Setup between the two seems to be about the same. "PostgreSQL supports constraints. MySQL doesn't; programmers need to take care of that from the client side" Again, InnoDB supports constraints. "Transactions: We've been here before. Suffice to say, MySQL+InnoDB is almost there. Plain ol' MySQL doesn't have it, which tells you something about their philosophy towards database design." InnoDB supports transactions very nicely, has the equivalent of WAL, and one thing I really like: a tablespace (comprised of data files that can be spread around multiple hard drives), and in a month or so, InnoDB will support multiple tablespaces. To be fair, here are a few MySQL "bad-things" that weren't mentioned: 1) InnoDB can't do a hot-backup with the basic backup tools. To hot-backup an InnoDB database, you need to pay $450 US per database per year ($1150 per database perpetual) for a proprietary hot-backup tool 2) InnoDB can't do full-text searching. 3) I see alot more corrupt-database bugs on the MySQL lists (most are MyISAM, but a few InnoDB bugs pop up from time to time) - way more than I see on the Postgres lists. 4) There are some really cranky people on the MySQL lists; the Postgres lists seem to be much more effective (esp. with people like Tom Lane). Maybe it's because they get alot of
Re: [PERFORM] PostgreSQL vs MySQL
On Thu, 9 Oct 2003, David Griffiths wrote: 1) the MySQL docs are better (sorry - I found them easier to read, and more comprehensive; I had an easier time finding the answers I needed) Huh. I had the opposite experience. Each to his own. I think everybody agrees PG needs a better tuning doc (or pointers to it, or something). Speed depends on the nature of use and the complexity of queries. If you are doing updates of related tables, ACID is of vital importance and MySQL doesn't provide it. I don't know if you looked at my presentation. But in preparation for it I checked out MySQL 4.0.x[most recent stable]. I found that I violates the C in acid in some places. ie you can insert a date of /00/00 and have it sit there and be fine. Perhaps this is the fault of mysql's timestamp type. MyISAM. Complex updates are also very very fast. We have not tried flooding either database with dozens of complex statements from multiple clients; You don't need complex statements to topple mysql over in high concurrency. I was doing fairly simple queries with 20 load generators - it didn't like it. Not at all (mysql: 650 seconds pg: 220) 3) I see alot more corrupt-database bugs on the MySQL lists (most are MyISAM, but a few InnoDB bugs pop up from time to time) - way more than I see on the Postgres lists. I saw this as well. I was seeing things in the changelog as late as september (this year) about fixing bugs that cause horrific corruption. That doesn't make me feel comfy. Remember - in reality InnoDB is still very new. The PG stuff has been tinkered with for years. I like innovation and new things, but in some cases, I prefer the old code that has been looked at for years. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL vs MySQL
On Thu, 9 Oct 2003, Jeff wrote: On Thu, 9 Oct 2003, David Griffiths wrote: 1) the MySQL docs are better (sorry - I found them easier to read, and more comprehensive; I had an easier time finding the answers I needed) Huh. I had the opposite experience. Each to his own. I think everybody agrees PG needs a better tuning doc (or pointers to it, or something). I think the issue is that Postgresql documentation is oriented towards DBA types, who already understand databases in general, so they can find what they want, while MySQL docs are oriented towards dbms newbies, who don't know much, if anything, about databases. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL vs. MySQL
Andrew Sullivan wrote: On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote: Andrew Sullivan wrote: results under production conditions, and not bother to read even the basic quickstart-type stuff that is kicking around. Then please point out where it sais, in the documentation, that the value for the shared_memory of 64 is too low and that 4000 is a nice value to start with? I think I did indeed speak too soon, as the criticism is a fair one: nowhere in the installation instructions or the getting started docs does it say that you really ought to do some tuning once you have the system installed. Can I suggest for the time being that something along these lines should go in 14.6.3, Tuning the installation: ---snip--- By default, PostgreSQL is configured to run on minimal hardware. As a result, some tuning of your installation will be necessary before using it for anything other than extremely small databases. At the very least, it will probably be necessary to increase your shared buffers setting. See Chapter 16 for details on what tuning options are available to you. ---snip--- I'm sorry to put this in a such a confronting manner, but you simply can't expect people to search for information that they don't know the existence of. No need to apologise; I think you're right. Agreed. Text added to install docs: para By default, productnamePostgreSQL/ is configured to run on minimal hardware. This allows it to start up with almost any hardware configuration. However, the default configuration is not designed for optimum performance. To achieve optimum performance, several server variables must be adjusted, the two most common being varnameshared_buffers/varname and varname sort_mem/varname mentioned in ![%standalone-include[the documentation]] ![%standalone-ignore[xref linkend=runtime-config-resource-memory]]. Other parameters in ![%standalone-include[the documentation]] ![%standalone-ignore[xref linkend=runtime-config-resource]] also affect performance. /para -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL vs. MySQL
Josh Berkus wrote: Bruce, Agreed. Text added to install docs: para By default, productnamePostgreSQL/ is configured to run on minimal hardware. This allows it to start up with almost any hardware configuration. However, the default configuration is not designed for optimum performance. To achieve optimum performance, several server variables must be adjusted, the two most common being varnameshared_buffers/varname and varname sort_mem/varname mentioned in ![%standalone-include[the documentation]] ![%standalone-ignore[xref linkend=runtime-config-resource-memory]]. Other parameters in ![%standalone-include[the documentation]] ![%standalone-ignore[xref linkend=runtime-config-resource]] also affect performance. /para What would you think of adding a condensed version of my and Shridhar's guide to the install docs? I think I can offer a 3-paragraph version which would cover the major points of setting PostgreSQL.conf. Yes, I think that is a good idea --- now, does it go in the install docs, or in the docs next to each GUC item? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL vs. MySQL
On Wed, 2003-10-08 at 14:05, Josh Berkus wrote: Hmmm ... both, I think. The Install Docs should have: Here are the top # things you will want to adjust in your PostgreSQL.conf: 1) Shared_buffers link 2) Sort_mem link 3) effective_cache_size link 4) random_page_cost link 5) Fsync link etc. Barring an objection, I'll get to work on this. I think this kind of information belongs in the documentation proper, not in the installation instructions. I think you should put this kind of tuning information in the Performance Tips chapter, and include a pointer to it in the installation instructions. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] PostgreSQL vs. MySQL
JB == Josh Berkus [EMAIL PROTECTED] writes: JB Hmmm ... both, I think. The Install Docs should have: JB Here are the top # things you will want to adjust in your PostgreSQL.conf: JB 1) Shared_buffers link JB 2) Sort_mem link JB 3) effective_cache_size link JB 4) random_page_cost link JB 5) Fsync link JB etc. Add: max_fsm_relations (perhaps it is ok with current default) max_fsm_pages I don't think you really want to diddle with fsync in the name of speed at the cost of safety. and possibly: checkpoint_segments (if you do a lot of writes to the DB for extended durations of time) With 7.4 it warns you in the logs if you should increase this. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 3: 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] PostgreSQL vs. MySQL
I think the issue with multiple users is that a car is good for moving a few people, but it can't move lots of large boxes. A truck can move large boxes, but it can't move a few people efficiently. PostgreSQL is more like a truck, while MySQL is more like a car. As an aside, I think Solaris is slower than other OS's because it is built to scale efficiently to many CPU's, and that takes a performance hit in a machine with just a few CPU's, though they are working on tuning those cases. Of course, this is all just a generalization. --- scott.marlowe wrote: On Fri, 4 Jul 2003, Brian Tarbox wrote: I'm actually leaving this list but I can answer this question. Our results were with a single user and we were running Inodb. We were running on RedHat 8.0 / 9.0 with vanilla linux settings. Hi Brian, I just wanted to add that if you aren't testing your setup for multiple users, you are doing yourself a disservice. The performance of your app with one user is somewhat interesting, the performance of the system with a dozen or a hundred users is of paramount importance. A server that dies under heavy parallel load is useless, no matter how fast it ran when tested for one user. Conversely, one would prefer a server that was a little slow for single users but can hold up under load. When I first built my test box a few years ago, I tested postgresql / apache / php at 100 or more parallel users. That's where things start getting ugly, and you've got to test for it now, before you commit to a platform. Postgresql is designed to work on anything out of the box, which means it's not optimized for high performance, but for running on old Sparc 2s with 128 meg of ram. If you're going to test it against MySQL, be fair to yourself and performance tune them both before testing, they're performance on vanilla linux with vanilla configuration tuning teachs you little about how they'll behave in production on heavy iron. Good luck on your testing, and please, don't quit testing at the first sign one or the other is faster, be throrough and complete, including heavy parallel load testing with reads AND writes. Know the point at which each system begins to fail / become unresponsive, and how they behave in overload. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] PostgreSQL vs. MySQL
Brian Tarbox wrote: Oddly enough, the particular application in question will have an extremely small user base...perhaps a few simultainous users at most. As to the testing, I neglected to say early in this thread that my manager instructed me _not_ to do further performance testing...so as a good consultant I complied. I'm not going to touch if that was a smart instruction to give :-) Performance is probably 'good enough', and you can revisit it later when you have more time. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL vs. MySQL
On Mon, 7 Jul 2003, Brian Tarbox wrote: Oddly enough, the particular application in question will have an extremely small user base...perhaps a few simultainous users at most. As to the testing, I neglected to say early in this thread that my manager instructed me _not_ to do further performance testing...so as a good consultant I complied. I'm not going to touch if that was a smart instruction to give :-) But remember, you can always rename your performance testing as compliance testing and then it's ok, as long as you don't keep any detailed records about the time it took to run the compliance testing queries. Definitely look at the output from explain analyze select ... to see what the planner THINKS the query is gonna cost versus what it really costs. If you see a huge difference between, say estimated rows and actual rows, or some other value, it points to the analyzer not getting the right data for the planner. You can adjust the percentage of a table sampled with alter table to force more data into analyze. ---(end of broadcast)--- TIP 3: 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] PostgreSQL vs. MySQL
On Friday 04 July 2003 18:16, Michael Mattox wrote: I'm actually leaving this list but I can answer this question. Our results were with a single user and we were running Inodb. We were running on RedHat 8.0 / 9.0 with vanilla linux settings. That's funny, you make a statement that Postgres was 3 times slower than MySQL and then you promptly leave the list! Just kidding. It'd be interesting to see what happens if you test your system with a hundred users. If it's a webapp you can use JMeter to do this really easily. Hundred users is a later scenario. I am curious about vanilla linux settings What does that mean. Postgresql communmity would always like to help who need it but this thread so far gives me impression that OP isn't willing to provide sufficient information.. Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL vs. MySQL
On Fri, 2003-07-04 at 09:20, Shridhar Daithankar wrote: On 4 Jul 2003 at 9:11, Rod Taylor wrote: Unless you provide these, it's difficult to help.. http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php Well, even in that thread there wasn't enough information I asked for in other mail. It was bit too vague to be a comfortable DB tuning problem. Completely too little information, and it stopped with Tom asking for additional information. I don't think Brian has any interest in being helped. Many here would be more than happy to do so if the information were to flow. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] PostgreSQL vs. MySQL
I recently took a system from MySQL to Postgres. Same HW, SW, same data. The major operations where moderately complex queries (joins on 8 tables). The results we got was that Postgres was fully 3 times slower than MySql. We were on this list a fair bit looking for answers and tried all the standard answers. It was still much much much slower. I have never found a query in MySQL that was faster than one in PostgreSQL. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL vs. MySQL
Please understand the limits of how much information a consultant can submit to an open list like this about a client's confidential information. I've answered every question I _can_ answer and when I get hostility in response all I can do is sigh and move on. Is there any chance you could show us an EXPLAIN ANALYZE output of the poor performing query in question? I'm sorry if Shridhar is upset that I can't validate his favorite db but ad hominin comments aren't helpful. It was me who gave the comment based upon previous threads which requested information that had gone unanswered (not even a response stating such information could not be provided). The database you describe is quite small, so I'm not surprised MySQL does well with it. That said, it isn't normal to experience poor performance with PostgreSQL unless you've stumbled upon a poor spot (IN based sub-queries used to be poor performing, aggregates can be slow, mismatched datatypes, etc.). Output of EXPLAIN ANALYZE of a contrived query representative of the type of work done (that demonstrates the problem) with renamed tables and columns would go a long way to helping us help you. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] PostgreSQL vs. MySQL
Brian Tarbox [EMAIL PROTECTED] writes: I'm not permitted to post the actual tables as per company policy. Nobody wants to see your data, only the table schemas and queries. If you feel that even that contains some sensitive information, just rename the table and field names to something meaningless. But the kinds of problems I am interested in finding out about require seeing the column datatypes and the form of the queries. The hardware and platform details you gave mean nothing to me (and probably not to anyone else either, given that you were comparing to MySQL on the same platform). I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum analyze. If you didn't at least bump up shared_buffers, you were deliberately skewing the results against Postgres. Surely you can't have been subscribed to pgsql-performance very long without knowing that the default postgresql.conf settings are set up for a toy installation. all I can do is sigh and move on. You're still looking for reasons not to answer our questions, aren't you? Do you actually want to find out what the problem was here? If not, you're wasting our list bandwidth. I'd like to find out, if only so I can try to fix it in future releases, but without useful information I'll just have to write this off as an unsubstantiated report. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL vs. MySQL
2. Postgresql uses shared memory being process based architecture. Mysql uses process memory being threaded application. It does not need kernel settings to work and usually works best it can. MySQL has other issues with the kernel due to their threading choice such as memory limits per process, or poor threaded SMP support on some platforms (inability for a single process to use more than one CPU at a time regardless of thread count). Threads aren't an easy way around kernel limitations, which is probably why Apache has gone for a combination of the two -- but of course that adds complexity. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] PostgreSQL vs. MySQL
I'm not saying (and never did say) that postgres could not be fast. All I ever said was that with the same minimal effort applied to both DBs, postgres was slower. Afaik, your original posting said postgresql was 3 times slower than mysql and that you are going to leave this list now. This implied that you have made your decision between postgresql and mysql, taking mysql because it is faster. Now you say your testing setup has minimal effort applied. Well, it is not very surprising that mysql is faster in standard configurations. As Shridhar pointed out, postgresql has very conservative default values, so that it starts on nearly every machine. If I was your client and gave you the task to choose a suitable database for my application and you evaluated suitable databases this way, then something is seriously wrong with your work. Regards, Bjoern ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL vs. MySQL
My goodness people!! If you are just going to bash people who are trying to learn PostgreSQL then you have no chance of ever getting new people using it! Cut out this crap and do what this list is meant to do, which is, I'm assuming, helping people figure out why their installations aren't running as fast as they would like. This is pathetic!! Kevin - Original Message - From: Bjoern Metzdorf [EMAIL PROTECTED] To: Postgresql Performance [EMAIL PROTECTED] Sent: Friday, July 04, 2003 11:22 AM Subject: Re: [PERFORM] PostgreSQL vs. MySQL I'm not saying (and never did say) that postgres could not be fast. All I ever said was that with the same minimal effort applied to both DBs, postgres was slower. Afaik, your original posting said postgresql was 3 times slower than mysql and that you are going to leave this list now. This implied that you have made your decision between postgresql and mysql, taking mysql because it is faster. Now you say your testing setup has minimal effort applied. Well, it is not very surprising that mysql is faster in standard configurations. As Shridhar pointed out, postgresql has very conservative default values, so that it starts on nearly every machine. If I was your client and gave you the task to choose a suitable database for my application and you evaluated suitable databases this way, then something is seriously wrong with your work. Regards, Bjoern ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
