MySqlConnector: Heavy usage of information schema periodically causing server performance issue

I’ve been reviewing the Event log for the servers in question around the time of the issue. I think the cache was cleared due to an App Pool recycle. That makes sense. I’ve reconfigured the recycling interval so hopefully the cache will get time to “warm up”. So looks like not an issue with the cache behavior itself, just the performance of these information_schema queries.

Our previous code (with Connector/Net) used mysql.proc to cache, and this did perform well. So I agree that switching over to that for <MySql 8.0 would be a good move. Like many others, there is little prospect of us upgrading to MySql 8.0 in foreseeable.

One other thing we used to do was load the whole cache on start rather than on demand. Even with 3000 sps, this was very quick against mysql.proc. This way you won’t get uncached behaviour during operational hours.

You don’t actually need to use SHOW CREATE PROCURE, as the parameters list is available in mysql.proc. Our previous code just used:

SELECT name, param_list as params FROM mysql.proc WHERE db='plus' AND type = 'PROCEDURE';

This code handled it:

private void SetupStoredProcs(HttpContextBase context)
{
	if (factory.Config.DataCache)
	{
		log.Debug("Set up stored procs from cache");
		object cachedData = context.Cache[storedProcedureCacheName]; // This as SQL above

		if (cachedData != null)
		{
			Sps = cachedData as Dictionary<string, SP>;
		}
	}

	if (Sps == null)
	{
		log.Debug("Set up stored procs from db");
		Sps = new Dictionary<string, SP>();
		DataSet ds = GetSystemData(storedProcedureStoredProcedureName, null);
		if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
		{
			foreach (DataRow row in ds.Tables[0].Rows)
			{
				Dictionary<string, Param> dictParams = new Dictionary<string, Param>();
				string spName = row[0].ToString();
				log.DebugFormat("Adding stored procedure: {0}", spName);
				
				DataColumn column = ds.Tables[0].Columns[1];
				string spParamsData;
				if (column.DataType == typeof(string))
					spParamsData = row[1].ToString();
				else
					spParamsData = Encoding.UTF8.GetString((byte[])(row[1]));

				// remove comment block (/* */) from string
				spParamsData = Regex.Replace(spParamsData, @"/\*(.*?)\*/", 
					me => { if (me.Value.StartsWith("/*") || me.Value.StartsWith("//")) return me.Value.StartsWith("//") ? Environment.NewLine : "";
						return me.Value;
					}, RegexOptions.Singleline);

				string[] spParams = spParamsData.Split(',');
				foreach (string param in spParams)
				{
					string spParam = param;

					// skip singleline comment (--) from param array
					if (spParam.Contains("--"))
						continue;

					spParam = Regex.Replace(spParam, @"\bINOUT\b", string.Empty);
					
					Regex regexParamParts = new Regex(@"\W*(\w+)\W+(\w+)\W*(\d*)\W*");
					Match match = regexParamParts.Match(spParam);
					if (match.Success)
					{
						string spParamName = match.Groups[1].Value;
						string spParamType = match.Groups[2].Value;
						int spParamLength = 0;
						if (match.Groups.Count > 3)
							Int32.TryParse(match.Groups[3].Value, out spParamLength);
						Param p = new Param();
						p.Name = spParamName;
						p.Type = spParamType.ToLowerInvariant();
						p.Length = spParamLength;
						dictParams[p.Name] = p;
					}
				}

				SP sp = new SP();
				sp.Name = spName;
				sp.Params = dictParams;
				Sps.Add(sp.Name, sp);
			}
		}
		if (factory.Config.DataCache)
			context.Cache.Insert(storedProcedureCacheName, Sps, null, DateTime.MaxValue, TimeSpan.Zero, CacheItemPriority.NotRemovable, null);
	}
}