2009/11/20 Lorenzo Allegrucci <lorenzo.allegrucci@xxxxxxxxxxxx>: > > 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@xxxxxxxxxxxxxx) > 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
