Re: Help with postgresql memory issue

On Wed, 28 Oct 2009, Greg Stark wrote:

>>   PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
>>  5069 postgres   1  52    0  167M   20M sleep    0:04 13.50% postgres
>
> Hm, well 400 processes if each were taking 190M would be 76G. But that
> doesn't really make much sense since most of the 167M of that process
> is presumably the shared buffers. What is your shared buffers set to
> btw? And your work_mem and maintenance_work_mem?

Pieced together from the upthread config file posts:

shared_buffers = 128MB
work_mem = 16MB
max_connections = 400

So somewhere bewteen 128MB and 167MB of that SIZE is shared_buffers plus
the other usual shared memory suspects. Let's say each process is using
40MB, which is on the high side. I'd guess this system might peak at 40MB
* 400 connections+170MB~=16GB of database RAM used, which is so much less
than physical RAM it seems more like a software limit is being run into
instead.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>From pgsql-general-owner(at)postgresql(dot)org Wed Oct 28 18:56:46 2009
Received: from maia.hub.org (unknown [200.46.204.183])
by mail.postgresql.org (Postfix) with ESMTP id 466E263369E
for <pgsql-general-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Wed, 28 Oct 2009 18:56:46 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
with ESMTP id 24234-07
for <pgsql-general-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
Wed, 28 Oct 2009 21:56:40 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
by mail.postgresql.org (Postfix) with ESMTP id 5F7066334ED
for <pgsql-general(at)postgresql(dot)org>; Wed, 28 Oct 2009 18:56:36 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])
by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n9SLuWvS020041;
Wed, 28 Oct 2009 17:56:32 -0400 (EDT)
To: Greg Smith <gsmith(at)gregsmith(dot)com>
cc: Brooks Lyrette <brooks(dot)lyrette(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Help with postgresql memory issue
In-reply-to: <alpine(dot)GSO(dot)2(dot)01(dot)0910281734210(dot)18269(at)westnet(dot)com>
References: <CE2F951B-6FAF-488A-86AC-C204797670D0(at)gmail(dot)com> <4400(dot)1256760074(at)sss(dot)pgh(dot)pa(dot)us> <alpine(dot)GSO(dot)2(dot)01(dot)0910281734210(dot)18269(at)westnet(dot)com>
Comments: In-reply-to Greg Smith <gsmith(at)gregsmith(dot)com>
message dated "Wed, 28 Oct 2009 17:37:38 -0400"
Date: Wed, 28 Oct 2009 17:56:32 -0400
Message-ID: <20040(dot)1256766992(at)sss(dot)pgh(dot)pa(dot)us>
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
tests=BAYES_00=-2.599
X-Spam-Level:
X-Archive-Number: 200910/1131
X-Sequence-Number: 154825

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> On Wed, 28 Oct 2009, Tom Lane wrote:
>> What's the platform exactly? Is it possible that the postmaster is
>> being launched under very restrictive ulimit settings?

> Now that Brooks mentioned this being run inside of a Solaris zone, seems
> like this might be running into some memory upper limit controlled by the
> zone configuration.

A bit of quick googling confirmed that there is (or can be) a per-zone
memory cap. I'll bet Greg has nailed it. The docs I found claim that
the cap management code is smart enough to count shared memory only
once, which would eliminate the most obvious way in which the cap might
be way off; but it still sounds like checking into that configuration
setting is job #1.

regards, tom lane