Re: 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,

----- Original Message -----
From: Igor Neyman <ineyman(at)perceptron(dot)com>
To: Ioana Danes <ioanasoftware(at)yahoo(dot)ca>; PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Cc:
Sent: Tuesday, May 14, 2013 11:06:25 AM
Subject: Re: [GENERAL] Running out of memory on vacuum

> -----Original Message-----
> From: Ioana Danes [mailto:ioanasoftware(at)yahoo(dot)ca]
> Sent: Tuesday, May 14, 2013 10:30 AM
> To: Igor Neyman; PostgreSQL General
> Subject: Re: [GENERAL] Running out of memory on vacuum
>
> Hi Igor,
>
> 1. I could remove the nightly vacuum but I think that is  not the
> cause. The vacuum is only catching the problem. If I ignore the  vacuum
> message for few days the system is gonna run out of memory on
> queries...
> 2. There is no autovacuum running in the same time. I tried to run
> vacuum verbose manually and checked what else was going on on the
> server.
> I also reduced the maintenance work mem to 1 GB but I get the same
> error.
> 3.
>  I do use connection pooling. I have 1500 terminals selling and at busy
> times I might need more than 100 active connections but just
> occationally...
>
> Thanks for quick response,
>
>

You still didn't explain, why do you need manual vacuuming.
You have autovacuum set "on", so it'll wake up every so often do its job based on other autovacuum config parameters.

What kind of connection pooling are you using?
Is it set for session pooling, or transaction, or statement pooling?

Having more than 100 active connections at a time does not mean that all of them executing queries at the same time.
Unless you have a lot of processor cores (and I mean a LOT!), it does not make sense to set pool at 180, even less so at 300.

Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general