Question regarding usage and connection pooling

[jasen]

We are developing a fairly high volume MySQL application. The database tables are going to be relatively small (each less than 20k rows), but LOTS of reads/writes (up to 200/sec) so we are using MySQL's in memory tables.

Anyway, everytime we need a MySQL transaction, we make the following call:

oCmd.Connection = new MySqlConnection(m_sConnectionString);

My question is, should we be creating a single, global instance of the MySqlConnection object, and re-using it for every call, or is the above allocation "proper" for every single call (all 200/sec)?

Thanks,
jasen

[Serious]

MySQLDirect .NET uses connection pooling, so it is fast to open new connection each time.

At the lower level there is no difference between two scenarios you've proposed: MySQLDirect .NET uses the same internal connection to server when you use single MySqlConnection object or when you create it for each call. However in second scenario some additional work is required to create MySqlConnection object, to parse conection string etc.

[jasen]

In the scenario where we create a single connection instance, and re-use it over and over, will new multiple connections be made behind the scenes in a high-volume asp.net app?

The reason I ask is this: We some code into production on our web app, which currently receives about 10million hits per day. We used the methodology of creating a new connection each time, and even though everthing works, it doesn't work all the time. Our error log is FILLED with this exception (IP removed):

Description:
Can't connect to MySQL server on '192.168.x.x' (10061)
at CoreLab.MySql.b.a(String A_0, String A_1, String A_2, String A_3, Int32 A_4, String A_5, Int32 A_6, SshOptions A_7, SslOptions A_8)
at CoreLab.MySql.MySqlInternalConnection.Connect(String userId, String password, String host, String database, Int32 port, Int32 connectionTimeout, MySqlProtocol protocol, Boolean compress)
at CoreLab.MySql.MySqlInternalConnection..ctor(ad connectionOptions)
at CoreLab.MySql.a2.a(DbConnectionOptions A_0, Object A_1, DbConnectionBase A_2)
at CoreLab.Common.DbConnectionFactory.a(DbConnectionBase A_0, DbConnectionOptions A_1)
at CoreLab.Common.DbConnectionFactory.a(DbConnectionBase A_0)
at CoreLab.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at CoreLab.Common.DbConnectionBase.Open()
at CoreLab.MySql.MySqlConnection.Open()
at Velaro.DataLayer.MySqlDB.Connect(CommandType cmdType, String cmdText)
at Velaro.DataLayer.MySqlDB.PrepareSqlCommand(String sSql)
at Velaro.DataLayer.ANSIDatabase.VisitorUpdateTime(String sessionid)

and the weird thing is, when we first start the server, everything will work fine for a few minutes under stress, but then this message starts getting added to the mix (even though other transactions are going through).. then over time, more and more of the above messages, and less and less actual trnasactions make it through, until eventualy nothing.

What are all the specific things that could be causing that error message? I am sure there is no physical connectivity issue as the DB server is also running SQL Server all day, every day, getting a lot of other transactions.

-Jasen

[Serious]

Maybe you limited max connections on your MySQL Server?
See 'max_connections' value in my.ini config file.

[jasen]

My max connections is set to 200, and watching things haven't seen anything come close to that. Even so, shouldn't further connections be queued until one can be made available?

What are the other issues that can return that error code?

Also note, I changed the connection method to NOT use the string, and I set each parameter individually now and the issue seems to have disappeared for the time being. However, now I am randomly getting this error:

Description:
Object reference not set to an instance of an object.
at CoreLab.Common.DbConnectionPoolGroup.a(DbConnectionOptions A_0)
at CoreLab.Common.DbConnectionFactory.a(String A_0, DbConnectionOptions& A_1)
at CoreLab.Common.DbConnectionBase.a(DbConnectionOptions A_0)
at CoreLab.MySql.MySqlConnection.a(String A_0, Object A_1)
at CoreLab.MySql.MySqlConnection.set_Password(String value)
at Velaro.DataLayer.MySqlDB.Connect(CommandType cmdType, String cmdText)
at Velaro.DataLayer.MySqlDB.PrepareSqlCommand(String sSql)
at Velaro.DataLayer.ANSIDatabase.VisitorUpdatePages(String sessionid, String onpage, String referredby, NameValueCollection qs)

I 100% completely gaurantee that that password is filled. In fact, I seem to randomly get this error when setting some of the other connection object fields randomly. Again, I know for a fact these are set. These values are loaded up 1 time at the beginning of the application and reused on every connection call. We only have Connect in 1 single location, and it is being called upwards of 200 times a second, and 99.999% of the time it worked fine, however, this error and a few like it re: other Connection parameters each showed up a few times within a 24 hour span.

-jasen

[Serious]

Please specify MySQLDirect .NET version, .NET Framework version, MySQL Server version.

If you can reproduce this Xbehavior when some connection parameters are lost when you assign another connection properties please send us a piece of code which reproduces it.

What 'Max Pool Size' did you specify when you was setting connection properties via ConnectionString? Did you use default value? Did you close connections properly?

[jasen]

Hi,

Thanks for the quick response.

We are using the latest version of the MySQLDirect.Net product (just found your product a few days ago, downloaded the trial from your site, and purchased yestery).. so I assume its the latest, but VS reports 3.0.2.0

It will be very very difficult to reproduce this, as it is only happening under high volume, and very sporadic, and very (seemingly) random.

We are not setting MaxPoolSize at all, leaving the default. Should the connection pool be queuing new requests until something comes available? or is it expected behavior to return an exception instead?

Yes, we close every connection, every time. We are using primarily your Asynch calls.

[jasen]

Sorry. We are connecting to MySql 4.1

[Serious]

Check if you get the errors if you do not use asynchronous methods (I talk about first case when you got 'Can't connect ...' exception).
Please specify .NET Framework version.

[jasen]

We've been doing some more testing. We seem to have completely rid ourselves of the 10016 error completely. I'm not sure what did it, but the only thing we really changed was stopped using the actual connection string, and started setting each parameter in the connection object individually.

That said, we still get the following 2 erros a handful of times throughout the day:

Object reference not set to an instance of an object.
at CoreLab.Common.DbConnectionPoolGroup.a(DbConnectionOptions A_0)
at CoreLab.Common.DbConnectionFactory.a(String A_0, DbConnectionOptions& A_1)
at CoreLab.Common.DbConnectionBase.a(DbConnectionOptions A_0)
at CoreLab.MySql.MySqlConnection.a(String A_0, Object A_1)
at CoreLab.MySql.MySqlConnection.set_Host(String value)
at Velaro.DataLayer.MySqlDB.Connect(CommandType cmdType, String cmdText)
at Velaro.DataLayer.MySqlDB.PrepareSqlCommand(String sSql)
at Velaro.DataLayer.ANSIDatabase.VisitorUpdateTime(String sessionid)

Either with set_Host or set_Password. Here's the odd thing: First of all, again, I can guarantee those fields are being set properly from our side. We only connect in 1 place throughout the entire system and those variables are set when the application starts up. Further more, in the last 48 hours, the database has called and SUCCESSFULLY processed about 33 million calls from the VisitorUpdateTime methord above. However, there just seems to be these random occurences of this error popping up. The thing is, it doesn't seem to happen under any particular load or stress either. It happened a few times during high consumption, but we just caught it happen once during a relative slow time as well.

There's no reasonable way for me to switch to non-asynch mode, we're simply processing too many transactions.

[Serious]

We have made some changes to the DbConnectionPoolGroup class.
Hope this will help you.
Look forward for the next build.

[jasen]

Desperately need this fix... any ETA on the update?

thanks!

-Jasen

[Serious]

MySQLDirect .NET 3.5.3 was released 18.10.05.
Did you test it?

[jasen]

No, do you guys send out e-mails to existing customers? I don't recall getting anything. Where do customers go to retrieve their licensed copy of an upgrade?
-jasen

[Serious]

You can upgrade MySQLDirect .NET using link provided in registration e-mail.