Database FAQ - Database Connection and Pooling Questions

Do I need to call Connection.close()? Won't the connection be closed automatically when the session is destroyed?

No, physical database connections will only free up if the java object is destroyed.

• If you are using a connection pool, the pool will retain a reference to the connection, meaning the garbage collector never sees it as an orphaned object, so never destroys it.
• If your VM has a large heap (eg: 512mb RAM), it might take a long time for the garbage collection to even run at all
• Even if the connection did close automatically, if you have a session timeout of 60 minutes, and have 100 people visit the site during an hour, those 100 connections would stay connected for the next hour.

A better solution is to use a connection pool (see DBCP), and close connections when you are finished with them. This also provides a large performance boost, because connections can be re-used.

How many connections should I allow in my connection pool?

In a local development environment, setting a connection pool to allow a maximum of 100 connections is ok, because you are the only user of the database. However, on a server, this can have disastrous effects.

Imagine if 50 applications were accessing the same database server, and each of them had 100 concurrent connections. That's 5000 connections in total. Using connections in this way wastes resources such as network sockets, file handles, and RAM.

Most sites should have a minimum of 2-3 connections and a maximum of 8-15. A site allowing 15 concurrent connections would be a very busy site, handling 15 concurrent page accesses (approx 200-400 concurrent users).

In a busy CMS, handling over 50 websites, it would make sense to have a higher number than 15 connections. In this case 15-25 is a more sensible limit.

Allowing too many connections also hides problems during development. If you are the only user accessing your development server, try setting your connection limit to between 2 and 5. If there is a connection leak (connections being opened, but not closed again), you should be able to find out quickly, before risking deployment to the production environment.

How can I set up a connection pool?

This wiki has many examples of using Database Connection Pools, using the Apache Commons DBCP mechanism which is built into Tomcat.

The following pages are a good place to start:

• Accessing a JNDI DataSource from Java
• Accessing a JNDI DataSource with JSTL from JSP

What are the benefits of connection pooling?

• Connection details (username/password/URL) are all managed from a single location. This prevents duplication all through your code, which increases portability and makes moving between the development, test and production environments easier.
• Connections are re-used, providing a significant performance increase to your application
• Resource usage is less, providing increased server performance