Hi all,
We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then, we have a lot of memory issues in our QA environment (which is a bit tense in resources). We did not have these problems before the migration, and we do not have them in our production environment, which has a lot more memory. So, it is not super critical for us, but I would still like to understand better how we can improve our configuration.
Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The database server is a dedicated server with 15 GB RAM (and 4 cores, if this matters).
We used the following settings:
shared_buffers = 4GB
work_mem = 4MB
After a while, we saw the following error in the logs:
<2024-05-20 12:01:03 CEST - > LOG: could not fork autovacuum worker process: Cannot allocate memory
However, according to "free", a lot of memory was available:
# free -m
total used free shared buff/cache available
Mem: 15882 4992 463 4195 10427 6365
Swap: 1999 271 1728
Our Grafana charts showed a slow increase in memory consumption until it plateaus at 4.66 GB.
We also found the following error:
<2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on device
I thought this could all be related to our "shared_buffers" setting, so I increased it to 8 GB. This almost immediately (after a few minutes) gave me these errors:
<2024-05-27 11:45:59 CEST - > ERROR: out of memory
<2024-05-27 11:45:59 CEST - > DETAIL: Failed on request of size 201088574 in memory context "TopTransactionContext".
...
<2024-05-27 11:58:02 CEST - > ERROR: out of memory
<2024-05-27 11:58:02 CEST - > DETAIL: Failed while creating memory context "dynahash".
<2024-05-27 11:58:02 CEST - > LOG: background worker "parallel worker" (PID 21480) exited with exit code 1
...
<2024-05-27 12:01:02 CEST - > LOG: could not fork new process for connection: Cannot allocate memory
<2024-05-27 12:01:03 CEST - > LOG: could not fork autovacuum worker process: Cannot allocate memory
<2024-05-27 12:02:02 CEST - > LOG: could not fork new process for connection: Cannot allocate memory
Since this seemed worse than before, I changed the setting back to 4 GB. I noticed that "free" now reports even more available memory:
# free -m
total used free shared buff/cache available
Mem: 15882 621 320 2256 14940 12674
Swap: 1999 199 1800
So, does the "shared_buffers" setting have the opposite effect than I though? If I correctly remember similar discussions years ago, the database needs both "normal" and shared memory. By increasing the "shared_buffers" to 8 GB, I may have deprived it of "normal" memory. On the other hand, I would have expected the remaining 7 GB to still be enough.
At this point, I am out of ideas. I clearly seem to misunderstand how the database manages its memory. This may have changed between 9.4 and 15, so my prior knowledge may be useless. I definitely need some help. ☹
Thanks in advance,
Christian
----------------------------------------------
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team at support@xxxxxxx. Our dedicated team is available to help you and provide prompt assistance.
CONFIDENTIALITY NOTICE:
This email and any attachments are confidential and intended solely for the use of the individual or entity to whom it is addressed. If you have received this email in error, please notify the sender immediately and delete it from your system.
