From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Marco Cassiano <mcassiano@manord.com>
To: pgsql-admin@postgresql.org
Subject: Re: R: Out of memory...
Date: Mon, 20 Feb 2012 11:09:56 -0600
Message-ID: <4F422A0402000025000458DA@gw.wicourts.gov> (raw)
In-Reply-To: <BC53C974C3B9E542BC0A9BD4C5B1168A08BBCA28@NEWMAIL.manord.com>
References: <BC53C974C3B9E542BC0A9BD4C5B1168A0E9326E0@NEWMAIL.manord.com>
<4F3E6BA3020000250004581A@gw.wicourts.gov>
<BC53C974C3B9E542BC0A9BD4C5B1168A08BBCA28@NEWMAIL.manord.com>
"Cassiano, Marco" <mcassiano@manord.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, mcassiano@manord.com
Subject: Re: R: Out of memory...
In-Reply-To: <4F422A0402000025000458DA@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox