Thread

Pre-forking backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-09-29T18:22:44Z

How hard would it be to pre-fork an extra backend for the database a
user just requested so if they next user asks for the same database, the
backend would already be started?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Pre-forking backend

Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-29T18:36:59Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> How hard would it be to pre-fork an extra backend

How are you going to pass the connection socket to an already-forked
child process?  AFAIK there's no remotely portable way ...

			regards, tom lane

Re: Pre-forking backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-09-29T18:38:29Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > How hard would it be to pre-fork an extra backend
> 
> How are you going to pass the connection socket to an already-forked
> child process?  AFAIK there's no remotely portable way ...

No idea but it seemed like a nice optimization if we could do it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Pre-forking backend

mlw <markw@mohawksoft.com> — 2001-09-29T18:50:25Z

Bruce Momjian wrote:
> 
> How hard would it be to pre-fork an extra backend for the database a
> user just requested so if they next user asks for the same database, the
> backend would already be started?

The only problem I could see is the socket. The pre-forked() back-end would
have to do the accept() for the new connection, but you could always have a
forked process waiting to go in the accept() routine. When it accepts a new
socket, it sends a signal off to the parent back-end to fork() over (couldn't
resist) a new backend.

That way there would be no fork() over head for new connections.

Re: Pre-forking backend

Ken Hirsch <kenhirsch@myself.com> — 2001-09-29T20:28:00Z

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > How hard would it be to pre-fork an extra backend
> >
> > How are you going to pass the connection socket to an already-forked
> > child process?  AFAIK there's no remotely portable way ...
>
> No idea but it seemed like a nice optimization if we could do it.

What can be done is to have the parent process open and listen() on the
socket, then have each child do an accept() on the socket.   That way you
don't have to pass the socket. The function of the parent process would then
be only to decide when to start new children.

On some operating systems, only one child at a time can accept() on the
socket.  On these, you have to lock around the call to accept().

Re: Pre-forking backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-09-29T20:29:44Z

> Bruce Momjian wrote:
> > Tom Lane wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > How hard would it be to pre-fork an extra backend
> > >
> > > How are you going to pass the connection socket to an already-forked
> > > child process?  AFAIK there's no remotely portable way ...
> >
> > No idea but it seemed like a nice optimization if we could do it.
> 
> What can be done is to have the parent process open and listen() on the
> socket, then have each child do an accept() on the socket.   That way you
> don't have to pass the socket. The function of the parent process would then
> be only to decide when to start new children.
> 
> On some operating systems, only one child at a time can accept() on the
> socket.  On these, you have to lock around the call to accept().

But how do you know the client wants the database you have forked?  They
could want a different one.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Pre-forking backend

Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-29T20:50:22Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> On some operating systems, only one child at a time can accept() on the
>> socket.  On these, you have to lock around the call to accept().

> But how do you know the client wants the database you have forked?  They
> could want a different one.

This approach would only work as far as saving the fork() call itself,
not the backend setup time.  Not sure it's worth the trouble.  I doubt
that the fork itself is a huge component of our start time; it's setting
up all the catalog caches and so forth that's expensive.

			regards, tom lane

Re: Pre-forking backend

Ken Hirsch <kenhirsch@myself.com> — 2001-09-29T21:06:13Z

Tom Lane wrote:
>
> This approach would only work as far as saving the fork() call itself,
> not the backend setup time.  Not sure it's worth the trouble.  I doubt
> that the fork itself is a huge component of our start time; it's setting
> up all the catalog caches and so forth that's expensive.

On Unix, yeah, but on Windows, VMS, MPE/iX, possibly others, forking is
expensive.  Even on Unix, you're not losing anything by this architecture.

The simple solution is to have wait on separate sockets and add a redirect
capability to the protocol.  The program would be:

If the clients wants the database I have open,
  great, we're in business
else if the client supports redirect,
  do redirect
else if I can pass file descriptor on this OS,
  pass file descriptor to the right process
else
  throw away what we've done and open the right database.

Simple!  It's just a small matter of programming.

Re: Pre-forking backend

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-09-30T01:54:44Z

At 04:50 PM 9/29/01 -0400, Tom Lane wrote:
>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> On some operating systems, only one child at a time can accept() on the
>>> socket.  On these, you have to lock around the call to accept().
>
>> But how do you know the client wants the database you have forked?  They
>> could want a different one.
>
>This approach would only work as far as saving the fork() call itself,
>not the backend setup time.  Not sure it's worth the trouble.  I doubt
>that the fork itself is a huge component of our start time; it's setting
>up all the catalog caches and so forth that's expensive.

I don't think there's much benefit as well.

For most cases where preforking would help, you could just simply not
disconnect. Get the app to connect to the correct DB on startup and then
just wait, do stuff then don't disconnect either rollback or commit. Or
have a DB connection pool.

What would be good is a DB that can handle lots of connections well. That
would help almost any case.

Preforking is good for web servers but for DB servers it doesn't seem as
useful.

Cheerio,
Link.

Re: Pre-forking backend

Sean Chittenden <sean-pgsql-hackers@chittenden.org> — 2001-09-30T02:28:01Z

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > How hard would it be to pre-fork an extra backend
> 
> How are you going to pass the connection socket to an already-forked
> child process?  AFAIK there's no remotely portable way ...

Umm... Apache?  They use a preforking model and it works quite well for 
every *NIX that Apache runs on.  ;)  Maybe RSE can comment on this 
further... -sc

-- 
Sean Chittenden

Re: Pre-forking backend

Jan Wieck <janwieck@yahoo.com> — 2001-09-30T04:40:49Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > How hard would it be to pre-fork an extra backend
>
> How are you going to pass the connection socket to an already-forked
> child process?  AFAIK there's no remotely portable way ...

    One  of  the mechanisms I've seen was that the master process
    just does the socket(), bind(), listen(), than forks off  and
    the  children  coordinate via a semaphore that at most one of
    them executes a blocking accept(). I think  it  was  in  some
    older apache release.

    But  in  contrast  to  apache,  we  currently  do most of the
    initialization after we authenticated the user and know  what
    database to connect to.  I'm not sure how much of the backend
    startup could be done before accepting the connection.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: Pre-forking backend

Gavin Sherry <swm@linuxworld.com.au> — 2001-09-30T10:12:20Z

On Sat, 29 Sep 2001 sean-pgsql-hackers@chittenden.org wrote:

> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > How hard would it be to pre-fork an extra backend
> > 
> > How are you going to pass the connection socket to an already-forked
> > child process?  AFAIK there's no remotely portable way ...
> 
> Umm... Apache?  They use a preforking model and it works quite well for 
> every *NIX that Apache runs on.  ;)  Maybe RSE can comment on this 
> further... -sc

It works very good for what Apache requires. Namely, to have a queue of
processes ready to serve pages. Its not that simple with PostgreSQL - as
the discussion so far has drawn out - since there is no simple way to
guarantee that the 'right' child gets the socket. The reason why there
needs to be a 'right' child is that a socket needs to be passed to a child
which has started up for a given database. Otherwise, there's no benefit.

This aside, isn't it possible to just copy the socket and some
data about the database required into shared memory and have the preforked
children pick the socket up from there. Combined with a framework which
tests that there are still idle pre-forked children waiting for this
database and some configuration options to allow users to specify a number
of waiting backends for a given database, and this would work pretty well.

Gavin

Re: Pre-forking backend

Doug McNaught <doug@wireboard.com> — 2001-09-30T15:11:43Z

Gavin Sherry <swm@linuxworld.com.au> writes:

> This aside, isn't it possible to just copy the socket and some
> data about the database required into shared memory and have the preforked
> children pick the socket up from there.

Ummm....  No.  There's no Unix API for doing so.

You can pass open file descriptors across Unix domain sockets on most
systems, which is a possible way to address the problem, but probably
not worth it for the reasons discussed earlier.

-Doug
-- 
In a world of steel-eyed death, and men who are fighting to be warm,
Come in, she said, I'll give you shelter from the storm.    -Dylan

Re: Pre-forking backend

Bradley McLean <brad@bradm.net> — 2001-09-30T15:56:53Z

* Gavin Sherry (swm@linuxworld.com.au) [010930 06:13]:
> On Sat, 29 Sep 2001 sean-pgsql-hackers@chittenden.org wrote:
> 
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > How hard would it be to pre-fork an extra backend
> > > 
> > > How are you going to pass the connection socket to an already-forked
> > > child process?  AFAIK there's no remotely portable way ...
> > 
> > Umm... Apache?  They use a preforking model and it works quite well for 
> > every *NIX that Apache runs on.  ;)  Maybe RSE can comment on this 
> > further... -sc
> 
> It works very good for what Apache requires. Namely, to have a queue of
> processes ready to serve pages. Its not that simple with PostgreSQL - as
> the discussion so far has drawn out - since there is no simple way to
> guarantee that the 'right' child gets the socket. The reason why there
> needs to be a 'right' child is that a socket needs to be passed to a child
> which has started up for a given database. Otherwise, there's no benefit.

Interesting:  So as the number of databases served by a given system
approaches one, the efficiency of this increases.

Is it useful if it only works for one database within a server?  I can
envision applications for this.

-Brad

Re: Pre-forking backend

Ken Hirsch <kenhirsch@myself.com> — 2001-09-30T16:45:57Z

Doug McNaught wrote:
>
> You can pass open file descriptors across Unix domain sockets on most
> systems, which is a possible way to address the problem, but probably
> not worth it for the reasons discussed earlier.

I think that it does solve the problem.  The only drawback is that it's not
portable.  Almost all systems do support one of two methods, though.

Re: Pre-forking backend

Tom Lane <tgl@sss.pgh.pa.us> — 2001-09-30T17:35:06Z

Bradley McLean <brad@bradm.net> writes:
> Is it useful if it only works for one database within a server?

Once we have schemas (7.3, I hope), I think a lot of installations will
have only one production database.  However, if we were going to do this
what we'd probably do is allow the DBA to configure the postmaster to
start N pre-forked backends per database, where N can depend on the
database.  There's no reason to limit it to just one database.

			regards, tom lane

Re: Pre-forking backend

Darren Johnson <darren.johnson@home.com> — 2001-09-30T23:51:06Z

> 
> Once we have schemas (7.3, I hope), I think a lot of installations will
> have only one production database.  However, if we were going to do this
> what we'd probably do is allow the DBA to configure the postmaster to
> start N pre-forked backends per database, where N can depend on the
> database.  There's no reason to limit it to just one database.

The optimized version of Postgres-R uses pre-forked backends for 
handling remote
write sets.  It currently uses one user/database, so I'm all for having 
a configurable
parameter for starting a pool of backends for each database.  We'll have 
to make sure
that number * the number of databases is lower than the max number of 
backends at
start up.

Darren

> 
>

Re: Pre-forking backend

Steve Wolfe <steve@iboats.com> — 2001-10-01T02:16:27Z

> >
> > How hard would it be to pre-fork an extra backend for the database a
> > user just requested so if they next user asks for the same database, the
> > backend would already be started?

  Perhaps I'm missing something, but it seems to me that the cost of forking
a new backend would be pretty trivial compared to the expense of processing
anything but the most simple query.  Am I wrong in that?

steve

Re: Pre-forking backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-01T02:50:17Z

> > >
> > > How hard would it be to pre-fork an extra backend for the database a
> > > user just requested so if they next user asks for the same database, the
> > > backend would already be started?
> 
>   Perhaps I'm missing something, but it seems to me that the cost of forking
> a new backend would be pretty trivial compared to the expense of processing
> anything but the most simple query.  Am I wrong in that?

True on most OS's, but on Solaris, fork is pretty expensive, or at least
we are told.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Pre-forking backend

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-10-01T03:27:25Z

At 08:16 PM 30-09-2001 -0600, Steve Wolfe wrote:
>> >
>> > How hard would it be to pre-fork an extra backend for the database a
>> > user just requested so if they next user asks for the same database, the
>> > backend would already be started?
>
>  Perhaps I'm missing something, but it seems to me that the cost of forking
>a new backend would be pretty trivial compared to the expense of processing
>anything but the most simple query.  Am I wrong in that?

I think forking costs a lot on Solaris. That's why Sun promotes threads :).

I still don't see many advantages of doing the preforking in postgresql.
What would the benefits be? Able to open and close db connections many
times a second? Any other advantages?

Can't the apps do their own preforking? All they do is preopen their own db
connections. Then they can take care of whatever initialization and details
they want.

It seems that opening and closing db connections over the network will
always be slower than just leaving a prepared connection open, looking at
just the network connection setup time alone.

I suppose it is helpful for plain cgi scripts, but those don't scale do they?

Cheerio,
Link.

Re: Pre-forking backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-12T16:19:52Z

> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > How hard would it be to pre-fork an extra backend
> >
> > How are you going to pass the connection socket to an already-forked
> > child process?  AFAIK there's no remotely portable way ...
> 
>     One  of  the mechanisms I've seen was that the master process
>     just does the socket(), bind(), listen(), than forks off  and
>     the  children  coordinate via a semaphore that at most one of
>     them executes a blocking accept(). I think  it  was  in  some
>     older apache release.
> 
>     But  in  contrast  to  apache,  we  currently  do most of the
>     initialization after we authenticated the user and know  what
>     database to connect to.  I'm not sure how much of the backend
>     startup could be done before accepting the connection.

I agree this may not be a big win on most platforms, but for platforms
like Solaris and NT, it could be a big win.  Added to TODO:

	* Do listen() in postmaster and accept() in pre-forked backend

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Pre-forking backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-12T16:29:27Z

> Gavin Sherry <swm@linuxworld.com.au> writes:
> 
> > This aside, isn't it possible to just copy the socket and some
> > data about the database required into shared memory and have the preforked
> > children pick the socket up from there.
> 
> Ummm....  No.  There's no Unix API for doing so.
> 
> You can pass open file descriptors across Unix domain sockets on most
> systems, which is a possible way to address the problem, but probably
> not worth it for the reasons discussed earlier.

OK, let's assume we have pre-forked backends that do the accept().  One
enhancement would be for the child to connect to the last requested
database.  If the accept() user wants the same database, it is already
connected, or at least its cache is loaded.  If they want another one,
we can disconnect and connect to the database they request.  This would
be portable for all OS's because there is no file descriptor passing.

Added to TODO:

* Have pre-forked backend pre-connect to last requested database or pass
  file descriptor to backend pre-forked for matching database

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Pre-forking backend

Peter Eisentraut <peter_e@gmx.net> — 2001-10-13T18:14:19Z

Bruce Momjian writes:

> OK, let's assume we have pre-forked backends that do the accept().  One
> enhancement would be for the child to connect to the last requested
> database.  If the accept() user wants the same database, it is already
> connected, or at least its cache is loaded.  If they want another one,
> we can disconnect and connect to the database they request.  This would
> be portable for all OS's because there is no file descriptor passing.

This is bad because you have hidden "connection pooling" that cannot be
circumvented, and I guarantee that it will become a problem because "new
connection" will no longer equal "new connection".  Additionally, you're
assuming a setup were any new connection will connect to a random (from
the server's point of view) database.  I claim these setups are not the
majority.  In fact, any one client application would usually only connect
to exactly one database, so it might as well keep that connection open.
For systems were this is not possible for some reason or where different
databases or connection parameters are really required, there are already
plenty of solutions available that are tuned or tunable to the situation
at hand, so your solution would just get in the way.  In short, you're
adding a level of complexity where there is no problem.

> Added to TODO:

I haven't seen a consensus yet.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: Pre-forking backend

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-10-13T19:55:16Z

> Bruce Momjian writes:
> 
> > OK, let's assume we have pre-forked backends that do the accept().  One
> > enhancement would be for the child to connect to the last requested
> > database.  If the accept() user wants the same database, it is already
> > connected, or at least its cache is loaded.  If they want another one,
> > we can disconnect and connect to the database they request.  This would
> > be portable for all OS's because there is no file descriptor passing.
> 
> This is bad because you have hidden "connection pooling" that cannot be
> circumvented, and I guarantee that it will become a problem because "new
> connection" will no longer equal "new connection".  Additionally, you're
> assuming a setup were any new connection will connect to a random (from
> the server's point of view) database.  I claim these setups are not the
> majority.  In fact, any one client application would usually only connect
> to exactly one database, so it might as well keep that connection open.
> For systems were this is not possible for some reason or where different
> databases or connection parameters are really required, there are already
> plenty of solutions available that are tuned or tunable to the situation
> at hand, so your solution would just get in the way.  In short, you're
> adding a level of complexity where there is no problem.

Of course, there needs more work on the item.  My assumption is that GUC
would control this and that perhaps X requests for the same database
would have to occur before such pre-loading would start.  Another idea
is to somehow pass the requested database name before the accept() so
you could have multiple database ready to go and have the proper backend
do the accept().

I realize this is all pie-in-the-sky but I think we need some connection
pooling capability in the backend someday.  We are fine with Apache and
PHP becuase they can pool themselves but at some point we have too many
clients reinventing the wheel rather than having our backend do it.

Also, this relates to pre-forking backends and does not related to
re-using backends, which is another nice feature we should have someday.

> > Added to TODO:
> 
> I haven't seen a consensus yet.

True.  I can remove it or improve it.  It is actually:

* Have pre-forked backend pre-connect to last requested database or pass
  file descriptor to backend pre-forked for matching database

which mentions passing file descriptors to backends, which we have
discussed and should be recorded for posterity.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Pre-forking backend

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-10-15T02:32:51Z

How would authentication and access control be done with a preforking
backend? I personally find a preforking backend desirable, but that's just me.

But if people really want preforking how about not doing it in the backend.

Create a small program that makes a few connections to postgresql, does
some initialization, preconnects to various DBs (or maybe limited to one DB
specified on startup), and listens on one port/socket. It might not even
prefork, just cache connections so first connection is slow, subsequent
ones are cached along with the user-pass for faster authentication. 

Then your apps can connect to that small program, authenticate, and get the
relevant connection. Call it a "Listener" if you want ;).

It does mean double the number of processes. But if done decently it is
likely to mean two less complex and less buggy processes, compared to one
more complex process. 

Would the performance be that much lower using this method? There are other
configurations possible with this approach e.g.:

app--unixsocket--"listener"--SSL--backend on another host.

This configuration should reduce the TCP and SSL connection set up times
over a network.

Could have different types of preforkers. Then if a certain mode gets very
popular and performance is insufficient then it could be appropriate to
move that mode to the backend.

Cheerio,
Link.

At 03:55 PM 13-10-2001 -0400, Bruce Momjian wrote:
>
>I realize this is all pie-in-the-sky but I think we need some connection
>pooling capability in the backend someday.  We are fine with Apache and
>PHP becuase they can pool themselves but at some point we have too many
>clients reinventing the wheel rather than having our backend do it.
>
>Also, this relates to pre-forking backends and does not related to
>re-using backends, which is another nice feature we should have someday.
>
>> > Added to TODO:
>> 
>> I haven't seen a consensus yet.
>
>True.  I can remove it or improve it.  It is actually:
>
>* Have pre-forked backend pre-connect to last requested database or pass
>  file descriptor to backend pre-forked for matching database
>
>which mentions passing file descriptors to backends, which we have
>discussed and should be recorded for posterity.

Re: Pre-forking backend

Tom Lane <tgl@sss.pgh.pa.us> — 2001-10-15T14:18:31Z

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Create a small program that makes a few connections to postgresql, does
> some initialization, preconnects to various DBs (or maybe limited to one DB
> specified on startup), and listens on one port/socket. It might not even
> prefork, just cache connections so first connection is slow, subsequent
> ones are cached along with the user-pass for faster authentication. 

> Then your apps can connect to that small program, authenticate, and get the
> relevant connection. Call it a "Listener" if you want ;).

Couple of problems...

(a) where is this outside program going to get authentication
information from?

(b) it seems that not only the authentication exchange, but also all
subsequent data exchange of each connection would have to go through
this additional program.  That middleman is going to become a
bottleneck.

			regards, tom lane

Re: Pre-forking backend, an idea

mlw <markw@mohawksoft.com> — 2001-10-15T16:37:50Z

I was just combing through an Oracle control file, it occured to me that a
pre-forking Postgres could be designed to work similar to Oracle.

Oracle has a listener keyed to an IP port. The instance of the listener is
configured to listen for a particular database instance. This may be a bit
flakey, but hear me out here. This would be similar to having Postgres listen on
different ports.

A single Postgres instance can start and run as it does now, but it can also be
configured to prefork "listeners" for specified databases on different ports.
That way you do not need the main postgres instance to be able to pass the socket
to the forked child. Also, in the configuration files for the "listeners" you
could also specify Postgres settings for each database.

Re: Pre-forking backend - new idea

mlw <markw@mohawksoft.com> — 2001-10-15T21:05:05Z

(I'm having trouble with e-mail, so if you get this twice, sorry)

I was looking at some Oracle configuration files today, and it occurred to me
how Postgres can be made to pre-fork, similarly to Oracle.

Oracle has "listener" processes that listen on a port for Oracle clients. The
listeners are configured for a database. Postgres could work the same way. It
could start up on port 5432 and work as it always has, and, in addition, it
could read a configuration script which directs it to "pre-fork" listeners on
other ports, one port per database. This would work because they already know
the database that they should be ready to use. The back-end does not need to be
involved.

Once you connect to the pre-forked back end, it will already be ready to
perform a query because it has already loaded the database. The file which
configures the "pre-forked" database could also contain run-time changeable
tuning options for each "pre-forked" instance, presumably, because you would
tune it for each database on which it would operate.

Re: Pre-forking backend

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-10-16T09:52:19Z

At 10:18 AM 15-10-2001 -0400, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
>> Create a small program that makes a few connections to postgresql, does
>> some initialization, preconnects to various DBs (or maybe limited to one DB
>> specified on startup), and listens on one port/socket. It might not even
>> prefork, just cache connections so first connection is slow, subsequent
>> ones are cached along with the user-pass for faster authentication. 
>
>> Then your apps can connect to that small program, authenticate, and get the
>> relevant connection. Call it a "Listener" if you want ;).
>
>Couple of problems...
>
>(a) where is this outside program going to get authentication
>information from?

Various options:
1) No authentication required by client - authentication supplied on
startup/config.
2) Local authentication - runs as postgres user, reads from postgres files.
3) Local authentication - from config file, mapped to actual remote
authentication
4) Authentication from remote server, then cached in memory.

>(b) it seems that not only the authentication exchange, but also all
>subsequent data exchange of each connection would have to go through
>this additional program.  That middleman is going to become a
>bottleneck.

The authentication exchange doesn't happen that often, since the DB
connections are reused - no reconnection.

True it might be a bottleneck. But in certain setups the middleman is not
running on the DB server and thus not using the DB server resources.

---
Are there really compelling reasons for having a preforking backend? What
would the benefits be? Faster connection setup times? Connecting and
disconnecting quickly is important for a webserver because of the HTTP
protocol, but for a DB server? Would it really be fast in cases where
there's authentication and access control to various databases? 

Perhaps it's undesirable for people to roll their own DB connection
pooling. But my worry is that there's such a great diversity that most
people may still have to roll their own DB connection pooling, then a
preforking backend just adds complexity and sucks up a bit more resources
for little gain. 

For example in my case if connection setup times are a problem, I'd just
preconnect and reuse the connections for many transactions. Wouldn't that
still be much faster than a preforking backend? How fast would a preforking
backend be?

Regards,
Link.

Re: Pre-forking backend

Daniel Varela Santoalla <dvs@arrakis.es> — 2001-10-17T17:21:51Z

On Mon 15 Oct 2001 04:32, you wrote:


DBBalancer (http://www.sourceforge.net/projects/dbbalancer/) does something 
like that.


>
> Create a small program that makes a few connections to postgresql, does
> some initialization, preconnects to various DBs (or maybe limited to one DB
> specified on startup), and listens on one port/socket. It might not even
> prefork, just cache connections so first connection is slow, subsequent
> ones are cached along with the user-pass for faster authentication.
>


> Then your apps can connect to that small program, authenticate, and get the
> relevant connection. Call it a "Listener" if you want ;).


-- 

----------------------------------
Regards from Spain.  Daniel Varela
----------------------------------

If you think education is expensive, try ignorance.
           -Derek Bok (Former Harvard President)