4 ms·

I guess this is sarcasm, but it's not clear what you're trying to actually say. How is a connection-pooling proxy supposed to make an application more resilient

I guess this is sarcasm, but it's not clear what you're trying to actually say. How is a connection-pooling proxy supposed to make an application more resilient to network partitions? If anything, it's worse, since you have twice as many points of failure -- if either the application can't talk to the proxy, or the proxy can't talk to the database, you're hosed.

yummyfajitas 13y ago
Practically speaking, you typically run pgbouncer on the same box as postgres. Even if you ran pgbouncer on a different box, the situation is still better. Suppose you have a probability P of having a partition between any two boxes. Then the probability of a partition taking down the system with pgbouncer is P. If you instead had N clients coordinating among themselves how many connections to use, your odds go way up. The probability of a partition existing between two clients is 1-(1-P)^(N(N-1)/2) ~= N(N-1)P/2 (for very small P). Once two clients can't communicate, neither one can connect to the DB (because they don't know if the other two have pushed the db over the limit). All clients will need to stop connecting to the DB if any client is ever disconnected from the other N-1 of them. In a distributed system, ensuring that SUM(x[i]) remains bounded for all time is a tricky problem to solve.
- wmf 13y ago
  Or you could give each client C/N connections.
  - yummyfajitas 13y ago
    This can easily result in too few connections at a local peak. If each box is allocated 3 connections (and postgres can handle 12), you get high latency in the event that your load looks like [6, 1, 1, 1]. This happens occasionally due to random chance.
- marcosdumay 13y ago
  I can't tell if everybody does the same, but I simply enable enough connections for the total of all clients, and am done with that. Clients talking between themselves to establish if they can create another connection or not is madness. They'll keep their connections in the maximum all the time anyway.
- saurik 13y ago
  > Practically speaking, you typically run pgbouncer on the same box as postgres. I've done this, long-term, in production. This is not how PGbouncer is designed to be used, and it causes serious problems under load. (I am still doing this, but I have it on my todo list to remove this glitch from my architecture, as it caused my entire site infrastructure to fail once during a launch.) Put simply: PGbouncer is single-threaded; it is not difficult to end up in situations where the one core PGbouncer is running on to be at 100% while the remaining cores (in my case, seven) running PostgreSQL are having no issue with the traffic.
  - yummyfajitas 13y ago
    Just curious, how does that happen? I've never found myself in a situation where PGBouncer can't handle the load but Postgres can. I'm guessing it's a situation where you are running a large volume of very easy queries, for example primary key lookups with no joins?
    - _hans_ 13y ago
      We do have this problem! It is only a matter of how small the PGBouncer machine is compared to the PostgreSQL. In our case the PGBouncer had to deal with 16000 req/s, coming from 700 clients going through 100 connections to the PostgreSQL. We now have 4 PGBouncers for this DB, and we're 'load balancing' them with DNS.

yummyfajitas 13y ago

Practically speaking, you typically run pgbouncer on the same box as postgres. Even if you ran pgbouncer on a different box, the situation is still better. Suppose you have a probability P of having a partition between any two boxes. Then the probability of a partition taking down the system with pgbouncer is P. If you instead had N clients coordinating among themselves how many connections to use, your odds go way up. The probability of a partition existing between two clients is 1-(1-P)^(N(N-1)/2) ~= N(N-1)P/2 (for very small P). Once two clients can't communicate, neither one can connect to the DB (because they don't know if the other two have pushed the db over the limit). All clients will need to stop connecting to the DB if any client is ever disconnected from the other N-1 of them. In a distributed system, ensuring that SUM(x[i]) remains bounded for all time is a tricky problem to solve.

wmf 13y ago
Or you could give each client C/N connections.
- yummyfajitas 13y ago
  This can easily result in too few connections at a local peak. If each box is allocated 3 connections (and postgres can handle 12), you get high latency in the event that your load looks like [6, 1, 1, 1]. This happens occasionally due to random chance.
marcosdumay 13y ago
I can't tell if everybody does the same, but I simply enable enough connections for the total of all clients, and am done with that. Clients talking between themselves to establish if they can create another connection or not is madness. They'll keep their connections in the maximum all the time anyway.
saurik 13y ago
> Practically speaking, you typically run pgbouncer on the same box as postgres. I've done this, long-term, in production. This is not how PGbouncer is designed to be used, and it causes serious problems under load. (I am still doing this, but I have it on my todo list to remove this glitch from my architecture, as it caused my entire site infrastructure to fail once during a launch.) Put simply: PGbouncer is single-threaded; it is not difficult to end up in situations where the one core PGbouncer is running on to be at 100% while the remaining cores (in my case, seven) running PostgreSQL are having no issue with the traffic.
- yummyfajitas 13y ago
  Just curious, how does that happen? I've never found myself in a situation where PGBouncer can't handle the load but Postgres can. I'm guessing it's a situation where you are running a large volume of very easy queries, for example primary key lookups with no joins?
  - _hans_ 13y ago
    We do have this problem! It is only a matter of how small the PGBouncer machine is compared to the PostgreSQL. In our case the PGBouncer had to deal with 16000 req/s, coming from 700 clients going through 100 connections to the PostgreSQL. We now have 4 PGBouncers for this DB, and we're 'load balancing' them with DNS.