Re: Strange performance degradation

2009/11/20 Lorenzo Allegrucci <lorenzo(dot)allegrucci(at)forinicom(dot)it>:
>
> 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).

I had a similar problem: I did a large delete, and then a selct which
"covered" the previous rows.
It took ages, because the index still had those deleted rows.
Possibly the same happens with update.

Try this:
vacuum analyse
reindex database ....
(your database name instead of ...)

or, rather do this table by table:
vacuum analyse ....
reindex table ...

Autovacuum is a generally good thing.

> So, my main question is.. how can just a plain simple restart of postgres
> restore the original performance (3% cpu time)?

there were probably some long transactions running. Stopping postgres
effectively kills them off.

> I can post my postgresql.conf if needed.
> Thank you for your help,
>
> --
> Lorenzo
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/