Memory Size Matters to PostgreSQL
pgdba.org
Memory Size Matters to PostgreSQL
1–10 of 13 posts
Re: Memory Size Matters to PostgreSQL
#2for the rest of us:
Re: Memory Size Matters to PostgreSQL
#3Interesting read, but I find it very lacking. PGSQL memory consists of lots of parts; shared_buffers, work_mem, maintenance_work_men, OS Cache size, etc. For those who are interested in a deeper Postgresql tuning tutorial, I suggest this[1] as it highlights a number of important tuning options - specifically for PGSQL 17. Once your DB has been running for a while, run the postgresql-tuner app[2] to see if you have correctly applied RAM settings[2]. The repo has been around for a long time, but I find the recommendations (especially the estimated RAM usage) really good.
[1] https://www.pgedge.com/blog/postgresql-performance-tuning
Re: Memory Size Matters to PostgreSQL
#4[dead]
Re: Memory Size Matters to PostgreSQL
#5Interesting read, but I find it very lacking. PGSQL memory consists of lots of parts; shared_buffers, work_mem, maintenance_work_men, OS Cache size, etc. For those who are interested in a deeper Postgresql tuning tutorial, I suggest this[1] as it highlights a number of important tuning options - specifically for PGSQL 17. Once your DB has been running for a while, run the postgresql-tuner app[2] to see if you have co…
Beware, the first link appears to be AI slop with at least some bogus information. For example, it says "While PostgreSQL 15 introduced basic WAL compression", but WAL compression has been around since before 15.
Re: Memory Size Matters to PostgreSQL
#6I often use pgtune to set the memory size, just select the correct information
Re: Memory Size Matters to PostgreSQL
#7I don't fully understand this article but this point stuck out as probably fractally wrong:
Modern DDR4 memories have a theoretical throughput of 25-30 GB/s. This is more realistically ranging between 5-10 GB/s. With a 100 GB full packed shared buffer the time required to perform one single full scan ranges between 3 and 20 seconds.
Obviously DDR5 now exists and servers have multiple memory channels giving total memory bandwidth more like 200-500 GB/s. An old rule of thumb is that a computer should be able to read its entire memory in one second, although these days it may be more like 1-4 seconds.
The clock replacement algorithm only needs to read metadata, so a full sweep of the metadata for 100 GB of buffers should be milliseconds not seconds. (If they're talking about a table scan instead then obviously reading from buffers is going to be faster than disk.)
Re: Memory Size Matters to PostgreSQL
#8Interesting read, but I find it very lacking. PGSQL memory consists of lots of parts; shared_buffers, work_mem, maintenance_work_men, OS Cache size, etc. For those who are interested in a deeper Postgresql tuning tutorial, I suggest this[1] as it highlights a number of important tuning options - specifically for PGSQL 17. Once your DB has been running for a while, run the postgresql-tuner app[2] to see if you have co…
Beware, the first link appears to be AI slop with at least some bogus information. For example, it says "While PostgreSQL 15 introduced basic WAL compression", but WAL compression has been around since before 15.
That's a bit unfair, don't you think? https://www.percona.com/blog/new-wal-archive-module-library-...
Re: Memory Size Matters to PostgreSQL
#9I don't fully understand this article but this point stuck out as probably fractally wrong: Modern DDR4 memories have a theoretical throughput of 25-30 GB/s. This is more realistically ranging between 5-10 GB/s. With a 100 GB full packed shared buffer the time required to perform one single full scan ranges between 3 and 20 seconds. Obviously DDR5 now exists and servers have multiple memory channels giving total memo…
Even saying 25-30GB/s is weird.
DDR4-3200 is ~26GB/s per channel, and is the upper end of what you'll see on ECC DDR4. DDR5-5600 is common now, and is ~45GB/s.
Zen 2/3 Epycs on SP3 have 8 channels, Zen 4/5 Epycs on the SP5 have 12 channels per socket, and with both you get to have two sockets. That'd be ~410GB/s on dual socket SP3 and ~1080GB/s on dual socket SP5.
So, yeah, RAM goes brrr.
Re: Memory Size Matters to PostgreSQL
#10I don't fully understand this article but this point stuck out as probably fractally wrong: Modern DDR4 memories have a theoretical throughput of 25-30 GB/s. This is more realistically ranging between 5-10 GB/s. With a 100 GB full packed shared buffer the time required to perform one single full scan ranges between 3 and 20 seconds. Obviously DDR5 now exists and servers have multiple memory channels giving total memo…
It was a while since I run memtest86+ (probably it was DDR3) but a single pass of a single test took more than 1-4sec to make a pass (over 8-16Gb). Granted DDR4/5 faster but servers with 256Gb or 512Gb are common nowadays so full memory scan can take even more time.