mysql连接池泄露,如何找到泄漏数据库连接池处理？

I'm seeing the dreaded "The timeout period elapsed prior to obtaining a connection from the pool" error.

I've searched the code for any unclosed db connections, but couldn't find any.

What I want to do is this: the next time we get this error, have the system dump a list of which procs or http requests are holding all the handles, so I can figure out which code is causing the problem.

Even better would be to see how long those handles had been held, so I could spot used-but-unclosed connections.

Is there any way to do this?

解决方案

There are some good links for monitoring connection pools. Do a google search for ".net connection pool monitoring".

One article I referred to a while back was Bill Vaughn's article (Note this is old but still contains useful info). It has some info on monitoring connection pools, but some great insights as to where leaks could be occuring as well.

For monitoring, he suggests;

"Monitoring the connection pool

Okay, so you opened a connection and closed it and want to know if the

connection is still in place—languishing in the connection pool on an

air mattress. Well, there are several ways to determine how many

connections are still in place (still connected) and even what they

are doing. I discuss several of these here and in my book:

· Use the SQL Profiler with the SQLProfiler TSQL_Replay

template for the trace. For those of you familiar with the Profiler,

this is easier than polling using SP_WHO.

· Run SP_WHO or SP_WHO2, which return information from the

sysprocesses table on all working processes showing the current status

of each process. Generally, there’s one SPID server process per

connection. If you named your connection, using the Application Name

argument in the connection string, it’ll be easy to find.

· Use the Performance Monitor (PerfMon) to monitor the pools

and connections. I discuss this in detail next.

· Monitor performance counters in code. This option permits

you to display or simply monitor the health of your connection pool

and the number of established connections. I discuss this in a

subsequent section in this paper."

Edit:

As always, check out some of the other similar posts here on SO

Second Edit:

Once you've confirmed that connections aren't being reclaimed by the pool, another thing you could try is to utilise the StateChange event to confirm when connections are being opened and closed. If you are finding that there are a lot more state changes to opened than to closed, then that would indicate that there are leaks somewhere. You could also then log the data in the statechanged event along with a timestamp, and if you have any other logging on your application, you could start to parse the log files for instances where there appears to be state changes of closed to open, with no corresponding open to closed. See this link for more info on how to handle the StateChangedEvent.