4 ms·

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

by chasil 3y ago

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 architectural perspective, and Microsoft SQL Server has certainly retained and improved on that model.

EvanAnderson 3y ago
> 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.
- devit 3y ago
  Use pgbouncer
  - ethbr0 3y ago
    Was curious about this as an architectural solution as well. We're really talking about X-per-client as the primary reason to move away from processes, right? So if you can get most of the benefit via pooling... why inherit the pain of porting? Presumably latency jitter would be a difficult problem with pools, but it seems easier (and safer) than porting processes -> threads. Disclaimer: High performance / low latency DB code is pretty far outside my wheelhouse.
    - ddorian43 3y ago
      > We're really talking about X-per-client as the primary reason to move away from processes, right? Many other things too. Like better sharing of caches. Lower overhead of thread instead of process. Etc. (read the thread)
    - ilyt 3y ago
      The reasons are explained in article. Read the article
      - ethbr0 3y ago
        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?
        ilyt 3y ago
        > 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.
        ethbr0 3y ago
        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).
    - anarazel 3y ago
      pgbouncer is not transparent, you loose features, particularly when using the pooling mode actually allowing a larger number of active concurrent connections. Solving those issues is a lot easier with threads than with processes.
  - treis 3y ago
    That helps a lot but it's not a replacement for large number of persistent connections. If you had that you could simplify things in the application layer and do interesting things with the DB.
- chasil 3y ago
  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.
- ComputerGuru 3y ago
  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.
blinkingled 3y ago
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.
- chasil 3y ago
  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
  - blinkingled 3y ago
    https://oracle-base.com/articles/12c/multithreaded-model-using-threaded_execution_12cr1 https://oracle-base.com/articles/12c/multithreaded-model-usi... Probably still requires the parameter to be set.
    - chasil 3y ago
      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/sqlservr
      - blinkingled 3y ago
        Yeah multiprocess isn't Microsoft's style given how expensive creating processes is on Windows. Oracle - never had a scalability issue on very big Linux, Solaris and HPUX systems though - they do it well in my experience.
      - 3y ago

by chasil 3y ago

EvanAnderson 3y ago

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

devit 3y ago
Use pgbouncer
- ethbr0 3y ago
  Was curious about this as an architectural solution as well. We're really talking about X-per-client as the primary reason to move away from processes, right? So if you can get most of the benefit via pooling... why inherit the pain of porting? Presumably latency jitter would be a difficult problem with pools, but it seems easier (and safer) than porting processes -> threads. Disclaimer: High performance / low latency DB code is pretty far outside my wheelhouse.
  - ddorian43 3y ago
    > We're really talking about X-per-client as the primary reason to move away from processes, right? Many other things too. Like better sharing of caches. Lower overhead of thread instead of process. Etc. (read the thread)
  - ilyt 3y ago
    The reasons are explained in article. Read the article
    - ethbr0 3y ago
      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?
      - ilyt 3y ago
        > 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.
        ethbr0 3y ago
        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).
  - anarazel 3y ago
    pgbouncer is not transparent, you loose features, particularly when using the pooling mode actually allowing a larger number of active concurrent connections. Solving those issues is a lot easier with threads than with processes.
- treis 3y ago
  That helps a lot but it's not a replacement for large number of persistent connections. If you had that you could simplify things in the application layer and do interesting things with the DB.
chasil 3y ago
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.
ComputerGuru 3y ago
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.

blinkingled 3y ago

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.

chasil 3y ago
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
- blinkingled 3y ago
  https://oracle-base.com/articles/12c/multithreaded-model-using-threaded_execution_12cr1 https://oracle-base.com/articles/12c/multithreaded-model-usi... Probably still requires the parameter to be set.
  - chasil 3y ago
    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/sqlservr
    - blinkingled 3y ago
      Yeah multiprocess isn't Microsoft's style given how expensive creating processes is on Windows. Oracle - never had a scalability issue on very big Linux, Solaris and HPUX systems though - they do it well in my experience.
    - 3y ago