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

In article <66knes$h1u_at_cello.hpl.hp.com>, Chris Ruemmler <ruemmler_at_cello.hpl.hp.com> writes
>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.c
 om
>>>> 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?
>
  Sybase box had much more memory, different disks..   These figures can't be compared.

>>> 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
  Psst! Wrong Locking individual data structures is row level locking.   Locking one entry in a list is one row. Surely 1 row = ! C struct?   

>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
 

-- 
David Williams