Connection pooling not pooling

jammerjam · Post by **jammerjam** » Mon 07 Apr 2008 15:13

Using this connection string in machine.config:

Host=MYSERVER;Port=3306;User ID=MYUSER;Password=MYPASSWORD;Pooling=true

In each app, I load this connection string, and replace the user, password, and database values accordingly.

I'm loading an ASP.NET page that pulls info from a database, then 5 seconds later reloads the page (which again pulls info from a database).

In my global.asax file when the page is unloaded the connection is closed. I've debugged this portion and can confirm the object = nothing before the browser displays the page.

I started DBMonitor because I'm noticing a new connection every time the page is refreshed. In the left pane I see a new MySqlInternalConnection and MySqlConnection every time it refreshes.

Here's the log:
http://img230.imageshack.us/my.php?imag ... 031mu6.gif

Question: why did it create new connections?

Alexey.mdr · Post by **Alexey.mdr** » Tue 08 Apr 2008 08:42

There were no bugs found.
DBMonitor shows all connections, that are established to the database.
If a connection is taken from a connection pool DBMonitor calls it as MySqlConnection,
if an application creates a new physical connection DBMonitor calls it MySqlInternalConnection.
For the first connection MySqlInternalConnection is definitely created.

jammerjam · Post by **jammerjam** » Tue 08 Apr 2008 12:49

Hmm...I'm not sure I follow. From what you describe, when the page is accessed, it's creating both a new connection from the connection pool, and a new physical connection.

How is that not a bug?

Alexey.mdr · Post by **Alexey.mdr** » Tue 08 Apr 2008 14:24

No, it's not a bug.
MySqlInternalConnection is a physical representation of a connection between an application and a MySQL database.
If pooling = true, the connection is stored in the pool after it has been closed. MySqlConnection is a component that uses MySqlInternalConnection.
Only internal connection interferes with the database, MySqlConnection is a component that uses the internal connection.

jammerjam · Post by **jammerjam** » Tue 08 Apr 2008 14:35

Makes sense. However, when I execute

Code: Select all

show full processlist

against the mysql database why do I get 3 connections returned?

Alexey.mdr · Post by **Alexey.mdr** » Wed 09 Apr 2008 11:01

Well, the actual processes that are working behind the stage are quite complicated.
It depends on several circumstances like timespan between closing and opening a connection,
Max Pool Size property, Min Pool Size property, Connection LifeTime property etc.
Try playing around with open/closing connections.
I'm pretty sure you will get different results.

jammerjam · Post by **jammerjam** » Wed 16 Apr 2008 12:25

I deployed an app that refreshes every 5 seconds, in which a connection is created then destroyed. This app is looked at by 5 seperate machines. At any point in time, I'll have 24 open connections to mySQL from this app, with the sleep time < 100.

Why so many? Other drivers (ODBC) only create 1 connection coming from the website server, while this has 24!?!

Alexey.mdr · Post by **Alexey.mdr** » Thu 17 Apr 2008 09:14

How did you count the currently opened connections?
What MySQL server do you use?
Could you post the pseudo connection string?
Please send me (alexeyman*crlab*com) your project to reproduce the problem.
We will investigate the issue.