public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomas Vondra <[email protected]>
To: [email protected]
Subject: Re: https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgressql-server
Date: Tue, 19 Sep 2017 15:08:54 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAGPtGMd1dVK3cYYjQnoNx5D8U=D_RGA5Nx_89X0m5DV7n_ffRg@mail.gmail.com>
References: <CAGPtGMd1dVK3cYYjQnoNx5D8U=D_RGA5Nx_89X0m5DV7n_ffRg@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>



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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

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: [email protected]
  Cc: [email protected]
  Subject: Re: https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgressql-server
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox