MySQL Bugs: #102964: MySQL .NET Connector got considerably slower since 8.0.19

Bug #102964	MySQL .NET Connector got considerably slower since 8.0.19
Submitted:	14 Mar 2021 19:40	Modified:	17 Nov 2022 17:48
Reporter:	Stefan Elstner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S5 (Performance)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Starting from 8.0.19 the .NET MySQL connector got considerably slower (almost 5 times) in an environment with concurrent operations and threads.

How to repeat:
The following code snippet demonstrates the issue. On my system with 8.0.18 creating 20k connections and doing a select takes approx. 1500 ms. With version 8.0.19 and above (tested also with 8.0.23) this takes approx. 7000 ms, around five times slower.

using MySql.Data.MySqlClient;
using System;
using System.Collections.Concurrent;
using System.Diagnostics;
using System.Threading;

namespace MySQLPerfTest
{
    class Program
    {
        private const string ConnectionString = "Server=...; Database=...; UID=...; PWD=...; CharSet=utf8mb4; SslMode=none;";
        private static ConcurrentQueue<int> queue = new System.Collections.Concurrent.ConcurrentQueue<int>();

        /// <summary>
        /// Process queue
        /// </summary>
        public static void ProcessQueue()
        {
            while (queue.Count > 0)
            {
                if (queue.TryDequeue(out var index))
                {
                    Do();
                }
            }
        }

        /// <summary>
        /// Do it
        /// </summary>
        public static void Do()
        {
            using (var conn = new MySqlConnection(ConnectionString))
            {
                conn.Open();

                using (var command = new MySqlCommand("SELECT 1", conn))
                {
                    command.ExecuteScalar();
                }

                conn.Close();
            }
        }

        static void Main(string[] args)
        {
            for(int i = 0; i < 20000; i++)
            {
                queue.Enqueue(i);
            }

            var w = new Stopwatch();
            w.Start();
            var threads = new Thread[120];
            for (int i = 0; i < threads.Length; i++)
            {
                threads[i] = new Thread(new ThreadStart(ProcessQueue));
                threads[i].Start();
            }

            foreach(var thread in threads)
            {
                thread.Join();
            }

            w.Stop();
            Console.WriteLine(w.ElapsedMilliseconds + " ms");
        }
    }
}

Suggested fix:
Must be somehow multi threading / locking related. The performance difference is there, but not as much, when using just one thread. (20k operations as described above with 8.0.18 took 8 sec., with 8.0.23 they took 11 sec.)

One issue I noticed is that in the MySqlConnection constructor the connection string is now analyzed every time, this might be undesirable (or the connection string cache might be used to check if it was previously analyzed).

Hello Stefan Elstner,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

Posted by developer:
 
There were some differences in the performance between v8.0.18 and the latest, v8.0.31. Although it wasn't that much, a fix was applied to change how the connection string was analyzed in order to avoid checking it twice by adding an internal property to the Connection object that will tell if the connection string has been already analyzed or not, this has increased the performance.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.32 release, and here's the proposed changelog entry from the documentation team:

To enhance the performance of connections, Connector/NET now determines
whether a connection string has been analyzed already before performing
the analysis for a second time.

Thank you for the bug report.