Обсуждение: postgres processes
Hi! I have following problem: I use php with postgres as backend. Every time, I run some queries, postgres creates a lot of processes - this causes extremely high processor usage. I execute some queries in quasi-parallel way, cause I need its results. But other are executing and every time I free result - all of those queries are executing on the same connection. So is it normal, that I get so much processes? And if there is some way to limit it? Or may be change process live time? TIA Rem -------------------------------------------------------------------*------------ Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * * -----------------------------------------------------------------*****----------
Remigiusz Sokolowski wrote: > > Hi! > I have following problem: > > I use php with postgres as backend. Every time, I run some queries, > postgres creates a lot of processes - this causes extremely high processor > usage. > I execute some queries in quasi-parallel way, cause I need its results. > But other are executing and every time I free result - all of those > queries are executing on the same connection. So is it normal, that I get > so much processes? And if there is some way to limit it? Or may be change > process live time? That's a general problem when using PostgreSQL in the background for Apache CGI or php scripts. The defaults in the Apache configuration are StartServers 5 MaxClients 256 MinSpareServers 5 MaxSpareServers 10 This means, that at startup Apache will create 5 server processes that can handle requests simultaneously. When the site gets busy and some of them take longer to handle requests (especially scripting requests), it will start new servers (max one per second) until the limit of 256 parallel server processes is reached. If they finish their requests and become idle again, some of them get killed if there are more than 10 idle Apache processes. This is normally a good policy. It ensures that small file requests can still get served while some long running CGI's block their server process. In the case of having PostgreSQL in the background, any such CGI request causes another backend to get started too. So when there's a peak of such requests, PostgreSQL will have to serve more parallel queries, Apache will start more servers to handle the incoming requests, causing more PostgreSQL connections and more parallel queries... What you can try is to take down the MaxClients directive in the Apache configuration. But that would mean, that a plain html file request, that could be served in milliseconds, will have to wait if all servers are blocked waiting for CGI's. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
> The defaults in the Apache configuration are > > StartServers 5 > MaxClients 256 > MinSpareServers 5 > MaxSpareServers 10 > > This means, that at startup Apache will create 5 server > processes that can handle requests simultaneously. When the > site gets busy and some of them take longer to handle > requests (especially scripting requests), it will start new > servers (max one per second) until the limit of 256 parallel > server processes is reached. If they finish their requests > and become idle again, some of them get killed if there are > more than 10 idle Apache processes. > > This is normally a good policy. It ensures that small file > requests can still get served while some long running CGI's > block their server process. My problem is, that server is used not only as database server, but also (and in general) as mail server - I think that tehre are some other services too. I've used persistent connections to database (and I think I now understand why so big processor usage), so postgres processes haven't die after serve requests but wait for another. Hmm... I have one question more - every postgres process takes about 5% of processor time ( I've used to measure top command ) - it is normal or may be processor is too slow? Rem
From: Remigiusz Sokolowski <rems@gdansk.sprint.pl> cc: pgsql-general@postgreSQL.org, pgsql-hackers@postgreSQL.org Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: owner-pgsql-general@postgreSQL.org Precedence: bulk > The defaults in the Apache configuration are > > StartServers 5 > MaxClients 256 > MinSpareServers 5 > MaxSpareServers 10 > > This means, that at startup Apache will create 5 server > processes that can handle requests simultaneously. When the > site gets busy and some of them take longer to handle > requests (especially scripting requests), it will start new > servers (max one per second) until the limit of 256 parallel > server processes is reached. If they finish their requests > and become idle again, some of them get killed if there are > more than 10 idle Apache processes. > > This is normally a good policy. It ensures that small file > requests can still get served while some long running CGI's > block their server process. My problem is, that server is used not only as database server, but also (and in general) as mail server - I think that tehre are some other services too. I've used persistent connections to database (and I think I now understand why so big processor usage), so postgres processes haven't die after serve requests but wait for another. Hmm... I have one question more - every postgres process takes about 5% of processor time ( I've used to measure top command ) - it is normal or may be processor is too slow? Rem We use a similar configuration, and initially had similar problems. We just don't use persistent connections in php anymore, and things work fine - In our case, the number of reconnects saved by pconnect would be small anyway. Another strategy would be to start a second apache server on a different port or different machine, use it only for redirects to the pages that call postgres (assuming this is not your whole site). Then throttle the second server back as described above (we haven't actually done this - but it seems it should work). -- Karl DeBisschop <kdebisschop@spaceheater.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper
Karl DeBisschop wrote: > From: Remigiusz Sokolowski <rems@gdansk.sprint.pl> > > My problem is, that server is used not only as database server, but also > (and in general) as mail server - I think that tehre are some other > services too. > I've used persistent connections to database (and I think I now understand > why so big processor usage), so postgres processes haven't die after > serve requests but wait for another. > Hmm... I have one question more - every postgres process takes about 5% of > processor time ( I've used to measure top command ) - it is normal or may > be processor is too slow? > > We use a similar configuration, and initially had similar problems. > We just don't use persistent connections in php anymore, and things > work fine - In our case, the number of reconnects saved by pconnect > would be small anyway. > > Another strategy would be to start a second apache server on a > different port or different machine, use it only for redirects to the > pages that call postgres (assuming this is not your whole site). Then > throttle the second server back as described above (we haven't > actually done this - but it seems it should work). I don't know anything about the internals of php, but I have similar problems with an Apache module I'm (still) writing. Using Apache 1.3.3 I've tried to use persistent connections to a content server. The methods available for a module include a call AtExit, which is called just before the actual Apache server process will be terminated (due to Apache shutdown or because too many spare servers). But there's nothing that tells about a timeout. In the case of a communication or scripting timeout, Apache does a longjmp() back into the mainloop and clears the pool. That's the reason why sockets, files and memory allocations under Apache should be done via the Apache pool utilities, and not using the low level functions (as in PostgreSQL with palloc()). It MIGHT be the case, that php has that problem when using persistent database connections and that after a timeout, an unclosed connection is hanging around causing another hanging PostgreSQL backend. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
On Mon, Jun 14, 1999 at 12:10:01PM +0200, Jan Wieck wrote: > Karl DeBisschop wrote: > > > From: Remigiusz Sokolowski <rems@gdansk.sprint.pl> > > > > My problem is, that server is used not only as database server, but also > > (and in general) as mail server - I think that tehre are some other > > services too. > > I've used persistent connections to database (and I think I now understand > > why so big processor usage), so postgres processes haven't die after > > serve requests but wait for another. > > Hmm... I have one question more - every postgres process takes about 5% of > > processor time ( I've used to measure top command ) - it is normal or may > > be processor is too slow? > > > > We use a similar configuration, and initially had similar problems. > > We just don't use persistent connections in php anymore, and things > > work fine - In our case, the number of reconnects saved by pconnect > > would be small anyway. > > > > Another strategy would be to start a second apache server on a > > different port or different machine, use it only for redirects to the > > pages that call postgres (assuming this is not your whole site). Then > > throttle the second server back as described above (we haven't > > actually done this - but it seems it should work). > > I don't know anything about the internals of php, but I have > similar problems with an Apache module I'm (still) writing. > > Using Apache 1.3.3 I've tried to use persistent connections > to a content server. The methods available for a module > include a call AtExit, which is called just before the actual > Apache server process will be terminated (due to Apache > shutdown or because too many spare servers). PHP3's module version uses the standard ap_register_cleanup() call to register its shutdown function. This function calls then all PHP module specific shutdown functions, so that PostgreSQL persistent connection should be closed properly. I don't know of any situation where Apache would not call these registered handlers (I'm not familiar with the Apache internals...). Additionally, I've been running a site for a client for about two years now which uses PHP and persistent connections to PostgreSQL heavily - I don't think we have ever seen such problems as described previously. > > But there's nothing that tells about a timeout. In the case > of a communication or scripting timeout, Apache does a > longjmp() back into the mainloop and clears the pool. That's > the reason why sockets, files and memory allocations under > Apache should be done via the Apache pool utilities, and not > using the low level functions (as in PostgreSQL with > palloc()). > > It MIGHT be the case, that php has that problem when using > persistent database connections and that after a timeout, an > unclosed connection is hanging around causing another hanging > PostgreSQL backend. > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #========================================= wieck@debis.com (Jan Wieck) # > > > -- Regards, Sascha Schumann Consultant
On Mon, Jun 14, 1999 at 12:10:01PM +0200, Jan Wieck wrote: > Karl DeBisschop wrote: > > > From: Remigiusz Sokolowski <rems@gdansk.sprint.pl> > > > > My problem is, that server is used not only as database server, but also > > (and in general) as mail server - I think that tehre are some other > > services too. > > I've used persistent connections to database (and I think I now understand > > why so big processor usage), so postgres processes haven't die after > > serve requests but wait for another. > > Hmm... I have one question more - every postgres process takes about 5% of > > processor time ( I've used to measure top command ) - it is normal or may > > be processor is too slow? > > > > We use a similar configuration, and initially had similar problems. > > We just don't use persistent connections in php anymore, and things > > work fine - In our case, the number of reconnects saved by pconnect > > would be small anyway. > > > > Another strategy would be to start a second apache server on a > > different port or different machine, use it only for redirects to the > > pages that call postgres (assuming this is not your whole site). Then > > throttle the second server back as described above (we haven't > > actually done this - but it seems it should work). > > I don't know anything about the internals of php, but I have > similar problems with an Apache module I'm (still) writing. > > Using Apache 1.3.3 I've tried to use persistent connections > to a content server. The methods available for a module > include a call AtExit, which is called just before the actual > Apache server process will be terminated (due to Apache > shutdown or because too many spare servers). PHP3's module version uses the standard ap_register_cleanup() call to register its shutdown function. This function calls then all PHP module specific shutdown functions, so that PostgreSQL persistent connection should be closed properly. I don't know of any situation where Apache would not call these registered handlers (I'm not familiar with the Apache internals...). Additionally, I've been running a site for a client for about two years now which uses PHP and persistent connections to PostgreSQL heavily - I don't think we have ever seen such problems as described previously. > > But there's nothing that tells about a timeout. In the case > of a communication or scripting timeout, Apache does a > longjmp() back into the mainloop and clears the pool. That's > the reason why sockets, files and memory allocations under > Apache should be done via the Apache pool utilities, and not > using the low level functions (as in PostgreSQL with > palloc()). > > It MIGHT be the case, that php has that problem when using > persistent database connections and that after a timeout, an > unclosed connection is hanging around causing another hanging > PostgreSQL backend. > > > Jan > > -- > > #======================================================================# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #========================================= wieck@debis.com (Jan Wieck) # > > > -- Regards, Sascha Schumann Consultant