Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 11:54, Hannu Krosing <hannuk(at)google(dot)com> wrote:
>
> On Wed, Jun 7, 2023 at 11:37 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> >
> > Hi,
> >
> > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote:
> > > 2. While I wouldn't want to necessarily discourage a moonshot effort, I
> > > would ask if developer time could be better spent on tackling some of the
> > > other problems around vertical scalability? Per some PGCon discussions,
> > > there's still room for improvement in how PostgreSQL can best utilize
> > > resources available very large "commodity" machines (a 448-core / 24TB RAM
> > > instance comes to mind).
> >
> > 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.
>
>
> This part was touched in the "AMA with a Linux Kernale Hacker"
> Unconference session where he mentioned that the had proposed a
> 'mshare' syscall for this.
>
> So maybe a more fruitful way to fixing the perceived issues with
> process model is to push for small changes in Linux to overcome these
> avoiding a wholesale rewrite ?

We support not just Linux, but also Windows and several (?) BSDs. I'm
not against pushing Linux to make things easier for us, but Linux is
an open source project, too, where someone need to put in time to get
the shiny things that you want. And I'd rather see our time spent in
PostgreSQL, as Linux is only used by a part of our user base.

> > 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.
>
> Maybe we can already remove the distinction between static and dynamic
> shared memory ?

That sounds like a bad idea, dynamic shared memory is more expensive
to maintain than our static shared memory systems, not in the least
because DSM is not guaranteed to share the same addresses in each
process' address space.

> Though I already heard some complaints at the conference discussions
> that having the dynamic version available has made some developers
> sloppy in using it resulting in wastefulness.

Do you know any examples of this wastefulness?

> > > I'm purposely giving a nonanswer on whether it's a worthwhile goal, but
> > > rather I'd be curious where it could stack up against some other efforts to
> > > continue to help PostgreSQL improve performance and handle very large
> > > workloads.
> >
> > There's plenty of things we can do before, but in the end I think tackling the
> > issues you mention and moving to threads are quite tightly linked.
>
> Still we should be focusing our attention at solving the issues and
> not at "moving to threads" and hoping this will fix the issues by
> itself.

I suspect that it is much easier to solve some of the issues when
working in a shared address space.
E.g. resizing shared_buffers is difficult right now due to the use of
a static allocation of shared memory, but if we had access to a single
shared address space, it'd be easier to do any cleanup necessary for
dynamically increasing/decreasing its size.
Same with parallel workers - if we have a shared address space, the
workers can pass any sized objects around without being required to
move the tuples through DSM and waiting for the leader process to
empty that buffer when it gets full.

Sure, most of that is probably possible with DSM as well, it's just
that I see a lot more issues that you need to take care of when you
don't have a shared address space (such as the pointer translation we
do in dsa_get_address).

Kind regards,

Matthias van de Meent
Neon, Inc.