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: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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.076 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.