PostgreSQL getting slower over time, restart of service cures the problem - Mailing list pgsql-general

Operating System: Windows 2003 Server
PostgreSQL 8.1.4 from the official installer

After starting the service, a series of big queries runs blazingly fast ... for around 2 days.
Then they get slower by day.

After 3-4 days, the postgresql.exe processes have grown to around 120Megabyte "Memory Usage" and "Max Memory Usage" in Task Manager

And the queries which took 1-8 seconds before now take 30 seconds and up, longest logged 140seconds.

Changes in the relevant tables are around 5%, new rows and updates and deletes.

VACUUM FULL ANALYZE hangs then, interruptable. Trying to pg_ctl stop the server does not succeed.

But with services.msc and "stop service", "start service" the Database comes back up as good as new. The queries are speedy again, back to 1-8 seconds.

Autovacuum is activated as:
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------

autovacuum = on            # enable autovacuum subprocess?
autovacuum_naptime = 1800        # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400    # min # of tuple updates before
                    # vacuum
autovacuum_analyze_threshold = 200    # min # of tuple updates before
                    # analyze
autovacuum_vacuum_scale_factor = 0.4    # fraction of rel size before
                    # vacuum
autovacuum_analyze_scale_factor = 0.1    # fraction of rel size before
                    # analyze
#autovacuum_vacuum_cost_delay = -1    # default vacuum cost delay for
                    # autovac, -1 means use
                    # vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
                    # autovac, -1 means use
                    # vacuum_cost_limit


and within the log there are also statements from autovacuum:

2006-08-17 05:59:02 LOG:  autovacuum: processing database "swcheck"
2006-08-17 06:29:51 LOG:  autovacuum: processing database "postgres"
2006-08-17 07:00:20 LOG:  autovacuum: processing database "ibox"

With half an hour distance, as written for the naptime.

apart from that, there are mainly errors like
"2006-08-17 06:50:57 ERROR:  update or delete on "filesall" violates foreign key constraint "$2" on "otformularlink""
within the lockfile. My application trys to delete records; knowing that it may not be allowed to
and deals with the PostgreSQL error.


memory relevant parameters from postgresql.conf:

max_connections = 250        # 400
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 10000            # min 16 or max_connections*2, 8KB each
max_fsm_pages = 25000            # min max_fsm_relations*16, 6 bytes each

and
effective_cache_size = 50000        # typically 8KB each

all the rest is on default

The machine has 1 GB of main memory.

What can I do to stop PostgreSQL from getting slower over time? What can I do to help isolating the problem?

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.