Re: installing Oracle on Debian AMD64
Sven Mueller wrote:
It locks finer than a single column?
MySQL only locks that fine if you're blessed enough not to be using
MyISAM. Postgresql locks at less than table-granularity all the time.
Full-text indexes and real-time replication support (or lack of mature
solutions for this) really are downsides of Postgres.
Too bad FTI on MySQL requires using nasty MyISAM
It is also easier to be fast writing if you lock the
whole table and prevent others from accessing it while you update
things. Slows down reading to stopped while you do a write though.
More like stops every other read and write.
True. But MySQL gives you the choice: MyISAM tables are fast in writing
and might get slow if you concurrently try to read.
No, you have that backwards. They're incredibly quick to read, and
absolutely kill /all/ concurrency when writes come in, because it's a
table-write. Sure, that one write may be fast, but it doesn't take many
concurrent writes or a high read load to drop your performance to almost
nothing.
That's ignoring all the other horrible things about MyISAM.
I really don't like this repeated Postgres is better than MySQL bashing
that is mostly based on long-outdated versions of MySQL.
MySQL 5 isn't even a month old, and MySQL 4.1 hasn't been GA for even a
full year. It's not "long-outdated". And tons of shops still have
3.3.x in production.
Both Postgres
and MySQL are good database systems. However, each seems to have its own
pro´s and con´s, and it all depends on what _you_ want to do with your
database. For me, real-time replication is far more important than some
obscure ACID things (Adam Skutt said MySQL had tons of things which can
cause transaction invalidation, but honestly: transactions are there
specifically to catch these invalidations instead of running headlong
into an inconsistant database, and I actually never experienced a single
transaction failure with MySQL).
This kind of statement shows you don't understand what a trasnscation is
about.
The whole point of a transaction is to prevent an inconsistent database
in the first place. And MySQL forbids transactions in places that are
totally nonsensical. For example, any user-created lock (i.e., CREATE
LOCK) invalidates the transaction context.
So you say, what's the big deal? No other transaction can mess with
yours, because of the lock. But what if your transaction fails? Now,
the onus of rolling back any changes is on the user, not the DBMS. For
complicated transactions (hell, maybe even simple ones, depending on
schema), that's a major PITA.
So no, it is a big deal, if you want to do more than just shove data
in/out of a DB. Because not being able to keep a transaction inside a
lock will cause an inconsistent database.
The point of a transaction isn't to catch these invalidations. Frankly,
I don't even know what you mean there, it makes no sense--the purpose of
a transaction isn't to just to note scope. It's to provide an atomic
context.
And MySQL forbids you from having an atomic context everywhere it's
useful, which is a major shortcoming.
To be fair, PostgreSQL has one major shortcoming with it's transactions
I forgot to mention: a UDF always has an implicit BEGIN/END transaction
associated with it, which makes them impractical for bulk data loading.
This is a big deal, as committing transactions in PostgreSQL is slow.
But at least my data is still intact.
And even though Adam said MySQL would
lack working row-locking, this has never failed for me.
It doesn't, because the user based locking is useless, mostly due to the
above.
Adam
Reply to: