Re: Major Performance decrease after some hours

It appears to me that work_mem is a more significant configuration
option than previously assumed by many PostgreSQL users, myself
included. As with many database optimizations, it's an obscure
problem to diagnose because you generally only observe it through I/O
activity.

One possibility would be to log a warning whenever work_mem is
exceeded (or exceeded by a certain ratio). I would also love a couple
of new statistics counters tracking the amount of work memory used
and the amount of work memory that has spilled over into pgsql_tmp.

Alexander.

On Oct 5, 2006, at 10:48 , Peter Bauer wrote:

> Hi all,
>
> inspired by the last posting "Weird disk write load caused by
> PostgreSQL?" i increased the work_mem from 1 to 7MB and did some
> loadtest with vacuum every 10 minutes. The system load (harddisk) went
> down and everything was very stable at 80% idle for nearly 24 hours!
> I am currently performing some pgbench runs to evaluate the hardware
> and configuration for the system but i think the biggest problems are
> solved so far.
>
> thx everybody,
> Peter
>
> 2006/10/2, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Ray Stell <stellr(at)cns(dot)vt(dot)edu> writes:
>> > How would one determine the lock situation definitively? Is there
>> > an internal mechanism that can be queried?
>>
>> pg_locks view.
>>
>> regards, tom lane
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 2: Don't 'kill -9' the postmaster
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org