Thread: Pre-forking backend
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, Pennsylvania19026
> 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, Pennsylvania19026
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 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, Pennsylvania19026
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
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
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
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, Pennsylvania19026
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.
> 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 matchingdatabase -- 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, Pennsylvania19026
> 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, Pennsylvania19026
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
> 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 matchingdatabase 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, Pennsylvania19026
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.
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
(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.
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.
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)
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.