Thread: Postmaster options, process spawning, logging, etc.
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
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
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 Serviceand other goodies at http://donb.photo.net.
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
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
>
> ************
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
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 > > ************
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...]
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