Memory knob testing (was Re: Let's make PostgreSQL multi-threaded)

On Fri, Aug 25, 2023 at 8:35 AM Stephen Frost wrote: > Greetings, > > This is getting a bit far afield in terms of this specific thread, but > there's an ongoing effort to give PG administrators knobs to be able to > control how much actual memory is used rather than depending on the > kernel to

Re: Let's make PostgreSQL multi-threaded

Greetings, * David Geier (geidav...@gmail.com) wrote: > On 8/11/23 14:05, Merlin Moncure wrote: > > Hm, noted this upthread, but asking again, does this > > help/benefit interactions with the operating system make oom kill > > situations less likely?   These things are the bane of my existence, an

Re: Let's make PostgreSQL multi-threaded

Hi, On 8/11/23 14:05, Merlin Moncure wrote: On Thu, Jul 27, 2023 at 8:28 AM David Geier wrote: Hi, On 6/7/23 23:37, Andres Freund wrote: > I think we're starting to hit quite a few limits related to the process model, > particularly on bigger machines. The overhead of cros

Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 12, 2023 at 5:17 PM Heikki Linnakangas wrote: > On 10/06/2023 21:01, Hannu Krosing wrote: > > On Mon, Jun 5, 2023 at 4:52 PM Heikki Linnakangas > wrote: > > <<>> > > > * The backend code would be more complex. > > -- this is still the case > > I don't quite buy that. A multi-threade

Re: Let's make PostgreSQL multi-threaded

On Thu, Jul 27, 2023 at 8:28 AM David Geier wrote: > Hi, > > On 6/7/23 23:37, Andres Freund wrote: > > 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

Re: Let's make PostgreSQL multi-threaded

On Thu, 15 Jun 2023 at 11:07, Hannu Krosing wrote: > > One more unexpected benefit of having shared caches would be easing > access to other databases. > > If the system caches are there for all databases anyway, then it > becomes much easier to make queries using objects from multiple > databases

Re: Let's make PostgreSQL multi-threaded

Hi, On 6/7/23 23:37, Andres Freund wrote: 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 t

Re: Let's make PostgreSQL multi-threaded

I think planner would also benefit from threads. There are many tasks in planner that are independent and can be scheduled using dependency graph. They are too small to be parallelized through separate backends but large enough to be performed by threads. Planning queries involving partitioned tabl

Re: Let's make PostgreSQL multi-threaded

On 15.06.2023 12:04 PM, Hannu Krosing wrote: So a fair bit of work but also a clearly defined benefits of 1) reduced memory usage 2) no need to rebuild caches for each new connection 3) no need to track PREPARE statements inside connection poolers. Shared plan cache (not only prepared statem

Re: Let's make PostgreSQL multi-threaded

On 15.06.2023 11:41 AM, James Addison wrote: On Thu, 15 Jun 2023 at 08:12, Konstantin Knizhnik wrote: On 15.06.2023 1:23 AM, James Addison wrote: On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik wrote: On 12.06.2023 3:23 PM, Pavel Borisov wrote: Is the following true or not? 1. If w

Re: Let's make PostgreSQL multi-threaded

One more unexpected benefit of having shared caches would be easing access to other databases. If the system caches are there for all databases anyway, then it becomes much easier to make queries using objects from multiple databases. Note that this does not strictly need threads, just shared cac

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 15, 2023 at 10:41 AM James Addison wrote: > > This is making me wonder about other performance/scalability areas > that might not have been considered due to focus on the details of the > existing codebase, but I'll save that for another thread and will try > to learn more first. A gr

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 15, 2023 at 9:12 AM Konstantin Knizhnik wrote: > There are three different but related directions of improving current > Postgres: > 1. Replacing processes with threads Here we could likely start with making parallel query multi-threaded. This would also remove the big blocker for

Re: Let's make PostgreSQL multi-threaded

On Thu, 15 Jun 2023 at 08:12, Konstantin Knizhnik wrote: > > > > On 15.06.2023 1:23 AM, James Addison wrote: > > On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik wrote: > > > On 12.06.2023 3:23 PM, Pavel Borisov wrote: > > Is the following true or not? > > 1. If we switch processes to threads bu

Re: Let's make PostgreSQL multi-threaded

On 15.06.2023 1:23 AM, James Addison wrote: On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik wrote: On 12.06.2023 3:23 PM, Pavel Borisov wrote: Is the following true or not? 1. If we switch processes to threads but leave the amount of session local variables unchanged, there would be har

Re: Let's make PostgreSQL multi-threaded

On Tue, 13 Jun 2023 at 07:55, Konstantin Knizhnik wrote: > > > > On 12.06.2023 3:23 PM, Pavel Borisov wrote: > > Is the following true or not? > > > > 1. If we switch processes to threads but leave the amount of session > > local variables unchanged, there would be hardly any performance gain. > >

Re: Let's make PostgreSQL multi-threaded

On Wed, 14 Jun 2023 at 20:48, Robert Haas wrote: > > On Wed, Jun 14, 2023 at 3:16 PM James Addison wrote: > > I think that they're practical performance-related questions about the > > benefits of performing a technical migration that could involve > > significant development time, take years to

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 14, 2023 at 3:46 PM Hannu Krosing wrote: > I remember a few times when memory leaks in some PostGIS packages > cause slow memory exhaustion and the simple fix was limiting > connection lifetime to something between 15 min and an hour. > > The main problem here is that PostGIS uses a fe

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-13 16:55:12 +0900, Kyotaro Horiguchi wrote: > At Tue, 13 Jun 2023 09:55:36 +0300, Konstantin Knizhnik > wrote in > > Postgres backend is "thick" not because of large number of local > > variables. > > It is because of local caches: catalog cache, relation cache, prepared > > stat

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 14, 2023 at 3:16 PM James Addison wrote: > I think that they're practical performance-related questions about the > benefits of performing a technical migration that could involve > significant development time, take years to complete, and uncover > problems that cause reliability issu

Re: Let's make PostgreSQL multi-threaded

On Tue, Jun 13, 2023 at 9:55 AM Kyotaro Horiguchi wrote: > > At Tue, 13 Jun 2023 09:55:36 +0300, Konstantin Knizhnik > wrote in > > Postgres backend is "thick" not because of large number of local > > variables. > > It is because of local caches: catalog cache, relation cache, prepared > > state

Re: Let's make PostgreSQL multi-threaded

On Mon, 12 Jun 2023 at 20:24, Andres Freund wrote: > > Hi, > > On 2023-06-12 16:23:14 +0400, Pavel Borisov wrote: > > Is the following true or not? > > > > 1. If we switch processes to threads but leave the amount of session > > local variables unchanged, there would be hardly any performance gain

Re: Let's make PostgreSQL multi-threaded

On 6/14/23 09:01, Kyotaro Horiguchi wrote: At Wed, 14 Jun 2023 08:46:05 +0300, Konstantin Knizhnik wrote in But I do not think that it is somehow related with using threads instead of process. The question whether to use private or shared cache is not directly related to threads vs. process ch

Re: Let's make PostgreSQL multi-threaded

At Wed, 14 Jun 2023 08:46:05 +0300, Konstantin Knizhnik wrote in > But I do not think that it is somehow related with using threads > instead of process. > The question whether to use private or shared cache is not directly > related to threads vs. process choice. Yeah, I unconsciously conflate

Re: Let's make PostgreSQL multi-threaded

On 13.06.2023 11:46 AM, Kyotaro Horiguchi wrote: So we can assume that catalog  and relation cache should always fit in memory memory (otherwise significant rewriting of all Postgtres code working with relations will be needed). I'm not sure that is ture.. But likely to be? Sorry, looks li

Re: Let's make PostgreSQL multi-threaded

On 6/13/23 10:20, Konstantin Knizhnik wrote: The fact that it is flushed out upon reconnection can not help much: what if backends are not going to disconnect? This is why many connection pools have a maximum connection lifetime which can be configured. So in practice flushing all caches on d

Re: Let's make PostgreSQL multi-threaded

At Tue, 13 Jun 2023 11:20:56 +0300, Konstantin Knizhnik wrote in > > > On 13.06.2023 10:55 AM, Kyotaro Horiguchi wrote: > > At Tue, 13 Jun 2023 09:55:36 +0300, Konstantin Knizhnik > > wrote in > >> Postgres backend is "thick" not because of large number of local > >> variables. > >> It is bec

Re: Let's make PostgreSQL multi-threaded

On 13.06.2023 10:55 AM, Kyotaro Horiguchi wrote: At Tue, 13 Jun 2023 09:55:36 +0300, Konstantin Knizhnik wrote in Postgres backend is "thick" not because of large number of local variables. It is because of local caches: catalog cache, relation cache, prepared statements cache,... If they a

Re: Let's make PostgreSQL multi-threaded

At Tue, 13 Jun 2023 09:55:36 +0300, Konstantin Knizhnik wrote in > Postgres backend is "thick" not because of large number of local > variables. > It is because of local caches: catalog cache, relation cache, prepared > statements cache,... > If they are not rewritten, then backend still may con

Re: Let's make PostgreSQL multi-threaded

On 12.06.2023 3:23 PM, Pavel Borisov wrote: Is the following true or not? 1. If we switch processes to threads but leave the amount of session local variables unchanged, there would be hardly any performance gain. 2. If we move some backend's local variables into shared memory then the perfor

Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 12, 2023 at 12:24:30PM -0700, Andres Freund wrote: > Those points seems pretty much unrelated to the potential gains from switching > to a threading model. The main advantages are: > > 1) We'd gain from being able to share state more efficiently (using normal >pointers) and more dy

Re: Let's make PostgreSQL multi-threaded

On 10/06/2023 21:01, Hannu Krosing wrote: On Mon, Jun 5, 2023 at 4:52 PM Heikki Linnakangas wrote: If there are no major objections, I'm going to update the developer FAQ, removing the excuses there for why we don't use threads [1]. I think it is not wise to start the wholesale removal of th

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-12 16:23:14 +0400, Pavel Borisov wrote: > Is the following true or not? > > 1. If we switch processes to threads but leave the amount of session > local variables unchanged, there would be hardly any performance gain. False. > 2. If we move some backend's local variables into sha

Re: Let's make PostgreSQL multi-threaded

Is the following true or not? 1. If we switch processes to threads but leave the amount of session local variables unchanged, there would be hardly any performance gain. 2. If we move some backend's local variables into shared memory then the performance gain would be very near to what we get with

Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 12, 2023, at 13:53, Tomas Vondra wrote: > In a way, I think this "split into independently beneficial steps" > strategy is the only option with a meaningful chance of success. +1 /Joel

Re: Let's make PostgreSQL multi-threaded

On 6/10/23 13:20, Dave Cramer wrote: > > > On Fri, 9 Jun 2023 at 18:29, Stephen Frost > wrote: > > Greetings, > > * Dave Cramer (davecramer@postgres.rocks) wrote: > > One thing I can think of is upgrading. AFAIK dump and restore is > the only >

Re: Let's make PostgreSQL multi-threaded

On Sat, Jun 10, 2023 at 11:32 PM Hannu Krosing wrote: > > On Mon, Jun 5, 2023 at 4:52 PM Heikki Linnakangas wrote: > > > > If there are no major objections, I'm going to update the developer FAQ, > > removing the excuses there for why we don't use threads [1]. > > I think it is not wise to start

Re: Let's make PostgreSQL multi-threaded

I don't have an objection, but I do wonder: can one (or perhaps a few) queries/workloads be provided where threading would be significantly beneficial? (some material there could help get people on-board with the idea and potentially guide many of the smaller questions that arise along the way) O

Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 5, 2023 at 4:52 PM Heikki Linnakangas wrote: > > If there are no major objections, I'm going to update the developer FAQ, > removing the excuses there for why we don't use threads [1]. I think it is not wise to start the wholesale removal of the objections there. But I think it is wo

Re: Let's make PostgreSQL multi-threaded

On Fri, 9 Jun 2023 at 18:29, Stephen Frost wrote: > Greetings, > > * Dave Cramer (davecramer@postgres.rocks) wrote: > > One thing I can think of is upgrading. AFAIK dump and restore is the only > > way to change the on disk format. > > Presuming that eventually we will be forced to change the on

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 11:37:00AM +1200, Thomas Munro wrote: > It's old, but this describes the 4 main models and which well known > RDBMSes use them in section 2.3: > > https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf > > TL;DR DB2 is the winner, it can do process-per-connection, > thre

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 06:38:38PM +0530, Ashutosh Bapat wrote: > With multiple processes, we can use all the available cores (at least > theoretically if all those processes are independent). But is that > guaranteed with single process multi-thread model? Google didn't throw > any definitive answ

Re: Let's make PostgreSQL multi-threaded

Greetings, * Dave Cramer (davecramer@postgres.rocks) wrote: > One thing I can think of is upgrading. AFAIK dump and restore is the only > way to change the on disk format. > Presuming that eventually we will be forced to change the on disk format it > would be nice to be able to do so in a manner

Re: Let's make PostgreSQL multi-threaded

On Fri, 9 Jun 2023 at 17:20, Dave Cramer wrote: > > This is somewhat orthogonal to the topic of threading but relevant to the use > of resources. > > If we are going to undertake some hard problems perhaps we should be looking > at other problems that solve other long term issues before we commi

Re: Let's make PostgreSQL multi-threaded

This is somewhat orthogonal to the topic of threading but relevant to the use of resources. If we are going to undertake some hard problems perhaps we should be looking at other problems that solve other long term issues before we commit to spending resources on changing the process model. One th

Re: Let's make PostgreSQL multi-threaded

This is an interesting message thread. I think in regards to the OP's call to make PG multi-threaded, there should be a clear and identifiable performance target and use cases for the target. How much performance boost can be expected, and if so, in which data application context? Will queries retu

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-09 07:34:49 +1200, Thomas Munro wrote: > I wasn't in Mathew Wilcox's unconference in Ottawa but I found an > older article on LWN: > > https://lwn.net/Articles/895217/ > > For what it's worth, FreeBSD hackers have studied this topic too (and > it's been done in Android and no doub

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 13:08, Hannu Krosing wrote: > I discovered this thread from a Twitter post "PostgreSQL will finally > be rewritten in Rust" :) > By the time we got around to finishing this, there would be a better language to write it in. Dave

Re: Let's make PostgreSQL multi-threaded

> On Mon, Jun 05, 2023 at 06:43:54PM +0300, Heikki Linnakangas wrote: > On 05/06/2023 11:28, Tristan Partin wrote: > > > # Exposed PIDs > > > > > > We expose backend process PIDs to users in a few places. > > > pg_stat_activity.pid and pg_terminate_backend(), for example. They need > > > to be repl

Re: Let's make PostgreSQL multi-threaded

On Fri, Jun 9, 2023 at 4:00 AM Andres Freund wrote: > On 2023-06-08 12:15:58 +0200, Hannu Krosing wrote: > > > 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. > > As-is that'd just

Re: Let's make PostgreSQL multi-threaded

On 8/6/23 15:56, Robert Haas wrote: Yeah, I've had similar thoughts. I'm not exactly sure what the advantages of such a refactoring might be, but the current structure feels pretty limiting. It works OK because we don't do anything in the postmaster other than fork a new backend, but I'm not sure

Re: Let's make PostgreSQL multi-threaded

On Fri, Jun 9, 2023 at 5:02 AM Ilya Anfimov wrote: > Isn't all the memory operations would require nearly the same > shared memory allocators if someone switches to a threaded imple- > mentation? It's true that we'd need concurrency-aware MemoryContext implementations (details can be debated)

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 11:56:13 -0400, Robert Haas wrote: > On Thu, Jun 8, 2023 at 11:02 AM Hannu Krosing wrote: > > No, I meant that this needs to be fixed at OS level, by being able to > > use the same mapping. > > > > We should not shy away from asking the OS people for adding the useful > > featur

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 17:55:57 +0200, Matthias van de Meent wrote: > While I agree that "sharing page tables across processes" is useful, > it looks like it'd be much more effort to correctly implement for e.g. > DSM than implementing threading. > Konstantin's diff is "only" 20.1k lines [0] added and/

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 17:02:08 +0200, Hannu Krosing wrote: > On Thu, Jun 8, 2023 at 4:56 PM Robert Haas wrote: > > > > On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > > > That sounds like a bad idea, dynamic shared memory is more expensive > > > > to maintain than our static shared memory sy

Re: Let's make PostgreSQL multi-threaded

I discovered this thread from a Twitter post "PostgreSQL will finally be rewritten in Rust" :) On Mon, Jun 5, 2023 at 5:18 PM Tom Lane wrote: > > Heikki Linnakangas writes: > > I spoke with some folks at PGCon about making PostgreSQL multi-threaded, > > so that the whole server runs in a single

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 07, 2023 at 10:26:07AM +1200, Thomas Munro wrote: > On Tue, Jun 6, 2023 at 6:52???AM Andrew Dunstan wrote: > > If we were starting out today we would probably choose a threaded > > implementation. But moving to threaded now seems to me like a > > multi-year-multi-person project with

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 16:47:48 +0300, Konstantin Knizhnik wrote: > Actually TLS not not more expensive then accessing struct fields (at least > at x86 platform), consider the following program: It really depends on the OS and the architecture, not just the architecture. And even on x86-64 Linux, the

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > Do we have any statistics for the distribution of our user base ? > > My gut feeling says that for performance-critical use the non-Linux is > in low single digits at best. > Stats are probably not possible, but based on years of consulting,

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-08 12:15:58 +0200, Hannu Krosing wrote: > On Thu, Jun 8, 2023 at 11:54 AM Hannu Krosing wrote: > > > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund wrote: > > > > > > Hi, > > > > > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote: > > > > 2. While I wouldn't want to necessa

Re: Let's make PostgreSQL multi-threaded

On 2023-06-08 14:01:16 +0200, Jose Luis Tallon wrote: > * 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

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 11:02 AM Hannu Krosing wrote: > No, I meant that this needs to be fixed at OS level, by being able to > use the same mapping. > > We should not shy away from asking the OS people for adding the useful > features still missing. > > It was mentioned in the Unconference Kernel

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 17:02, Hannu Krosing wrote: > > On Thu, Jun 8, 2023 at 4:56 PM Robert Haas wrote: > > > > On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > > > That sounds like a bad idea, dynamic shared memory is more expensive > > > > to maintain than our static shared memory syste

Re: Let's make PostgreSQL multi-threaded

On 2023-06-08 10:33:26 -0400, Greg Stark wrote: > On Wed, 7 Jun 2023 at 18:09, Andres Freund wrote: > > Having the same memory mapping between threads makes allows the > > hardware to share the TLB (on x86 via process context identifiers), which > > isn't realistically possible with different proc

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 14:44, Hannu Krosing wrote: > > On Thu, Jun 8, 2023 at 2:15 PM Matthias van de Meent > wrote: > > > > On Thu, 8 Jun 2023 at 11:54, Hannu Krosing wrote: > > > > > > This part was touched in the "AMA with a Linux Kernale Hacker" > > > Unconference session where he mentioned t

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 4:56 PM Robert Haas wrote: > > On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > > 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 sha

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 8:44 AM Hannu Krosing wrote: > > 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. > >

Re: Let's make PostgreSQL multi-threaded

On Wed, 7 Jun 2023 at 18:09, Andres Freund wrote: > Having the same memory mapping between threads makes allows the > hardware to share the TLB (on x86 via process context identifiers), which > isn't realistically possible with different processes. As a matter of historical interest Solaris actua

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:45 PM Andres Freund wrote: > People have argued that the process model is more robust. But it turns out > that we have to crash-restart for just about any "bad failure" anyway. It used > to be (a long time ago) that we didn't, but that was just broken. How hard have you t

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:39 PM Peter Eisentraut wrote: > On 07.06.23 23:30, Andres Freund wrote: > > Yea, we definitely need the supervisor function in a separate > > process. Presumably that means we need to split off some of the postmaster > > responsibilities - e.g. I don't think it'd make sens

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:37 PM Andres Freund wrote: > 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 m

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 5:30 PM Andres Freund wrote: > On 2023-06-05 17:51:57 +0300, Heikki Linnakangas wrote: > > If there are no major objections, I'm going to update the developer FAQ, > > removing the excuses there for why we don't use threads [1]. > > I think we should do this even if there's

Re: Let's make PostgreSQL multi-threaded

On 07.06.2023 3:53 PM, Robert Haas wrote: I think I remember a previous conversation with Andres where he opined that thread-local variables are "really expensive" (and I apologize in advance if I'm mis-remembering this). Now, Andres is not a man who accepts a tax on performance of any size wi

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 6:04 AM Hannu Krosing wrote: > Here I was hoping to go in the opposite direction and support parallel > query across replicas. > > This looks much more doable based on the process model than the single > process / multiple threads model. I don't think this is any more or le

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 2:15 PM Matthias van de Meent wrote: > > On Thu, 8 Jun 2023 at 11:54, Hannu Krosing wrote: > > > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund wrote: > > > > > > Hi, > > > > > > On 2023-06-05 13:40:13 -0400, Jonathan S. Katz wrote: > > > > 2. While I wouldn't want to nec

Re: Let's make PostgreSQL multi-threaded

On Thu, 8 Jun 2023 at 11:54, Hannu Krosing wrote: > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund 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 coul

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

Re: Let's make PostgreSQL multi-threaded

On 2023-06-07 We 17:58, Andres Freund wrote: Hi, On 2023-06-07 08:53:24 -0400, Robert Haas wrote: Now, Andres is not a man who accepts a tax on performance of any size without a fight, so his "really expensive" might turn out to resemble my "pretty cheap." However, if widespread use of TLS is

Re: Let's make PostgreSQL multi-threaded

On 6/8/23 01:37, Thomas Munro wrote: > On Thu, Jun 8, 2023 at 10:37 AM Jeremy Schneider > wrote: >> On 6/7/23 2:39 PM, Thomas Kellerer wrote: >>> Tomas Vondra schrieb am 07.06.2023 um 21:20: Also, which other projects did this transition? Is there something we could learn from them? W

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 11:54 AM Hannu Krosing wrote: > > On Wed, Jun 7, 2023 at 11:37 PM Andres Freund 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 c

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 12:09 AM Andres Freund wrote: ... > We could e.g. eventually decide that we > don't support parallel query without threading support - which would allow us > to get rid of a very significant amount of code and runtime overhead. Here I was hoping to go in the opposite direc

Re: Let's make PostgreSQL multi-threaded

I think I remember that in the early days of development somebody did send a patch-set for making PostgreSQL threaded on Solaris. I don't remember why this did not catch on. On Wed, Jun 7, 2023 at 11:40 PM Thomas Kellerer wrote: > > Tomas Vondra schrieb am 07.06.2023 um 21:20: > > Also, which ot

Re: Let's make PostgreSQL multi-threaded

On Wed, Jun 7, 2023 at 11:37 PM Andres Freund 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

Re: Let's make PostgreSQL multi-threaded

Hi, On 6/8/23 12:37 AM, Jeremy Schneider wrote: On 6/7/23 2:39 PM, Thomas Kellerer wrote: Tomas Vondra schrieb am 07.06.2023 um 21:20: I did google search for "oracle threaded_execution" and browsed a bit; didn't see anything that seems earth shattering so far. FWIW, I recall Karl Arao's wi

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 3:00 AM Andres Freund wrote: > > Yea, we definitely need the supervisor function in a separate > process. Presumably that means we need to split off some of the postmaster > responsibilities - e.g. I don't think it'd make sense to handle connection > establishment in the su

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 10:37 AM Jeremy Schneider wrote: > On 6/7/23 2:39 PM, Thomas Kellerer wrote: > > Tomas Vondra schrieb am 07.06.2023 um 21:20: > >> Also, which other projects did this transition? Is there something we > >> could learn from them? Were they restricted to much smaller list of >

Re: Let's make PostgreSQL multi-threaded

On 6/7/23 2:39 PM, Thomas Kellerer wrote: > Tomas Vondra schrieb am 07.06.2023 um 21:20: >> Also, which other projects did this transition? Is there something we >> could learn from them? Were they restricted to much smaller list of >> platforms? > > Not open source, but Oracle was historically mu

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-06 16:14:41 -0400, Greg Stark wrote: > I think of processes and threads as fundamentally the same things, > just a slightly different API -- namely that in one memory is by > default unshared and needs to be explicitly shared and in the other > it's default shared and needs to be ex

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-07 08:53:24 -0400, Robert Haas wrote: > In my mind, the bigger question is how much further than that do you > have to go? I think I remember a previous conversation with Andres > where he opined that thread-local variables are "really expensive" > (and I apologize in advance if I'm

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-07 23:39:01 +0200, Peter Eisentraut wrote: > On 07.06.23 23:30, Andres Freund wrote: > > Yea, we definitely need the supervisor function in a separate > > process. Presumably that means we need to split off some of the postmaster > > responsibilities - e.g. I don't think it'd make s

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-05 20:15:56 -0400, Bruce Momjian wrote: > Yes, sorry, critical sections is what I was remembering. My question is > whether all unexpected backend exits should be treated as critical > sections? Yes. People have argued that the process model is more robust. But it turns out that

Re: Let's make PostgreSQL multi-threaded

Tomas Vondra schrieb am 07.06.2023 um 21:20: Also, which other projects did this transition? Is there something we could learn from them? Were they restricted to much smaller list of platforms? Firebird did this a while ago if I'm not mistaken. Not open source, but Oracle was historically mult

Re: Let's make PostgreSQL multi-threaded

On 07.06.23 23:30, Andres Freund wrote: Yea, we definitely need the supervisor function in a separate process. Presumably that means we need to split off some of the postmaster responsibilities - e.g. I don't think it'd make sense to handle connection establishment in the supervisor process. I wo

Re: Let's make PostgreSQL multi-threaded

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

Re: Let's make PostgreSQL multi-threaded

Hi, On 2023-06-05 17:51:57 +0300, Heikki Linnakangas wrote: > If there are no major objections, I'm going to update the developer FAQ, > removing the excuses there for why we don't use threads [1]. I think we should do this even if there's no concensus to slowly change to threads. There's clearly

Re: Let's make PostgreSQL multi-threaded

On Thu, Jun 8, 2023 at 7:20 AM Tomas Vondra wrote: > Is the platform support really there for all platforms we want/intend to > support? I have no problem believing that for modern Linux/BSD systems, > but what about the older stuff we currently support. There is a conversation to be had about wh

Re: Let's make PostgreSQL multi-threaded

On 6/5/23 17:33, Heikki Linnakangas wrote: > On 05/06/2023 11:18, Tom Lane wrote: >> Heikki Linnakangas 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 ma

Re: Let's make PostgreSQL multi-threaded

07.06.2023 15:53, Robert Haas wrote: Right now, if you need a bit of additional session-local state, you just declare a variable and you're all set. That's not a perfect system and does cause some problems, but we can't go from there to a system where it's impossible to add session-local state wi

Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 5, 2023 at 8:22 PM Heikki Linnakangas wrote: > > 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

Re: Let's make PostgreSQL multi-threaded

On Tue, Jun 6, 2023 at 10:02 PM Tom Lane wrote: > I agree that if we were building this system from scratch today, > we'd probably choose thread-per-session not process-per-session. > But the costs of getting to that from where we are will be enormous. > I seriously doubt that the net benefits cou

  1   2   >