Re: R: Out of memory...

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Marco Cassiano" <mcassiano(at)manord(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: R: Out of memory...
Date: 2012-02-20 17:09:56
Message-ID: 4F422A0402000025000458DA@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

"Cassiano, Marco" <mcassiano(at)manord(dot)com> wrote:

> "autovacuum_max_workers";"7"
> "autovacuum_naptime";"10min"
> "autovacuum_vacuum_cost_delay";"20ms"
> "autovacuum_vacuum_cost_limit";"200"

You've made autovacuum a little less aggressive for small,
heavily-updated tables with the 10min naptime, even with 7 workers.
That's some cause for concern, but probably not the immediate
problem.

> "fsync";"off"
> "full_page_writes";"off"

With those settings, be prepared to go to your last known good
backup if the OS or hardware crashes.

> "maintenance_work_mem";"300MB"

Each of your seven autovacuum processes can be using that, for 2.1GB
in autovacuum work space.

> "max_connections";"250"
> "temp_buffers";"8MB"
> "work_mem";"12MB

If you had all connections busy, using temp tables and doing sorts
or hash tables, you could hit 5GB or more in space for that. On top
of the shared_buffers and maintenance work memory, you could hit 8GB
or more in RAM usage pretty easily. You might want to consider a
connection pool to allow the 250 client connections to be limited to
a pool of 20 or 30 database connections on the server side.

Are you using the INET data type? There is a bug introduced in
9.1.2 which can leak memory when using that type. If that's your
problem, you might want to fall back to 9.1.1 and/or install 9.1.3
as soon as it becomes available.

-Kevin

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Michael Monnerie 2012-02-22 06:30:45 speedup pg_dumpall
Previous Message Cassiano, Marco 2012-02-20 16:18:36 R: Out of memory...