MySQL Developer Contests PostgreSQL Benchmarks

Lucas Marshall writes: "Developer Shed has an article written by the main MySQL developer Michael "Monty" Widenius in response to Great Bridge's press release reported here on Slashdot on Monday. He makes some pretty good points about fair benchmarking and software competition."

Hardly.

The "last line of defense"? Hardly. See the Functionality missing from MySQL [mysql.com] section.

Summary:

• No sub-selects (these "might be" available in 3.24)
• No SELECT INTO.
• No transactions. (Unstable development software does not count. If you have someone who wants to commit their data to an unstable version of a database, get a new DBA.)
• Stored procedures and triggers. ("We want to add this in the future")
• Foreign Keys (there are given "reasons NOT to use FK's" which scare me considerably if the developers of a database actually believe this!)
• Views (again, "in the future")
• "--" for comments (because they happen not to like this! Well, screw the standard, who needs one, right?)

MySQL has no referential integrity. (Under some definitions, this excludes it from even being considered a RDBMS.) It certainly lacks PostgreSQL's object-oriented capabilities, which are highly useful, such as table inheritence.

PostgreSQL has all of the above features, and has had them for some time (with the exception of FK's, which are new to 7.0). It is stable and well-tested. MySQL developers are still working on these features. I'd say MySQL has quite a way to go before it reaches PgSQL's "last line of defense".

Postgres (from a new postgre user)

I don't know how fast postgres really is. I am in the process of switching a few gig databases from MS SQL/IIS/php to Linux/PostgreSQL/php. Let's not get into an "internal war" over these benchmarks. To me, a "enterprise user", all that really matters is postgreSQL can compete with commercial systems for gig+ DB's.

Based on my requirements, and the need to have SQL 92 support (to port apps from larger systems, etc) mySQL and Postgres fall into different leagues. For sites that do not require the complex queries I do on a regular basis, mySQL is fine. That is what most Slashdot/php developers will ever need. I am having a hard enough time "betting the farm" to a free DB, that I know supports all the SQL I use.

There is a common mindset that benchmarks are biased, no matter who does them, or why. They are more to prove a product capable then to compare two products to one another.

Frankly, I'm impressed with how fast postgres can pull an 8k record out of a 1 gig table. It blows MS SQL server away for some things, while the boat rocks the other direction for "select DISTINCT field from table". (The same 148,000 record table.)

I liken the PostgreSQL benchmark to the Mindcraft study (The postgresql support company payed for the benchmarks.) While the actual performance figures of mySQL/postgresql many not be "fair", postgresql will fill a role for me a can't quite bring myself to use mysql for, and the next choice would be IBM DB2/Linux, which lacks good php support, and has much higher fees (to say the least!)

I hate mySQL users who have never used anything else saying "mySQL rul3z!", which I know will start to soon filter in (and down I hope) to this discussion.

-Pete

Re:You should drag it out anyway

ACID stands for Atomicity, Consistancy, Isolation and Duribility. Atomicity means, basically, a transaction. A series of commands are issued, then either commited or rolled back. All happens, or none happens. The classic example is doing an update on Joes_Account_Balance to subtract 100 dollars, then failing to do the update on Suzys_Account_Balance to add 100 dollars. The transaction failed, so it should be rolled back so that the 100 doesn't get removed from Joe. Consistency means that the database must always be in a valid state, and that usually means rules are enforced. If there's a database rule that states that any subtraction of 100 dollars from an account needs to add a record to the audit table, so you subtract 100 dollars from Joes_Account, but the audit table cannot be updated due to disk full/network break/act of Gods/whatever, then the subtract needs to be rolled back, or not happen in the first place. Isolation means that users don't see other users. If two people are both doing multi-stage updates to Joes_Account, user one doesn't see the fact that user two just did an insert, and an update. Durability means that if bad things happen, the database survives. Logs, backups, that sort of thing.

Benchmarks inherently flawed

I have a large distrust of most benchmarks anyway, as they usually want to test under "real world" conditions that, in practice, are almost never real world.

Yes and no, but MySQL is being marketed as more...

I agree, MySQL has its uses. It is, in some instances, a vastly superior tool to the alternatives. MySQL's problem, however, is largely created by themselves and their zealots. They have effectively billed MySQL as a replacement for commercial RDBMSs. That is equivelent to marketing a swiss army knife as a be all and end all replacement for every tool it offers. Yes, it looks something like it. It functions something like it. But that doesn't mean it is it. Nor does it mean that the swiss army knife has even 1/10th the engineering as the devices it replaces. That is essentially what MySQL is, an overglorified swiss army knife where people _might_ want to use an RDBMS. ...It's lighter, it's cheaper, it's quicker in some cases...but that doesn't mean people haven't made horrible mistakes by employing it. I can understand how many developers and database types get a bit annoyed with this....

PS: What exactly does "improving at open source speed" mean? If it means that you think MySQL, or for that matter any truely open source developed RDBMS, will ever be able to go toe to toe with the likes of Oracle or Sybase, then I'd take your bet any day. No previous Open Source project has ever taken on something of this size and complexity, and come out on top. So please don't tell me about this much fabled "open source speed" until it is a reality.

Be honest.

How many of you are just reading the comments on this article for the inevitable mysql vs postgresql vs commercial db flame fest? :)

Use whatever works best for you

If I was moderating I'd mark myself as "Redundant" but after all the trash talking over yesterday's article I feel it needs to be reiterated:

Use whatever works best for you.

Postgres is a fine solution for many things; MySQL is a fine solution for many things. I hope they both get better and better. Now why in the world would anyone get upset at the particular database choice someone else has made for themselves?

I must admit that the infomercial style press release by the Postgres people made me cringe. I would of course still consider using their database, but I would no longer trust their claims; I'll do the research myself. The MySQL team has always been very forthcoming with the strengths and limitations of MySQL, so I'll give them a little more credibility for now. I guess once you've hired a marketing team, you've gotta start slinging FUD, even if you're open source. Oh well.

In any case, I'm very glad that both databases are making strides to build a better product. I will continue to use and appreciate both.

MySQL and PostgreSQL *both* useful

Take a close look at what you are writing people. Especially those who have said "MySQL is useless in the real world." I can't imagine any sane definition of "real world" that doesn't include Slashdot.org, countless e-commerce sites and other non-Web applications.

Of course MySQL has limitations! Of course it does not meet the definition of an RDBMS! It meets the needs of a significant set of *real world* problems with great efficiency.

PostgreSQL meets a different, but overlapping, set of problems with ever increasing speed and efficiency.

They are both open source.
They are both improving at open source speed.
They both rock.

Speaking Of Independent DB benchmarks

I posted this yesterday but probably no one read it due to the fact that it was one of the latter posts (past 400). Anyway here goes...

According to the original article [slashdot.org] the names of the proprietary databases benchmarked were not given because it violated licensing agreements. Flawed benchmarks like this are the reason why.

As someone who has downloaded, installed and used Oracle 8i, IBM's DB2 and Borland's Interbase I can testify that configuring any of these DB's properly is a non-trivial task that can easily be messed up by someone who has no idea what he/she is doing. Using non-native drivers, not indexing tables properly, improper tablespace sizing, choosing an improper number of data files, improperly managing data blocks, etc can all lead to creation of a suboptimal database application performance.

Most of the major DB companies provide DB's for independent benchmarking from organisations like the Transaction Processing Performance Council [tpc.org]. As can be seen from this story [zdnet.com] these tests involve several thousand transactions per second and not several hundred as reached by this Great Bridge sponsored benchmark. I suggest that someone with a deep pockets or a vested interest in seeing Open Source DBs succeed should enter PostgreSQl or MySQL in these TPC-C tests.
The Queue Principle

Even more: Official PR on benchmark methodology

Great Bridge just released a Press Release (pun not intended) here [greatbridge.com] (http://www.greatbridge.com/news/p_081620001.html) regarding the software, tuning, etc. used in the benchmark.

Re:More info: make your own conclusions

So, well, the commercial databases could only be:

• Oracle, which is without any doubt the "biggest" one
• MS SQL, which is the only one "preferring to run on NT"

BTW:
I use Porstgres on FreeBSD since 1996 and MySQL on Linux since 1998 without any problems: easy to set up, fast and reliable...
But I also administer an online store (one of the biggest here in Italy) which uses IBM DB2 on NT4.0 since 1997: there exists no combination which gave us more headaches, and the last week was a real nightmare: We had to reboot the whole server (not only the hanging services) about every 2 hours and did reinstall from scratch 3 times in the last 2 days... The system still is unstable - we do not know why. We are now loosing millions of lire a day due to lost sales because the server is down... One thing is sure: we will switch to Linux.

:-)
ms

Read the version numbers! ;P

Oh, forgot to emphasize a very, very important part of the 2nd PR (as I posted before): The version numbers of the databases (BIG HINT):

What versions of the databases did you use?

1. PostgreSQL - 7.0 release version
2. Proprietary 1 - 8.1.5
3. Proprietary 2 - 7.0
4. MySQL - 3.22.32
5. Interbase - 6.0

Very nice workaround for the EULAs, dontcha think? :P

P.S. I really should learn to spell postgreSQL properly...

That's absurd

Feature set and ANSI SQL compliance aside, if it does what you want it to do (because a lot of us don't need reverse inside black hole pineapple upside down cake joins with recursive subsubsupersubselects), and you benchmark it yourself and you find one to be faster than the rest, for god's sake, use it and be proud, just don't flame other people's choices, because a good decision should be based on THEIR needs, not yours.

Mike

"I would kill everyone in this room for a drop of sweet, tasty beer."

Benchmarks are what they are . . .

Like all statistics, they are often interesting and provocative, but are almost never sufficient to judge which of several applications would be best for a particular application.

Monty's reference to "lies" and deceit in his article are troublesome and uncollegial -- while there was certainly a goodly amount of gloating in one press release, I would have been far more impressed with an impassive, nuts and bolts, criticism of the ANSI benchmarks than the unfortunate essay here.

One thing for which I have found Benchmarks useful is to prove concept -- that the application can, at least, run the Benchmark.

In this regard, there is one benchmark that I find quite persusasive at the moment: MySQL generates zero --that's 0.000- transactions per second. It is my hope that they will soon turn to implementing this important feature, so that we may then compare apples to apples.

Why MySQL or PostgreSQL won't get TPC-C results

TPC-C is a massive benchmark, requiring a massive effort. I've heard anecdotal evidence that is costs the big RDBMS vendors 6 figures for each run. However, there are a couple of specific reasons why MySQL and PostgreSQL will never see TPC-C results.

First, TPC-C requires transactions That's what the T is for! MySQL doesn't support transactions. So, it's right out.

Next, to get decent TPC-C numbers, most vendors need a TP-Monitor sitting in front of the database. Seeing as the commercial TP-Monitors ain't cheap, and there are no open source TP-Monitors, that sort of rules out an all open-source TPC-C number.

Finally, to simulate the thousands of concurrent users that TPC-C requires, one needs an awful lot of hardware just to drive the test -- the top scoring IBM DB2 results used 128 CPUs for the servers and 196 CPUs for the clients. Can you imagine how much time was spent tuning this beast?

Just as a final note on how expensive this is, note that there are no published TPC-C numbers for the existing commercial RDBSes running on Linux or BSD. Given Larry Ellison's famous hatred of Microsoft, and how much he'd love for Oracle on Linux to outperform Oracle on Windows, doesn't this say something about the expense of running these tests?

Re:I don't get these statements ...

That's a very novel argument... until you actually look at the price of the systems involved in that top ten information. For the price-performance list, none of the top ten costs less than $350,000, and the most expensive is well over $800k. I wouldn't say that it's a particularly useful guide for those of us that have a more earthbound budget.

As for the 'top ten by database vendor' results... number one comes in at a measly $3.6 MILLION. Oh. Like my company can afford that. Number 2 is the same. Number 3 is, well, cheap in comparison, at $2.2 million. In fact, the cheapest system came from Sun, only cost (only!) something over $550k, and was running some Fujitsu software.

All in all, I think it proves that benchmarks, at least the ones that you're looking at, can be *very* flawed.

My response to their response...

>As they haven't released any information about
>the test or even the results one can just assume
>that they have done a test that doesn't have
>anything to do with the real world :(

"In the AS3AP tests... Postgres peaked at 1127.8 transactions per second with five users, and still processed at a steady rate of 1070.4 with 100 users... MySQL reached a peak of 803.48 with two users, but its performance fell precipitously under the stress of additional users to a rate of 117.87 transactions per second with 100 users."

The test is standard, so the definition of what it entails is easy to come by and they provided what the results were. So your above complaint really has no substance to it.

However, I do agree there should have been more information - namely the software configuration.

>PostgreSQL has of course also a lot of weak
>areas, like slow connection, slow insert, slow
>create/drop tables, running long multiple
>transactions where you get a conflict
>at the end (in this page/row locking is better)

Yes, Postgres is slower to connect, though not grotesqely so.

Yes, Postgres is slower to do inserts, but updates and inserts don't block. So though MySQL may be impressive if you bench insert speed without considering its impact on overall performance, in real world applications you'll see very different behaviour.

I'm not sure exactly what you're refering to when you mention "conflicts" at the end of transaction blocks. This is one area where I find MySQL falls short, since a) it has no transactions and b) it doesn't have the framework for transactions (e.g. multi-version concurency control, etc).

>We here at MySQL has always tried to design very
>fair test that no one can misinterpret or lie
>about.

Unfortunately the benchmarks MySQL uses are all single client load, which is completely meaningless in a production environment and covers up the weakest parts of MySQL (e.g. all selects stalling while an insert or update is happening).

>It's a shame that Great Bridge funds a test that
>is solely done to confuse users instead of
>telling the truth; PostgreSQL is good in some
>areas, and bad in others, just like all other
>databases.

I fail to see where they state what you are claiming they did. They merely point that a) on the AS3AP test, using the current production release of MySQL with its ODBC implementation, the performance was lackluster, and that MySQL currently lacks the feature set to run TPC-C.

>The article doesn't mention anything about this >or even with which ODBC driver they used the
>different databases.

As opposed to the benchmark on the MySQL website, where they make no mention of how the databases were compiled, what limits were set, what version of Perl was used, what version of DBI was used, what version of the DBD drivers were used, what hardware platform it was run on, what operating system it was run on, etc, etc.

And futhermore they are benchtesting an alpha product against a faily old stable release (6.5 being current over a year before these benchmarks were posted, with a half dozen released in the meantime).

>I also don't agree with the argument that they
>are not testing MySQL 3.23 as this is still
>marked 'beta'. We have here at MySQL a completely
>different release schedule on our versions...
>Compared to our release schedule, PostgreSQL 7.0 >would be called alpha.

Nice way to imply that the Postgres team doesn't properly beta test. Fact of the matter is that the 7.0 release spend many many months in its alpha/beta/gamma cycle.

There were a couple bugs that slipped through the cracks, but the subsequent releases resolved all known issues, and came very promptly (15 days between 7.0 and 7.0.1, and then 3 days between 7.0.1 and 7.0.2).

As for the development cycles being different, I agree. Though I would go the other direction. The goals of the 7.0 development series were much less ambitious than those of the 3.23 series of MySQL has; e.g. replication, two new backends, transaction support, a real locking mechanism, etc. Not to mention that they stopped adding new features to 7.0 about 6 months ago, whereas MySQL is continuing to add features (and change existing features) of 3.23.

>The net result is that the posted test is about
>as wrong as you can do a test, the important
>thing is just to get the people that reads that
>page to understand that.

Funny, I would say the epitomy of both misleading and incorrect testing would be the sql-bench suite run by MySQL.

Benchmarks

What's the saying? If you want something done right, you have to do it yourself, right?

Benchmarks are worthless, even if they are from a supposed "independant" or "unbiased" source.

Benchmarks are just that - benchmarks. They are supposed to be used as a guide.

Really, if you want real-world benchmarks of open-source or freely downloadble databases (even free version of Oracle), download them, set them up, follow the instructions for tweaking the installation for the best performance, plug some sample data in and find out for yourself. If you truly care about the quality of whatever you're developing, this simple couple of hours should be well spent.

My $0.02.

Mike

"I would kill everyone in this room for a drop of sweet, tasty beer."

Doth he protest too much?

I dunno -- I like Postgres better cause I need transactions. MySQL wouldn't work for me as well.

OTOH, I like it a lot when nerds take punches at each other. Kinda like mud wrestling, but without the swinging breasts.

Maybe a Postgres/MySQL Nerf fight?

What about DBMaker?

All this talk about DB's for linux, but nobody ever talks about DBMaker (www.dbmaker.com). It is a free database for linux, just not open source. It has support for PHP - PERL - PYTHON - ODBC - JDBC, excellent documentation, and supports transactions, locking, triggers, proceedures, and etc.

I have not had the time to evaluate it in depth, but plan to in a few weeks. Soon I will have to make a choice between MySQL, PostgreSQL, and DBMaker. Has anyone had good results with DBMaker?

Re:My response to their response...

In case anybody's interested in the benchmark that they ran, it's AS3AP, which is a pretty independant benchmark. I VERY seldom see anybody run it, but it's here:

http://www.benchmarkresources.com/handbook/5.html [benchmarkresources.com]

MaxSQL

I'm surprised that in neither of the MySQL vs Postgres SQL stories has anyone commented on the near release of MaxSQL.

MaxSQL [mysql.com], made by the mySQL folks, will have transaction support, long the last line of defense in postgresSQL's "we're better because" flamewar.

Frankly, the next year is going to be very interesting for the open source SQL market, and not so good for the big players. (I hear Informix had to lay off hundreds on the peninsula last week).

Kevin Fox

Re:show me the numbers

Following the links from the page with the article, I find MySQL's site with their own benchmarks of MySQL vs. Postgres: http://www.mysql.com/information/benchmarks.html [mysql.com].

The same page contains benchmarks against other popular databases as well.

NOTE: Pay attention to the numbers, as well as the bar diagrams. The bars are all clipped at the right side of the chart, which is misleading for a few rows.

There Ain't No Fair Benchmark

The problem which these guys have all huddled around without actually saying is that there's No Fair Benchmark.

If you visit TPC.org [tpc.org], you will find that they don't have one benchmark, but rather about four, with substantially different purposes:

• TPC-C is intended to determine throughput of a transaction processing system in creating transactions;
• TPC-H measures performance on what is intended to be an "ad-hoc DSS environment."
• TPC-R measures performance on "business reporting," intended to be more like "typical DSS reports."
• TPC-W measures performance on a "web transaction" workload.

These are all substantially different kinds of workloads. Just as MySQL and PostgreSQL are substantially different kinds of database systems.

The notion that there can be a comparable benchmark between the databases is something of which people should disabuse themselves.

If you need to have high performance transactional behaviour, I would point out that ODBC is NOT the issue; regardless of whether the SQL-CLI drivers suck, the important point is that neither database fully supports the industry standard SQL/XA [jcc.com] or X/Open DTP and XA [aurorainfo.com] standards.

Serious transaction systems commonly use transaction monitors like BEA Tuxedo or Encina, interfacing via XA to a relational database (like Oracle, Sybase, DB/2, Sleepycat DB, [bea.com] TimesTen, ...). From that perspective, MySQL and PostgreSQL are both still "toys," although SDTP - A Multilevel-Secure Distributed Transaction Processing System [sri.com] outlines how an XA interface to PostgreSQL was constructed in Common Lisp [hex.net] for use in a set of applications running on FreeBSD. [freebsd.org]

If you build a benchmark based on an application exercising the strengths of MySQL, it will probably perform badly when used with PostgreSQL, and vice-versa.

Take these systems seriously when they start supporting things like XA, and when BEA makes Tuxedo available for use with them.

More info: make your own conclusions

See the The posgres-general [postgresql.org] mailing list for more info regarding the benchmark, esp. comments from the Great Bridge Hacker Relations guy (Ned Lilly). In short:

1. The proprietary RDBMS tested "were not IBM, Informix, or Sybase".
2. One "other" propriety RDBMS "prefers to run on NT"
3. Every RDBMS tested used ODBC, so every RDBMS is handicapped by it ("apples-to-apples" comparison).
4. The test system specs: single 600MHz Pentium III, 512MB RAM, 2x18GB SCSI hot-pluggable.

Re:My response to their response...

Other resource for information on AS3AP:
[www.cwi.nl]
http://www.cwi.nl/~kwakkel/pythagoras/testpilot/ Da.html#REF80734