Re: Thousands of schemas and ANALYZE goes out of memory

On Mon, Oct 1, 2012 at 12:52 PM, Hugo <Nabble> <hugo.tech@gmail.com> wrote:
> Hi everyone,
>
> We have two postgresql 9.0 databases (32-bits)

Why 32 bits?  Is that what your hardware is?

> with more than 10,000
> schemas. When we try to run ANALYZE in those databases we get errors like
> this (after a few hours):
>
> 2012-09-14 01:46:24 PDT ERROR:  out of memory
> 2012-09-14 01:46:24 PDT DETAIL:  Failed on request of size 421.
> 2012-09-14 01:46:24 PDT STATEMENT:  analyze;
>
> (Note that we do have plenty of memory available for postgresql:
> shared_buffers=2048MB, work_mem=128MB, maintenance_work_mem=384MB,
> effective_cache_size = 3072MB, etc.)

That might be the problem.  I think with 32 bits, you only 2GB of
address space available to any given process, and you just allowed
shared_buffers to grab all of it.

Cheers,

Jeff