3 ms·

I am only going to suggest a small edit -> s/Postgres can’t/Heroku's Postgres can't/ PG can scale up pretty well on a single box, but scaling PG on AWS can be

by bifrost 14y ago

I am only going to suggest a small edit -> s/Postgres can’t/Heroku's Postgres can't/ PG can scale up pretty well on a single box, but scaling PG on AWS can be problematic due to the disk io issue, so I suspect they just don't do it. I'd love to be corrected :)

joevandyk 14y ago
It has to deal with the number of open connections to postgresql, not disk IO. You don't want to have 4,000 connections open at once (unless you were using a connection balancer, but that would only work in transaction mode).
gleb 14y ago
Postgres has a limitation on number of open connections. This is because 1 connection = 1 process. MySQL uses threads, which scales better but has other downsides. The thread-based approach is also possible with Postgres using 3rd party connection pooling apps, e.g. pgbouncer.
- gleb 14y ago
  BTW, here's a counter-intuitive solution if using pgbouncer is not possible. Simply drop and reestablish connections on every request. In theory this is horrible, since PG connections are so expensive. In practice the cost of establishing a connection is negligible for a Rails app. I do suspect this will make performance "fall-off-the cliff" as you get close to capacity.
  - pvh 14y ago
    Eh, I wouldn't recommend going this route. I'd go down the pgbouncer road instead.
pvh 14y ago
Data Dep't here. Postgres scales great on Heroku, AWS, and in general. We've got users doing many thousands of query per second, and terabytes of data. Not a problem. The issue with the number of connections is that each connection creates a process on the server. We cap the connections at 500, because at that point you start to see problems with O(n^2) data structures in the Postgres internals that start to make all kinds of mischief. This has been improved over the last few releases, but in general, it's still a good idea to try and keep the number of concurrent connections down. *EDIT: thanks. not a thread. :)
- joevandyk 14y ago
  Each connection forks a new process on the server, not a thread.
- gleb 14y ago
  Shouldn't this be s/thread/process/ ?

by bifrost 14y ago

joevandyk 14y ago

It has to deal with the number of open connections to postgresql, not disk IO. You don't want to have 4,000 connections open at once (unless you were using a connection balancer, but that would only work in transaction mode).

gleb 14y ago

Postgres has a limitation on number of open connections. This is because 1 connection = 1 process. MySQL uses threads, which scales better but has other downsides. The thread-based approach is also possible with Postgres using 3rd party connection pooling apps, e.g. pgbouncer.

gleb 14y ago
BTW, here's a counter-intuitive solution if using pgbouncer is not possible. Simply drop and reestablish connections on every request. In theory this is horrible, since PG connections are so expensive. In practice the cost of establishing a connection is negligible for a Rails app. I do suspect this will make performance "fall-off-the cliff" as you get close to capacity.
- pvh 14y ago
  Eh, I wouldn't recommend going this route. I'd go down the pgbouncer road instead.

pvh 14y ago

Data Dep't here. Postgres scales great on Heroku, AWS, and in general. We've got users doing many thousands of query per second, and terabytes of data. Not a problem. The issue with the number of connections is that each connection creates a process on the server. We cap the connections at 500, because at that point you start to see problems with O(n^2) data structures in the Postgres internals that start to make all kinds of mischief. This has been improved over the last few releases, but in general, it's still a good idea to try and keep the number of concurrent connections down. *EDIT: thanks. not a thread. :)

joevandyk 14y ago
Each connection forks a new process on the server, not a thread.
gleb 14y ago
Shouldn't this be s/thread/process/ ?