Re: installing Oracle on Debian AMD64
Adam Skutt wrote on 31/10/2005 18:33:
> 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.
True.
> Postgresql locks at less than table-granularity all the time.
Fine. I never said it wouldn't.
>>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
I agree with you there. And in my opinion, this is definately a major
drawback.
>>
>>>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.
for as long as the write takes.
>>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.
Actually, I didn't exactly get it backwards. MyISAM _is_ fast to write
(and fast to read) as long as you only write or only read. If you try to
do both concurrently, reads and writes get in each others way, which
slows things down a lot. Reads are not possible while writing and writes
can't get their locks set while some process is still reading.
> That's ignoring all the other horrible things about MyISAM.
There are certainly other drawbacks in MyISAM, but I wouldn't call them
"horrible things".
>>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.
4.0 is far older than a year and fixed many things which where
problematic in 3.x. 5.0 has reached GA a few weeks ago and its release
candidates (and even its betas) effectively were really stable. That a
lot of shops still run 3.x is their problem, the upgrade can easily be
done with only a few (like 2) minutes of downtime if you are careful. If
you don't want to be careful, it still doesn't take more than 15 minutes
of downtime (and including compiling the complete mysql suite, not more
than 30 minutes).
> 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.
I simplified and possibly chose the wrong words.
A transaction is there to do multiple updates in an atomic way (i.e.
they are executed without interuption by other updates or reads). The
side effect (on which I perhaps put to much emphasis) is that if a
single update in the transaction fails, the whole transaction fails (and
should be rolled back).
> The whole point of a transaction is to prevent an inconsistent database
> in the first place.
Which doesn't contradict me.
> And MySQL forbids transactions in places that are
> totally nonsensical. For example, any user-created lock (i.e., CREATE
> LOCK) invalidates the transaction context.
I don't understand what you would want a lock for inside a transaction.
I mean either the transaction is atomic (and therefor doesn't need a
lock) or it isn't (and might need one). Maybe I overlook something, but
as far as I can tell, a transaction shouldn't need to set locks.
There might be other things which invalidate transactions in MySQL, but
I didn't come across anything I would have needed inside a transaction
until now.
> 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.
Just to make sure I understand you correctly:
You say that if you use a user set lock inside a transaction, the
transaction context is invalidated. And if I understand you correctly,
this means that the actions taken inside the transaction are executed as
if outside a transaction and therefor not automatically rolled back
anymore. Right?
Of course, manually rolling back multiple changes is a major PITA. I
certainly agree with you there.
> 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.
OK, this sounds different to what you said above. What you say here is
that you can't do something along the lines of:
<set lock>
<...>
<begin transaction>
.....
<commit transaction>
<remove lock>
If that is true (I can't tell, I never tried that), this is definately a
problem.
> 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
What I meant is that a transaction is (or rather: should be) either
completely executed or completely rolled back if an error occured.
> --the purpose of a transaction isn't to just to note scope.
> It's to provide an atomic context.
True.
> And MySQL forbids you from having an atomic context everywhere it's
> useful, which is a major shortcoming.
I still don't really see what you mean here, but I never intended to say
transactions are implemented perfectly in MySQL.
> 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.
If I understood this restriction correctly, it has another nasty effect:
You can't do multiple transactions inside a UDF in PostgreSQL. That's
bad IMHO. Can't say yet wether MySQL is better in this regard because I
didn't need this in my apps which use MySQL yet.
>> 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.
Which part of "the above"? You mean with regards to transactions?
User based locking is used a lot by the apps I maintained over the past
years, mostly without transactions. Most apps which use transactions
don't use user based locks. But I by no means think that these apps show
each and every possible use of databases.
As I already said:
Look at what _you_ need. Then check which RDBMS provides everything you
need. Then check which gives you the best use/cost ratio. There is no
best RDBMS (though possibly one with the best standard compliance or the
most features). Each has its own shortcomings and advantages.
cu,
sven
Reply to: