Re: Postmaster processes taking all the CPU

I mean, have you run a VACUUM FULL VERBOSE ANALYZE; lately?

SInce you're constantly inserting stuff (are you updating too?), if
you have have not analyzed recently, then the planner will give you
crappy queries.

Also, if you're updating that table frequently, lots of dead tuples
will remain in there if you don't do a VACUUM FULL regularly.

Reminds me of when I just started using Postgresql around 5 years ago,
and we had a tiny table with 10 records that was updated every few
seconds or so with a status. Over a fairly short period of time,
things got super slow and we did no know why. Doing a VACUUM VERBOSE
showed tens of thousands of records after just a couple of days.
VACUUM FULL cleaned that out right quick.

You might also want to do a dump and restore as well to prevent
transaction wraparound.

- Ericson Smith
Developer
http://www.funadvice.com

On 6/8/07, MC Moisei <mcmoisei(at)hotmail(dot)com> wrote:
>
>
>
> I'm not sure I understand the question. What else runs on it ?
>
> I have an Apache that fronts a Tomcat (Java Enterprise App Server). In
> tomcat I only run this application that has a connection pool of 30
> connections(if I remember correctly).
>
> Once the application starts to open connections it looks that the each
> postmaster associated with the connection is not exiting as fast as was
> before. I can follow up with a ps -aux capture if you think that's helpful.
> Till yesterday all was working smoothly for about 2 years. It looks like the
> postmasters are not finishing of if they do takes a good while to finish.
> Also I've seen that the swap increases. I never use to have swap used. I
> don't have space problems not errors in the syslog.
>
> Am I running out of memory and all gets delayed by the swap handling ? I
> have the feeling that I spin around my tail. So these processes are taking
> all the CPU and memory and they hold for too long just doing a select. The
> traffic didn't increase by any means so one can say that causes the problem
> - at one point it sustained 4 times more traffic without problems.
>
> Hope this provide more insight.
>
> MC
>
>
>
>
>
> ________________________________
> > Date: Fri, 8 Jun 2007 16:35:40 -0400
> > From: ajs(at)crankycanuck(dot)ca
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] Postmaster processes taking all the CPU
> >
> > On Fri, Jun 08, 2007 at 03:20:28PM -0500, MC Moisei wrote:
> > >
> > > pack of postmaster(4-22) processes ran by postgres user are taking
> > > over almost all the CPU.
> >
> > What else is the box doing? If it doesn't have any other work to do,
> > why shouldn't postgres use the CPU time? (This is a way of saying,
> > "You didn't tell us anything that would allow us to help.")
> >
> > A
> >
> > --
> > Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> > In the future this spectacle of the middle classes shocking the avant-
> > garde will probably become the textbook definition of Postmodernism.
> > --Brad Holland
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org/
>