Is DB connection pooling all that important?

Question

In the Java world it is pretty standard for app servers to pool "expensive" resources, like DB connections. On the other hand in dynamic languages, most stacks have little to do with pooled resources and especially DB connections.

E.g. for the popular PHP+MySQL combo, I've rarely seen it used with persistent connection, which can be considered poor-mans pooled connections.

If the concept of pooling DB connections is not that widely implemented, does this mean that the performance/scalability gains might not be all that important, in real-life deployments?

Answer 1

The main reason for connection pooling is the overhead in establishing the connection in the first instance. I have seen this take up to 0.5 seconds in the past.

In a high transactional environment, the ability to keep a connection open, and send multiple requests down the connection, one after the other have significant savings. Therefore, you may not see the gains in a low transactional database, but your application is not going to scale as well, if you ignore this useful pattern.

It also helps to managed the number of open connections in a much clearer way.

Answer 2

If the concept of pooling DB connections is not that widely implemented, does this mean that the performance/scalability gains might not be all that important, in real-life deployments?

It just means that there's no built-in connection pool for PHP. Doesn't mean you can't use one, though, for instance with Postgres you can use PGPool.

Answer 3

Connection polling is often used because some database vendors limit the number of connections that you have to a given database depending on your license. Open Source databases do not have such limits because they are free. So it is not much of a problem for MySQL.

Another reason to use connection polling is to limit the number of current connections to the database server, as each new connection consumes a lot of memory and you do not want to exhaust your servers memory.

The problem with persistent connections is that they never close until the client processes die. Client processes are in reality the Web server processes handling PHP requests. So, if you configure your Web server to limit the number of simultaneous requests, you also limit the number of opened persistent database connections. You can do that in Apache setting the MaxClients parameter to a reasonable value that does not exhaust your server RAM.

BTW, it would also be wise to move all your static content (CSS, JavaScript, images, etc..) to a separate multithreaded Web server (Nginx, lighttpd, etc..) so the simultaneous user accesses do not make your Apache fork to much processes.

Answer 4

My general opinion is that connection pools generally aren't used in environments where speed isn't considered as important as other concerns. AOLServer, for example, uses a dynamic language (Tcl) but is highly performant and uses connection pools.