To: Alexander Stanier <alexander.stanier@egsgroup.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Heavy virtual memory usage on production system 
In-reply-to: <42CAB1B6.50201@egsgroup.com> 
References: <42CAB1B6.50201@egsgroup.com>
Comments: In-reply-to Alexander Stanier <alexander.stanier@egsgroup.com>
	message dated "Tue, 05 Jul 2005 17:13:42 +0100"
Date: Tue, 05 Jul 2005 12:55:16 -0400
Message-ID: <23889.1120582516@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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