Thread: Load problems...
Hi,
Does postgresql have any problems managing load? I'm running a medium sized
(30k customers) ISP off a postgresql database but it can't seem to manage
very well with the dozen or so requests per second it receives. The machine
we are handling is plenty powerful enough for a database like this (a dual
1ghz p3 machine with 1gb of ram) but for some reason it never runs quite
perfectly. 7.1.3 would overload the system rapidly and slow to a crawl, so we
installed 7.2b4 only to find it runs like a dream (less that 10% cpu usage
most of the time) but eventually fails to fork and crashes and burns, at
which point i have to restart it. Could anyone shed any light on a possible
solution to these problems?
Thanks,
Daniel Andersen
I'm afraid you'll have to provide more info than you have... For 7.1.3, what queries are taking so long? You VACUUM'd your database often (if lots of updates, inserts and deletes were happening, yes)? For 7.2b4 - it shouldn't crash, but do you have any clue when it does or what query/situation might be to blame -- possibly even a backtrace from a core file you could submit? Also, this is probably best on -general or -bugs if you have some specific crash info. Good luck! -Mitch ----- Original Message ----- From: "Daniel Andersen" <zedar@free2air.com.au> To: <pgsql-admin@postgresql.org> Sent: Tuesday, December 18, 2001 8:56 PM Subject: [ADMIN] Load problems... > Hi, > Does postgresql have any problems managing load? I'm running a medium sized > (30k customers) ISP off a postgresql database but it can't seem to manage > very well with the dozen or so requests per second it receives. The machine > we are handling is plenty powerful enough for a database like this (a dual > 1ghz p3 machine with 1gb of ram) but for some reason it never runs quite > perfectly. 7.1.3 would overload the system rapidly and slow to a crawl, so we > installed 7.2b4 only to find it runs like a dream (less that 10% cpu usage > most of the time) but eventually fails to fork and crashes and burns, at > which point i have to restart it. Could anyone shed any light on a possible > solution to these problems? > Thanks, > Daniel Andersen > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Wed, 19 Dec 2001, Daniel Andersen wrote: > Hi, > Does postgresql have any problems managing load? I'm running a medium sized > (30k customers) ISP off a postgresql database but it can't seem to manage > very well with the dozen or so requests per second it receives. The machine > we are handling is plenty powerful enough for a database like this (a dual > 1ghz p3 machine with 1gb of ram) but for some reason it never runs quite > perfectly. 7.1.3 would overload the system rapidly and slow to a crawl, so we > installed 7.2b4 only to find it runs like a dream (less that 10% cpu usage > most of the time) but eventually fails to fork and crashes and burns, at > which point i have to restart it. Could anyone shed any light on a possible > solution to these problems? We're probably going to want to look for some more information. What does the log before failure look like? Are there any warning signs there (messages about running out of file descriptors, etc)? What do you have the memory settings (shared buffers, sort memory, etc) and max connections set to?
On Wed, 19 Dec 2001, Daniel Andersen wrote:
> Does postgresql have any problems managing load? I'm running a medium sized
> (30k customers) ISP off a postgresql database but it can't seem to manage
> very well with the dozen or so requests per second it receives. The machine
> we are handling is plenty powerful enough for a database like this (a dual
> 1ghz p3 machine with 1gb of ram) but for some reason it never runs quite
> perfectly. 7.1.3 would overload the system rapidly and slow to a crawl, so we
> installed 7.2b4 only to find it runs like a dream (less that 10% cpu usage
> most of the time) but eventually fails to fork and crashes and burns, at
> which point i have to restart it. Could anyone shed any light on a possible
> solution to these problems?
Hello Daniel,
I can't answer your specific question, but I _can_
tell you that the gurus will probably need more information
that you presented.
1) What operating system and version?
2) Have you tuned your OS, or is it a stock installation?
3) "running a medium sized (30k customers) ISP off a postgresql
database" ?? Ie, you're authenticating users via PostgreSQL?
You're serving them webmail via PostgreSQL? You're sniffing
their network traffic and stuffing it into PostgreSQL? ;)
You get the idea.
4) You mention that 7.2b4 runs better than before, but still
ends up with fork errors. Exact error message? How many
PostgreSQL processes are running when this happens? Does
the user that PostgreSQL is running under have resource
limits? Are they too low? Is this server also their STMP
host, along with HTTP, HTTPS, FTP .. etc? If you're
getting fork errors, you have too many processes running,
most likely. Are your processes closing the database
connection when they're done with it?
You see what I'm getting at. :) There are simply too many
variables without more details.
Benny
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"The onions are irritating my buttocks." - Sluggy Freelance
10-12-1998
> > Does postgresql have any problems managing load? I'm running a medium > > sized (30k customers) ISP off a postgresql database but it can't seem to > > manage very well with the dozen or so requests per second it receives. > > The machine we are handling is plenty powerful enough for a database like > > this (a dual 1ghz p3 machine with 1gb of ram) but for some reason it > > never runs quite perfectly. 7.1.3 would overload the system rapidly and > > slow to a crawl, so we installed 7.2b4 only to find it runs like a dream > > (less that 10% cpu usage most of the time) but eventually fails to fork > > and crashes and burns, at which point i have to restart it. Could anyone > > shed any light on a possible solution to these problems? > > We're probably going to want to look for some more information. What does > the log before failure look like? Are there any warning signs there > (messages about running out of file descriptors, etc)? What do you have > the memory settings (shared buffers, sort memory, etc) and max connections > set to? sort_mem = 1024 shared_buffers = 2048 (tried higher, but didnt' seem to improve anything) max_connections = 300 error message just comes out of the blue, the database is chugging along happily and then suddenly[4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: could not launch checkpoint process (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable etc etc etc the database is still responsive up till this point, so i'm assuming there are limitations somewhere that i'm not aware of...
Daniel Andersen <zedar@free2air.com.au> writes:
> installed 7.2b4 only to find it runs like a dream (less that 10% cpu usage
> most of the time) but eventually fails to fork and crashes and burns,
You'll need to be a *lot* more specific than that. Log entries,
corefile backtraces, etc, please.
regards, tom lane
> Hello Daniel, > > I can't answer your specific question, but I _can_ > tell you that the gurus will probably need more information > that you presented. > > 1) What operating system and version? Slackware linux 8.0, with the kernel upgraded to 2.4.16, stripped to the bare bones for efficiency > 2) Have you tuned your OS, or is it a stock installation? see above > 3) "running a medium sized (30k customers) ISP off a postgresql > database" ?? Ie, you're authenticating users via PostgreSQL? > You're serving them webmail via PostgreSQL? You're sniffing > their network traffic and stuffing it into PostgreSQL? ;) > You get the idea. Radius authentication, email authentication, customers checking their downloads, and a banner system (we're a free isp), so probably a bit heavier than the figures may have suggested... > 4) You mention that 7.2b4 runs better than before, but still > ends up with fork errors. Exact error message? How many > PostgreSQL processes are running when this happens? Does > the user that PostgreSQL is running under have resource > limits? Are they too low? Is this server also their STMP > host, along with HTTP, HTTPS, FTP .. etc? If you're > getting fork errors, you have too many processes running, > most likely. Are your processes closing the database > connection when they're done with it? [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: could not launch checkpoint process (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable etc ad nauseum there are generally up to 200 copies of postmaster running at any given time, but there aren't any limits imposed upon the postgres user. The server is purely for the database and radius (which doesn't use up enough cpu to matter) The connections are mostly coming in via the teapop mail server, which closes the connection when done, and php scripts, which are (at least theoretically) using persistent connections. Radius just opens a couple of connections and uses those exclusively. > > You see what I'm getting at. :) There are simply too many > variables without more details. > > Benny
> sort_mem = 1024 > shared_buffers = 2048 (tried higher, but didnt' seem to improve anything) > max_connections = 300 > > error message just comes out of the blue, the database is chugging along > happily and then suddenly[4059] DEBUG: connection startup failed (fork > failure): Resource temporarily unavailable > [4059] DEBUG: connection startup failed (fork failure): Resource > temporarily unavailable > [4059] DEBUG: could not launch checkpoint process (fork failure): Resource > temporarily unavailable > [4059] DEBUG: connection startup failed (fork failure): Resource > temporarily unavailable > [4059] DEBUG: connection startup failed (fork failure): Resource > temporarily unavailable Sounds like fork returning EAGAIN. What does ulimit -a show for the postgres user?
Daniel Andersen <zedar@free2air.com.au> writes:
> [4059] DEBUG: connection startup failed (fork failure): Resource
> temporarily unavailable
> [4059] DEBUG: could not launch checkpoint process (fork failure): Resource
> temporarily unavailable
Hmm ... a fork failure suggests resource exhaustion somewhere in the
kernel. Do you have enough swap space? A large enough NFILE (kernel
filetable size) setting?
> there are generally up to 200 copies of postmaster running at any given time,
200 * a few dozen open files per backend is a lot of open files. I'm
betting on NFILE being the issue, myself.
Linuxen tend to lie through their teeth about the number of open files
they can support per-process (sysconf(_SC_OPEN_MAX)). In 7.2 you can
compensate for that by setting PG's config variable
max_files_per_process to something sane, maybe 50 to 100.
You'll still need to make sure that the kernel is configured to support
max_files_per_process * max_connections open files, but at least that's
a definable number now.
regards, tom lane
On Wednesday 19 December 2001 12:10 am, Daniel Andersen wrote: > Slackware linux 8.0, with the kernel upgraded to 2.4.16, stripped to the > bare bones for efficiency > [4059] DEBUG: connection startup failed (fork failure): Resource > temporarily unavailable > there are generally up to 200 copies of postmaster running at any given > time, but there aren't any limits imposed upon the postgres user. What's the output of ulimit -a? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11