Re: Does RelCache/SysCache shrink except when relations are deleted?

On Thu, Sep 29, 2011 at 7:23 AM, MauMau <maumau307(at)gmail(dot)com> wrote:
> From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
> can we see all of your memory settings plus physical memory?  the
> solution is probably going to be reducing shared buffers an/or adding
> physical memory.
>
> Thank you for your response.
>
> The amount of physical memory is 8GB, which is enough for the workload. I
> asked the customer for the output of "SHOW ALL", but I haven't received it
> yet. However, shared_buffers should be less than 1.6GB because, as I wrote
> in the previous mail, top command showed 1.6GB in "VIRT" column before
> executing somefunc() PL/pgSQL function.
>
> The direct cause of "out of memory" is that the virtual memory became full.
> 32-bit Linux can allocate 3GB of user space in the virtual address space of
> each process. somefunc() used 1.0GB, which led to 2.6GB of virtual memory.
> After somefunc(), VACUUM tried to allocate 256MB of maintenance_work_mem.
> That allocation failed because the virtual address space was almost full.
>
> As you mentioned, decreasing shared_buffers will be one of the solutions.
> However, we want to know why somefunc() uses so much memory. Therefore, the
> following is the core question. Q2 and Q3 are supplementary ones. It is just
> my guess that RelCache/SysCache may be the cause.

Oh -- I missed earlier that this was 32 bit o/s. Well, I'd consider
drastically reducing shared buffers, down to say 256-512mb range.
Postgres function plans and various other structures, tables,
attributes are indeed cached and can use up a considerable amount of
memory in pathological cases -- this is largely depending on the
number of tables/views, number of functions and number of connections.
I briefly looked at the relcache etc a little while back on a related
complaint and the takeaway is that the caching is heavy handed and
fairly brute force but legit and a huge win for most cases. This stuff
lives in the cache memory context and a couple of users (not that
many) have bumped into high memory usage. Solutions tend to include:

*) not rely on implementation that requires 100000 tables
*) use connection pooler
*) reset connections
*) go to 64 bit o/s
*) reduce shared_buffers for leaner memory profile (especially in 32 bit os)

Like I said, this doesn't really come up this often but the 'real'
solution in terms of postgrs is probably some kind of upper bound in
the amount of cache memory used plus some intelligence in the cache
implementation. This is tricky stuff though and so far no credible
proposals have been made and the demand for the feature is not very
high.

merlin