Subject: Re:
 https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgressql-server
To: pgsql-performance@postgresql.org
References: 
 <CAGPtGMd1dVK3cYYjQnoNx5D8U=D_RGA5Nx_89X0m5DV7n_ffRg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <184477af-9ac6-6a29-40c5-be2699412d84@2ndquadrant.com>
Date: Tue, 19 Sep 2017 15:08:54 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGPtGMd1dVK3cYYjQnoNx5D8U=D_RGA5Nx_89X0m5DV7n_ffRg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 09/19/2017 02:49 AM, 園田祥平 wrote:
> Hi experts,
> 
> For an academic experiment I need to *restrict the total amount of
> memory that is available for a pgSQL server* to compute a given set of
> queries.
> 
> I know that I can do this through |postgressql.conf|file, where I can
> adjust some parameters related with Resource Management.
> 
> The problem is that: it's not clear for me--given the several parameters
> available on the config file--which is the parameter that I should change. 
> > When I first opened the config file I'm expecting someting like
> this: |max_server_memmory|. Instead I found a lot
> of: |shared_buffers|, |temp_buffers|, |work_mem|, and so on...
> 
> Given that, I've consulted pgSQL docs. on Resource Consumption
> <http://www.postgresql.org/docs/9.3/static/runtime-config-resource.html> and
> I come up with the |shared_buffers| as the best candidate for what I'm
> looking for: *the parameter that restricts the total amount of memory
> that a pgSQL server can use to perform its computation*. But I'm not
> completely sure about this. 
> 
> Can you guys give me some insight about which parameters should I adjust
> to restrict the pgSQL server's memory, please?
> 

The short answer is "You can't do that from within PostgreSQL alone."
You can define size of some memory buffers, but not some hard total
limit. One reason is that queries may use multiple work_mem buffers, we
don't know how much memory the other queries are consuming, etc. We also
don't have any control over page cache, for example.

If you really need to do that, you'll need to do that at the OS level,
e.g. by specifying "mem=X" kernel parameter, at the VM level, or
something like that.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance