Bug #77311 Connection pooling when using ReplicationManager is not working
Submitted: 11 Jun 2015 9:29 Modified: 6 Dec 2019 22:00
Reporter: Kenny Eliasson Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.9.6 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[11 Jun 2015 9:29] Kenny Eliasson
Description:
Hi

While working on a web-app I found it to be strange that each request was very slow and after some digging I found the culprit to be MySql.Data.MySqlClient.MySqlConnection.Open().

I created a simple console-app to verify my suspiscion and could easily recreate the problem when using the ReplicationManager. Each call to .Open() takes at least 600ms.

If I changed the connection string to a regular one and skip using the ReplicationManager connection pooling is working as expected. I.e. the first call takes at least 600ms and subsequent calls takes less than 5ms.

How to repeat:
In app.config use the replication settings

  <configSections>
    <section name="MySQL" type="MySql.Data.MySqlClient.MySqlConfiguration, MySql.Data, Version=6.9.6.0"/>
  </configSections>
    

  <MySQL>
    <Replication>
      <ServerGroups>
        <Group name="ReplicationTest">
          <Servers>
            <Server name="Master0" IsMaster="true" connectionstring="server=test;user id=user;password=pwd;database=oa"/>
          </Servers>
        </Group>

      </ServerGroups>
    </Replication>
  </MySQL>

In a regular console app I use the following code to check if regular connection string and replicated group works.

public static void Main()
{
	var connectionString = "server=server01;user id=user;password=pwd;port=3306;database=oa";
	var replicatedConnString = "server=ReplicationTest";

	while (Console.ReadLine() != "q")
	{
		TestConnection(connectionString);
		Console.WriteLine("===================");
		TestConnection(replicatedConnString );
	}
}

private static void TestConnection(string connectionString)
{
	var sw = new Stopwatch();
	sw.Start();

	using (var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
	{
		connection.Open();
	}
	sw.Stop();

	Console.WriteLine("Connection#1 {0} ms", sw.ElapsedMilliseconds);

	sw.Restart();
	using (var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
	{
		connection.Open();
	}
	sw.Stop();

	Console.WriteLine("Connection#2 {0} ms", sw.ElapsedMilliseconds);
	Console.ReadLine();
}

Suggested fix:
In ReplicationManager I changed

Driver driver = Driver.Create(new MySqlConnectionStringBuilder(server.ConnectionString));
connection.driver = driver;

to 

var pool = MySqlPoolManager.GetPool(new MySqlConnectionStringBuilder(server.ConnectionString));
connection.driver = pool.GetConnection();

And that worked. Theres probably some flaw in the naive code but the pooling does seem to work then.
[18 Jun 2015 12:36] Kenny Eliasson
Small comment.

In production, we removed the replication manager config and used a single connection to one of the master databases instead. We went from 2-4 transactions/sec to 75-80 transactions/sec with that change.

When can we expect a fix for this serious issue?
[10 May 2016 6:51] Chiranjeevi Battula
Hello  Kenny Eliasson,

Thank you for the bug report.
Verified based on internal discussion with dev's and take it as a feature request.

Thanks,
Chiranjeevi.
[25 May 2016 12:04] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=70467 marked as duplicate of this one.
[25 May 2016 12:28] Moshe Lampert
Just look in:

http://bugs.mysql.com/bug.php?id=70467

On the dup. bug I was open 3 years ago, I was found a  problem when you just enable connection pooling as you wrote. The opened connection is stay connected forever...

When you will fix this problem, please look in and validate that the old and original bug is still not here.

Thanks,
[21 Oct 2016 20:44] Robert Ginsburg
After some testing, this issue exists in 6.9.9 and 7.X as well.
[6 Dec 2019 18:21] Daniel Valdez
Posted by developer:
 
Ran the same tests provided by the user using C/NET 8.0.19 and the issue is no longer happening. All the connections take much less than 600ms.
[6 Dec 2019 22:00] Daniel Valdez
Posted by developer:
 
Ran the same tests provided by the user and the issue is no longer happening, all the connections take much less than 600ms. Verified using C/NET 8.0.19