6

We had a question over on Server Fault that raised an interesting question:
In light of the scalability improvements in Postgres 9.2 scalability improvements, is it better to use connection pooling mechanisms to avoid the overhead of making extra connections to the database, or is the connection overhead worth the improved read performance?

Relating it specifically to my environment: We have a web application that is database backed and read-centric, and we're currently running on Postgres 8.4.

Our re-implementation will be launching next year, along with an upgrade to 9.2, and gives each Apache worker process its own connection to the database (and thus its own Postgres backend which is preserved for the life of the Apache worker).
Based on what we've seen this seems to be a good balance between the overhead of connecting to the database and having more workers handling the read load, though we haven't yet done any substantial benchmarking of our own to confirm this.

Does that implementation seem like a reasonable one, and are there other options / connection pooling mechanisms which we should be considering in light of the recent scalability improvements?

Improve this question

1 Answer 1

Reset to default
8

I think you see a false dichotomy that does not exist.

It can be useful to have connection pooling in place even if you expect a 1:1 mapping of clients to back-ends. If your connections are long-lived, you won't benefit from reducing backend setup/teardown overhead, as it's small and amortized across a long period. A pool like PgBouncer may remain useful for other reasons:

  • Block until a connection is available rather than return an immediate max_connections exceeded error;
  • You can switch the pool target server if you fail-over to a standby without having to reconfigure applications;
  • You can limit application database workers to lower than max_connections, so you can still make reporting and maintenance connections as a non-superuser.

Additionally, if suitable for your application you can use transaction-level pooling to greatly increase the number of clients that can be served by your server.

I would not try to keep a strict 1:1 mapping from Apache workers to PostgreSQL workers, personally. If you've got (say) 16 cores and good I/O on your PostgreSQL box you might want something like 16-20 active PostgreSQL workers for optimal performance. You're almost certain to want more Apache workers than that, since they'll be kept busy by things like:

  • persistent HTTP connections from idle clients;
  • Unresponsive or very slow clients;
  • Intentional DoS connections;
  • Network interruptions between client and server; etc

If possible, consider a transaction-pooling design with short-lived transactions instead.

Improve this answer
2
  • That's a good point - at the moment our front-end loads are relatively light so even under load tests we're not spawning an insane number of Apache processes, but that may change in the future. (we've got "other problems" with poolers like pgpool breaking prepared statements (still?), but adjusting our app to do its own pooling is certainly possible and probably a good idea)
    – voretaq7
    Commented Dec 19, 2012 at 15:04
  • 1
    Not sure about pgpool, but pgbouncer doesn't brake prepared statements nor prepared transactions when you have the configuration right. Check the manual and ask questions if something doesn't work as expected.
    – Frank Heikens
    Commented Dec 20, 2012 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.