Re: Need Some Recent Information on the Differences between Postgres and MySql

On Fri, Jun 25, 2010 at 4:58 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> Next up: PostgreSQL stores its system catalogs in transaction safe
> table types, like everything else it stores.  MySQL stores its table
> defs in myisam, even if the whole of the db you create is innodb and
> innodb is the default.  System crash in the middle of DDL?  Might lose
> a table or two.

This is not true. MySQL stores users, acl, etc in MyISAM tables. In
almost all setups users and acl do not change often so the crash
sensitivity is not a big issue. I have dealt with (and still do deal
with) horribly abused MySQL instances and I very very rarely run into
issues with corruption on the system schema.

MyISAM in not involved in the storage of data about Innodb. MySQL
table definitions are stored in .frm files. Alterations to table
definitions in MySQL (in general) are done by building a temporary
table with the new definitions and the existing data and then shell
gaming the files in. It is possible to have issues from a crash, but
it is very rare.

>
> Next up: MySQL has optimizations made without proper testing.  For
> example, see this bug:
>
> http://bugs.mysql.com/bug.php?id=28591
>
> This "optimization" made MySQL ignore the DESC keywork in innodb
> tables.  It was pushed into live, production ready MySQL code
> midstream in version 5.0.28 on 2007-08-02.  MySQL GA (i.e. production
> ready) release had been made two years previously in March of 2005.
>
> Fix was pushed out on 2007-09-24, nearly 60 days later, to version
> 5.0.48.  Problem solved right?  Well, not really, according to
> http://bugs.mysql.com/bug.php?id=31001 it wasn't quite fixed.  The
> actual fix gets pushed out on 2008-09-12.
>
> This shows several things about the MySQL release philosophy, at least
> at the time.  1: Introducing performance enhancments without thorough
> testing in a production release is A-OK.  2: The fix may or may not
> actually work when it does get applied. and 3: It can take about a
> year to get that fix in place.
>
> Things may be a LOT better by now.  I'd certainly hope so.  But I have
> no real confidence or evidence of such an internal change.

Sun/Oracle has improved things a lot. Long standing bugs are being
closed and it feels like more care is being put into releases.

--
Rob Wultsch
wultsch(at)gmail(dot)com