1

Not being a database administrator (even less of a MS database admin :), I have received complaints that a piece of code I've written leaves "sleeping connections" behind in the database.

My code is Java, and uses Apache Commons DBCP for connection pooling. I also use Spring's JdbcTemplate to manage the connection's state, so not closing the connections is out of the question (since the library is doing that for me).

My main question is, from a DBA's point of view, can these connections cause outages or poor performance?

This question is related, currently the settings were left as they were there (infinite active/idle connections in the pool).

Community
  • 1
  • 1
Alex Ciminian
  • 11,398
  • 15
  • 60
  • 94

2 Answers2

1

Apache DBCP has maxIdle connections settings to 8 and maxActive settings to 8. This means that 8 number of active connections and 8 numbers of idle connections can exist in the pool. DBCP reuses the connections when the call for connection is made. You can set this according to your requirement. You can refer to the document below:

DBCP Configuration - Apache

James Jithin
  • 10,183
  • 5
  • 36
  • 51
  • I learned that when asking the previous question :), what I'm interested in is if these "sleeping" connections, as they say, can impact the performance of SQL Server. – Alex Ciminian May 06 '11 at 09:51
  • @Alex - Usually in a live system, the number of connections are allocated according to the usage requirement. For an application with 250 users realtime, we provided 30 max connections. If there are more applications which access the same database, it is better to allocate according to the number of connections each of them would require. And it is obvious that the number of concurrent active connections will affect the database server, also depends on the resources (RAM, CPU) of the system. – James Jithin May 06 '11 at 10:04
1

Really, to answer your question, an idea of the number of these "sleeping" connections would be good. It also matters whether this server's primary purpose is serving your application, or whether your application is one of many. Also relevant is whether there are multiple instances of your app (eg on multiple web servers), or whether it's just the one.

In my experience, there is little to no overhead associated with idle connections on modern hardware, as long as you don't reach into the hundreds. That said, looking at your previous question, allowing the pool to spawn an unbounded number of connections does not sound wise - I'd recommend setting a cap, even if you set it at a hundreds.

I can tell you from at least one painful situation with leaking connection pools, that having a thousand open connections to a single SQL server is expensive, even if they're idle. I seem to recall the server started losing it (failing to accept new connections, simple queries timing out, etc) when nearing the 2,000-connection range (this was SQL 2000 on mid-range hardware a few years ago).

Hope this helps!

Tao
  • 13,457
  • 7
  • 65
  • 76