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

In article <66knes$h1u@cello.hpl.hp.com>, Chris Ruemmler <ruemmler@cello.hpl.hp.com> writes >In article <MYJ26DA7yyg0EwI1@smooth1.demon.co.uk>, >David Williams <djw@smooth1.demon.co.uk> wrote: >>In article <yutra7x9dht.fsf@mew.corp.sgi.com>, Pablo Sanchez >><pablo@sgi.com> writes >>>>>>>> "Michael" == Michael Segel <Mikey@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