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