Thread

1. Re: Let's make PostgreSQL multi-threaded

   Ranier Vilela <ranier.vf@gmail.com> — 2023-06-05T16:26:00Z

   On 05/06/2023 11:18, Tom Lane wrote:
   > Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
   >> I spoke with some folks at PGCon about making PostgreSQL multi-threaded,
   >> so that the whole server runs in a single process, with multiple
   >> threads. It has been discussed many times in the past, last thread on
   >> pgsql-hackers was back in 2017 when Konstantin made some experiments [0].
   >
   >> I feel that there is now pretty strong consensus that it would be a good
   >> thing, more so than before. Lots of work to get there, and lots of
   >> details to be hashed out, but no objections to the idea at a high level.
   >
   >> The purpose of this email is to make that silent consensus explicit. If
   >> you have objections to switching from the current multi-process
   >> architecture to a single-process, multi-threaded architecture, please
   >> speak up.
   >
   > For the record, I think this will be a disaster. There is far too much
   > code that will get broken, largely silently, and much of it is not
   > under our control.
   
   I fully agreed with Tom.
   
   First, it is not clear what are the benefits of architecture change?
   
   Performance?
   
   Development becomes much more complicated and error-prone.
   
   There are still many low-hanging fruit to be had that can improve
   performance.
   And the code can gradually and safely remove multithreading barriers.
   
   1. gradual reduction of global variables
   2. introduction of local context structures
   3. shrink current structures (to fit in 32, 64 boundaries)
   
   4. scope reduction
   
   My 2c.
   
   regards,
   
   Ranier Vilela
   

2. Re: Let's make PostgreSQL multi-threaded

   Bruce Momjian <bruce@momjian.us> — 2023-06-05T16:42:06Z

   On Mon, Jun  5, 2023 at 01:26:00PM -0300, Ranier Vilela wrote:
   > On 05/06/2023 11:18, Tom Lane wrote:
   > > For the record, I think this will be a disaster. There is far too much
   > > code that will get broken, largely silently, and much of it is not
   > > under our control.
   > 
   > I fully agreed with Tom.
   > 
   > First, it is not clear what are the benefits of architecture change?
   > 
   > Performance?
   > 
   > Development becomes much more complicated and error-prone.
   
   I agree the costs of going threaded have been reduced with compiler and
   library improvements, but I don't know if they are reduced enough for
   the change to be a net benefit, except on Windows where the process
   creation overhead is high.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        https://momjian.us
     EDB                                      https://enterprisedb.com
   
     Only you can decide what is important to you.
   
   
   
   

3. Re: Let's make PostgreSQL multi-threaded

   Ranier Vilela <ranier.vf@gmail.com> — 2023-06-05T17:05:10Z

   Em seg., 5 de jun. de 2023 às 13:42, Bruce Momjian <bruce@momjian.us>
   escreveu:
   
   > On Mon, Jun  5, 2023 at 01:26:00PM -0300, Ranier Vilela wrote:
   > > On 05/06/2023 11:18, Tom Lane wrote:
   > > > For the record, I think this will be a disaster. There is far too much
   > > > code that will get broken, largely silently, and much of it is not
   > > > under our control.
   > >
   > > I fully agreed with Tom.
   > >
   > > First, it is not clear what are the benefits of architecture change?
   > >
   > > Performance?
   > >
   > > Development becomes much more complicated and error-prone.
   >
   > I agree the costs of going threaded have been reduced with compiler and
   > library improvements, but I don't know if they are reduced enough for
   > the change to be a net benefit, except on Windows where the process
   > creation overhead is high.
   >
   Yeah, but process creation, even on windows, is a tiny part of response
   time.
   SGDB has one connection per user, so one process or thread.
   
   Unlike a webserver like Nginx, with hundreds of thousands connections.
   For the record, Nginx is multithread and uses -Werror for default. (Make
   all warnings into errors)
   
   regards,
   Ranier Vilela
   

4. Re: Let's make PostgreSQL multi-threaded

   Heikki Linnakangas <hlinnaka@iki.fi> — 2023-06-05T17:25:05Z

   On 05/06/2023 12:26, Ranier Vilela wrote:
   > First, it is not clear what are the benefits of architecture change?
   > 
   > Performance?
   
   I doubt it makes much performance difference, at least not initially. It 
   might help a little with backend startup time, and maybe some other 
   things. And might reduce the overhead of context switches and TLB cache 
   misses.
   
   In the long run, a single-process architecture makes it easier to have 
   shared catalog caches, plan cache, etc. which can improve performance. 
   And it can make it easier to launch helper threads for things where 
   worker processes would be too heavy-weight. But those benefits will 
   require more work, they won't happen just by replacing processes with 
   threads.
   
   The ease of developing things like that is my motivation.
   
   > Development becomes much more complicated and error-prone.
   
   I don't agree with that.
   
   We currently bend over backwards to make all allocations fixed-sized in 
   shared memory. You learn to live with that, but a lot of things would be 
   simpler if you could allocate and free in shared memory more freely. 
   It's no panacea, you still need to be careful with locking and 
   concurrency. But a lot simpler.
   
   We have built dynamic shared memory etc. over the years to work around 
   the limitations of shared memory. But it's still a lot more complicated.
   
   Code that doesn't need to communicate with other processes/threads is 
   simple to write in either model.
   
   > There are still many low-hanging fruit to be had that can improve 
   > performance.
   > And the code can gradually and safely remove multithreading barriers.
   > 
   > 1. gradual reduction of global variables
   > 2. introduction of local context structures
   > 3. shrink current structures (to fit in 32, 64 boundaries)
   > 
   > 4. scope reduction
   
   Right, the reason I started this thread is to explicitly note that it is 
   a worthy goal. If it's not, the above steps would be pointless. But if 
   we agree that it is a worthy goal, we can start to incrementally work 
   towards it.
   
   -- 
   Heikki Linnakangas
   Neon (https://neon.tech)
   
   
   
   
   

5. Re: Let's make PostgreSQL multi-threaded

   Merlin Moncure <mmoncure@gmail.com> — 2023-06-05T17:32:34Z

   On Mon, Jun 5, 2023 at 12:25 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
   
   > We currently bend over backwards to make all allocations fixed-sized in
   > shared memory. You learn to live with that, but a lot of things would be
   > simpler if you could allocate and free in shared memory more freely.
   > It's no panacea, you still need to be careful with locking and
   > concurrency. But a lot simpler.
   
   
   Would this help with oom killer in linux?
   
   Isn't it true that pgbouncer provides a lot of the same benefits?
   
   merlin
   

6. Re: Let's make PostgreSQL multi-threaded

   Heikki Linnakangas <hlinnaka@iki.fi> — 2023-06-05T17:43:17Z

   On 05/06/2023 13:32, Merlin Moncure wrote:
   > Would this help with oom killer in linux?
   
   Hmm, I guess the OOM killer would better understand what Postgres is 
   doing, it's not very smart about accounting shared memory. You still 
   wouldn't want the OOM killer to kill Postgres, though, so I think you'd 
   still want to disable it in production systems.
   
   > Isn't it true that pgbouncer provides a lot of the same benefits?
   
   I guess there is some overlap, although I don't really think of it that 
   way. Firstly, pgbouncer has its own set of problems. Secondly, switching 
   to threads would not make connection poolers obsolete. Maybe in the 
   distant future, Postgres could handle thousands of connections with 
   ease, and threads would make that easier to achieve that, but that would 
   need a lot of more work.
   
   -- 
   Heikki Linnakangas
   Neon (https://neon.tech)
   
   
   
   
   

7. Re: Let's make PostgreSQL multi-threaded

   Pavel Stehule <pavel.stehule@gmail.com> — 2023-06-05T19:03:37Z

   Hi
   
   In the long run, a single-process architecture makes it easier to have
   > shared catalog caches, plan cache, etc. which can improve performance.
   > And it can make it easier to launch helper threads for things where
   > worker processes would be too heavy-weight. But those benefits will
   > require more work, they won't happen just by replacing processes with
   > threads.
   >
   
   The shared plan cache is not a silver bullet. The good management of shared
   plan cache is very very difficult. Our heuristic about custom plans in
   prepared statements is nothing, and you should reduce the usage of custom
   plans too.
   
   There are a lot of issues known from Oracle. The benefits can be just for
   very primitive very fast queries, or extra complex queries where generic
   plan is used.  Current implementation of local plan caches has lot of
   issues (that cannot be fixed), but shared plan cache is another level of
   complexity
   
   Regards
   
   Pavel
   
   
   
   >
   >