Re: Huge amount of memory consumed during transaction

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: henk de wit <henk53602(at)hotmail(dot)com>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Huge amount of memory consumed during transaction
Date: 2007-10-17 23:28:42
Message-ID: 12886.1192663722@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Alvaro Herrera <alvherre(at)commandprompt(dot)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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-10-18 00:23:39 Re: Incorrect estimates on columns
Previous Message Alvaro Herrera 2007-10-17 20:40:58 Re: Huge amount of memory consumed during transaction