The MySQL Connector/NET supports connection pooling for better performance and
scalability with database-intensive applications. This is enabled
by default. You can turn it off or adjust its performance
characteristics using the connection string options
Pooling
, Connection Reset
,
Connection Lifetime
, Cache Server
Properties
, Max Pool Size
and
Min Pool Size
. See
Section 5.1.1, “Creating a Connector/NET Connection String”
for further information.
Connection pooling works by keeping the native connection to the
server live when the client disposes of a
MySqlConnection
. Subsequently, if a new
MySqlConnection
object is opened, it will be
created from the connection pool, rather than creating a new
native connection. This improves performance.
Guidelines
To work as designed, it is best to let the connection pooling
system manage all connections. Do not create a globally accessible
instance of MySqlConnection
and then manually
open and close it. This interferes with the way the pooling works
and can lead to unpredictable results or even exceptions.
One approach that simplifies things is to avoid manually creating
a MySqlConnection
object. Instead use the
overloaded methods that take a connection string as an argument.
Using this approach, Connector/NET will automatically create, open, close
and destroy connections, using the connection pooling system for
best performance.
Typed Datasets and the MembershipProvider
and
RoleProvider
classes use this approach. Most
classes that have methods that take a
MySqlConnection
as an argument, also have
methods that take a connection string as an argument. This
includes MySqlDataAdapter
.
Instead of manually creating MySqlCommand
objects, you can use the static methods of the
MySqlHelper
class. These take a connection
string as an argument, and they fully support connection pooling.
Resource Usage
Starting with Connector/NET 6.2, there is a background job that runs every three minutes and removes connections from pool that have been idle (unused) for more than three minutes. The pool cleanup frees resources on both client and server side. This is because on the client side every connection uses a socket, and on the server side every connection uses a socket and a thread.
Prior to this change, connections were never removed from the
pool, and the pool always contained the peak number of open
connections. For example, a web application that peaked at 1000
concurrent database connections would consume 1000 threads and
1000 open sockets at the server, without ever freeing up those
resources from the connection pool. Connections, no matter how
old, will not be closed if the number of connections in the pool
is less than or equal to the value set by the Min Pool
Size
connection string parameter.