Greetings, The error message you encountered, "could not fork autovacuum worker process: Cannot allocate memory," indicates that your PostgreSQL server attempted to start an autovacuum worker process but failed because the system ran out of memory.
Steps to verify. 1 Check system available memory with commands. free -m top 2. Check PG configurations. shared_buffers --Typically 25% of total mem. work_mem maintenance_work_mem--For maintenance op like autovaccume create index etc. Increase it to 64MB or appropriate to your requirement. max_connections Monitor /var/log/messages file for errors. 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 Check share memory limits. /etc/sysctl.conf kernel.shmmax = 68719476736 # Example value, adjust as needed kernel.shmall = 16777216 # Example value, adjust as needed Restart system and db Ensure you have enough disk space available check and monitor disk space with command df -h Reduce max_parallel_workers_per_gather = 2; If it is set to high value. I think setting up OS parameter. Increasing maintenance mem value and reducing max paralell workers xan help in solution. Regards, Salahuddin. On Tue, 28 May 2024, 21:40 Christian Schröder, <christian.schroe...@wsd.com> wrote: > 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 supp...@wsd.com. 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. >
