Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

In article <MYJ26DA7yyg0EwI1_at_smooth1.demon.co.uk>, David Williams <djw_at_smooth1.demon.co.uk> wrote:
>In article <yutra7x9dht.fsf_at_mew.corp.sgi.com>, Pablo Sanchez
><pablo_at_sgi.com> writes
>>>>>>> "Michael" == Michael Segel <Mikey_at_NOSPAM.King.of.MyDomain.NOSPAM.Segel.com
>>> writes:
>>Michael>
>>Michael> [ examples deleted ]
>>Michael>
>>Michael> True, you can write these with page level locks,
>>Michael> however you won't get the performance, and you will
>>Michael> have to write extra code to compensate.
>>
>>What do you base your assertion that "you won't get the
>>performance"? The applications you talked about are all
>>applications that require a highly tuned application in
>>order to get excellent performance.
>>
>
> Imagine 1 page with 2 rows one it.
>
> 1. Page level locking
>
> User A updates row 1
> At the same time user B updates row 2. User B has to wait for
> User B to commit.
>
> 2. With row level locking
> User A updates row 1
> User B updates row 2 (with no waiting).
>
> How can 1 be faster than 2 if user B has to wait?
>

The fact is that user B probably never has to wait because the chance that user B and user A are accessing their data at exactly the same time is very small. How often do you think that I access my ATM account at the same time you do?

Now if this happened to be a table where every row is accessed at a very high frequency, then you just put 1 row per page and get on with life. If every row is accessed at high frequency, I doubt the table is very large in size.

So 1 is faster because less resources (and code) is used to lock the row vs locking the page. To lock the row you most likely have to grab a "page" lock first before getting the "row" lock. More overhead means less performance.

>>If you look at the TPC-C's (as I've mentioned, before)
>>you'll see another highly tuned (OLTP) application. If I
>>compare the *currently* submitted numbers for Sybase
>>vs Informix I see the following:
>>
>> Sybase..... 39,469 tpmC
>> Informix... 24,309 tpmC
>>
>>That's a 62% performance increase by using Sybase. Now I
>>grant you that I believe that we'll see Informix
>>Inc. publishing even higher numbers but that's not my
>>point. My point is:
>>
> I checked the results, these figure come from different machines.
> So Sybase runs their TPC benchmarks on larger hardware..so?
>

Actually, the Informix box used 28 processors, the Sybase box only used 16. What's your definition of larger?

>> For a finely tuned application, row level locking
>> does *not* matter.
> Lock granularity is still a problem. Why do UNIX kernels lock
> individual files and data structures rather than having one large
> kernel lock? Try reading
>
Well one large kernel lock would be equivalent to locking the entire database! I don't think page level locking does this :). Locking individual files is like a table lock, so looking pretty good so far. Locking individual data structures is a page lock so still looking good. What is the issue again? Row-level locking would be like locking every single cache-line in the kernel. I don't know of any OS that is dumb enough to do that.

> UNIX Systems for Modern Architectures
> Sysmmetric Multiprocessing and Caching for Kernel Programmers
> by Curt Schimmel
> Addison-Wesley
> ISBN 0-201-63338-8
>
> "If the processes in the application job mix use seperate kernel
> resources, each of whose data structures are protected by separate
> locks, then these processes will not contend for the same locks and
> will be able to run simultanueously on different CPUs, whether they
> are in user or kernel mode"
>
> Translate to databases and internal data structures.
>

See above for the translation.

--Chris
My own views Received on Tue Dec 09 1997 - 00:00:00 CET