Postgres Pro
Downloads
Home   >   mailing lists

Re: Understanding Postgres Memory Usage - Mailing list pgsql-general

From Theron Luhn
Subject Re: Understanding Postgres Memory Usage
Date
Msg-id CAHYFdT_kYBCD1x6WcUunDGzHRsDnOiC1Vj=366csvAs-mFky8g@mail.gmail.com
Whole thread Raw
In response to Re: Understanding Postgres Memory Usage  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Understanding Postgres Memory Usage  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
> 9.3.which?  We do fix memory leaks from time to time ...

9.3.14

> 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.

> 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.


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?


— Theron

On Thu, Aug 25, 2016 at 9:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Theron Luhn <theron@luhn.com> writes:
> I have an application that uses Postgres 9.3 as the primary datastore.

9.3.which?  We do fix memory leaks from time to time ...

> Some of these queries use quite a bit of memory.  I've observed a
> "high-water mark" behavior in memory usage:  running a query increases the
> worker memory by many MBs (beyond shared buffers), but the memory is not
> released until the connection is closed.

Hm.  I'm not familiar with smem, but assuming that that USS column
really is process-private space, that definitely looks bad.

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.

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.

                        regards, tom lane

pgsql-general by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Re: Updated RUM-index and support for bigint as part of index
Next
From: Peter Geoghegan
Date:
Subject: Re: ON CONFLICT does not support deferrable unique constraints