Thread: [PERFORM] https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgressql-server

On Tue, 19 Sep 2017 00:49:14 +0000, ???? <shohei.nkapl@gmail.com> wrote:

> 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.conffile, 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?

What you are asking - a way to configure Postgresql to a hard memory 
limit - effectively is impossible.  Shared memory isn't really a hard 
limit on anything - it's just a cache for query results.  You can limit 
how much is available, but there isn't any way to limit how much a 
particular query [worker process] can take.  Then, local [to the worker 
process] work buffers are allocated as needed to perform the joins, 
sorts, groupings, etc. as specified by the query.  For any given query, 
you may be able to explain/analyze your way to a reasonable estimate of 
the maximum allocation, but there isn't any way via configuration to 
actually limit the worker process to that maximum.

The only way I can think of to impose such limits would be to sandbox 
the processes with ULIMIT.  If you set appropriate limits before 
starting the postmaster process, those limits will apply to every worker 
process it spawns afterwards.   The thing to remember is that limits on 
processes apply individually - e.g., if you say "ulimit -d 500000" and 
then start Postgresql, each individual worker process will be able to 
use up to 500MB.  And when you limit the data size or the address space, 
you need to consider and include the shared memory.
see https://ss64.com/bash/ulimit.html

If you want to place a global limit on the entire Postgresql "server" 
[i.e. the collection of worker processes], you can limit the user that 
owns the processes (in /etc/security/limits.conf) - which usually is 
"postgres" when Postgresql is run as a service.


Using ulimit isn't difficult if you are starting/stopping Postgresql 
manually, but it's a pain when Postgresql is running as a system 
service.  To limit a service, you have to either limit the owning user 
[and hope that doesn't break something else], or find and edit the init 
scripts that start the service, and what to do there depends on whether 
the system is using SysVinit or Upstart to manage the services.

If you're on Windows, good luck.   I know that there are things called 
"Job objects"  [something in between Linux sessions and process groups] 
that can be used to limit process resources ... but I have no idea how 
to do that.

Hope this ... doesn't confuse even more.
George


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

On 09/18/2017 10:44 PM, George Neuner wrote:
> On Tue, 19 Sep 2017 00:49:14 +0000, ???? <shohei.nkapl@gmail.com> wrote:
>
>> 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.conffile, 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?
>
> What you are asking - a way to configure Postgresql to a hard memory
> limit - effectively is impossible.  Shared memory isn't really a hard
> limit on anything - it's just a cache for query results.  You can limit
> how much is available, but there isn't any way to limit how much a
> particular query [worker process] can take.  Then, local [to the worker
> process] work buffers are allocated as needed to perform the joins,
> sorts, groupings, etc. as specified by the query.  For any given query,
> you may be able to explain/analyze your way to a reasonable estimate of
> the maximum allocation, but there isn't any way via configuration to
> actually limit the worker process to that maximum.
>
> The only way I can think of to impose such limits would be to sandbox
> the processes with ULIMIT.  If you set appropriate limits before
> starting the postmaster process, those limits will apply to every worker
> process it spawns afterwards.   The thing to remember is that limits on
> processes apply individually - e.g., if you say "ulimit -d 500000" and
> then start Postgresql, each individual worker process will be able to
> use up to 500MB.  And when you limit the data size or the address space,
> you need to consider and include the shared memory.
> see https://ss64.com/bash/ulimit.html
>
> If you want to place a global limit on the entire Postgresql "server"
> [i.e. the collection of worker processes], you can limit the user that
> owns the processes (in /etc/security/limits.conf) - which usually is
> "postgres" when Postgresql is run as a service.


The easiest way to impose a limit on the entire Postgres cluster is to
run it in a container using Docker. For example you could use the image
from hub.docker.com and run it with the "--memory" argument.

https://hub.docker.com/_/postgres/
https://docs.docker.com/engine/reference/commandline/run/

--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com

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