Re: Thousands of schemas and ANALYZE goes out of memory

On Tue, Oct 02, 2012 at 10:38:38AM -0700, Hugo <Nabble> wrote:
> > 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.
>
> The address space for 32 bits is 4Gb. We just tried to reach a balance in
> the configuration and it seems to be working (except for the ANALYZE command
> when the number of schemas/tables is huge).

Are you sure about that? You don't say what OS you are using but on
Linux 3Gb is normal and on Windows 2Gb.

Here are some nice diagrams:

http://duartes.org/gustavo/blog/post/anatomy-of-a-program-in-memory

In my experience it's better to keep the shared buffers around your
working set size and let the kernel cache the rest as needed. Setting
the shared_buffers to 1Gb will give your server much more breathing
space for large operations like what you are asking.

Note that unlike the way some other database servers work, the
shared_buffers is the *minimum* postgres will use, not the maximum.

> Some questions I have:
>
> 1) Is there any reason to run the ANALYZE command in a single transaction?
> 2) Is there any difference running the ANALYZE in the whole database or
> running it per schema, table by table?

I don't think it does do everything in a single transaction, though I
can imagine that if you try to analyse the whole database it uses up
more memory to track the work it has to do. With 220,000 tables I
imagine this could add up.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer