4.4.2 Managing a Connection Pool in Connector/NET

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 4.4.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 is 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 creating a MySqlConnection object manually. Instead, use the overloaded methods that take a connection string as an argument. With this approach, Connector/NET automatically creates, opens, closes and destructs 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 creating MySqlCommand objects manually, you can use the static methods of the MySqlHelper class. These methods take a connection string as an argument and they fully support connection pooling.

Resource Usage

Connector/NET runs a background job 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.

Multiple endpoints.  Starting with Connector/NET 8.0.19, a connection string can include multiple endpoints (server:port) with connection pooling enabled. At runtime, Connector/NET selects one of the addresses from the pool randomly (or by priority when provided) and attempts to connect to it. If the connection attempt is unsuccessful, Connector/NET selects another address until the set of addresses is exhausted. Unsuccessful endpoints are retried every two minutes. Successful connections are managed by the connection pooling mechanism.