Thread: PostgreSQL oom_adj postmaster process to -17

Radovan Jablonovsky 

Radovan Jablonovsky <radovan.jablonovsky@replicon.com> wrote:

> We are running PostgreSQL version 9.1.1

You should apply the latest bug fixes by updating to 9.1.4.

http://www.postgresql.org/support/versioning/

> with 32GB of RAM, 32GB of SWAP and during high load we could reach
> (swap + RAM) memory limit.

If you're even *starting* to swap you're doing something wrong, much
less exhausting swap space equal to actual RAM.  What is your
configuration?

http://wiki.postgresql.org/wiki/Server_Configuration

While it's probably a good idea to configure the OOM killer to
behave more sanely, we tend to ignore it in favor of ensuring that
it never comes into play.  We run about 200 databases 24/7 and I
think I've seen it kick in about twice -- when we ran queries that
leaked memory on each row in a big query.

-Kevin

Radovan Jablonovsky <radovan.jablonovsky@replicon.com> writes:
> In usual load there are not much pressure on memory, but it is possible to
> have all clients start using heavy reports. They are valid requests and
> could consume all memory. In this border and not likely but possible
> scenario it could be useful to let OOM killer to kill client's
> processes/connections but leave PostgreSQL system processes (postmaster,
> writer, stat, log, streaming, ...) excluded  from reach of OOM killer.

The code already supports excluding the postmaster itself from OOM kills
while letting its children be subject to them.  Being selective about
which children are subject is pointless, though: if any child is kill
-9'd, we have to zap the rest and restart, because there is no way to be
sure that the victim left shared memory in a consistent state.

            regards, tom lane

Radovan Jablonovsky <radovan.jablonovsky@replicon.com> wrote:

> In usual load there are not much pressure on memory, but it is
> possible to have all clients start using heavy reports. They are
> valid requests and could consume all memory.

Your clients will get their results back faster if you can arrange
some way to queue these sorts of requests when they get beyond some
reasonable limit.  You might be able to do that using a connection
pool, or you might want to create some sort of job queue which
releases a limited number of such jobs at a time; but I guarantee
that every single person to submit a job to such a queue, including
the last person, will get their results sooner with such queuing
than turning loose a "thundering herd" of requests which puts the
system into swapping.  I guarantee it.

-Kevin

On Fri, Aug 3, 2012 at 12:08 PM, Radovan Jablonovsky
<radovan.jablonovsky@replicon.com> wrote:
> Thanks you for your response.
>
> Database config:
> shared_buffers = 8GB
> temp_buffers = 32MB
> work_mem = 64MB
> maintenance_work_mem = 512MB
> effective_cache_size = 16GB
>
> In usual load there are not much pressure on memory, but it is possible to
> have all clients start using heavy reports. They are valid requests and
> could consume all memory. In this border and not likely but possible
> scenario it could be useful to let OOM killer to kill client's
> processes/connections but leave PostgreSQL system processes (postmaster,
> writer, stat, log, streaming, ...) excluded  from reach of OOM killer.

You're only realistic solution is to either limit the incoming
connections via a connection pooler like pgbouncer or to lower your
work_mem to something smaller. What's you're current max connections
setting?

Radovan Jablonovsky | SaaS DBA | Phone 1-403-262-6519 (ext. 256) | Fax 1-403-233-8046

Replicon | Hassle-Free Time & Expense Management Software - 7,300 Customers - 70 Countries
www.replicon.com | facebook | twitter | blog | contact us

We are hiring! | search jobs

Radovan Jablonovsky <radovan.jablonovsky@replicon.com> wrote:

> PostgreSQL version 9.1.1 with 32GB of RAM

> shared_buffers = 8GB
> temp_buffers = 32MB
> work_mem = 64MB
> maintenance_work_mem = 512MB

> Currently there are maximum 600 connections.

Please read:

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

-Kevin

On 08/09/2012 04:24 AM, Kevin Grittner wrote:
> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
>
Can we please please PLEASE link to that as a comment above max_connections?

Last time this came up nobody was happy with wording of a comment so
nothing got done. It's a real usability wart - causing real-world
performance and reliability problems - that people unwittingly raise
max_connections to absurd levels because they get no warnings, hints or
guidance of any sort.

--
Craig Ringer

Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 08/09/2012 04:24 AM, Kevin Grittner wrote:
>> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
>>
> Can we please please PLEASE link to that as a comment above
> max_connections?
>
> Last time this came up nobody was happy with wording of a comment
> so nothing got done. It's a real usability wart - causing
> real-world performance and reliability problems - that people
> unwittingly raise max_connections to absurd levels because they
> get no warnings, hints or guidance of any sort.

I see that we currently have five links to wiki.postgresql.org in
release notes and four more in the rest of the docs.  Are people OK
with adding this link to the docs on max_connections?  (Feel free to
improve it before answering if you have qualms about the specifics
on that page.)

We do seem to get an awful lot of posts (between here and
StackOverflow) from people who assume they need one database
connection per active user, and then are surprised that performance
is horrible.

If we get consensus on linking to this I'll put together a patch to
make a brief comment in the docs with a link to the Wiki.

-Kevin