MySQL Bugs: #37461: Connection pooling

Bug #37461	Connection pooling
Submitted:	17 Jun 2008 20:57	Modified:	17 Apr 2012 21:32
Reporter:	Poul Bak	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET Documentation	Severity:	S4 (Feature request)
Version:	5.2.2	OS:	Any
Assigned to:	Tony Bedford	CPU Architecture:	Any
Tags:	pooling

Description:
I have had really big problems using connection pooling with the connector. These are now solved, but I think the help file mentions too little about connection pooling. For that reason I have written a text, attached with this report, that is intended to be included in the help file as a separate topic (I am not a help file writer, so feel free to change it).

To illustrate, why that would be a good idea, I will try to describe my experience.
I had first used the ODBC connector without big problems (also in asp for several years). Since the Membership- and RoleProvider used the native connector, I decided to give it a try. Out of old habit(or may be because "opening a connection is a time consuming task"), I created a global connection object and opened it at start of the request, using this one through the request and finally closing it at the end (I also tried to only open it when needed). This gave me problems, because the membershipproviders created and disposed it's own connections. This mix of manually and automatically handled connections gave me weird errors, like 'configuration error' and characters not recognized as utf-8. Turning off connection pooling stopped the errors, but performance was bad.
I finally found the solution - letting the connector handle all connections totally. This works!!!. Actually, when I turned on pooling again, my first reaction was: "Something is very bad here, it must have skipped all database access somehow". But in reality, I just saw, "what this baby can do" :-) Request processing time went down by a factor 5 to 10 !
All this is of course old news to experienced users of this connector, but the help file is for new users (who might be migrating), and I thought, when I can make this mistake, others can too. That's the reason, why I want a note about this in the help. In the text, I do not say, that this is the only way to do things, I merely explains to new users, how they can make it work. And a simple 'rule' is easy to understand and use.

How to repeat:
Don't repeat my mistakes :-)

Suggested fix:
Include the attached help text.

Help text

Attachment: Connection pooling.txt (text/plain), 1.43 KiB.

Now the short version:
Make it clear that pooling is 'All or nothing' and describe the 'all' approach.

Assigning to Tony

I've reworded the text as follows and added to the manual. I would really like to add some example code to this. Does anyone have a simple example that could be included?

1.5.2. Using Connector/NET with Connection Pooling

The Connector/NET supports connection pooling. This is enabled by default, but can be turned off via connection string options. See Section 1.3.3.3, “ConnectionString” 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.

To work as designed, it is best to let the connection pooling system manage all connections. You should 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.

In my opinion, the updated wording is sufficient without an extensive code example. Closing the bug now.