Forum OpenACS Q&A: Yet another PostgreSQL x MySQL Question

1: Yet another PostgreSQL x MySQL Question

Posted by Roberto Mello on 10/18/00 06:00 PM

Hi all,

Michael pointed me to this page (http://www.mysql.com/documentation/mysql/comme nted/manual.php?section=Compare_PostgreSQL) in the MySQL docs and I thought I should ask the PostgreSQL gurus that live around here.

PostgreSQL has some more advanced features like user-defined types, triggers, rules and some transaction support (currently it's has about same symantic as MySQL's transactions in that the transaction is not 100 % atomic) .

What? PostgreSQL's transactions are not atomic? Is this yet another ultra exaggeration by the MySQL implementors? They compared it to MySQL's semantics, which is "I see you wanted a transaction but can't do nothing about it".

However, PostgreSQL lacks many of the standard types and functions from ANSI SQL and ODBC. See the crash-me web page for a complete list of limits and which types and functions are supported or unsupported.

I found their "crash me" test to be highly misleading but wanted to know what are these standard types and functions that they say PG lacks.

Normally, PostgreSQL is a magnitude slower than MySQL. See section 12.7 Using your own benchmarks. This is due largely to they have only transaction safe tables and that their transactions system is not as sophisticated as Berkeley DB's. In MySQL you can decide per table if you want the table to be fast or take the speed penalty of making it transaction safe.

This is becoming more frequent now that MySQL comes linked to Copycat (Berkeley DB). Can anybody comment on Berkeley DB? Is the above statement true? I thought you could tell PostgreSQL if you wanted the super-safe mode or a less-safe mode. Am I wrong? What about the "one order of magnitude slower" deal?

2: Response to Yet another PostgreSQL x MySQL Question (response to 1)

Posted by Jonathan Ellis on 10/18/00 07:30 PM

As to speed issues, the most credible benchmark I've seen so far shows MySQL to be faster up to about 7 users, after which its performance degrades much faster than postgres's. You've probably already seen the article and followup; here's the summary:

My experience has been that it's generally safe to say that MySQL people are full of crap when making claims such as "MySQL is 10x faster" or "postgres doesn't support atomic transactions, either."

3: Response to Yet another PostgreSQL x MySQL Question (response to 1)

Posted by Don Baccus on 10/18/00 11:55 PM

Postgres transactions are 100% atomic with a few minor exceptions. DML statements like "DROP TABLE" can't be rolled back, for instance. Restrictions like this are common even in commercial systems like Oracle.

But basic INSERT/UPDATE statements are atomic and can be fully rolled-back. Postgres doesn't follow the SQL standard in that explicit BEGIN/END statements are required to wrap statements within a transaction - Illustra was the same. So it would be fair to describe Postgres as having non-standard SQL for transactions, but it their statement is incorrect.

As Jonathon hints, the MySQL folk don't score highly on personal integrity and basic honesty issues. That, in my mind, is enough reason to stay away from their offerings.

4: Response to Yet another PostgreSQL x MySQL Question (response to 1)

Posted by Adam Farkas on 10/19/00 12:32 AM

Don, I think that it may be premature to call the ethics of the entire MySQL community into question.

At the Gartner conference in Oralndo I met with a couple of folks from NuSphere, the company that is building a variety of enhancements into MySQL (including transactions & improvements in the locking model)

We spoke about benchmarking methodology, and this group, at least, takes the topic _very_ seriously. They talked my ear off about TPC, and the _correct_ (read: expensive) way to run a series of benchmarks. My gut sense is that when their product is ready, we'll finally get to see a real head-to-head, apples-to-apples comparison that we've heretofore lacked.

They seemed genuinely interested in real results, not marketing hype.

5: Response to Yet another PostgreSQL x MySQL Question (response to 1)

Posted by Roberto Mello on 10/19/00 12:41 AM

IMHO, it's not the community that has a problem... it's whoever writes their documentation. From there, the misinformation is spread and it's much harder to stop a rumor after it's been started than before it's started, especially if the rumor is not true.

When MySQL is ready ProstgreSQL will be just a couple more light years ahead 😊

6: Response to Yet another PostgreSQL x MySQL Question (response to 1)

Posted by Don Baccus on 10/19/00 11:56 PM

Adam - when I say "MySQL folk", I mean the implementors, in particular Monte (whose last name I forget) who seems to be their primaryspokesperson and lead.

7: Response to Yet another PostgreSQL x MySQL Question (response to 1)

Posted by Todd Gillespie on 10/20/00 02:49 AM

Normally, PostgreSQL is a magnitude slower than MySQL. See section 12.7 Using your own benchmarks. This is due largely to they have only transaction safe tables and that their transactions system is not as sophisticated as Berkeley DB's. In MySQL you can decide per table if you want the table to be fast or take the speed penalty of making it transaction safe.

This is becoming more frequent now that MySQL comes linked to Copycat (Berkeley DB). Can anybody comment on Berkeley DB? Is the above statement true? I thought you could tell PostgreSQL if you wanted the super-safe mode or a less-safe mode. Am I wrong? What about the "one order of magnitude slower" deal?

You _can_ tell PostgreSQL to change transaction isolation level, but the less-secure mode only allows committed changes to be read by uncommitted transactions - it's not like a complete breakdown of atomicity. And I've never noticed a speed difference between the two, (except me trying to figure out which is needed in a given action).

As for the whole 'order of magnitude slower b/c of safe tables' argument, I can quote someone on slashdot who responded to the latest IIS-is-faster-than-Apache news bit with the rebuttal: "You can't serve pages at any speed when your webserver's crashed." Not a precise analogy, but I hope you get the point....

As for Berkely DB, I really can't say much beyond that Perl is linked to it on some systems for use in their tied-DBM objects (for those who don't know Perl, the method of which I speak treats a disk object as a normal hash table). I never much thought of them as being all that amazing - worked fine, but I never saw any claims of concurrency control and rollback logs.