MySQL Bugs: #77311: Connection pooling when using ReplicationManager is not working

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:	Windows
Assigned to:		CPU Architecture:	Any

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.

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?

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.

http://bugs.mysql.com/bug.php?id=70467 marked as duplicate of this one.

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,

After some testing, this issue exists in 6.9.9 and 7.X as well.

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.

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