Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!shadooby!accuvax.nwu.edu!tank!cs_bob@gsbacd.uchicago.edu
From: cs_bob@gsbacd.uchicago.edu
Newsgroups: comp.databases
Subject: Single Server Bottlenecks (was RE:ORACLE REL 6, INGRES REL 6)
Message-ID: <3176@tank.uchicago.edu>
Date: 11 May 89 15:27:41 GMT
Sender: news@tank.uchicago.edu
Distribution: usa
Organization: University of Chicago Graduate School of Business
Lines: 55



 
In article <4185@sybase.sybase.com>, tim@phobos.sybase.com (Tim Wood) writes...
 
>In article <1588@bilpin.UUCP> mcvax!ukc!icdoc!bilpin!nick (Nick Price) writes:

>>particularly where bottle-necks are introduced by a single server
>>architecture. 
> 
>Can you give examples of these bottlenecks?  Is your point theoretical
>or from experience with a particular system?  If theoretical, could there
>be a server design that exploits SMP architecture?
> 
One example, from experience with Ingres Release 6.1, running under VMS,
which applies to SINGLE servers:

Imagine an small interactive, multi-user system where a typical mix of users
includes 5 interactive CPU bound non-DBMS jobs and 10 Ingres users. 
Under Ingres 5.0, each of the ten Ingres users had their own backend which
competed with other processes for CPU. Thus, in the extreme case where all
ten Ingres users become CPU bound, roughly 2/3 of the processor will be
available to the backends (that is, 10 out of 15 CPU bound jobs will be Ingres
backends). Under version 6.1, only 1/6 of the processor will be available,
since there is only one backend.

It is not feasible to raise the Ingres server to a higher priority, since
large reports/sorts do consume large amounts of CPU and can starve interactive
users. The only practical solution in this case is to start several backends,
but there are a couple of problems with this approach:

a) it cannot be done dynamically. That is, one cannot improve a bad situation
post hoc by starting new servers, because the current DBMS jobs running under
one server cannot be off-loaded to a new one. Even worse, in the true single
server environment, RTI provides FAST COMMIT and GROUP COMMIT options which
can only be disabled by taking down the server. If a server is running
with these features, designed to dramatically improve OLTP in particular,
no new servers can be started until it is taken down.

b) a corollary to this is that any multi-server configuration loses the
advantages of FAST COMMIT (including VAX clusters with one server per node).

An obvious solution to this would be to run the Ingres server at priority 5,
but have it monitor its own usage vis a vis other processes in the system
and periodically give up the processor if it is starving other processes.
While obvious, this solution is not exactly simple, and at the present time
the Ingres 6.1 server can very definitely become a system bottleneck.

Note also that in the example cited above, one would have to increase overall
CPU throughput dramactically to acheive the equivalent worst case capacity
provided under version 5.0 . While the worst case will probably never be
achieved, it has become fairly common for use to see an Ingres server running
on a VAX 8650 with only 4 or 5 Ingres users to become CPU bound while only
getting 15-20% of the available CPU. Moreover, this happens when the Ingres
users are competing with only 4 or 5 other processes for the processor.