From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: henk de wit <henk53602@hotmail.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Huge amount of memory consumed during transaction
Date: Wed, 17 Oct 2007 19:28:42 -0400
Message-ID: <12886.1192663722@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@commandprompt.com, henk53602@hotmail.com, scott.marlowe@gmail.com
Subject: Re: Huge amount of memory consumed during transaction
In-Reply-To: <12886.1192663722@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox