MySQL连接池数 [英] MySQL Connection Pool Count

问题描述

我已经开始收到我的VB.NET应用程序错误:

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.

我确实有一些方法不能正确处理连接.这些已使用Try和Finally修复.

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

但是，今天早上，我在尝试打开简单报告时再次收到错误消息.我打开了MySQL Workbench来监视客户端连接.当时我连接了4个线程.

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.

MySQL DB会杀死睡眠超过15秒的连接.

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.

应用程序是否读取最大池大小设置并保存自己的连接计数并在达到该数目时抛出错误，还是每次打开新连接时直接从MySQL DB获取该数目?

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?

编辑1

MySQL Workbench的一些统计信息
连接的线程数:3
正在运行的线程:1
创建线程:250
缓存的线程数:5
拒绝(超出限制):0
总连接数:2822
连接数限制:151
中止的客户:2694
中止的连接:84
错误:0

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

编辑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

推荐答案

您的代码中有几件事.

• 首先打开Option Strict.该函数声明为返回字符串，但是您尝试使用Return result
返回Object 实现Dispose方法的
• 所有应该在Using块内使用.这样，您就可以声明和初始化一个对象，使用它并在最后处理它.
• Parameters.Add优于AddWithValue.稍后迫使数据库提供者根据数据猜测数据类型.
• 根据负载以及是否经常使用该方法，您可以将数据加载到DataTable并在其中进行查找，而不是一遍又一遍地查询数据库.

• 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.

(可能)核心问题是您没有处置DBCommand对象.查看您使用的构造函数:

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)

DBCommand对象被传递对连接的引用.即使您明确处置了该连接，cmdx仍然具有对它的引用，并且未处置它. Using块使确定事物被处置的过程变得简单:

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

要减少缩进，可以将项目堆叠"到一个Using块中:

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).

这篇关于MySQL连接池数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！