How to monitor connection pooling for .NET MySQL D

2020-03-31 08:50发布

问题:

I have googled a fair bit on this, but not been able to find an exact answer. We are seeing the following errors in our logs:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Stack Trace: at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open()

I can monitor client connections on the MySQL server (and they appear fine), but the error is occurring in the ASP application. There is nothing obvious in my code, so I wanted to monitor the connection pool and see what was going on. I want to add MySQL performance counters into performance monitor in windows. But the only MySQL counters I see are HardProcedureQueries and SoftProcedureQueries. SQL Server and Oracle have a load related to Connection pooling and other items of interest. There have been some unanswered articles in different forums asking the same thing.

So, how do people out there monitor ASP .NET MySQl connection pool counters in IIS on Windows?

We're using .NET 4 on IIS 7.5 on Windows Server 2008

回答1:

I have done two things to help with this problem.

  • Upgraded the MySQL drivers.
  • Used code from How to query the current size of the MySQL's .Net connector's connection pool? to create a web page to monitor the state of the connection pool on my server.

The full code I use is:

string path = u.MapPath("~/bin/MySql.Data.dll");
Assembly ms = Assembly.LoadFrom(path);
Type type = ms.GetType("MySql.Data.MySqlClient.MySqlPoolManager");
MethodInfo mi = type.GetMethod("GetPool", BindingFlags.Static | BindingFlags.Public);

var pool = mi.Invoke(null, new object[] { new MySqlConnectionStringBuilder(ConnectionString) });
Type mip = ms.GetType("MySql.Data.MySqlClient.MySqlPool");
MemberInfo[] mei1 = mip.GetMember("inUsePool", BindingFlags.NonPublic);
totalAvailable = (int)pool.GetType().InvokeMember("available", BindingFlags.GetField | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, pool, new object[] { });
var o = pool.GetType().InvokeMember("inUsePool", BindingFlags.GetField | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance, null, pool, new object[] { });
var o1 = pool.GetType().InvokeMember("idlePool", BindingFlags.GetField | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance, null, pool, new object[] { });
inUseCount = (int)o.GetType().InvokeMember("Count", BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Public, null, o, null);
idleCount = (int)o1.GetType().InvokeMember("Count", BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Public, null, o1, null);