Re: Strange performance degradation

From: Lorenzo Allegrucci <lorenzo(dot)allegrucci(at)forinicom(dot)it>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Strange performance degradation
Date: 2009-11-20 11:00:28
Message-ID: 4B0676CC.9030902@forinicom.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

A. Kretschmer wrote:
> In response to Lorenzo Allegrucci :
>> Hi all,
>>
>> I'm experiencing a strange behavior with my postgresql 8.3:
>> performance is degrading after 3/4 days of running time but if I
>> just restart it performance returns back to it's normal value..
>> In normal conditions the postgres process uses about 3% of cpu time
>> but when is in "degraded" conditions it can use up to 25% of cpu time.
>> The load of my server is composed of many INSERTs on a table, and
>> many UPDATEs and SELECT on another table, no DELETEs.
>> I tried to run vacuum by the pg_maintenance script (Debian Lenny)
>> but it doesn't help. (I have autovacuum off).
>
> Bad idea. Really.

Why running vacuum by hand is a bad idea?
vacuum doesn't solve anyway, it seems only a plain restart stops the
performance degradation.

>> So, my main question is.. how can just a plain simple restart of postgres
>> restore the original performance (3% cpu time)?
>
> You should enable autovacuum.
>
> And you should run vacuum verbose manually and see the output.

below is the output of vacuum analyze verbose
(NOTE: I've already run vacuum this morning, this is a second run)

DETAIL: A total of 58224 page slots are in use (including overhead).
58224 page slots are required to track all free space.
Current limits are: 2000000 page slots, 1000 relations, using 11784 kB.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2009-11-20 11:15:35 Re: using window-functions to get freshest value - how?
Previous Message silly8888 2009-11-20 10:49:24 Re: using window-functions to get freshest value - how?

Browse pgsql-performance by date

  From Date Subject
Next Message Axel Rau 2009-11-20 11:06:05 Re: SSD + RAID
Previous Message Віталій Тимчишин 2009-11-20 10:30:33 Re: View based upon function won't use index on joins