0

My postgresql instance keeps throwing this error and it temporarily goes away after I restart the database.

Here's the logs:

ERROR:  parallel worker failed to initialize

2025-02-25 21:00:51.586 UTC [668] HINT:  More details may be available in the server log.

2025-02-25 21:00:51.586 UTC [668] STATEMENT:  ...some sql query

2025-02-25 21:00:51.663 UTC [5] LOG:  could not fork worker process: Resource temporarily

Here's my configuration:

shared_buffers = '8GB'
work_mem = '512MB'
maintenance_work_mem = '2GB'
effective_cache_size = '16GB'
max_parallel_workers_per_gather = 8
max_parallel_workers = 32
autovacuum_work_mem = '1GB'
vacuum_cost_limit = 1000
autovacuum = on

I have 32GB ram, 32vCPU, and 10GB storage. I've tried tweaking these settings up but no hope. Is there something I am getting wrong here?

Improve this question

3 Answers 3

Reset to default
0

When you increase max_parallel_workers you also have to increase max_worker_processes as parallel workers are taken from the pool of worker processes.

Also your work_mem seems bit high and a bunch of connections can easily eat all of your system memory. work_mem is applied to individual workers so a higher setting for parallel workers again leads to more memory utilisation. I suggest you find an optimal value for work_mem and max_parallel_workers_per_gather based on your query needs.

Improve this answer
2
  • See also postgresql.org/docs/current/… And a +1 for mentioning work_mem since that will be the next problem. Commented Feb 26 at 5:21
  • I did notice changing some queries not to use relationships and also adding indexes for fields that were frequently being looked up temporarily fixed the issue. What is a reasonable value I can start from for the work_mem? Also, if you can point me to some links I can use to learn how to properly tune a PostgreSQL application Commented Feb 26 at 10:58
1

The error messages indicates EAGAIN. According to the fork() manual page, that means:

EAGAIN A system-imposed limit on the number of threads was encountered. There are a number of limits that may trigger this error:

  • the RLIMIT_NPROC soft resource limit (set via setrlimit(2)), which limits the number of processes and threads for a real user ID, was reached;
  • the kernel's system-wide limit on the number of processes and threads, /proc/sys/kernel/threads-max, was reached (see proc(5));
  • the maximum number of PIDs, /proc/sys/kernel/pid_max, was reached (see proc(5)); or
  • the PID limit (pids.max) imposed by the cgroup "process number" (PIDs) controller was reached.

Usually, it is the first limit. You'd have to increase the ulimit for the postmaster process. How to best do that depends on how you start the PostgreSQL server. You can see the limit in /proc/<postmaster PID>/limits under "Max processes".

The likely cause that you are hitting that limit is that you are allowing too many client connections. While increasing the limit will get rid of the error, you will get other problems, and you would be better off with a reasonably sized connection pool.

Improve this answer
10
  • I am using Railway and the default ulimit is 99999. My current max_connections is 25000. I increased it to this because the pgbouncer keeps running out of connections Commented Feb 26 at 11:03
  • Is the log you show the pgBouncer log, the PostgreSQL log or the client log? Commented Feb 26 at 11:53
  • that's the PostgreSQL log Commented Feb 26 at 12:21
  • Then my answer stands. Reduce max_connections and use a connection pool. Commented Feb 26 at 12:30
  • 1
    Yes. 25000 sessions is insane, sorry for the strong language. You will get into other trouble with that: memory exhaustion, bad performance. Do you have very long running transactions by any chance? Commented Feb 26 at 12:40
0

I am getting confused of that large server having 10 GiB of storage. May we know its purpose?

Also, based on your configuration, you should increase max_worker_processes to the same of max_parallel_workers. Also the work_mem are extremely high and increase max_connections won't help due to latency, thread context switching. A good number is min(75, vCPU*4).

You can either try https://pgtune.sainth.de/ for simple use-case when you don't know the system and how database works, or try a more advanced version of https://pgtuner.onrender.com

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.