CODEWITHSUNDEEP
postgresql
user3714601
user3714601

Reputation: 1281

How to figure out optimal max_worker_processes?

Yet I can not find any reliable recommendation regarding the optimal value for max_worker_processes.

Some sources suppose that the value should not be higher than the number of available cores, but is that correct taking that server threads do a lot of IO?

Say I have 8 cores for PG container and plan to handle about 100 clients in parallel. Is that feasible, especially with the default max_worker_processes=8 ?

Any trusted reference would be much appreciated.

Upvotes: 0

Views: 3520

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246653

The reasonable limit dies not depend on the number of client connections, but on the actual upper limit on concurrent queries.

If it is guaranteed that only one of these clients will ever be active at the same time, you could set max_worker_processes, max_parallel_workers and max_parallel_workers_per_gather one less than the number of cores or parallel I/O operations that your storage can handle, whatever of the two is smaller. In essence, one query can then consume all the available resources.

On the other hand, if many of these clients are likely to run queries concurrently, you should disable parallel query by setting max_parallel_workers_per_gather to 0 to avoid overloading your database.

Concerning your comments to my answer: if your goal is to limit the number of active queries, use a connection pool.

Upvotes: 1

Related Questions