mysql 连接池 频率,MySQL连接池数

I have started to receive an error with my VB.NET application:

Timeout Expired. The timeout elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I did have a few methods that were not correctly disposing of the connection. These have been fixed using Try and Finally.

However, this morning I received the error again whilst trying to open a simple report. I had MySQL Workbench open monitoring client connections. At the time I had 4 threads connected.

The MySQL DB kills connections that have been asleep for more than 15 seconds.

I am at a loss as to how I could have reached the max pool size, if indeed that is what the error is referring to.

Does the application read the max pool size setting and hold its own count of connections and throw the error when that number is reached, or does it get the number from the MySQL DB directly every time a new connection is opened?

Or could the error be due to something else?

EDIT 1

Some stats from MySQL Workbench

Threads Connected:3

Threads Running: 1

Threads Created: 250

Threads Cached: 5

Rejected (over limit): 0

Total Connections: 2822

Connection limit: 151

Aborted Clients: 2694

Aborted Connections: 84

Errors: 0

EDIT 2

Sample code calling and disposing of connection:

Public Shared Function GetCoursePaperCertificate(ByVal CourseTypeID As Integer) As String

Dim connx As New MySqlConnection(My.Settings.BMConnString)

Try

Dim cmdTextx = "Select `Cert` From `Courses` WHERE `ID`=@ID"

Dim cmdx As New MySqlCommand(cmdTextx, connx)

cmdx.Parameters.AddWithValue("@ID", CourseTypeID)

connx.Open()

Dim result = cmdx.ExecuteScalar

If result Is Nothing OrElse result Is DBNull.Value Then

Return String.Empty

Else

Return result

End If

Catch ex As Exception

Return String.Empty

Finally

connx.Dispose()

connx = Nothing

End Try

End Function

解决方案

There are several things in your code.

First turn on Option Strict. The function is declared to return a string, but you are trying to return Object with Return result

Everything which implements a Dispose method ought to be used inside a Using block. This allows you to declare and initialize an object, use it and dispose of it at the end.

Parameters.Add is better than AddWithValue. The later forces the DB Provider to guess the datatype based on the data.

Depending on the load and whether that method is used a lot, you could load the data to a DataTable and do lookups on that rather than query the DB over and over.

The core issue is (probably) that you do not dispose of the DBCommand object. Look at the constructor you use:

Dim cmdx As New MySqlCommand(cmdTextx, connx)

The DBCommand object is passed a reference to the connection. Even though you explicitly dispose of the connection, cmdx still has a reference to it, and it was not disposed. Using blocks make it simple to be sure things are disposed:

Dim sql = "Select `Cert` From `Courses` WHERE `ID`=@ID"

Using dbCon As New MySqlConnection(MySQLConnStr)

Using cmd As New MySqlCommand(sql, dbCon)

cmd.Parameters.Add("@Id", MySqlDbType.Int32).Value = CourseTypeID

dbCon.Open()

Dim result = cmd.ExecuteScalar

If result Is Nothing OrElse result Is DBNull.Value Then

Return String.Empty

Else

Return result.ToString()

End If

End Using ' close, dispose of conn

End Using ' dispose of DBCommand

To reduce indentation, you can "stack" items into one Using block:

Using connx As New MySqlConnection(MySQLConnStr),

cmd As New MySqlCommand(sql, connx)

...

End Using

Note the comma at the end of the first line.

I'd be surprised if this was not the cause of your leak (of course all the code would need to be changed).