Re: PG 8.3 and server load

From: Ivan Voras
Subject: Re: PG 8.3 and server load
Date: ,
Msg-id: h6h058$692$1@ger.gmane.org
(view: Whole thread, Raw)
In response to: PG 8.3 and server load  (Phoenix Kiula)
Responses: Re: PG 8.3 and server load  (Guillaume Cottenceau)
List: pgsql-performance

Tree view

PG 8.3 and server load  (Phoenix Kiula, )
 Re: PG 8.3 and server load  (Ivan Voras, )
  Re: PG 8.3 and server load  (Guillaume Cottenceau, )
 Re: PG 8.3 and server load  (Andy Colson, )
  Re: PG 8.3 and server load  (Karl Denninger, )
 Re: PG 8.3 and server load  (Andy Colson, )
  Re: PG 8.3 and server load  (Phoenix Kiula, )
   Re: PG 8.3 and server load  (Matthew Wakeling, )
  Re: PG 8.3 and server load  ("Kevin Grittner", )
   Re: PG 8.3 and server load  (Andy Colson, )
    Re: PG 8.3 and server load  (Phoenix Kiula, )
     Re: PG 8.3 and server load  (Andy Colson, )
     Re: PG 8.3 and server load  (Tom Lane, )
     Re: PG 8.3 and server load  (Guillaume Cottenceau, )
     Re: PG 8.3 and server load  (Scott Marlowe, )
      Re: PG 8.3 and server load  (Ivan Voras, )

Phoenix Kiula wrote:
> I'm on a CentOS 5 OS 64 bit, latest kernel and all of that.
> PG version is 8.3.7, compiled as 64bit.
> The memory is 8GB.
> It's a 2 x Dual Core Intel 5310.
> Hard disks are Raid 1, SCSI 15 rpm.
>
> The server is running just one website. So there's Apache 2.2.11,
> MySQL (for some small tasks, almost negligible).
>
> And then there's PG, which in the "top" command shows up as the main beast.
>
> My server load is going to 64, 63, 65, and so on.
>
> Where should I start debugging? What should I see? TOP command does
> not yield anything meaningful. I mean, even if it shows that postgres
> user for "postmaster" and nobody user for "httpd" (apache) are the
> main resource hogs, what should I start with in terms of debugging?

If postgres or apache are the reason for the high load, it means you
have lots of simultaneous users hitting either server.

The only thing you can do (except of course denying service to the
users) is investigate which requests / queries take the most time and
optimize them.

pgtop (http://pgfoundry.org/projects/pgtop/) might help you see what is
your database doing. You will also probably need to use something like
pqa (http://pqa.projects.postgresql.org/) to find top running queries.

Unfortunately, if you cannot significantly optimize your queries, there
is not much else you can do with the hardware you have.


pgsql-performance by date:

From: Kevin Kempter
Date:
Subject: Re: Query tuning
From: Jaime Casanova
Date:
Subject: [PERFORMANCE] how to set wal_buffers