Pre-forking backend

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

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

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.

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().

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

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

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.

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.

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

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

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

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

* 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

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.

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

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

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

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

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.