How postgresql works with memory?

PostgreSQL
1

Hi.

In one recent research, i fails to understand of how PostgreSQL work with physical memory when executing a queries. Perhaps, someone had experience in the analysis of this issue.
All tests was acted on PostgreSQL 12, Debian 10.
In first, we have an execution plan of query. The execution plan contains HASH JOIN operations (search by query (regex “hash.+join”) shows us about 82 hash joins).
In FIRST test, work_mem on the postgresql-server is set to 256MB.
The request consumes RAM in the amount of 20GB. It can be seen from HTOP (RSS - process resident memory).
If you look at the execution plan by searching for the keyword “Memory Usage”, you can see that the total amount of memory consumption is much less - around 100Mb.

Search by “Memory Usage”:
Row 14456: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 14461: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 14472: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 14477: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 14482: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 14487: Buckets: 1024Batches: 1Memory Usage: 74kB
Row 17343: Buckets: 1024Batches: 1Memory Usage: 15kB
Row 17348: Buckets: 1024Batches: 1Memory Usage: 15kB
Row 17364: Buckets: 1024Batches: 1Memory Usage: 14kB
Row 17369: Buckets: 1024Batches: 1Memory Usage: 14kB
Row 17374: Buckets: 4096Batches: 1Memory Usage: 373kB
Row 20935: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 20940: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 20951: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 20956: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 20961: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 20966: Buckets: 1024Batches: 1Memory Usage: 74kB
Row 22665: Buckets: 1024Batches: 1Memory Usage: 101kB
Row 22973: Buckets: 1024Batches: 1Memory Usage: 10kB
Row 23074: Buckets: 1024Batches: 1Memory Usage: 21kB
Row 23111: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23145: Buckets: 1024Batches: 1Memory Usage: 9kB
Row 23149: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23183: Buckets: 2048Batches: 1Memory Usage: 80kB
Row 23282: Buckets: 2048Batches: 1Memory Usage: 76kB
Row 23286: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23290: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23325: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23357: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23389: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23421: Buckets: 2048Batches: 1Memory Usage: 106kB
Row 23455: Buckets: 2048Batches: 1Memory Usage: 97kB
Row 23521: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23525: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23557: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23589: Buckets: 4096Batches: 1Memory Usage: 166kB
Row 23593: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23597: Buckets: 1024Batches: 1Memory Usage: 9kB
Row 23719: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23723: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23755: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23787: Buckets: 1024Batches: 1Memory Usage: 9kB
Row 23852: Buckets: 1024Batches: 1Memory Usage: 9kB
Row 23884: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23916: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23948: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 23980: Buckets: 1024Batches: 1Memory Usage: 10kB
Row 23984: Buckets: 1024Batches: 1Memory Usage: 51kB
Row 23988: Buckets: 2048Batches: 1Memory Usage: 113kB
Row 24052: Buckets: 1024Batches: 1Memory Usage: 20kB
Row 24056: Buckets: 1024Batches: 1Memory Usage: 13kB
Row 24060: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 24092: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 24096: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 24128: Buckets: 1024Batches: 1Memory Usage: 9kB
Row 24160: Buckets: 1024Batches: 1Memory Usage: 10kB
Row 24264: Buckets: 1024Batches: 1Memory Usage: 10kB
Row 24268: Buckets: 2048Batches: 1Memory Usage: 128kB
Row 24279: Buckets: 1024Batches: 1Memory Usage: 100kB
Row 24283: Buckets: 65536Batches: 1Memory Usage: 5289kB
Row 24290: Buckets: 8192Batches: 1Memory Usage: 447kB
Row 24293: Buckets: 1024Batches: 1Memory Usage: 10kB
Row 24604: Buckets: 65536Batches: 1Memory Usage: 17414kB
Row 24617: Buckets: 65536Batches: 1Memory Usage: 16899kB
Row 24622: Buckets: 65536Batches: 1Memory Usage: 16899kB
Row 24639: Buckets: 65536Batches: 1Memory Usage: 13806kB
Row 24642: Buckets: 8192Batches: 1Memory Usage: 634kB
Row 24645: Buckets: 1024Batches: 1Memory Usage: 10kB
Row 24649: Buckets: 32768Batches: 1Memory Usage: 1795kB
Row 24652: Buckets: 1024Batches: 1Memory Usage: 8kB
Row 24656: Buckets: 8192Batches: 1Memory Usage: 1335kB
Row 24659: Buckets: 1024Batches: 1Memory Usage: 13kB
Row 24666: Buckets: 131072Batches: 1Memory Usage: 7118kB
Row 24951: Buckets: 1024Batches: 1Memory Usage: 79kB
Row 24955: Buckets: 1024Batches: 1Memory Usage: 10kB
Row 24959: Buckets: 2048Batches: 1Memory Usage: 128kB
Row 24963: Buckets: 32768Batches: 1Memory Usage: 2723kB
Row 24998: Buckets: 131072Batches: 1Memory Usage: 6467kB
Row 25033: Buckets: 4096Batches: 1Memory Usage: 307kB
Row 25037: Buckets: 1024Batches: 1Memory Usage: 100kB
Row 25041: Buckets: 4096Batches: 1Memory Usage: 295kB
Row 25045: Buckets: 1024Batches: 1Memory Usage: 59kB

At the same time, it is known that the memory of the postgresql backend process includes 4 types of memory:

  1. temp_buffers
  2. work_mem
  3. maintenance_work_mem
  4. vacuum_buffers

work_mem has the following purpose:
This is the amount of memory reserved for either a single sort or hash table operation in a query and it is controlled by work_mem database parameter. A sort operation could be one of an ORDER BY, DISTINCT or Merge join and a hash table operation could be due to a hash-join, hash based aggregation or an IN subquery.
A single complex query may have many number of such sort or hash table operations, and as many chunks of memory allocations defined by the work_mem parameter will be created for each of those operations in a user connection. It is for this reason, that work_mem should not be declared to a very big value as it might lead to aggressively utilizing all the available memory from operating system for a considerably huge query, thereby starving the operating system of RAM which might be needed for other processes.

Thus, there are several questions:

  1. IS IT TRUE that 20GB displayed in HTOP VSS is real physical memory? Does this memory NOT include shared memory (shared_buffers) ?
  2. How can i generally determine which operations the scheduler needed 20GB of PHYSICAL memory for?
  3. How can i determine the memory assignment between the 4 types of memory described above?

Next step i performed - i changed the amount of work_mem to default value (4Mb).
BTW, the amount of memory consumed by the query did NOT change - it remained at the same value of 20GB.

  1. Can you explain why changing the work_mem parameter does not affect the request memory consumed? In the execution plan, there are both sorts and hash joins.
1 Like
2

Hi

Postgres utilizes memory for several tasks however when work_mem is involved it consumes “free RAM memory” that is totally unrelated to the postgres memoery requirements for shared buffer and the postgres application process.

Every time work_mem is allocated it is from the remaining RAM that hasn’t been used up.

Example

top - 11:10:46 up 3 days, 22:54,  3 users,  load average: 0.64, 0.92, 0.98
Tasks: 454 total,   1 running, 453 sleeping,   0 stopped,   0 zombie
%Cpu(s):  5.2 us,  3.2 sy,  0.0 ni, 90.9 id,  0.1 wa,  0.0 hi,  0.6 si,  0.0 st
MiB Mem :   7606.4 total,    553.1 free,   3836.5 used,   3216.8 buff/cache
MiB Swap:   2048.0 total,      5.2 free,   2042.8 used.   2239.6 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND

In this snippet, from top, you can see there is 2239.6 avail Mem and that’s where you get the RAM memory for work_mem, maintenance_work_mem etc. Mind you postgres tries very hard to use RAM only memory and avoids the swap memory as much as possible. However, when there is an over allocation of assigned free RAM, such as the case when took many SQL statements use too much free RAM from very large work_mem values, postgres will start using swap and that’s when performance can drop.

Hope this helps.

1 Like