Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL reconsiders its process-based model

lwn.net

Post reply on HN
Print view View original

141–150 of 377 posts

Re: PostgreSQL reconsiders its process-based model

#141

Post by wizofaus »

vbezhenar wrote: post #137
wielebny wrote: post #83

Having been using and administering a lot of PostgreSQL servers, I hope they don't lose any stability over this. I've seen (and reported) bugs that caused panics/segfaults in specific psql processes. Not just connections, also processes related to wal writing or replication. The way it's built right now, a child process can be just forced to quit and it does not affect other processes. Hopefully switching into thread…

Of course it will. That's better than continue working with damaged memory structures and unpredictable consequences. For database it's more important than ever. Imagine writing corrupted data because other thread went crazy.

You're implying that only an OS can provide memory separation between units of execution - at least in .NET AppDomains give you the same protection within a single process, so why couldn't postgres have its own such mechanism? I'd also think with a database engine shared state is not just in-memory - i.e. one process can potentially corrupt the behaviour of another by what it writes to disk, so moving to a single-process model doesn't necessarily introduce problems that could never have existed previously (but, yes, would arguably make them more likely)

Re: PostgreSQL reconsiders its process-based model

#142

Post by chasil »

EvanAnderson wrote: post #40
chasil wrote: post #24

Oracle 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.

It would not be difficult to simply "pg_dump" all the data to Postgres on a Linux machine, then quietly set the clients to use the new server.

Re: PostgreSQL reconsiders its process-based model

#143

Post by moonchrome »

niccl wrote: post #139
zeroimpl wrote: post #131

Earlier 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

Restart on crash doesn't sound that difficult to do.

Re: PostgreSQL reconsiders its process-based model

#144

Post by mgaunard »

nextaccountic wrote: post #51
topspin wrote: post #38

Earlier quoted context omitted.

> I'm honestly surprised it took them so long to reach this conclusion. I'm not. You can get a long way with conventional IPC, and OS processes provide a lot of value. For most PostgreSQL instances the TLB flush penalty is at least 3rd or 4th on the list of performance concerns, far below prevailing storage and network bottlenecks. I share the concerns cited in this LWN story. Reworking this massive code base around…

From what I gather postgres isn't doing conventional IPC but instead it uses shared memory, which means the same mechanism threads use but with way higher complexity

What do you think IPC is?

Re: PostgreSQL reconsiders its process-based model

#145

Post by faangsticle »

topspin wrote: post #96
nextaccountic wrote: post #51

Earlier quoted context omitted.

From what I gather postgres isn't doing conventional IPC but instead it uses shared memory, which means the same mechanism threads use but with way higher complexity

As does Oracle, and others. I'm aware. IPC, to me, includes the conventional shared memory resources (memory segments, locks, semaphores, condition variable, etc.) used by these systems: resources acquired by processes for the purpose of communication with other processes. I get it though. The most general concept of shared memory is not coupled to an OS "process." You made me question whether my concept of term IPC…

> Why does citing ChatGPT make me feel so ugly inside?

Its the modern let me Google that for you. Just like people don't care what the #1 result on Google is, they also don't care what ChatGPT has to say about it. If they did, they'd ask it themselves.

Re: PostgreSQL reconsiders its process-based model

#146

Post by chasil »

blinkingled wrote: post #122
chasil wrote: post #24

Oracle 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…

Didn't Oracle switch to threaded model in 12c - at least on Linux I remember there being a parameter to do that - it dropped the number of processes significantly.

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

Re: PostgreSQL reconsiders its process-based model

#147

Post by jtc331 »

niccl wrote: post #139
zeroimpl wrote: post #131

Earlier 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

If you read the thread you’d see the discussion includes still having e.g. postmaster as a separate process.

Re: PostgreSQL reconsiders its process-based model

#148

Post by bsder »

masklinn wrote: post #32
cactusfrog wrote: post #26

Earlier quoted context omitted.

This is different because there isn’t a whole ecosystem of packages that depend on access to a thread unsafe C API. Getting the GIL out of core Python isn’t too challenging. Getting all of the packages that depend on Python’s C API working is.

An other component of the Gil story is that removing the Gil require adding fine grained locks, which (aside from making VM development more complicated) significantly increases lock traffic and thus runtime costs, which noticeably impacts single-threaded performance, which is of major import. Postgres starts from a share-nothing architecture, it’s quite a bit easier to evaluate the addition of sharing.

> which noticeably impacts single-threaded performance, which is of major import.

1) I don't buy this a priori. Almost everybody who removed a gigantic lock suddenly realizes that there was more contention than they thought and that atomizing it made performance improve.

2) Had Python bitten the bullet and removed the GIL back at Python 3.0, the performance would likely already be back to normal or better. You can't optimize hypothetically. Optimization on something like Python is an accumulation of lots of small wins.

Re: PostgreSQL reconsiders its process-based model

#149

Post by jtc331 »

wielebny wrote: post #83

Having been using and administering a lot of PostgreSQL servers, I hope they don't lose any stability over this. I've seen (and reported) bugs that caused panics/segfaults in specific psql processes. Not just connections, also processes related to wal writing or replication. The way it's built right now, a child process can be just forced to quit and it does not affect other processes. Hopefully switching into thread…

Because of shared memory most panics and seg faults in a worker process take down the entire server already (this wasn’t always the case, but not doing so was a bug).

Re: PostgreSQL reconsiders its process-based model

#150

Post by mattashii »

niccl wrote: post #139
zeroimpl wrote: post #131

Earlier 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

The threaded design wouldn't necessarily be single-process, it would just not have 1 process for every connection. Things like crash detection could still be handled in a separate process. The reason to use threading in most cases is to reduce communication and switching overhead, but for low-traffic backends like a crash handler the overhead of it being a process is quite limited - when it gets triggered context switching overhead is the least of your problems.
Post reply on HN