473,385 Members | 1,907 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > postgresql database > questions > how postgresql works with memory

Join Bytes to post your question to a community of 473,385 software developers and data experts.

How postgresql works with memory

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":
Expand|Select|Wrap|Line Numbers
  1.     Row 14456:     Buckets: 1024Batches: 1Memory Usage: 8kB
  2.     Row 14461:     Buckets: 1024Batches: 1Memory Usage: 8kB
  3.     Row 14472:     Buckets: 1024Batches: 1Memory Usage: 8kB
  4.     Row 14477:     Buckets: 1024Batches: 1Memory Usage: 8kB
  5.     Row 14482:     Buckets: 1024Batches: 1Memory Usage: 8kB
  6.     Row 14487:     Buckets: 1024Batches: 1Memory Usage: 74kB
  7.     Row 17343:     Buckets: 1024Batches: 1Memory Usage: 15kB
  8.     Row 17348:     Buckets: 1024Batches: 1Memory Usage: 15kB
  9.     Row 17364:     Buckets: 1024Batches: 1Memory Usage: 14kB
  10.     Row 17369:     Buckets: 1024Batches: 1Memory Usage: 14kB
  11.     Row 17374:     Buckets: 4096Batches: 1Memory Usage: 373kB
  12.     Row 20935:     Buckets: 1024Batches: 1Memory Usage: 8kB
  13.     Row 20940:     Buckets: 1024Batches: 1Memory Usage: 8kB
  14.     Row 20951:     Buckets: 1024Batches: 1Memory Usage: 8kB
  15.     Row 20956:     Buckets: 1024Batches: 1Memory Usage: 8kB
  16.     Row 20961:     Buckets: 1024Batches: 1Memory Usage: 8kB
  17.     Row 20966:     Buckets: 1024Batches: 1Memory Usage: 74kB
  18.     Row 22665:     Buckets: 1024Batches: 1Memory Usage: 101kB
  19.     Row 22973:     Buckets: 1024Batches: 1Memory Usage: 10kB
  20.     Row 23074:     Buckets: 1024Batches: 1Memory Usage: 21kB
  21.     Row 23111:     Buckets: 1024Batches: 1Memory Usage: 8kB
  22.     Row 23145:     Buckets: 1024Batches: 1Memory Usage: 9kB
  23.     Row 23149:     Buckets: 1024Batches: 1Memory Usage: 8kB
  24.     Row 23183:     Buckets: 2048Batches: 1Memory Usage: 80kB
  25.     Row 23282:     Buckets: 2048Batches: 1Memory Usage: 76kB
  26.     Row 23286:     Buckets: 1024Batches: 1Memory Usage: 8kB
  27.     Row 23290:     Buckets: 1024Batches: 1Memory Usage: 8kB
  28.     Row 23325:     Buckets: 1024Batches: 1Memory Usage: 8kB
  29.     Row 23357:     Buckets: 1024Batches: 1Memory Usage: 8kB
  30.     Row 23389:     Buckets: 1024Batches: 1Memory Usage: 8kB
  31.     Row 23421:     Buckets: 2048Batches: 1Memory Usage: 106kB
  32.     Row 23455:     Buckets: 2048Batches: 1Memory Usage: 97kB
  33.     Row 23521:     Buckets: 1024Batches: 1Memory Usage: 8kB
  34.     Row 23525:     Buckets: 1024Batches: 1Memory Usage: 8kB
  35.     Row 23557:     Buckets: 1024Batches: 1Memory Usage: 8kB
  36.     Row 23589:     Buckets: 4096Batches: 1Memory Usage: 166kB
  37.     Row 23593:     Buckets: 1024Batches: 1Memory Usage: 8kB
  38.     Row 23597:     Buckets: 1024Batches: 1Memory Usage: 9kB
  39.     Row 23719:     Buckets: 1024Batches: 1Memory Usage: 8kB
  40.     Row 23723:     Buckets: 1024Batches: 1Memory Usage: 8kB
  41.     Row 23755:     Buckets: 1024Batches: 1Memory Usage: 8kB
  42.     Row 23787:     Buckets: 1024Batches: 1Memory Usage: 9kB
  43.     Row 23852:     Buckets: 1024Batches: 1Memory Usage: 9kB
  44.     Row 23884:     Buckets: 1024Batches: 1Memory Usage: 8kB
  45.     Row 23916:     Buckets: 1024Batches: 1Memory Usage: 8kB
  46.     Row 23948:     Buckets: 1024Batches: 1Memory Usage: 8kB
  47.     Row 23980:     Buckets: 1024Batches: 1Memory Usage: 10kB
  48.     Row 23984:     Buckets: 1024Batches: 1Memory Usage: 51kB
  49.     Row 23988:     Buckets: 2048Batches: 1Memory Usage: 113kB
  50.     Row 24052:     Buckets: 1024Batches: 1Memory Usage: 20kB
  51.     Row 24056:     Buckets: 1024Batches: 1Memory Usage: 13kB
  52.     Row 24060:     Buckets: 1024Batches: 1Memory Usage: 8kB
  53.     Row 24092:     Buckets: 1024Batches: 1Memory Usage: 8kB
  54.     Row 24096:     Buckets: 1024Batches: 1Memory Usage: 8kB
  55.     Row 24128:     Buckets: 1024Batches: 1Memory Usage: 9kB
  56.     Row 24160:     Buckets: 1024Batches: 1Memory Usage: 10kB
  57.     Row 24264:     Buckets: 1024Batches: 1Memory Usage: 10kB
  58.     Row 24268:     Buckets: 2048Batches: 1Memory Usage: 128kB
  59.     Row 24279:     Buckets: 1024Batches: 1Memory Usage: 100kB
  60.     Row 24283:     Buckets: 65536Batches: 1Memory Usage: 5289kB
  61.     Row 24290:     Buckets: 8192Batches: 1Memory Usage: 447kB
  62.     Row 24293:     Buckets: 1024Batches: 1Memory Usage: 10kB
  63.     Row 24604:     Buckets: 65536Batches: 1Memory Usage: 17414kB
  64.     Row 24617:     Buckets: 65536Batches: 1Memory Usage: 16899kB
  65.     Row 24622:     Buckets: 65536Batches: 1Memory Usage: 16899kB
  66.     Row 24639:     Buckets: 65536Batches: 1Memory Usage: 13806kB
  67.     Row 24642:     Buckets: 8192Batches: 1Memory Usage: 634kB
  68.     Row 24645:     Buckets: 1024Batches: 1Memory Usage: 10kB
  69.     Row 24649:     Buckets: 32768Batches: 1Memory Usage: 1795kB
  70.     Row 24652:     Buckets: 1024Batches: 1Memory Usage: 8kB
  71.     Row 24656:     Buckets: 8192Batches: 1Memory Usage: 1335kB
  72.     Row 24659:     Buckets: 1024Batches: 1Memory Usage: 13kB
  73.     Row 24666:     Buckets: 131072Batches: 1Memory Usage: 7118kB
  74.     Row 24951:     Buckets: 1024Batches: 1Memory Usage: 79kB
  75.     Row 24955:     Buckets: 1024Batches: 1Memory Usage: 10kB
  76.     Row 24959:     Buckets: 2048Batches: 1Memory Usage: 128kB
  77.     Row 24963:     Buckets: 32768Batches: 1Memory Usage: 2723kB
  78.     Row 24998:     Buckets: 131072Batches: 1Memory Usage: 6467kB
  79.     Row 25033:     Buckets: 4096Batches: 1Memory Usage: 307kB
  80.     Row 25037:     Buckets: 1024Batches: 1Memory Usage: 100kB
  81.     Row 25041:     Buckets: 4096Batches: 1Memory Usage: 295kB
  82.     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.

4. 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.
Dec 7 '22 #1
0 8062

Sign in to post your reply or Sign up for a free account.

Similar topics

3
Can Postgresql run under Windows?
by: PhilM | last post by:
Hi, I am looking for a database engine which: 1) runs under Windows (multiple versions) 2) Can be redistributed without additional fees as part of another product Is postgresql...
PostgreSQL Database
12
High-volume shop uses PostgreSQL
by: Ron Johnson | last post by:
http://www.computerworld.com.au/index.php?id=1482975508&fp=16&fpid=0 -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA...
PostgreSQL Database
8
An out of memory error when doing a vacuum full
by: Sean Shanny | last post by:
To all, The facts: PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI drives in hardware RAID 0 configuration. Database size with indexes is currently 122GB. DB size...
PostgreSQL Database
19
postgresql + apache under heavy load
by: Alex Madon | last post by:
Hello, I am testing a web application (using the DBX PHP function to call a Postgresql backend). I have 375Mb RAM on my test home box. I ran ab (apache benchmark) to test the behaviour of the...
PostgreSQL Database
4
"out of memory" error
by: Rachel McConnell | last post by:
Hello, I have a Java web application using Hibernate to connect to a PostgreSQL backend. I am seeing the below stack trace during processing of a set of data consisting of around 1000 objects;...
PostgreSQL Database
1
Shared memory segment error
by: Alexander Cohen | last post by:
Hi, sometimes ill get this error: FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=2, size=4153344, 03600). HINT: This error...
PostgreSQL Database
9
postgresql +AMD64 +big address spaces - does it work?
by: Andy B | last post by:
If I bought one of these boxes/OS combos as a postgresql database server, would postgresql be able to make the best use of it with a huge (e.g. 40GB) database? Box: HP ProLiant DL585, with ...
PostgreSQL Database
4
PostgreSQL and Fedora?
by: Daniel Secomb | last post by:
Hi, I'm curious to know whether PostgreSQL works well with Fedora RedHat Linux? If there are any issues, I would like to know about them. Many thanks. Dan
PostgreSQL Database
1
postgresql hanging (blocking) with smp kernel
by: Marcel Groner | last post by:
I have a problem with postgresql runnung on smp kernel. setup: master: ------- - Pentium 4 (hyperthreading) - 2 GB Memory - os: fedora core 1 - kernel: 2.4.22-1.2188.nptlsmp
PostgreSQL Database
4
Getting an out of memory failure.... (long email)
by: Sean Shanny | last post by:
To all, Running into an out of memory error on our data warehouse server. This occurs only with our data from the 'September' section of a large fact table. The exact same query running over...
PostgreSQL Database
0
Basic Javascript concepts
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
Javascript
0
Batch import of multiple excel files into the database
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
Data Management
0
Merging data from multiple Excel files
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
0
BarryA
Navigating the Data Structures and Algorithms (DSA)
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
Algorithms / Advanced Math
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
How to build RAID in BIOS?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
Computer Hardware
0
Changing the language in Windows 10
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
Windows Server
0
jinu1996
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Online Marketing

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!