Question 200987 in PostgreSQL Question posted in
The official documentation can be found here.

Unused Ram in PostgreSQL Database Server

I was hoping to get answer for an old post, but it seems my necromancy skills are not powerful yet. So, the question is what is "unused RAM" in the PostgreSQL database server?

For example; with a PostgreSQL database serverwith 120 GB, shared_buffers is 30 GB, work_mem 500 MB and max_connections is 100. If all the sessions utilize the all of the work_mem(let’ s assume we do not use hash_multiplier due to has join), so in that case 30 GB + (500 MB * 100) = 80GB. For those processes spill to disk will use local temporary areas, but what happens that 40 GB?

Thanks!

Tags:

2

Answers


  1. 0

    Unused RAM in a PostgreSQL database server is simply memory that is not actively being utilized by the PostgreSQL server for any of its operations.

    In the case of a PostgreSQL server with 120 GB of total memory, and with shared_buffers set to 30 GB, work_mem set to 500 MB, and max_connections set to 100, you’re right that the total memory allocation could add up to 80 GB if every connection used its maximum work_mem. However, in reality, not every connection will necessarily be using its full work_mem allocation all the time.

    The remaining 40 GB of memory isn’t specifically allocated to any PostgreSQL process or operation, but it can be used by the PostgreSQL server or other system processes as needed.

    A few points on where it might go:

    • Operating System Cache: The OS automatically uses free memory to
      cache file system data. This is a very efficient use of memory that
      PostgreSQL can’t use for other purposes.
    • Other Postgres Parameters: There are other Postgres parameters that
      also use memory, such as maintenance_work_mem for routine maintenance
      tasks (like VACUUM, CREATE INDEX, etc.), wal_buffers for write-ahead
      logging, etc. However, these generally don’t use a lot of memory.
    • Other Applications: If there are other applications or services
      running on the same server, they could also be using some of this
      memory. Disk Caching: PostgreSQL uses the operating system’s disk
      cache in addition to its own caches. Often this is the most effective
      use of memory, especially if you have a workload with a larger
      "working set" than fits in shared_buffers.
    • Unused: Some of this memory may simply remain unused.

    Lastly, it’s also worth noting that oversubscribing memory (i.e., setting your configuration so that under maximum load your server would require more memory than is physically available) can lead to severe performance degradation, so it’s good to have some memory left unallocated.

    Login or Signup to reply.
  2. 0

    You cannot calculate like this. Not every session uses work_mem, and a single session may use multiple times that amount of memory.

    There are two things you have to distinguish: truly unused memory and available memory. You can see that in the output of free on Linux:

    free -m
               total        used        free      shared  buff/cache   available
Mem:           15792        3391        7734         912        4666       11305
Swap:          16183           0       16183

    Here, 3391 MB are used by processes, 912 MB are allocated as shared memory, 7734 MB are unused and 4666 are used to cache files. Together, there are 11305 MB that can be freed by the operating system if a process needs more memory.

    On a busy system, unused memory is usually very low. You have to make sure that there is always enough available memory, so that you don’t go out of memory. On a PostgreSQL machine, "buff/cache" will cache database files, so it is memory that is used to cache PostgreSQL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search