List:General Discussion« Previous MessageNext Message »
From:Jules Bean Date:September 1 1999 7:56am
Subject:Re: MySQL Speed ?? slower the Oracle ?
View as plain text  
I hope no one feels this is badly off-topic.  I'm finding it very
interesting, myself...

James Rogers wrote:
> 
> >> 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.

When you say 'transactional reads may occur on uncommmited writes' do
you mean reads in the same transaction as the writes (which would be
expected behaviour, IMO) or reads in other transactions (which sounds
dangerous to me)?

> 
> >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.

An alternative approach is to simulate your transaction writes until the
commit happens, so that reads within the transaction can access the
data, but reads from other transactions still access the committed
state.

Then, when the commit comes, the write lock is held for just long enough
to commit the net changes caused by the transaction.

> 
> >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.

Although, if a database is sufficiently configurable that you can tune
it for either circumstance, then it becomes unconditionally superior
(go, TCX!).

Jules


-- 
/----------------+-------------------------------+---------------------\
|  Jelibean aka  | jules@stripped         |  6 Evelyn Rd        |
|  Jules aka     |                               |  Richmond, Surrey   |
|  Julian Bean   | jmlb2@stripped        |  TW9 2TF *UK*       |
+----------------+-------------------------------+---------------------+
|  War doesn't demonstrate who's right... just who's left.             |
|  When privacy is outlawed... only the outlaws have privacy.          |
\----------------------------------------------------------------------/
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