Обсуждение: [PERFORM] https://stackoverflow.com/questions/28844170/how-to-limit-the-memory-that-is-available-for-postgressql-server
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 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?
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