Re: PostgreSQL performance issues

On 8/30/06, Willo van der Merwe <willo(at)studentvillage(dot)co(dot)za> wrote:
> This was just an example. All queries have slowed down. Could it be that
> I've reached some cut-off and now my disk is thrashing?
>
> Currently the load looks like this:
> Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si
> Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si
> Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si
> Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si
>
I don't think so, it looks like you are cpu bound. Your server has a
(fairly high) budget of records per second it can crunch through. You
have hit that limit and backpressure is building up and server load is
escalating. This almost certainly due to inefficient sql, which is
very easy to do especially if you are using some type of middleware
which writes the sql for you. The trick here would be to turn all sql
logging on and find out where your budget is getting spent. solving
the problem may be a simple matter of adding an index or crafting a
stored procedure.

merlin