Re: Strange performance degradation

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.