4 ms·

Not the author but PG connection scalability being blamed on per-process model is somewhat misunderstood. You can easily have many hundreds of thousands of proc

by jpgvm 5y ago

Not the author but PG connection scalability being blamed on per-process model is somewhat misunderstood. You can easily have many hundreds of thousands of processes, they are mostly equivalent to threads on Linux anyway (with some minor differences ofc). Instead the poor scalability stems from the amount of resources allocated to each connection. If postgres was to switch to threads without changing this architecture it's likely scalability wouldn't be majorly improved. By the same token a connection pooler that is fork based but only allocates a very small amount of resources per connection is still likely to a net win as it can do statement based pooling of a smaller number of postgres connections.

kasey_junk 5y ago
Pretty good discussion of Postgres’ process bottlenecks: https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/ https://www.citusdata.com/blog/2020/10/08/analyzing-connecti... Summary: The resource per process issue is isolated to just the transaction isolation mechanism and can likely be improved but you are never getting around the context switching and memory locality issues of the per process model.
anarazel 5y ago
> You can easily have many hundreds of thousands of processes, they are mostly equivalent to threads on Linux anyway (with some minor differences ofc). Not really. The page-table overhead of a 100k processes alone is going to a eat up a lot of memory - and you don't have that with threads. And with something like postgres the page tables aren't going to be tiny, even with explicit use of huge pages for applicable allocations. The decrease in TLB hit ratio also is very painful. In a lot of workloads postgres spends way too much time in TLB faults, and that's to a good degree caused by the process model (also caused by linux making it way too hard to map executables with huge pages, itlb faults are a significant issue). That's not to say that a 1 connection: 1 thread approach is the right thing for a pooler - threads area also don't scale great. I think there are good simplicity arguments to be made for a 1 connection: 1 thread model for a database server itself, but for poolers the benefits seem much smaller.
- anarazel 5y ago
  (Although the sibling post pointing to my postgres connection scalability post is right - processes are not the major bottleneck at the moment, and certainly not with < PG 14)
  - jpgvm 5y ago
    Yeah I agree wholeheartedly. What I was trying to get across was that simply s/processes/threads isn't going to make a massive difference to postgres if that was the only change you were to make. Your analysis seems pretty on-point with what I expected, i.e snapshot scalability and other postgres specific arch being the major bottlenecks for higher connection counts.
    - anarazel 5y ago
      FWIW, a good chunk of the snapshot scalability changes are in PG 14, so the situation has gone a bit further towards s/processes/threads/ being the primary bottleneck. It depends quite a bit on whether you're write-mostly (bottlenecks almost completely unrelated to processes vs threads) or read-mostly. And in the latter case whether it's a context switch heavy workload (processes vs threads may be a significant factor), or not (processes vs threads can be a significant factor, but less likely).

by jpgvm 5y ago

kasey_junk 5y ago

Pretty good discussion of Postgres’ process bottlenecks: https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/ https://www.citusdata.com/blog/2020/10/08/analyzing-connecti... Summary: The resource per process issue is isolated to just the transaction isolation mechanism and can likely be improved but you are never getting around the context switching and memory locality issues of the per process model.

anarazel 5y ago

> You can easily have many hundreds of thousands of processes, they are mostly equivalent to threads on Linux anyway (with some minor differences ofc). Not really. The page-table overhead of a 100k processes alone is going to a eat up a lot of memory - and you don't have that with threads. And with something like postgres the page tables aren't going to be tiny, even with explicit use of huge pages for applicable allocations. The decrease in TLB hit ratio also is very painful. In a lot of workloads postgres spends way too much time in TLB faults, and that's to a good degree caused by the process model (also caused by linux making it way too hard to map executables with huge pages, itlb faults are a significant issue). That's not to say that a 1 connection: 1 thread approach is the right thing for a pooler - threads area also don't scale great. I think there are good simplicity arguments to be made for a 1 connection: 1 thread model for a database server itself, but for poolers the benefits seem much smaller.

anarazel 5y ago
(Although the sibling post pointing to my postgres connection scalability post is right - processes are not the major bottleneck at the moment, and certainly not with < PG 14)
- jpgvm 5y ago
  Yeah I agree wholeheartedly. What I was trying to get across was that simply s/processes/threads isn't going to make a massive difference to postgres if that was the only change you were to make. Your analysis seems pretty on-point with what I expected, i.e snapshot scalability and other postgres specific arch being the major bottlenecks for higher connection counts.
  - anarazel 5y ago
    FWIW, a good chunk of the snapshot scalability changes are in PG 14, so the situation has gone a bit further towards s/processes/threads/ being the primary bottleneck. It depends quite a bit on whether you're write-mostly (bottlenecks almost completely unrelated to processes vs threads) or read-mostly. And in the latter case whether it's a context switch heavy workload (processes vs threads may be a significant factor), or not (processes vs threads can be a significant factor, but less likely).