Re: Running out of memory on vacuum

> -----Original Message-----
> From: Ioana Danes [mailto:ioanasoftware(at)yahoo(dot)ca]
> Sent: Tuesday, May 14, 2013 11:29 AM
> To: Igor Neyman; PostgreSQL General
> Subject: Re: [GENERAL] Running out of memory on vacuum
>
>
>
> Hi Igor,
>
> I don't need the manual vacuum. I just don't want to remove it now
> because it gives me a clue that something is wrong and I need to
> restart postgres to free up the memory. Otherwise I run out of memory
> later in the day and that is something I want to avoid.
>
> Even if "I don't need it" I don't think it is "normal" to run out of
> memory in these conditions. (I had it off for a while and I did run out
> of memory on pg_dump).
>
>
> We use jdbc connection pooling and it does make sense to have it higher
> than 100 at some points during the day. Anyhow at the time of the
> vacuum there is nothing else going on on the database. Sales are off.
>
> Thanks,
>

Ioana,

You are probably correct that manual vacuum just highlights the problem, causing it to occur sooner rather than later.

The real problem is the number of connections, your hardware isn't good enough to support over a 100 of connections.
Connection pooler is needed, but not JDBC connection pooling, which probably does nothing for you.

I'd suggest that server-side connection pooler would fix your problem, if set up properly.
Take a look at PgBouncer. It is "lightweight", very easy to install and configure. I've been using it for years and have nothing but the praise for it.

Igor Neyman