Re: proposal: Allocate work_mem From Pool

On Tue, Jul 12, 2022 at 08:49:10PM -0700, Joseph D Wagner wrote:
> > > Before I try to answer that, I need to know how the scheduler works.
> 
> > As I understand the term used, there is no scheduler inside Postgres
> > for user connections -- they're handled by the OS kernel.
> 
> Then, I'm probably using the wrong term. Right now, I have
> max_worker_processes set to 16. What happens when query #17
> wants some work done? What do you call the thing that handles
> that? What is its algorithm for allocating work to the processes?
> Or am I completely misunderstanding the role worker processes
> play in execution?

max_connections limits the number of client connections (queries).
Background workers are a relatively new thing - they didn't exist until v9.3.

There is no scheduler, unless you run a connection pooler between the
application and the DB.  Which you should probably do, since you've set
work_mem measured in GB on a server with 10s of GB of RAM, and while using
partitioning.

https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
|max_connections (integer)
|
|    Determines the maximum number of concurrent connections to the database server. The default is typically 100
connections,but might be less if your kernel settings will not support it (as determined during initdb). This parameter
canonly be set at server start.
 

https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES
|max_worker_processes (integer)
|    Sets the maximum number of background processes that the system can support. This parameter can only be set at
serverstart. The default is 8.
 
|    When running a standby server, you must set this parameter to the same or higher value than on the primary server.
Otherwise,queries will not be allowed in the standby server.
 
|    When changing this value, consider also adjusting max_parallel_workers, max_parallel_maintenance_workers, and
max_parallel_workers_per_gather.

https://www.postgresql.org/docs/current/connect-estab.html
|PostgreSQL implements a “process per user” client/server model. In this model, every client process connects to
exactlyone backend process. As we do not know ahead of time how many connections will be made, we have to use a
“supervisorprocess” that spawns a new backend process every time a connection is requested. This supervisor process is
calledpostmaster and listens at a specified TCP/IP port for incoming connections. Whenever it detects a request for a
connection,it spawns a new backend process. Those backend processes communicate with each other and with other
processesof the instance using semaphores and shared memory to ensure data integrity throughout concurrent data
access.
|
|The client process can be any program that understands the PostgreSQL protocol described in Chapter 53. Many clients
arebased on the C-language library libpq, but several independent implementations of the protocol exist, such as the
JavaJDBC driver.
 
|
|Once a connection is established, the client process can send a query to the backend process it's connected to. The
queryis transmitted using plain text, i.e., there is no parsing done in the client. The backend process parses the
query,creates an execution plan, executes the plan, and returns the retrieved rows to the client by transmitting them
overthe established connection.
 

https://www.postgresql.org/docs/current/tutorial-arch.html
| The PostgreSQL server can handle multiple concurrent connections from clients. To achieve this it starts (“forks”) a
newprocess for each connection. From that point on, the client and the new server process communicate without
interventionby the original postgres process. Thus, the supervisor server process is always running, waiting for client
connections,whereas client and associated server processes come and go. (All of this is of course invisible to the
user.We only mention it here for completeness.)
 

https://wiki.postgresql.org/wiki/FAQ#How_does_PostgreSQL_use_CPU_resources.3F
|The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating
system(OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses
CPUsto handle disk I/O and run other non-database tasks. Client applications can use threads, each of which connects to
aseparate database process. Since version 9.6, portions of some queries can be run in parallel, in separate OS
processes,allowing use of multiple CPU cores. Parallel queries are enabled by default in version 10
(max_parallel_workers_per_gather),with additional parallelism expected in future releases. 
 

BTW, since this is amply documented, I have to point out that it's not on-topic
for the development list.

-- 
Justin