Mysql guys: Smart move, Postgres!
PostgreSQL reconsiders its process-based model
341–350 of 377 posts
Re: PostgreSQL reconsiders its process-based model
#342Why should TLB flush performance ever be a problem on big machines? You can have one process per core with 128 or more cores, never flush any TLB if you pin those processes. And as it is a database, shoveling data from/to disk/SSD is your main concern anyways.
PostgreSQL uses synchronous IO, so you won't saturate the CPU with one process (or thread) per core. That said, I think there have been efforts to use io_uring on Linux. I'm not sure how that would work with the process per connection model. Haven't been following it...
Problem with all kinds of asynchronous I/O is that your processes then need internal multiplexing, akin to what certain lightweight userspace thread models are doing. In the end, it might be harder to introduce than just using OS threads.
Re: PostgreSQL reconsiders its process-based model
#343Earlier quoted context omitted.
Yeah. Without being familiar with the Postgres source, this seems to be what I call a "somersault problem": hard to break down into sub-goals. I have heard that the Postgres codebase is solid which makes it easier but it's still mature and highly complex. It doesn't sound feasible to me. [link redacted]
The original post does describe several sub-problems. The group could first chip away at global state, signals, libraries. They can do this before changing the process model in any way.
Good point.
Re: PostgreSQL reconsiders its process-based model
#344Earlier quoted context omitted.
As an outsider it doesn't sound like something a few people could spin off in a branch in a couple months and see how code review goes. They're talking about doing it over multiple (yearly?) releases. It seems like it'll take a lot of expert attention, which won't be available for other work and the changes themselves will impact all other ongoing work. I'm not trying to naysay it per se, bc again I don't have techni…
You are talking about implementation, the OP was talking about raising the concept with interested parties and seeing whether it is worth even starting to think about it. They could fork, they could add threading to some sub systems and roll it out over several versions. I don't know enough about the code but, of course, it is a hard problem but the solution might be to build it from the ground up as a threaded syste…
Am I going crazy, or has the obvious implementation of such a change been missed on people? If they were proposing taking a multi-threaded app and splitting it into a multi-process one, I would predict they would find a hell of a lot of unexpected or unknown implicit communication between threads, which would be a nightmare to untangle.
Going the other way, there is an extremely well understood interface between all the processes which run in isolation: shared memory. Nearly by definition this must be well coordinated between the processes.
So the first step in moving to a multi-threaded implementation would be to change nearly nothing about each process, and then just run each process in its own pthread, keeping all the shared memory ‘n all.
You would expect performance to be about the same, maybe a little better with the reduces TLB churn, but the architecture is basically unchanged. At that point, you can start to look at what are more appropriate communication/synchronisation mechanisms now you’re working in the same address space.
I just don’t understand why so many people seem to think this requires an enormous rewrite - having developed as a multi-process system means you’ve had to make so much of the problematic things explicit and control for them, and none of these threads would know anything at all about each other’s internals.
Re: PostgreSQL reconsiders its process-based model
#345Why should TLB flush performance ever be a problem on big machines? You can have one process per core with 128 or more cores, never flush any TLB if you pin those processes. And as it is a database, shoveling data from/to disk/SSD is your main concern anyways.
PostgreSQL uses synchronous IO, so you won't saturate the CPU with one process (or thread) per core. That said, I think there have been efforts to use io_uring on Linux. I'm not sure how that would work with the process per connection model. Haven't been following it...
> That said, I think there have been efforts to use io_uring on Linux. I'm not sure how that would work with the process per connection model. Haven't been following it...
There's some minor details that are easier with threads in that context, but on the whole it doesn't make much of a difference.
Re: PostgreSQL reconsiders its process-based model
#346Worked on a codebase which was separate processes, each of which has a shedload of global variables. It was a nightmare working out what was going on, not helped by the fact that there was no naming convention for the globals, plus they were not declared in a single place. I believe their use was a performance move, ie having the linker pin a var to a specific memory location rather than copying it to the stack as a…
There are 2000 globals here, so more like a couple of shedloads. While this is something you'd sort of expect for a product that's been around 30+ years, it really seems like there's a lot of optimization that could happen and still stick with the process model.
Re: PostgreSQL reconsiders its process-based model
#347For 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. regards, tom lane (via https://lwn.net/ml/pgsql-hackers/4178104.1685978307@sss.pgh.... ) If Tom Lane says it will be a disaster, I believe it will be a disaster.
Reminds me of PHP 6... For those who don't follow PHP closely - that version was an attempted refactor of the string implementation which essentially shut down nearly all work on PHP for a decade, stagnating the language until it became pretty terrible compared to other options. They finally gave up and started work on PHP 7 which uses the (perfectly good) PHP 5 strings. Ten years of wasted time by the best internal…
Implying it had ever not been terrible compared to other options
Re: PostgreSQL reconsiders its process-based model
#348For 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. regards, tom lane (via https://lwn.net/ml/pgsql-hackers/4178104.1685978307@sss.pgh.... ) If Tom Lane says it will be a disaster, I believe it will be a disaster.
Feel like the PostgreSQL Core Team should just build a new database from scratch using what they have learned from experience instead of attempting such a fundamental architectural migration. It would give them more freedom to change things also. Call it "postgendb" and provide a data migrator.
That's a great idea. I've been considering whether or not to use Cockroach Db at work, and I love the fact that it's distributed from the get go.
Why not work on something like that instead of changing something that works? Especially since they the process model really only runs into trouble on large systems.
Re: PostgreSQL reconsiders its process-based model
#349Earlier quoted context omitted.
I appear to have missed them, then. Could you point out, aside from the large numbers of clients I mentioned (and the development overhead of implementing multi-process memory management code), what the article mentions is a primary drawback of using processes over threads?
> 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.
Yes, that's per-client performance scaling ("significant number of connections"), which indicates a pooled connection model might mitigate most of the performance impact while allowing some core code to remain process-oriented (and thus, not rewritten).
Re: PostgreSQL reconsiders its process-based model
#350Earlier quoted context omitted.
PostgreSQL uses synchronous IO, so you won't saturate the CPU with one process (or thread) per core. That said, I think there have been efforts to use io_uring on Linux. I'm not sure how that would work with the process per connection model. Haven't been following it...
> That said, I think there have been efforts to use io_uring on Linux. I'm not sure how that would work with the process per connection model. Haven't been following it... There's some minor details that are easier with threads in that context, but on the whole it doesn't make much of a difference.
I don't understand how it works with thread per connection either. io_uring is designed for systems that have a thread and ring per core, for you to give it a bunch of IO to do at once (batches and chains), and your threads to do other work in the meantime. The syscall cost is amortized or even (through IORING_SETUP_SQPOLL) eliminated. If your code is instead designed to be synchronous and thus can only do one IO at a time and needs a syscall to block on it, I don't think there's much if any benefit in using io_uring.
Possibly they'd have a ring per connection and just get an advantage when there's parallel IO going on for a single query? or these per-connection processes wouldn't directly do IO but send it via IPC to some IO-handling thread/process? Not sure either of those models are actually an improvement over the status quo, but who knows.