This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: [HACKERS] PostgreSQL wins against some other SQL RDBMS From: Ferruccio <nonsolosoft@diff.org> Date: Tue, 15 Aug 2000 13:16:08 +0200 (CEST) I've just found a good news on slashdot, it links to a good test published on Apache Today where PostgreSQL is tested against some other DBMS: "In the AS3AP tests, PostgreSQL 7.0 significantly outperformed both the leading commercial and open source applications in speed and scalability. In the tested configuration, Postgres peaked at 1127.8 transactions per second with five users, and still processed at a steady rate of 1070.4 with 100 users. The proprietary leader also performed consistently, with a high of 314.15 transactions per second with eight users, which fell slightly to 288.37 transactions per second with 100 users. The other leading proprietary database also demonstrated consistency, running at 200.21 transactions per second with six users and 197.4 with 100." http://apachetoday.com/news_story.php3?ltsn=2000-08-14-008-01-PR-MR-SW I'm sure that it is to add to your news section. === Subject: Re: [HACKERS] Open Source Database Routs Competition in New Benchmark Tests From: Joe Brenner <doom@kzsu.stanford.edu> Date: Tue, 15 Aug 2000 01:24:59 -0700 Kaare Rasmussen <kar@webline.dk> wrote: > I think a bit of explanation is required for this story: > > http://www.newsalert.com/bin/story?StoryId=CozDUWbKbytiXnZy&FQ=Linux&Nav=na-search-&StoryTitle=Linux > > Up until now, the MySQL people have been boasting performance as the > product's great advantage. Now this contradicts thi sfor the first time. I > believe it has to do with the test. Perhaps MySQL is faster when you just > do one simple SELECT * FROM table, and that it has never really been > tested in a real-life (or as close as possible) environment? I wouldn't say that this is exactly the first time we've heard about problems with MySQL's famed "speed". Take the Tim Perdue article that came out a while back: http://www.phpbuilder.com/columns/tim20000705.php3?page=1 The most interesting thing about my test results was to see how much of a load Postgres could withstand before giving any errors. In fact, Postgres seemed to scale 3 times higher than MySQL before giving any errors at all. MySQL begins collapsing at about 40-50 concurrent connections, whereas Postgres handily scaled to 120 before balking. My guess is, that Postgres could have gone far past 120 connections with enough memory and CPU. On the surface, this can appear to be a huge win for Postgres, but if you look at the results in more detail, you'll see that Postgres took up to 2-3 times longer to generate each page, so it needs to scale 2-3 times higher just to break even with MySQL. So in terms of max numbers of pages generated concurrently without giving errors, it's pretty much a dead heat between the two databases. In terms of generating one page at a time, MySQL does it up to 2-3 times faster. As written, this is not exactly slanted toward postgresql, but you could easily rephrase this as "MySQL is fast, but not under heavy load. When heavily loaded, it degrades much faster than Postgresql, and they're both roughly the same speed, despite the fact that postgresql is doing more (transaction processing, etc.)." This story has made slashdot: http://slashdot.org/article.pl?sid=00/08/14/2128237&mode=nested Some of the comments are interesting. One MySQL defender claims that the bottle neck in the benchmarks Great Bridge used is the ODBC drivers. It's possible that all the test really shows is that MySQL has a poor ODBC driver. === Subject: Re: [HACKERS] Open Source Database Routs Competition in New From: Don Baccus <dhogaza@pacifier.com> Date: Tue, 15 Aug 2000 06:13:35 -0700 At 11:12 AM 8/15/00 +0200, Kaare Rasmussen wrote: >This is the first benchmark saying that PostgreSQL is actually faster >than MySQL. And as we all know, benchmarks can be stretched any way you >like it, so that's why I'd like some comments before I go out and >advocate too strongly :-) Good scaling characteristics are a lot more important than raw speed for the web environment, at least, where short, quick queries to personalize content, etc are the rule. If only a couple of folks are using the site simultaneously, who cares if it takes an extra 50 milliseconds to return the page? If I've got a hundred users on my site, though, and the database engine "starts falling apart around 40-50 users", then I'm in deep doo-doo. In practice, MySQL users have to implement the atomic updating of a set of tables "by hand" using special locking tables, etc. All the cruft surrounding this is not very likely to be more efficient than the built-in transaction code of a real RDBMS. When people talk about the raw speed of MySQL they forget that working around its table locking granularity and lack of transaction semantics is a pain that costs CPU as well as programmer cycles. I came back to Postgres after rejecting it for website development work when I heard that MVCC was replacing the older table-level locking model. I've never been excited about MySQL for the same reason (among many others). === Subject: Re: [HACKERS] Open Source Database Routs Competition in New From: Don Baccus <dhogaza@pacifier.com> Date: Tue, 15 Aug 2000 06:02:35 -0700 At 09:26 AM 8/15/00 +0200, Kaare Rasmussen wrote: >I think a bit of explanation is required for this story: > >http://www.newsalert.com/bin/story?StoryId=CozDUWbKbytiXnZy&FQ=Linux&Nav=na -search-&StoryTitle=Linux > >Up until now, the MySQL people have been boasting performance as the >product's great advantage. Now this contradicts thi sfor the first time. I >believe it has to do with the test. Perhaps MySQL is faster when you just >do one simple SELECT * FROM table, and that it has never really been >tested in a real-life (or as close as possible) environment? It's no secret that MySQL falls apart under load when there are inserts and updates in the mix. They do table-level locking. If you read various threads about "hints and tricks" in MySQL-land concerning performance in high-concurrency (i.e. web site) situations, there are all sorts of suggestions about periodically caching copies of tables for reading so readers don't get blocked, etc. The sickness lies in the fact that the folks writing these complex workarounds are still convinced that MySQL is the fastest, most efficient DB tool available, that the lack of transactions is making their system faster, and that the concurrency problems they see are no worse than are seen with "real" a RDBMS like Oracle or Postgres. The level of ignorance in the MySQL world is just stunning at times, mostly due to a lot of openly dishonest (IMO) claims and advocacy by the authors of MySQL, in their documentation, for instance. A significant percentage of MySQL users seem to take these statements as gospel and are offended when you suggest, for instance, that table-level locking isn't such a hot idea for a DB used to drive a popular website. At least now when they all shout "Slashdot's popular, and they use MySQL" we can answer, "yeah, but the Slashdot folks are the ones who paid for the integration of MySQL with the SleepyCat backend, and guess why?" And the Slashdot folks have been openly talking about rewriting their code to be more DB agnostic (I refuse to call MySQL an RDBMS) and about perhaps switching to Oracle in the future. Maybe tests like this and more user advocacy will convince them to consider Postgres! === Subject: Re: [HACKERS] Open Source Database Routs Competition in New Benchmark From: Thomas Lockhart <lockhart@alumni.caltech.edu> Date: Tue, 15 Aug 2000 13:59:00 +0000 Some of the comments are interesting. One MySQL defender > claims that the bottle neck in the benchmarks Great Bridge > used is the ODBC drivers. It's possible that all the test > really shows is that MySQL has a poor ODBC driver. Nope. If it were due to the ODBC driver, then MySQL and PostgreSQL would not have had comparable performance in the 1-2 user case. The ODBC driver is a per-client interface so would have no role as the number of users goes up. The Postgres core group were as suprised as anyone with the test results. There was no effort to "cook the books" on the testing: afaik GB did the testing as part of *their* evaluation of whether PostgreSQL would be a viable product for their company. I believe that the tests were all run on the same system, and, especially given the results, they did go through and verify that the settings for each DB were reasonable. The AS3AP test is a *read only test*, which should have been MySQL's bread and butter according to their marketing literature. The shape of that curve shows that MySQL started wheezing at about 4 users, and tailed off rapidly after that point. The other guys barely made it out of the starting gate :) The thing that was the most fun about this (the PostgreSQL steering committee got a sneak preview of the results a couple of months ago) was that we have never made an effort to benchmark Postgres against other databases, so we had no quantitative measurement on how we were doing. And we are doing pretty good! === Subject: Re: [HACKERS] Open Source Database Routs Competition in New Benchmark From: The Hermit Hacker <scrappy@hub.org> Date: Tue, 15 Aug 2000 11:08:48 -0300 (ADT) On Tue, 15 Aug 2000, Thomas Lockhart wrote: > The AS3AP test is a *read only test*, which should have > been MySQL's bread and butter according to their marketing > literature. The shape of that curve shows that MySQL > started wheezing at about 4 users, and tailed off rapidly > after that point. The other guys barely made it out of the > starting gate :) Ah, cool, that answers one of my previous questions ... and scary that we beat "the best database for read only apps" *grin* === Subject: Re: [HACKERS] Open Source Database Routs Competition in New Benchmark From: Matthew Kirkwood <weejock@ferret.lmh.ox.ac.uk> Date: Tue, 15 Aug 2000 16:28:08 +0100 (BST) On Tue, 15 Aug 2000, Don Baccus wrote: > It's no secret that MySQL falls apart under load when there are > inserts and updates in the mix. They do table-level locking. If you > read various threads about "hints and tricks" in MySQL-land concerning > performance in high-concurrency (i.e. web site) situations, there are > all sorts of suggestions about periodically caching copies of tables > for reading so readers don't get blocked, etc. Here's one you might like. I am aware of a site (not one I run, and I shouldn't give its name) which has a share feed (or several). This means that, every 15 minutes, they have to get a bunch of rows into a few tables in a real hurry. MySQL's table level locking causes them such trouble that they run two instances. No big surprises there, but here's the fun bit: they both point at the same datafiles. Their web code accesses a mysqld which was started with their --readonly and --no-locking flags, so that it never writes to the datafiles. And the share feed goes through a separate, writable database. Every now and then a query fails with an error like "Eek! The table changed under us." so they modified (or wrapped - I'm not sure) the DBI driver to retry a couple of times under such circumstances. The result: it works. And actually quite well (ie. a lot better than before). I believe (hope!) that they are using the breathing space to investigate alternative solutions. === Subject: Re: [HACKERS] Open Source Database Routs Competition in New Benchmark Tests From: merlin <merlin@sixdegrees.com> Date: Tue, 15 Aug 2000 12:08:17 -0400 Given all this performance discussion, has anyone seen any numbersregarding the speed of PostgreSQl vs Oracle? === Subject: Re: [HACKERS] Open Source Database Routs Competition in New Benchmark From: Thomas Lockhart <lockhart@alumni.caltech.edu> Date: Tue, 15 Aug 2000 16:56:04 +0000 Given all this performance discussion, has anyone seen any numbers regarding > the speed of PostgreSQl vs Oracle? Um, er... No, but perhaps you could consider "one of the leading closed source database products" which was compared in the Great Bridge test to be similar in speed and features to The Database You Have Named. *hint hint* === Subject: Re: [HACKERS] Open Source Database Routs Competition in New From: Don Baccus <dhogaza@pacifier.com> Date: Tue, 15 Aug 2000 06:04:59 -0700 At 01:24 AM 8/15/00 -0700, you wrote: > It's possible that all the test >really shows is that MySQL has a poor ODBC driver. It would have to be really, really bad for this to be the case. ===