Re: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL
I've wondered and am still wondering what a lot of these benchmark tests are out to prove. In this case, the "benchmark test" was not out to prove anything. It was an good-faith result of a porting effort with a suprising (to the tester) result. I'm not sure that any PostgreSQL advocate has ever said or implied that PostgreSQL is faster than anything, much less MySQL. While I'm sure it's faster than some, I've just never heard the argument for using PostgreSQL as "It's faster than anything else". Very true. But it turns out that in at least some real-world tests, in this case a real application *built for MySQL*, PostgreSQL was substantially faster when the number of users climbed above a very small number. These results are consistant with and supported by GB's initial published benchmark results. Two separate styles of comparisons with consistant results might help someone choose the right solution for their application. No harm in that, eh? I chose PostgreSQL by what it could do, not how fast it can SELECT... No benchmark between MySQL and PostgreSQL (or any other RDBMS ) is ever going to be truly accurate since there are so many things MySQL simply can't to that PostgreSQL (and others) can.. Well, that is another dimension to the evaluation/comparison. But the testing results stand on their own: you *can* choose PostgreSQL for its performance, and you *will* have made the right choice. This is especially gratifying for all of us who have contributed to PostgreSQL because we *didn't* benchmark it, and *assumed* that MySQL claims for superior speed under all circumstances were accurate. Turns out it may be true for single-user mode, but that we've built a darn fast RDBMS for real-world applications. One *very unfair* part of these benchmarks and comparisons is that both MySQL and PostgreSQL can be identified by name for the comparisons, so they tend to be talked about the most. But the GB benchmarks could lead one to conclude that if SourceForge had been built with another database product it would also have seen a performance improvement when tested with PostgreSQL. - Thomas
[HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL
Still...Regardless of what database they're running, either their abstraction layer is shit or their queries really need optimized. Is that perhaps why, even at 5 clients, the page views he shows never went significantly above 10/sec? I think this could be because they used real killer pages in the test, and maybe this also the reason PgSQL fared this good (I've always been and I'm still a postgres fan, but looking at that results I've been quite astonished!!). Have you looked the spec? If I remember well, Tim was talking about executing cuncurrently a page that joined a dozen tables and another that was doing update/select/insert on the same tables. Under these condition, 10 pages/sec it seems lighting to me bye! /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED]
Re: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL
I've wondered and am still wondering what a lot of these benchmark tests are out to prove. I'm not sure that any PostgreSQL advocate has ever said or implied that PostgreSQL is faster than anything, much less MySQL. While I'm sure it's faster than some, I've just never heard the argument for using PostgreSQL as "It's faster than anything else". I chose PostgreSQL by what it could do, not how fast it can SELECT... No benchmark between MySQL and PostgreSQL (or any other RDBMS ) is ever going to be truly accurate since there are so many things MySQL simply can't to that PostgreSQL (and others) can.. As Don often out often and accurately points out, MySQL is not an RDBMS, I'm not sure what it really is beyond a semi-fancy SQL interface to a file system.. Is it fast? Yes, it is pretty fast. Fast at the expense of functionality and stability -- two things that just aren't optional when you're talking about a good database for anything more complicated than click-through tracking... I don't dislike MySQL for any other reason except that it can't do what I need it to do, period... I'm sure it's good for some things and some people, I've tried MySQL, tested MySQL and then tossed MySQL into the garbage can... I found some very educational conversation here : http://openacs.org/philosophy/why-not-mysql.html courtesy of Don and others. -Mitch - Original Message - From: "Don Baccus" [EMAIL PROTECTED] To: "Robert D. Nelson" [EMAIL PROTECTED]; [EMAIL PROTECTED]; "Michael Fork" [EMAIL PROTECTED]; "Poul L.Christiansen" [EMAIL PROTECTED] Cc: "pgsql-general" [EMAIL PROTECTED]; "pgsql-hackers" [EMAIL PROTECTED] Sent: Monday, November 20, 2000 8:48 PM Subject: Re: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL At 11:22 AM 11/13/00 -0500, Robert D. Nelson wrote: Still...Regardless of what database they're running, either their abstraction layer is shit or their queries really need optimized. Is that perhaps why, even at 5 clients, the page views he shows never went significantly above 10/sec? They don't appear to do any client-side query caching, which is understandable from one point of view (you need some sort of handle on which queries are hit frequently enough to warrant devoting RAM to caching the result, or else you risk caching things that don't gain you much and cut down on the amount of the DB cached in RAM which hits you on other queries). On the other hand, you'd think they'd do some analysis... Still, the table-locking of MySQL just gets in the way. If you can cache everything, then you can send a postal worker to the mailbox to retrieve uncached data without significantly impacting throughput (in other words, the MySQL argument that simple select benchmarks are all you need are not relevant). If you can't cache anything but have few users, then perhaps low levels of concurrency don't hurt. If you don't cache anything but have lots of users, scaling well under high levels of load rule. My thinking is that intellegent caching coupled with a highly-scalable database wins. That's the world I'm used to... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 09:43 AM 11/13/00 -0600, [EMAIL PROTECTED] wrote: I made it all the way through the article. I'll summarize it for you: Postgres - hooray! MySQL - boo! Since this is an open source database article linked off of slashdot, I imagine they're getting pounded. Why is all this e-mail showing up so late? (I'm curious because there have been complaints about the mail server here, and the article is old hat). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 01:53 PM 11/15/00 -0500, markw wrote:
I'd rather not pollute the application's SQL with postgres-isms. Not that I
don't love postgres, but there are always critics looking for a reason to use
Oracle or (gasp) MS-SQL.
Define some global variable with the name of the database being run (currently
only Postgres) and guard the SET statement with a conditional...
In the OpenACS project we've got little functions that return query snippets
called things like "db_nextval" that return either "sequence_name.nextval"
or "nextval('sequence_name')" depending on whether the code's running
under Oracle or Postgres. That helps us minimize differences in the source.
- Don Baccus, Portland OR [EMAIL PROTECTED]
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
On Wed, 15 Nov 2000, carl garland wrote: perhaps why, even at 5 clients, the page views he shows never went significantly above 10/sec? I think alot of it has to do with the web server/db setup not pg. They are using Apache/PHP and looking at their code every page has the additional overhead of making the db connection. Now if they had used AOLserver with its persistent db connecction pooling scheme they may have faired better ;) I doubt it. PostgreSQL has a higher connection startup overhead than MySQL, so if every view required a new database connection, it would been quite a detriment to the PostgreSQL scores. PHP can maintain persisitant connections. Unfortunately, this means that you end up with a database connection per httpd process. That really isn't a problem for PostgreSQL though, it just requires sufficent memory. No doubt that is what was being done. AOLServer isn't the only system that can pool database connections, so can servlets/JSP, ColdFusion, ASP, etc. No doubt AOLServer would be more widely accepted if it used something other than TCL. Tom
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
On Wed, 15 Nov 2000, carl garland wrote: # perhaps why, even at 5 clients, the page views he shows never went # significantly above 10/sec? # # I think alot of it has to do with the web server/db setup not pg. They are # using Apache/PHP and looking at their code every page has the additional # overhead of making the db connection. Now if they had used AOLserver with # its persistent db connecction pooling scheme they may have faired better ;) I haven't actually looked at their code they used to test with to see if they are actually using it, but Apache/PHP has the ability to do persistent db connections. I would be surprised that someone like Tim ( who seems to have done a fair bit of php with db stuff) would not make use of such a feature. If you can point out an example of where they are not using this feature I will gladly stand corrected. | Joseph Scott The Office Of Water Programs | | [EMAIL PROTECTED] [EMAIL PROTECTED] |
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
markw [EMAIL PROTECTED] writes: Just a question, however, what is the feeling about the way statistics are currently being calculated? They suck, no question about it ;-) My feeling is that some sort of windowing algorithm be used to normalize the statistics to the majority of the entries in a table. It could be as simple as discarding the upper and lower 10% of the record stats, and use the remaining 80% for statistics. I think what most of the discussion has focused on is building histograms. The current upper-and-lower-bounds-only approach just plain isn't enough data, even if you discard outliers so that the data isn't actively misleading. As far as the most-common-value issue goes, if you have one value that is vastly more common than any other, I think it would be a real mistake to throw away that information --- that would mean the planner would do the wrong thing for queries that do involve that value. What we need is to save info about several top-frequency values, maybe three or so, not just one. Also the finding of those values needs to be much more robust than it is currently. See past discussions in pghackers --- there have been plenty... regards, tom lane
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
perhaps why, even at 5 clients, the page views he shows never went significantly above 10/sec? I think alot of it has to do with the web server/db setup not pg. They are using Apache/PHP and looking at their code every page has the additional overhead of making the db connection. Now if they had used AOLserver with its persistent db connecction pooling scheme they may have faired better ;) _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 09:27 AM 11/15/00 -0800, Tom Samplonius wrote: AOLServer isn't the only system that can pool database connections, so can servlets/JSP, ColdFusion, ASP, etc. No doubt AOLServer would be more widely accepted if it used something other than TCL. There are two separate modules that support Java in AOLserver: ns_tomcat which provides an identical interface as Apache tomcat (and no real advantages) and ns_java, which is coming out of the OpenACS project. ns_java exposes AOLserver's pooled, persistent database API to java. There's also support available for Python, though there's still a lot of work to be done to support the full AOLserver API (same's true of ns_java, actually). If you use ADP pages, your use of Tcl is typically restricted to snippets of code anyway, so I've never really understood the complaints about Tcl... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
Andrew McMillan wrote: mlw wrote: My music database has 50,000 arises and 210,000 albums. Many artists have only one or 2 entries in the albums table (for the youngsters, CD table ;-). About 34,000 have the integer key for "Various Artists" as their artist entry, and another few thousand have things like "Movie Soundtrack" and so on. When the statistics are computed, these relatively few records with a huge number of relations distort the statistics and make it impossible to get postgres to use an index on that table without the -fs switch. This is bad because it always forces use of an index, even when postgres would legitimately ignore it. What about doing: SET enable_seqscan TO 'Off'; Just before the query in question? That way you'd only affect the single query. Possibly you could even code to spot the two aberrant situations and not do it in those ones. I'd rather not pollute the application's SQL with postgres-isms. Not that I don't love postgres, but there are always critics looking for a reason to use Oracle or (gasp) MS-SQL. As for "code to spot.." I am fairly new to hacking postgres. (Though, I have been using it in various projects since ~1995), but I am excellent C/C++ guy, give me a pointer to where (a) statistics are calculated, and (b) where they are interpreted, and I would do that. Just a question, however, what is the feeling about the way statistics are currently being calculated? My feeling is that some sort of windowing algorithm be used to normalize the statistics to the majority of the entries in a table. It could be as simple as discarding the upper and lower 10% of the record stats, and use the remaining 80% for statistics. That would certainly take care of my problem (and others I am sure), and I'd be glad to write it. ;-) Regards, Andrew. -- _ Andrew McMillan, e-mail: [EMAIL PROTECTED] Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
[HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
[ Charset ISO-8859-1 unsupported, converting... ] And now it's on www.slashdot.org ... http://slashdot.org/articles/00/11/13/1342208.shtml Poul L. Christiansen Michael Fork wrote: Thought this may be of interest to some... http://www.phpbuilder.com/columns/tim20001112.php3 Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio It would be nice if the fourth page were there. Am I the only one getting "Not Found"? They are getting lots of hits. Hit reload and eventually it will appear. I am on page six now. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
And now it's on www.slashdot.org ... http://slashdot.org/articles/00/11/13/1342208.shtml Poul L. Christiansen Michael Fork wrote: Thought this may be of interest to some... http://www.phpbuilder.com/columns/tim20001112.php3 Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio
