Re: PostgreSQL vs MySQL

On Thu, 2003-10-09 at 13:30, David Griffiths wrote:
> 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.

Actually it only kinda sorta has acid. As Jeff mentioned, and it can be
expanded upon, mysql has a nasty habit of transforming invalid data into
something that will insert into a table and not telling you about it. I
think Josh mentioned reports that it ignores some constraint
definitions. And then theres the whole mixing MyISAM and InnoDB tables
completely breaks the ability to rollback transactions...

>
> "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.

Well, I've yet to see MySQL benchmark themselves vs. the big boys using
InnoDB tables, I'm only guessing that it's because those tables are
slower. (Well, guessing and calling upon experience) Sure there may be
work arounds, but that does add a certain complexity. (Bonus for us,
PostgreSQL is just complex from the get go :-P )

>
> "PostgreSQL supports constraints. MySQL doesn't; programmers need to
> take care of that from the client side"
> Again, InnoDB supports constraints.
>

We've seen evidence it doesn't. If they've fixed this great. Of course
I'll quote from the mysql docs

"InnoDB allows you to drop any table even though that would break the
foreign key constraints which reference the table."

last I knew it did this silently and without warning. there are other
issues as well, so it's support is relative...

> "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.
>

Just don't mix InnoDB and MyISAM tables together or you could end up in
a world of trouble... its unfortunate that this breaks one of the main
keys to building a DBMS, namely hiding implementation details from the
end users.

> Maybe the Postgres community needs an anti-FUD individual or two; people
> that know both databases, and can provide the proper information for
> answering questions like this.

Well, among the major advocacy folk we do have a mantra about no FUD,
but these are public lists so we cant really stop people from posting.
Of course this overlooks the fact that different people interpret
different information differently. (heh) Take this quote I saw posted
in a non postgresql forum a while back: "MySQL doesn't fully support
subqueries" which of course created a slew of posts about FUD and
postgresql users being idiots. If course, when the posted responded back
with the question "Can mysql do subselects in the SELECT, FROM, and
WHERE clauses like postgresql, and nest subselects within those
subselects?" it stopped everyone in their tracks...

> A section in the docs would help as well.

In the docs no, on techdocs, maybe.

> Yes, I know many of the people advocating Postgres do not want to
> compare themselves to MySQL (but rather to Oracle, Sybase, DB2, etc) ,
> but the volume of responses on a thread like this indicates that the
> comparison is going to happen regardless. Better to nip it in the bud
> quickly than let it go on over 3-4 days.
>

It was due to the help of postgresql users that the following site has
become available: http://sql-info.de/mysql/gotchas.html
I'd suggest you look it over if your trying to evaluate a switch from
Oracle to MySQL.

And anyone is welcome, actually encouraged, to correct erroneous
information they see posted about any system on these lists. God bless
if you're willing to try and follow every list every day to watch for
these types of posts.

> One last observation: someone looking at both databases, reading those
> posts, might get a bad impression of Postgres based on the inconsistency
> and incorrectness of some of the statements made about MySQL. If a
> salesperson provides misinformation about a competitors product and you
> find out about it, that salesperson has most likely lost a customer.
>

Unfortunate that you'd attribute anyone who posts on these lists as a
sales person for postgresql...

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL