Bug #80030 Connector/NET slow connecting to newer MySQL versions with pooling disabled
Submitted: 18 Jan 2016 10:32 Modified: 31 Jul 2018 14:43
Reporter: Jan Reilink Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.9.8.0 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: Connector/Net, slow

[18 Jan 2016 10:32] Jan Reilink
Description:
We found Connector/NET version 6.9.8.0 to be very slow when connecting to newer MySQL versions, like MariaDB 10.1. This is the case if connection pooling is disabled.

Connector/NET 6.9.8.0 is fast when connecting to MySQL version 5.5, and Connector/NET 6.9.8.0 is fast when connecting to MariaDB 10.1 using connection pooling.
The connect slowness can be reproduced with Connector/NET GA versions 6.5.7.0, 6.6.7.0, 6.7.9.0, 6.8.7.0. The slowness does not exist in version 6.4.6.0.

We tested other/older minor version releases, to determine in what version the slowness began: 6.5.3.0, 6.5.4.0 and 6.5.5.0 are fast. Version 6.5.6.0 is slow.

Environment:
* mysql  Ver 15.1 Distrib 10.1.9-MariaDB, for Linux (x86_64) using readline 5.1 (example01-db, see test script below)
* mysql  Ver 15.1 Distrib 5.5.37-MariaDB, for Linux (x86_64) using readline 5.1 (example02-db, see test script below)
* Windows Server 2012 R2, IIS 8.5
* .NET Framework version, System.Environment.Version.ToString: 4.0.30319.34209

How to repeat:
Our test script:
 
<%@ Page Language="C#" ValidateRequest="false"  %>
<%@ Import namespace="MySql.Data.MySqlClient" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>SpeedTest</title>
    <script runat="server">
        
        private static DateTime startTime = DateTime.MinValue;
        private static DateTime lastTime = DateTime.MinValue;
        private static StringBuilder content = null;
        private static String lineTemplate =
            "<div class=\"row {0}\">" +
                "<span>{1}</span>" +
                "<span>{2}</span>" +
                "<span>{3}</span>" +
            "</div>";

        #region connectionStrings

        private String example01ConnectionString
        {
            get
            {
                # slow, MariaDB 10.1
                MySqlConnectionStringBuilder connString = new MySqlConnectionStringBuilder();
                connString.UserID = "example01";
                connString.Password = "p4ssw0rd";
                connString.Database = "example01-db";
                connString.Port = 3306;
                connString.Server = "example01-host";
                connString.CheckParameters = false;
                connString.Pooling = false;

                return connString.ToString();
            }
        }

        private String example02ConnectionString
        {
            get
            {
                # fast, MySQL 5.5
                MySqlConnectionStringBuilder connString = new MySqlConnectionStringBuilder();
                connString.UserID = "example02";
                connString.Password = "p4ssw0rd";
                connString.Database = "example02-db";
                connString.Port = 3306;
                connString.Server = "example02-host";
                connString.CheckParameters = false;
                connString.Pooling = false;

                return connString.ToString();
            }
        }
       
        #endregion

        protected String GetContents()
        {
            content = new StringBuilder();

            TestDB("example01", example01ConnectionString);
            content.AppendLine("<br/><br/><br/>");
            TestDB("example02", example02ConnectionString);

            return content.ToString();
        }

        private void TestDB(String name, String connectionString)
        {
            startTime = DateTime.Now;
            lastTime = DateTime.Now;

            content.AppendFormat(lineTemplate, "header", name, String.Empty, String.Empty);
            content.AppendFormat(lineTemplate, "header", "Actie", "Verschil", "Totaal");

            AddLine("Starting test");
            AddLine("Creating connection");

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                AddLine("Opening connection");
                conn.Open();
                AddLine("Connection opened");

                AddLine("Creating command");
                using (MySqlCommand command = conn.CreateCommand())
                {
                    AddLine("Command created");
                    AddLine("Executing scalar: SELECT 1 FROM `user`; ");

                    command.CommandText = "SELECT 1 FROM `user`; ";
                    command.ExecuteScalar();
                    AddLine("Command executed");

                    AddLine("Executing reader: SELECT `id` FROM `user`; ");
                    command.CommandText = "SELECT `id` FROM `user`; ";
                    command.ExecuteReader();
                    AddLine("Command executed");

                    AddLine("Disposing command");
                }
                AddLine("Command disposed");
                AddLine("Closing connection");

                conn.Close();
                AddLine("Connection closed");
                AddLine("Disposing connection");
            }
            AddLine("Connection disposed");

        }

        private void AddLine(String text)
        {
            DateTime current = DateTime.Now;
            TimeSpan gap = (current - lastTime);
            TimeSpan gapTotal = (current - startTime);

            String ms = Math.Round(gap.TotalMilliseconds, 0).ToString().PadLeft(3, '0');
            String s = Math.Round(gap.TotalSeconds, 0).ToString().PadLeft(2, '0');
            String timeString = s + "." + ms;

            String msTotal = Math.Round(gapTotal.TotalMilliseconds, 0).ToString().PadLeft(3, '0');
            String sTotal = Math.Round(gapTotal.TotalSeconds, 0).ToString().PadLeft(2, '0');
            String timeStringTotal = sTotal + "." + msTotal;

            content.AppendFormat(lineTemplate, "", text, timeString, timeStringTotal);
            lastTime = DateTime.Now;
        }
    </script>
</head>
<body>
    <%= GetContents() %>
</body>
</html>

Suggested fix:
-
[18 Jan 2016 11:09] Onno Molenkamp
The delay is caused by the new connection attribute feature, and specifically the code that determines the OS details. It uses an (inefficient) WMI-query for this, which can be very slow.
[18 Jan 2016 11:10] Onno Molenkamp
This change makes connecting to a server that supports connection attributes significantly faster

Attachment: mysql-connector-net-slowness.patch (text/x-patch), 636 bytes.

[21 Jan 2016 12:18] Chiranjeevi Battula
Hello Jan Reilink,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.8.

Thanks,
Chiranjeevi.
[21 Jan 2016 12:19] Chiranjeevi Battula
Test Results

Attachment: 80030.zip (application/zip, text), 4.82 KiB.

[4 Jul 2016 16:22] Pedro Borges
Do you have a timeline for merging this patch?
[7 Sep 2016 10:06] NOT_FOUND NOT_FOUND
these client properties should be queried only once at startup, because they are invariant during the process lifetime

Attachment: 0001-client-properties-performance.patch (application/octet-stream, text), 6.93 KiB.

[7 Sep 2016 10:30] Michiel Hazelhof
Nice patch, please incorporate the earlier patch as wel: "SELECT Caption FROM Win32_OperatingSystem"
[31 Jul 2018 14:43] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 6.10.8 and 8.0.13 releases, and here's the changelog entry:

Slow connections made to MySQL were improved by reducing the frequency and
scope of operating system details required by the server to establish and
maintain a connection.

Thank you for the bug report.