Re: postgre vs MySQL

On Wed, Mar 12, 2008 at 1:02 PM, paul rivers <rivers(dot)paul(at)gmail(dot)com> wrote:

> - Auto_increment columns as pkeys in InnoDB tables are practically
> required, yet severely limited scalability due to how a transaction
> would lock the structure to get the next auto-increment (significantly
> improved in 5.1)

Pretty sure they implemented the fix for that in an early 5.0 release.
I remember chatting with Heikki Turri about it.

> There are other complaints you'd expect people to have, but don't seem
> to get talked about much, because people are so used to (from my point
> of view) working around them. For example, statistics on an InnoDB table
> are calculated when the table is first accessed, but not stored
> anywhere, so there are extra costs on database startup. The backup issue
> with InnoDB has already been covered. Tablespace management in InnoDB
> seems exceptionally primitive, and is helped somewhat by the
> tablespace-per-table option. There are many more, again imho.

I remember seeing something about some problems that using the
tablespace per table option on some mysql site... goes to look...
paraphrased from the Mysql Performance Blod... Using the
innodb_file_per_table=1 setting really tends to work against you, as
you tend to get lots of bloated tables over time. If all your innodb
tables are in the same file, then when one frees space, another can
use it. with files per table, you can't recover space this way.

My real complaint with InnoDB is it's a red headed step child. If
mysql supported only innodb, it would be a very different database,
and probably a bit simpler as well. no need to worry about how you
state fk-pk relationships (currently column level references are
silently dropped for innodb OR myisam). If there was a run time
switch that said "use only innodb and use syntax that's sane" I'd
probably be willing to test that out.