Re: Let's make PostgreSQL multi-threaded

On 7/6/23 23:37, Andres Freund wrote:
> [snip]
> I think we're starting to hit quite a few limits related to the process model,
> particularly on bigger machines. The overhead of cross-process context
> switches is inherently higher than switching between threads in the same
> process - and my suspicion is that that overhead will continue to
> increase. Once you have a significant number of connections we end up spending
> a *lot* of time in TLB misses, and that's inherent to the process model,
> because you can't share the TLB across processes.

IMHO, as one sysadmin who has previously played with Postgres on "quite
large" machines, I'd propose what most would call a "hybrid model"....

* Threads are a very valuable addition for the "frontend" of the server.
Most would call this a built-in session-aware connection pooler :)

    Heikki's (and others') efforts towards separating connection state
into discrete structs is clearly a prerequisite for this;
Implementation-wise, just toss the connState into a TLS[thread-local
storage] variable and many problems just vanish.

    Postgres wouldn't be the first to adopt this approach, either...

* For "heavyweight" queries, the scalability of "almost independent"
processes w.r.t. NUMA is just _impossible to achieve_ (locality of
reference!) with a pure threaded system. When CPU+mem-bound
(bandwidth-wise), threads add nothing IMO.

Indeed a separate postmaster is very much needed in order to control the
processes / guard overall integrity.

Hence, my humble suggestion is to consider a hybrid architecture which
benefits from each model's strengths. I am quite convinced that
transition would be much safer and simpler (I do share most of Tom and
other's concerns...)

Other projects to draw inspiration from:

 * Postfix -- multi-process, postfix's master guards processes and
performs privileged operations; unprivileged "subsystems". Interesting
IPC solutions
 * Apache -- MPMs provide flexibility and support for e.g. non-threaded
workloads (PHP is the most popular; cfr. "prefork" multi-process MPM)
 * NginX is actually multi-process (one per CPU) + event-based
(multiplexing) ...
 * PowerDNS is internally threaded, but has a "guardian" process. Seems
to be evolving to a more hybrid model.

I would suggest something along the lines of :

* postmaster -- process supervision and (potentially privileged)
operations; process coordination (i.e descriptor passing); mostly as-is
* *frontend* -- connection/session handling; possibly even event-driven
* backends -- process heavyweight queries as independently as possible.
Can span worker threads AND processes when needed
* *dispatcher* -- takes care of cached/lightweight queries (cached
catalog / full snapshot visibility+processing)
* utility processes can be left "as is" mostly, except to be made
multi-threaded for heavy-sync ones (e.g. vacuum workers, stat workers)

For fixed-size buffers, i.e. pages / chunks, I'd say mmaped (anonymous)
shared memory isn't that bad... but haven't read the actual code in years.

For message queues / invalidation messages, i guess that shmem-based
sync is really a nuisance. My understanding is that Linux-specific (i.e.
eventfd) mechanisms aren't quite considered .. or are they?

> The amount of duplicated code we have to deal with due to to the process model
> is quite substantial. We have local memory, statically allocated shared memory
> and dynamically allocated shared memory variants for some things. And that's
> just going to continue.

Code duplication is indeed a problem... but I wouldn't call "different
approaches/solution for very similar problems depending on
context/requirement" a duplicate. I might well be wrong / lack detail,
though... (again: haven't read PG's code for some years already).

Just my two cents.

Thanks,

    J.L.

--
Parkinson's Law: Work expands to fill the time alloted to it.