Re: Understanding Postgres Memory Usage

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Theron Luhn <theron(at)luhn(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Understanding Postgres Memory Usage
Date: 2016-08-25 18:25:45
Message-ID: 18313.1472149545@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Theron Luhn <theron(at)luhn(dot)com> writes:
>> If it's not an outright leak, it's probably consumption of cache space.
>> We cache stuff that we've read from system catalogs, so sessions that
>> touch lots of tables (like thousands) can grow due to that. Another
>> possible source of large cache consumption is calling lots-and-lots of
>> plpgsql functions.

> I have a reasonable number of tables (around 50) and very few plpgsql
> functions.

Doesn't sound like a lot ...

>> If the same query, repeated over and over, causes memory to continue
>> to grow, I'd call it a leak (ie bug). If repeat executions consume
>> no additional memory then it's probably intentional caching behavior.

> Here's the results of that:
> https://gist.github.com/luhn/e09522d524354d96d297b153d1479c13

> So kind of a combination of the two: Memory usage increases up to a
> certain point but then plateaus. So... cache? It's ~100MB increase,
> though, which seems an excessive amount. What could be taking up that much
> cache?

Hmm. I find it mighty suspicious that the USS, PSS, and RSS numbers are
all increasing to pretty much the same tune, ie from very little to circa
100MB. I think there is a decent chance that smem is not doing what it
says on the tin, and in fact is including shared memory consumption in
"USS". In which case the apparent leak just corresponds to the process
gradually touching more and more of the shared buffer arena. (If your
shared_buffers settings is not somewhere near 100MB, then this theory
breaks down.)

It would be worth using plain old top to watch this process. We have
enough experience with that to be pretty sure how to interpret its
numbers: "RES minus SHR" is the value to be worried about.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2016-08-25 18:34:46 Re: Understanding Postgres Memory Usage
Previous Message Peter Geoghegan 2016-08-25 18:24:36 Re: ON CONFLICT does not support deferrable unique constraints