List:General Discussion« Previous MessageNext Message »
From:James Rogers Date:September 1 1999 3:40am
Subject:Re: MySQL Speed ?? slower the Oracle ?
View as plain text  
>> On Tue, 31 Aug 1999, Michael Widenius wrote:
>> 
>> > It's only when you mix many slow queries with writes and can't let the
>> > readers wait as MySQL will not perform as good as Oracle)
>> 
>> Doug Salot wrote:
>> Is this because MySQL does table locking and Oracle does row locking, or
>> is it that Oracle does some sort of predictive query prioritizing?
>> 
>At 10:26 PM 8/31/99 +0100, Jules Bean <jmlb2@stripped> wrote:
>My understanding of it is this:
>
>1) Yes, row locking helps.


Correct.  This allows a very high number of table writes to be interleaved
without blocking.


>2) Oracle uses a 'read uncommited' isolation level which essentially
>allows read access to coexist with write access, if you don't mind
>reading broken data from time to time (I don't understand this - it
>sounds like a potential source of subtle and dangerous bugs to me, but
>that's what read uncommited means).


This isn't quite right, and the truth is fairly complex.  Oracle (last I
checked) uses a multiversioning timestamp protocol.  This class of
transaction protocols is really quite elegant, their only drawback being
that they allow for the possibility of cascade aborts (though, there is no
such thing as a perfect protocol).  The primary advantage of this protocol
is that it allows multiple transactions to be interleaved on a single row.

Without explaining the protocol in detail, I can say that the protocol is
complete and guarantees a consistent view of the database.  While it is
true that transactional reads may occur on uncommited writes (indeed writes
may occur on uncommited writes with MVTS), this is actually a correct
behavior for the protocol; most "optimistic" locking protocols share this
feature.  It should be noted that in most multiversioning protocols,
non-transactional reads only occur on commited data.


>3) Transaction processing allows you to hold off your write locks until
>the last minute, which may give a performance gain.


Err...how so?  In the case of Oracle, row writes occur as each row is
accessed; a commit writes no data to the database (other than a footnote in
the transaction log).  Generally speaking, writes are non-blocking in a
MVTS database and a write lock on a row is held no longer than it takes to
update that row.  Any performance gains in this department are
architectural rather than being an intrinsic property of transactional
databases.


>I'd be very interested if someone else had some more authoritative data
>on how Oracle is superior (or whether it really is at all...)


The architecture of Oracle is optimized for the maximum level of
transaction concurrency possible.  MySQL is optimized for maximal levels of
read concurrency.  Apples and oranges.  Performance has more to do with
using the right database for the job i.e. "superior" is context sensitive.

-James Rogers
 jamesr@stripped

Thread
MySQL Speed ?? slower the Oracle ?Ben-Nes Michael31 Aug
  • Re: MySQL Speed ?? slower the Oracle ?Thimble Smith31 Aug
  • Re: MySQL Speed ?? slower the Oracle ?Paul DuBois31 Aug
    • Re: MySQL Speed ?? slower the Oracle ?Ben-Nes Michael31 Aug
    • Re: MySQL Speed ?? slower the Oracle ?info31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?sinisa31 Aug
  • Re: MySQL Speed ?? slower the Oracle ?Ben-Nes Michael31 Aug
    • Re: MySQL Speed ?? slower the Oracle ?Michael Widenius31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?Doug Salot1 Sep
        • Re: MySQL Speed ?? slower the Oracle ?Jules Bean1 Sep
          • Re: MySQL Speed ?? slower the Oracle ?James Rogers1 Sep
        • Re: MySQL Speed ?? slower the Oracle ?Jules Bean1 Sep
        • Re: MySQL Speed ?? slower the Oracle ?Michael Widenius1 Sep
  • Re: MySQL Speed ?? slower the Oracle ?Cary Collett31 Aug
  • Re: MySQL Speed ?? slower the Oracle ?Rick Moore31 Aug
    • Re: MySQL Speed ?? slower the Oracle ?Steve Ruby31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?Jules Bean31 Aug
        • Re: MySQL Speed ?? slower the Oracle ?Michael Widenius31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?sasha31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?Jules Bean31 Aug
        • Job Available - Perl/MySQLArlen1 Sep
      • Re: MySQL Speed ?? slower the Oracle ?Michael Widenius31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?Jules Bean1 Sep
      • Re: MySQL Speed ?? slower the Oracle ?Rick Moore1 Sep
      • Re: MySQL Speed ?? slower the Oracle ?Sasha Pachev6 Sep
    • Re: MySQL Speed ?? slower the Oracle ?Orlando Andico31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?ed31 Aug
        • Re: MySQL Speed ?? slower the Oracle ?Cary Collett31 Aug
          • Re: MySQL Speed ?? slower the Oracle ?ed31 Aug
        • Re: MySQL Speed ?? slower the Oracle ?Orlando Andico31 Aug
          • Re: MySQL Speed ?? slower the Oracle ?Michael Widenius1 Sep
            • MySQL, GPL and RedHatMike Wexler1 Sep
              • Re: MySQL, GPL and RedHatThimble Smith1 Sep
              • MySQL, GPL and RedHatMichael Widenius1 Sep
    • Re: MySQL Speed ?? slower the Oracle ?James Manning31 Aug
      • Re: MySQL Speed ?? slower the Oracle ?Michael Widenius1 Sep
      • Re: MySQL Speed ?? slower the Oracle ?Ričardas Čepas1 Sep
  • Re: MySQL Speed ?? slower the Oracle ?Ben-Nes Michael31 Aug
  • Re: MySQL Speed ?? slower the Oracle ?sasha31 Aug
Re: MySQL Speed ?? slower the Oracle ?cmaurand1 Sep