To: Alvaro Herrera <alvherre@commandprompt.com>
cc: henk de wit <henk53602@hotmail.com>,
	Scott Marlowe <scott.marlowe@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Huge amount of memory consumed during transaction 
In-reply-to: <20071017204058.GR5737@alvh.no-ip.org> 
References: <BAY124-W276814F77A7A7ABE7596B1F5A70@phx.gbl>
	<dcc563d10710110727p224f69aeoa54647185a70ebef@mail.gmail.com>
	<BAY124-W594E8378868281E1035467F5A70@phx.gbl>
	<20071017204058.GR5737@alvh.no-ip.org>
Comments: In-reply-to Alvaro Herrera <alvherre@commandprompt.com>
	message dated "Wed, 17 Oct 2007 17:40:58 -0300"
Date: Wed, 17 Oct 2007 19:28:42 -0400
Message-ID: <12886.1192663722@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Alvaro Herrera <alvherre@commandprompt.com> writes:
> henk de wit escribió:
>>> How is the memory consumed?  How are you measuring it? I assume you
>>> mean the postgres process that is running the query uses the memory.
>>> If so, which tool(s) are you using and what's the output that shows it
>>> being used?
>> 
>> It's periodically measured and recorded by a script from which the relevant parts are:
>> 
>> GET_VSZ="ps aux | grep $REQ_GREP | grep -v grep | grep -v $$ | awk '{print \$5}'
>> | sort -n | tail -n1";
>> GET_RSS="ps aux | grep $REQ_GREP | grep -v grep | grep -v $$ | awk '{print \$6}'
>> | sort -n | tail -n1";

On many variants of Unix, this is going to lead to a totally misleading
number.  The first problem is that shared buffers will be counted
multiple times (once for each PG process).  The second problem is that,
depending on platform, a specific page of shared memory is counted
against a process only after it first touches that page.  This means
that when you run a big seqscan, or anything else that touches a lot of
buffers, the reported size of the process gradually increases from just
its local memory space to its local memory space plus the total size
of the Postgres shared buffer arena.  This change in the reported size
is *utterly meaningless* in terms of actual memory consumption.

It's not easy to get useful measurements from "ps" when dealing with
heavy memory sharing.  There have been some discussions recently of
alternative tools that let you get a clearer picture; check the
PG list archives.

			regards, tom lane