It sounds to me like migrating to a fully multi-threaded architecture may not be worth the effort. Simply reducing the number of processes from thousands to hundreds would be a huge win and likely much more feasible than a complete re-architecture.
PostgreSQL reconsiders its process-based model
161–170 of 377 posts
Re: PostgreSQL reconsiders its process-based model
#162It would be interesting to have something between threads and processes. I'll call them heavy-threads for sake of discussion. Like light-threads, heavy-threads would share the same process-security-boundary and therefore switching between them would be cheap. No need to flush TLB, I$, D$. Like processes, heavy-threads would have mostly-separate address spaces by default. Similar to forking a process, they could share…
You cannot COW and share the TLB state. The caches aren't flushed in process changes either: it's that the data is different so evictions happen.
Re: PostgreSQL reconsiders its process-based model
#163Earlier quoted context omitted.
No, I ran that on v19. $ ps -ef | grep smon UID PID PPID C STIME TTY TIME CMD oracle 22131 1 0 Mar28 ? 00:03:09 ora_smon_yourdb $ $ORACLE_HOME/bin/sqlplus -silent '/ as sysdba' select version_full from v$instance; VERSION_FULL ----------------- 19.18.0.0.0
https://oracle-base.com/articles/12c/multithreaded-model-usi... Probably still requires the parameter to be set.
Contrast this to Microsoft SQL Server:
$ systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2023-06-19 15:48:05 CDT; 1min 18s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 2125 (sqlservr)
Tasks: 123
CGroup: /system.slice/mssql-server.service
├─2125 /opt/mssql/bin/sqlservr
└─2156 /opt/mssql/bin/sqlservrRe: PostgreSQL reconsiders its process-based model
#164It would be interesting to have something between threads and processes. I'll call them heavy-threads for sake of discussion. Like light-threads, heavy-threads would share the same process-security-boundary and therefore switching between them would be cheap. No need to flush TLB, I$, D$. Like processes, heavy-threads would have mostly-separate address spaces by default. Similar to forking a process, they could share…
> No need to flush TLB
TLB isn't "flushed" so much as it is useless across different memory address spaces. Switching processes means switching address spaces, which means you have to switch the contents of the TLB to the new process' TLB entries, which eventually indeed flushes the TLB, but that is only over time, not necessarily the moment you switch processes.
> Like processes, heavy-threads would have mostly-separate address spaces by default.
This thus conflicts with the need to not flush TLBs. You can't not change TLB contents across address spaces.
Re: PostgreSQL reconsiders its process-based model
#165Sorry if I offend anybody, but this sounds like such a bad idea. I have been running various versions of postgres in production for 15 years with thousands of processes on super beefy machines, and I can tell you without a doubt that sometimes those processes crash - specially if you are running any of the extensions. Nevertheless, Postgres has 99% of the time proven to be resilient. The idea that a bad client can br…
Also, reducing context switching overhead (or any other CPU overhead) is probably not gonna fix the garbage I/O performance.
Re: PostgreSQL reconsiders its process-based model
#166Earlier quoted context omitted.
> But there is another way. Ok?
The person probably implied that Postgres should switch to another toolchain that guarantees more things at compile time, so probably Rust.
You can take a chunk of code and just rewrite it in Rust. You'll learn a lot quickly by this.
Re: PostgreSQL reconsiders its process-based model
#167Oracle has similar problems. On UNIX systems, Oracle uses a multi-process model, and you can see these: $ ps -ef | grep smon USER PID PPID STARTED TIME %CPU %MEM COMMAND oracle 22131 1 Mar 28 3:09 0.0 4.0 ora_smon_yourdb Windows forks processes about 100x slower than Linux, so Oracle runs threaded on that platform in one great big PID. Sybase was the first major database that fully adopted threads from an architectur…
> Windows forks processes about 100x slower than Linux... I work with a Windows-based COTS webapp that uses Postgres w/o any connection pooling. It's nearly excruciating to use because it spins-up new Postgres processes for each page load. If not for the fact that the Postgres install is "turnkey" with the app I'd just move Postgres over to a Linux machine.
If you run postgres under WSLv1 (now available on Server Edition as well), the WSL subsystem handles processes and virtual memory in a way that has been specifically designed to optimize process initialization as compared to the traditional Win32 approach.
Re: PostgreSQL reconsiders its process-based model
#168Earlier quoted context omitted.
> there isn’t a whole ecosystem of packages that depend on access to a thread unsafe C API They mentioned a similar issue for Postgres extensions, no? > Haas, though, is not convinced that it would ever be possible to remove support for the process-based mode. Threads might not perform better for all use cases, or some important extensions may never gain support for running in threads.
I question how important an extension is if there’s not enough incentive to port it to the new paradigm, at least eventually.
Well. The thing with that is just that there are a lot of extensions. Like, a lot!
Re: PostgreSQL reconsiders its process-based model
#169This has Python 3 vibes.
Re: PostgreSQL reconsiders its process-based model
#170Earlier quoted context omitted.
However, it's already the case that if a postgres process crashes, the whole cluster gets restarted. I've occasionally seen this message: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a momen…
yes, but postmaster is still running to roll back the transaction. If you crash a single multi-threaded process, you may lose postmaster as well and then sadness would ensue
PostgreSQL can recover from abruptly aborted transactions (think "pulled the power cord") by replaying the journal. This is not going to change anyway.