Greetings,

Sorry for all the posts, but I'm trying to put my finger on my backend crash.

Any insight on any of the following would be very helpful:

How many backend processes is considered a large number?  The man pages
says the default is 32.  Does anyone set their number higher?

Kind of related to the question above; when does the postmaster spawn
another backend process?  Is it for each additional connection, or will
each backend process handle several connections/queries before another
process is started?

The postmaster log file, why are the entries not datestamped?  If I start
the postmaster with a debug level of 2 or greater do I get datestamped
entries?  Also, what is the highest debug level and how big can I expect
the log to grow?  Can I rotate the log without stopping the postmaster?

What is the pg_log file in the data directory?

What are the major system resources used by postgres, i.e. semaphores, file
handles, mbufs, etc.?  I'm trying to determine if I have my resources
configured high enough for my user base.

Again, thank you!  I'll try not to be such a problem in the future! :)

Matthew

Wed, 15 Dec 1999 18:26:31 -0500
Matthew Hagerty <matthew@venux.net>
Postmaster options, process spawning, logging, etc.
At 06:26 PM 12/15/99 -0500, Matthew Hagerty wrote:

>How many backend processes is considered a large number?  The man pages
>says the default is 32.  Does anyone set their number higher?

That would depend on your situation.  I use AOLserver to service
my web site.  It maintains a pool of persistent connections and
I throttle the number of connections to the database via the
web server.  So, I like having a high limit on backend processes
for the postmaster itself, and 32 suits me fine.  I like throttling
at the webserver level because I can throttle on individual virtual
servers, etc.

>Kind of related to the question above; when does the postmaster spawn
>another backend process?  Is it for each additional connection,

Yes.  Each connection.  I assume your PHP environment includes some
means to allocate a database handle either out of a persistent pool
or otherwise.  Each time that pool mechanism opens a database
connection the postmaster forks a new backend process.  It goes
away when you close a connection (normally, unless the backend
crashes etc).

> or will
>each backend process handle several connections/queries before another
>process is started?

Once forked, the process stays alive until the connection's closed.
You can feed that process as many queries as you want in serial
fashion.

However, in practice, the API you're using to connect PHP to the
database may or may not pool persistent connections.  In this case,
it will probably be shutting down and reopening connections 
frequently, say once per web page or the like.

I'll leave your other questions to folks who know more about
postgres specifics.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Wed, 15 Dec 1999 15:40:59 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Postmaster options, process spawning, logging,
  etc.
On Wed, 15 Dec 1999, Matthew Hagerty wrote:

> Greetings,
> 
> Sorry for all the posts, but I'm trying to put my finger on my backend crash.
> 
> Any insight on any of the following would be very helpful:
> 
> How many backend processes is considered a large number?  The man pages
> says the default is 32.  Does anyone set their number higher?
> 
> Kind of related to the question above; when does the postmaster spawn
> another backend process?  Is it for each additional connection, or will
> each backend process handle several connections/queries before another
> process is started?

spawns a new backend for each new connection...

> The postmaster log file, why are the entries not datestamped?  If I start
> the postmaster with a debug level of 2 or greater do I get datestamped
> entries?  Also, what is the highest debug level and how big can I expect
> the log to grow?  Can I rotate the log without stopping the postmaster?

pg_options provides the ability to send the log to syslog, which would
give you both the timestamping, and the ability to 'cleanly' rotate the
logs...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 15 Dec 1999 19:43:34 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Postmaster options, process spawning, logging, etc.
Matthew Hagerty <matthew@venux.net> writes:
> How many backend processes is considered a large number?  The man pages
> says the default is 32.  Does anyone set their number higher?

I've run test cases with 100, which is about as high as I can go on my
personal box without running out of swap space.  I think some people
are using several hundred.

> Kind of related to the question above; when does the postmaster spawn
> another backend process?  Is it for each additional connection,

Per connection.  The backend quits when the client disconnects.  Of
course, it's up to the client how long it stays connected or how many
queries it asks...

> The postmaster log file, why are the entries not datestamped?

Uncomment #define ELOG_TIMESTAMPS in include/config.h after configure
and before make...

> Also, what is the highest debug level and how big can I expect
> the log to grow?  Can I rotate the log without stopping the postmaster?

Not very readily.  There is someone working on using syslog logging,
which'd be a lot nicer than what we have.

> What is the pg_log file in the data directory?

Transaction commit data.  Don't touch it ;-).  It shouldn't be all that
big, though...

> What are the major system resources used by postgres, i.e. semaphores, file
> handles, mbufs, etc.?  I'm trying to determine if I have my resources
> configured high enough for my user base.

In 6.5, the postmaster won't start up if you don't have enough
semaphores and shared memory.  I've never heard of anyone running out of
file handles, but it certainly seems possible if you start enough
backends.  Still, though, I wouldn't expect a hard coredump such as you
are getting from running out of any of these resources.  There should at
least be something showing up in the postmaster log if we fail to open a
file or something like that...

			regards, tom lane

Wed, 15 Dec 1999 18:46:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Postmaster options, process spawning, logging, etc. 
1. Matthew's problem sounds an awful lot like what's being reported
by Lucio Andres Perez in v6.4.2. Maybe some kind of bug in detecting
and handling over-the-limit backends. Can someone cook up a Q&D backend-
spawner? I've spend enough time inside that part of the system lately that
I could probably track it down from a core file.

2. Yup, there's a "patch" out on pgsql-patches that previews an advanced log
system. Although the traditional elog method has seen a lot of enhancement
lately, it tends to be more geared towards development over administration.
Plus it was never designed to support national languages or machine parsing.

So far I've had not feedback on itm however, so I don't know if it will ever
make its way into a production release.

    regards,

       Tim Holloway

Tom Lane wrote:
> 
> Matthew Hagerty <matthew@venux.net> writes:
> > How many backend processes is considered a large number?  The man pages
> > says the default is 32.  Does anyone set their number higher?
> 
> I've run test cases with 100, which is about as high as I can go on my
> personal box without running out of swap space.  I think some people
> are using several hundred.
> 
> > Kind of related to the question above; when does the postmaster spawn
> > another backend process?  Is it for each additional connection,
> 
> Per connection.  The backend quits when the client disconnects.  Of
> course, it's up to the client how long it stays connected or how many
> queries it asks...
> 
> > The postmaster log file, why are the entries not datestamped?
> 
> Uncomment #define ELOG_TIMESTAMPS in include/config.h after configure
> and before make...
> 
> > Also, what is the highest debug level and how big can I expect
> > the log to grow?  Can I rotate the log without stopping the postmaster?
> 
> Not very readily.  There is someone working on using syslog logging,
> which'd be a lot nicer than what we have.
> 
> > What is the pg_log file in the data directory?
> 
> Transaction commit data.  Don't touch it ;-).  It shouldn't be all that
> big, though...
> 
> > What are the major system resources used by postgres, i.e. semaphores, file
> > handles, mbufs, etc.?  I'm trying to determine if I have my resources
> > configured high enough for my user base.
> 
> In 6.5, the postmaster won't start up if you don't have enough
> semaphores and shared memory.  I've never heard of anyone running out of
> file handles, but it certainly seems possible if you start enough
> backends.  Still, though, I wouldn't expect a hard coredump such as you
> are getting from running out of any of these resources.  There should at
> least be something showing up in the postmaster log if we fail to open a
> file or something like that...
> 
>                         regards, tom lane
> 
> ************

Thu, 16 Dec 1999 08:28:43 -0500
Tim Holloway <mtsinc@southeast.net>
Re: [HACKERS] Postmaster options, process spawning, logging, etc.
Tim Holloway <mtsinc@southeast.net> writes:
> 1. Matthew's problem sounds an awful lot like what's being reported
> by Lucio Andres Perez in v6.4.2. Maybe some kind of bug in detecting
> and handling over-the-limit backends.

6.4.* didn't really have any check/defense against spawning more
backends than it had resources for.  6.5 does check and enforce the
maxbackends limit.  It's certainly possible that Matthew's running into
some kind of resource-exhaustion problem, but I doubt that it's just
the number of backends that's at issue, except indirectly.  (He could
be running out of swap space or filetable slots, possibly.)

			regards, tom lane

Thu, 16 Dec 1999 09:40:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Postmaster options, process spawning, logging, etc. 
Ah. I hadn't noticed they were that far back. I've passed your news on
to our distraught friends in Columbia with a suggestion to try the 6.5.3
RPM. 

Tom Lane wrote:
> 
> Tim Holloway <mtsinc@southeast.net> writes:
> > 1. Matthew's problem sounds an awful lot like what's being reported
> > by Lucio Andres Perez in v6.4.2. Maybe some kind of bug in detecting
> > and handling over-the-limit backends.
> 
> 6.4.* didn't really have any check/defense against spawning more
> backends than it had resources for.  6.5 does check and enforce the
> maxbackends limit.  It's certainly possible that Matthew's running into
> some kind of resource-exhaustion problem, but I doubt that it's just
> the number of backends that's at issue, except indirectly.  (He could
> be running out of swap space or filetable slots, possibly.)
> 
>                         regards, tom lane
> 
> ************

Thu, 16 Dec 1999 14:26:58 -0500
Tim Holloway <mtsinc@southeast.net>
Re: [HACKERS] Postmaster options, process spawning, logging, etc.
Tom Lane wrote:

> > The postmaster log file, why are the entries not datestamped?
>
> Uncomment #define ELOG_TIMESTAMPS in include/config.h after configure
> and before make...

I'm still missing something...

After running ./configure, I modifed  ...src/include/config.h to uncomment
this...

#define ELOG_TIMESTAMPS

[I also came back later and tried uncommenting #define USE_SYSLOG and repeating
the process, but to no avail...]

Then I ran make, etc, created the file $PGDATA/pg_options...

% cat $PGDATA/pg_options
verbose=2
query
syslog=2

And restarted the server...and still no timestamps.

I verified most everything syslog-wise (configured in /etc/syslog.conf) is
being sent to /var/log/messages...

Anyone notice what am I missing?

Cheers,
Ed Loehr

[ps - Forgive my spewage...I mistakenly sent this out of context to
pgsql-general as well...]


Fri, 17 Dec 1999 02:06:08 -0600
Ed Loehr <ELOEHR@austin.rr.com>
Re: [HACKERS] Postmaster options, process spawning, logging, etc.
You need only ELOG_TIMESTAMPS defined. syslogd is another story - 
I had no luck with it under Linux. Don't forget to make clean and
recompile sources. It should works.

	Oleg

On Fri, 17 Dec 1999, Ed Loehr wrote:

> Date: Fri, 17 Dec 1999 02:06:08 -0600
> From: Ed Loehr <ELOEHR@austin.rr.com>
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: Matthew Hagerty <matthew@venux.net>, pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] Postmaster options, process spawning, logging, etc.
> 
> Tom Lane wrote:
> 
> > > The postmaster log file, why are the entries not datestamped?
> >
> > Uncomment #define ELOG_TIMESTAMPS in include/config.h after configure
> > and before make...
> 
> I'm still missing something...
> 
> After running ./configure, I modifed  ...src/include/config.h to uncomment
> this...
> 
> #define ELOG_TIMESTAMPS
> 
> [I also came back later and tried uncommenting #define USE_SYSLOG and repeating
> the process, but to no avail...]
> 
> Then I ran make, etc, created the file $PGDATA/pg_options...
> 
> % cat $PGDATA/pg_options
> verbose=2
> query
> syslog=2
> 
> And restarted the server...and still no timestamps.
> 
> I verified most everything syslog-wise (configured in /etc/syslog.conf) is
> being sent to /var/log/messages...
> 
> Anyone notice what am I missing?
> 
> Cheers,
> Ed Loehr
> 
> [ps - Forgive my spewage...I mistakenly sent this out of context to
> pgsql-general as well...]
> 
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Fri, 17 Dec 1999 13:39:38 +0300 (MSK)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] Postmaster options, process spawning, logging, etc.