From: markk@nic.ddn.mil (Mark Kosters)
Subject: Re: socket-to-socket connections...
Organization: National Radio Astronomy Observatory
Date: Fri, 11 Sep 1992 18:57:49 GMT

Jud Kenney writes...
> 
> A few months ago there was a discussion here about improving performance
> across a network with ingres, and one of the items was on socket-to-socket 
> connections...
> 
> Someone posted a summary of the discussion; does anyone have that archived,
> and could you repost it?

Here's the summary (I think this is the one you wanted).  My idea
of queing up INGRES connections and forking off children processes whenever
a program was called to "speed up connections" never worked. Although the 
TCP connection remains intact when transfered to the child, I surmise that 
the server must also look at the tid of the process which is different 
than the parent that issued the connection. Thus, when issuing a select from 
the child, the server checks the pid of the child which is different than 
what it expects and consequently the communications channel to the server 
fails.

Thanks,
Mark Kosters

------------------------ SUMMARY -------------------------------------

Many thanks to all who replied, as follows:

noel@essex.ac.uk (Wilson N G)
billc@ingres.com (Bill Coffin)
brad@ingres.com 
gnb@duke.bby.oz.au (Gregory N. Bond)
bankilco@ux2.cso.uiuc.edu (Bank of Illinois - Invoice)

Summary:

1) Shave off some time by bypassing iigcn by setting the II_DBMS_SERVER to 
   the correct tcp port.
I am registering my iidbms with the name server since I've found that 
using the II_DBMS_SERVER to bypass iigcn really did not change the 
connection time by much :(.

2) Set the quantum down in the rundbms.opt file to 500.
I checked my quantum and have mine set to 500 also.

3) Set up a second-level server.
Unfortunately, our applications query everything in the database so
a specialized server would not be beneficial (It would be too complex!).

What I'd like to do is something like the following:

EXEC SQL CONNECT <db>;
pid = fork()
if (pid == 0)
  execl ("child", "child", NULL); <== child would be able to select data
else
  while (wait(0) != pid);

Since the connection to the iidbms is a tcp connection and children 
inherit the parent's environment the INGRES connection should still be
there for the child to use after the fork.  Each child will have its
own connection (ie.  the parent will cue up connections and give them out 
when needed) so the parent absorbs all the time starting the connections.
Unfortunately, I can't get it to work so far and was wondering if anyone 
else has had any success ???


Mark Kosters
Network Information Center
markk@nic.ddn.mil

--------------------------------------------------------------------------
Original Message:

Our ingres server has to endure a *lot* of short connections from our users
under role accounts.  Unfortunately, the time it takes to connect to a 
database is ten times the time it takes for a user to select/retrieve the 
information they desire. 

I'd like to minimize the connection time so I'm wondering if anyone out 
there has tried to store multiple connections under some daemon. Then an
application program can then call the daemon to get the appropriate TCP 
handle(s) for that session, and do the selections/retrievals from the 
database without consulting iigcn and going through the iidbdb.  
(I'll know that the user is a valid INGRES user and the location of the target 
database). Hopefully, this would be faster.

I've run both sysmod and optimizedb on the system catalogs in both the 
iidbdb and the target databases. While connecting, the trace output for 
disk io and locking are fine (minimal disk io and shared locks). So I'm
fairly certain that the database configuration is fine.

I'm running INGRES 6.4 under SunOS 4.1.1.
----------------------------------------------------------------------------
Wilson N G <noel@essex.ac.uk> writes:

Two things worth mentioning - if you set the quantum down, the connections
are established *much* more quickly (I've put "-quantum 500" in the rundbms.opt
file; this is declared as the minimum in the 6.3 manual) and not using the
name server (-nonames in rundbms.opt) if you can avoid it again makes a
dramatic difference. If these are insufficient/inappropriate, you could
consider an enquiry daemon, which provides an rpc service to perform enquiries
on the database. The more limited these can be (e.g. a service to return the
maximum value of a field in a known table) the easier to write, but it could
easily be written to be almost completely general.

---------------------------------------------------------------------------
billc@ingres.com (Bill Coffin) writes:

>I'd like to minimize the connection time [ ... ]

I don't know if this still helps in 6.4, but I've heard of a simple trick
that works in earlier versions.  The first connection to a server has
more overhead than subsequent connects.  So after starting a server,
start a connection (like a Terminal Monitor session) and just leave it
permanently connected and inactive.

The II_DBMS_SERVER trick works, but I believe that it's only 
quasi-supported.  And of course it's a hassle and it won't work in
heterogeneous environments.  Ingres/Net performance has improved
significantly in 6.4, or so I'm told, and will continue to improve
in subsequent releases.

---------------------------------------------------------------------------
brad@ingres.com  writes:

why not just have the daemon process do the query ?  lots of users have set
up systems like that, where the actual users just get data back from a 
"front-end server".........

---------------------------------------------------------------------------
gnb@duke.bby.oz.au (Gregory N. Bond) writes:

Philip> By going direct to the iidbms on the server, we gained 35-45%
Philip> of the cpu back.

Philip> The negative to this process is that you are using the port
Philip> address.  This address changes each time that Ingres is
Philip> re-started.

Philip> I don't have my manuals at home but if people are interested in some
Philip> examples of how we did this, drop us a note.

We do the same thing, but rather than CPU speed, the problem is more
that IINAMU is an administration nightmare.  The way we fix it is to
use the unsupported II_DMBS_SERVER environment variable.  At startup
time, run
	ingsetenv II_DBMS_SERVER <hostname>::<portname>
on each machine as user ingres, where portname is the argument to the
iidbms program (use ps to find out).

---------------------------------------------------------------------------
bankilco@ux2.cso.uiuc.edu (Bank of Illinois - Invoice) writes:

One solution to the reposne problem is to connect directly to iidbms
without going thru the iigcn.  During a benchmark of our application
at HP and IBM, we found that iigcn and iigcc (communication server)
were taking up too much cpu time.

By going direct to the iidbms on the server, we gained 35-45% of the cpu
back.

The negative to this process is that you are using the port address.  This
address changes each time that Ingres is re-started.

I don't have my manuals at home but if people are interested in some
examples of how we did this, drop us a note.

Philip McKinney
BankIllinois Co.