5.4 Using Connector/NET with Connection Pooling

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.