Memory | Postgres.FM 050 |
Вставка
- Опубліковано 17 вер 2024
- Nikolay and Michael discuss memory in PostgreSQL - how it works, important settings, and how to go about tuning them.
Here are links to a few things we mentioned:
* Resource Consumption (PostgreSQL docs) www.postgresql...
* Andres Freud tweet about shared_buffers / 1438912583554113537
* Henrietta (Hettie) Dombrovskaya hdombrovskaya....
* annotated.conf (by ash Berkus) github.com/jbe...
* Our episode about checkpoint tuning postgres.fm/ep...
* Our episode about BUFFERS postgres.fm/ep...
* Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) techcommunity....
* Tuning memory parameters for Aurora PostgreSQL docs.aws.amazo...
* RDS for PostgreSQL memory docs.aws.amazo...
* EDB guide by Via Fearing www.enterprise...
* pg_stat_kcache github.com/pow...
* pg_buffercache www.postgresql...
* Process and Memory Architecture chapter (from Hironobu SUZUKI) www.interdb.jp...
* PostgreSQL 14 internals PDF book from Egor Rogov (pages 37, 184) edu.postgrespr...
* src/backend/storage/buffer/README github.com/pos...
* pg_backend_memory_contexts (PostgreSQL 14+) www.postgresql...
* pg_stat_io (coming in PostgreSQL 16) www.postgresql...
* pg_prewarm www.postgresql...
* Configuring work_mem blog post www.pgmustard....
~~~
What did you like or not like? What should we discuss next time? Let us know in the comments, or by tweeting us on @samokhvalov / samokhvalov and @michristofides / michristofides
~~~
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai
- Michael Christofides, founder of pgMustard
~~~
This is the uncut version, with video. Check out Postgres.fm to find audio-only podcast episodes, edited for convenient listening.
Excellent stuff
when talking about increasing buffers cache to like 80% of ram, you have to remember that there are processes like bgwriter, which do (more or less) sequential scans of buffer cache. So the bigger page cache you have, the more work such processes will have to do, and, potentially, eat more resources. It is not at all obvious how much resources will they consume on specific workload, and if it will matter for you at all, so you will just have to benchmark before increasing buffer cache a lot on your prod.
So in general I would agree, set it to 25% of ram and start increasing it slowly only when you see that you exhausted other means of optimization.
Yeah. That's a problem, and it was bad in the past. We didn't discuss a lot of things -- e.g., somehow forgot to at least touch huge pages.
@@NikolaySamokhvalov Maybe huge pages can be combined with other kernel config/tuning stuff one might want (overcommit, swap (or lack thereof), Numa settings etc.) in a future episode
hi @NikolaySamokhvalov . I regularly listen to your podcasts, and I've been actively researching memory management topics in the documentation as well as various sources on Google. While doing so, I came across several misleading or inaccurate blog posts regarding certain cases. Therefore, I have a specific question: Is Vacuum Buffers considered a form of local memory (similar to work memory) or shared memory? Thanks
shared (256KB ringbuffer)
see also: BUFFER_USAGE_LIMIT and vacuum_buffer_usage_limit in PG16+
The problem with work_mem is that every user can just set it themselves to 1TB if they want... Earlier Azure Managed Postgres patched the max value for it to something sane, but that is not the case in general
This is a good point. There is an extension called "noset" by OnGres, aiming to limit changes of particular settings - good idea that probably should exist in core.
Promo SM 😜