Subject Re: [IB-Architect] Classic vs SuperServer was IB/FB Lock manager failures
Author Paul Schmidt
Hiya Jim:

On 18 Sep 2002 at 13:02, Jim Starkey wrote:

> At 12:25 PM 9/18/02 -0400, Paul Schmidt wrote:
> >
> >I also understand that SuperServer uses threads to switch back and
> >forth
> between
> >queries, rather then processes. And that it's not superior to
> >classic,
> just different
> >internally (guess it was named by the folks in marketing).
> >
> It's not that simple. Classic has many costs that SuperServer does
> not. Among them:
> 1. In classic, when process A requests a lock on dirty page
> held by process B, it must send a signal to B. The OS
> must context switch to B. B must then write out the dirty page
> and downgrade the lock. The OS then context switches
> back to process A which gets the lock then reads the page.
> So classic has an interprocess signal, two context switches,
> a page write and a page read. Superserver does nothing.
> 2. Creating an attachment on classic requires creating a new
> OS process. SuperServer does not.
> 3. Before classic can do anything it must initialize itself,
> attach the shared memory and semaphores for the lock manager,
> open the database file(s), read the database header, scan
> RDB$PAGES to find keep stuff, read RDB$RELATIONS,
> RDB$RELATION_FIELDS, and RDB$FIELDS to compile a user
> request, and read RDB$FORMATS for each table format version it
> encounters. SuperServer does this only once.
> 4. Classic must compile every statement it sees, even if a
> SQL statement had been compiled 10,000 times that day.
> Compilation involves a precedence driver parse allocating
> a language node per BNF production, a compilation process
> requiring at least another language node per BNF production, an
> optimization process involving stream, rivers, and tributaries.
> SuperServer does the same damn thing, but it doesn't need to.

It would seem to me, that we could cache the compiled statements, and simply
empty the cache when a DDL statement gets executed or the database is restarted.
However saying that I have run big complex queries that prepared in under .1 sec,
so caching the prepared statement might save .01 sec per query, and the safety of
not having to compare almost identical statements and goofing it up, isn't worth the
time saved, or the memory used to cache the results.

> SuperServer unlike classic, is constrained by the number of sockets an
> OS will allow a process. Whether this is more or less than the number
> of processes supported by an OS depends from OS to OS.
> Classic has always suffered from scalability under a heavy
> contentious update load. SuperServer handles that nicely.
> If you believe, as I do, that the universal platform is the
> web, the architecture has to handle very large number of very
> short connections. Perhaps classic with connection sharing
> is an answer. I'm betting on single process/multi-thread
> architecture, albeit one that Borland's grubby, greasy fingers
> have never fouled.

It's likely that more and more stuff is going to be web based, however not all of
those connections are going to be short. Perhaps connection pooling makes more
sense, rather then creating and dropping 500 connections per minute, you have 50
connections, when someone needs one, they get "connected" to one of those 50,
and if all 50 are being used then it creates additional ones as needed.

The question with a single multi-threaded process, is that will an SMP machine be
able to run different threads on different CPUs? If so, what's holding SuperServer
back, from being able to do that now?

Paul Schmidt, President
Tricat Technologies