Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group