LinuxSA Mailing list archives

  From: Carlo <carlo@active-ideas.com.au>
  To  : Mark Newton <newton@atdot.dotat.org>
  Date: Fri, 16 Jun 2000 16:26:41 +1030

Re: Postgresql vs MySQL

Mark Newton wrote:
> 
> On Fri, Jun 16, 2000 at 03:57:16PM +1030, Carlo wrote:
> 
>  > This withdrawal example(select and update). If it is done in one
>  > thread, which is usually the case, you can lock the tables you will be
>  > selecting and updating etc.
> 
> But that's an abysmally sub-optimal solution on a busy site:  By locking
> the tables, you're guaranteeing that NOBODY ELSE can do any deposits or
> withdrawls anywhere in your entire bank while that transaction is in
> progress.  You're essentially turning a nice parallel thread-based access
> model into a degenerate sequential model, and that'll have ridiculous
> performance implications as your site scales.
> 
> You need *row locking*, not *table locking*.  Even better, locking for
> individual cells.  Anything with worse granularity than a row is going
> to slow you down to the point where any speed advantage you get by using
> mysql is utterly worthless.


Hmm... Have to agree with that.

> 
>  > The query with the deposit will then wait
>  > and not time out until the tables are unlocked or until the thread is
>  > closed(which ever comes first). More programming work than an RDBMS to
>  > make sure its all done correctly, but it can be done and will still end
> 
> And, of course, if you accidentally leave something out, you can either
> reach the point where your database deadlocks against itself because you
> forgot to release a lock, or you can do transactions which you've forgotten
> to acquire locks for and produce the exact data-smashing situation which
> made locks attractive in the first place.
> 
> It's much easier to let the database care about that stuff.

Um.. Yep that can happen.

> 
>  > up cheaper/quicker than most RDBMS.
> 
> I disagree.  It'll only be quicker if your site isn't very popular (you
> do want popular sites, don't you?).  Also, it'll almost never be cheaper:
> You actually need to go to special lengths to do your locking, extra
> testing, extra development, etc, etc, and someone has to pay for that.  If
> you use a database which supports transactions, however, you get all that
> for free because the database engine is looking after locks for you.

Ok if your going to be doing transactions, don't use Mysql. But doing
simple reads/writes that don't get in the way of other tables, over the
internet, Mysql is the way to go. Do you agree with that Mark. 

> 
>  > > I submit that it's possible to design around that limitation, so you
>  > > can still have fast response.
>  >
>  > Can you tell me, give some examples. Im very interested in more ideas
>  > on ways to do things.
> 
> That all depends on the specific situation, doesn't it? :-)  That's
> why DBAs and database application authors get paid lots of cash to
> design good systems.
> 
> In the end, though, it'll come back to data modelling, and a thorough
> understanding of how an RDBMS implements the various things you ask it
> to do. For example, by simply changing the order of a comple of nested
> SELECT statements you can sometimes cut down on the amount of unindexed
> data your RDBMS needs to search to perform a query by a factor of hundreds
> of thousands.  See the ars-technica article link Dan provided a couple
> of days ago for some good examples of that (sorry, I deleted his message
> after reading the article)

If anyone has this link can they please send it to me.

> 
>     - mark
> 
> --------------------------------------------------------------------
> I tried an internal modem,                    newton@atdot.dotat.org
>      but it hurt when I walked.                          Mark Newton
> ----- Voice: +61-4-1620-2223 ------------- Fax: +61-8-82231777 -----

-- 
Carlo Mattiazzo

-- 
LinuxSA WWW: http://www.linuxsa.org.au/  IRC: #linuxsa on irc.linux.org.au
To unsubscribe from the LinuxSA list:
  mail linuxsa-request@linuxsa.org.au with "unsubscribe" as the subject