The problem happened again this morning and I took the chance to check
out the locking situation. The number of locks increased dramatically up
to over 1000, but they were all "AccessShareLocks" and all were granted.
The odd "RowExclusiveLock" appeared but none persisted. On the basis
that nothing seems to be waiting for a lock, I don't think it is a
locking problem. I think the vast number of locks is symptom of the fact
that the server is trying to service a vast number of requests.
Eventually, the server seemed to catch up with itself - the CPU went up,
the VM went down and the number of queries in pg_stat_activity reduced.
The problem then occurred a second time and there seemed to be a lot of
pageouts and pageins going on, but I was only looking at top so it was
difficult to tell. I have now restarted with a statement_timeout of 2
mins to protect the server from poorly performing queries (fairly brutal
- but it does at least stop the downward spiral). I have also reduced
the sort_mem to 1024. I guess it could be that we simply need more
memory in the server.
I have got vmstat (vm_stat on Mac) running and I will watch the
behaviour......
Regards, Alex Stanier.
Tom Lane wrote:
>Alexander Stanier <alexander.stanier@egsgroup.com> writes:
>
>
>>The database seems to fine to start with, but then as the load increases
>>it seems to reach a threshold where the number of non-idle queries in
>>pg_stat_activity grows heavily and we appear to get something similar to
>>a motorway tail back with up to perhaps 140 queries awaiting processing.
>>At the same time the virtual memory usage (reported by the OS) appears
>>to grow heavily too (sometimes up to 50GB). The CPUs do not seems to be
>>working overly hard nor do the disks and the memory monitor reports
>>about 600MB of inactive memory.
>>
>>
>
>You shouldn't be putting a lot of credence in the virtual memory usage
>then, methinks. Some versions of top count the Postgres shared memory
>against *each* backend process, leading to a wildly inflated figure for
>total memory used. I'd suggest watching the output of "vmstat 1" (or
>local equivalent) to observe whether there's any significant amount of
>swapping going on; if not, excessive memory usage isn't the problem.
>
>Are you sure that the problem isn't at the level of some query taking an
>exclusive lock and then sitting on it? I would expect either CPU or
>disk bandwidth or both to be saturated if you were having a conventional
>resource limitation problem. Again, comparing vmstat readings during
>normal and slow response conditions would be instructive.
>
> regards, tom lane
>
>
>