async Call MVC controller to open/close connection is slow

[KW]

Code: Select all

 public ActionResult Get()
        { 
  using (var connnection = new Devart.Data.MySql.MySqlConnection("User Id=xxx;Password=xxx;Host=192.168.1.2;Port=3306;Database=xxx;Unicode=True;Compress=False;Protocol=Ssl;Persist Security Info=True;Connection Timeout=60;Pooling=True;Connection Lifetime=20;Validate Connection=False;Found Rows=True;SSL TLS Protocol=1.2"))
            {
                try
                {
                         connnection.Open();                    
                    try
                    {
                         
                    }
                    finally
                    {                         
                        connnection.Close();
                    }


                }
                finally
                {

                }
        }

Execute Code block that is called asynchronously 30 times from .net client:

Code: Select all

 
 public async Task<string> CallWeb()
        {
            HttpClient client = new HttpClient();             
            HttpResponseMessage response = await client.GetAsync(APIURL + "/v1/Test");
            response.EnsureSuccessStatusCode();
            return await response.Content.ReadAsStringAsync();
           
        }
        

Using Devarts mysql connection is 38 seconds for the last call to exit.

If I call this method, just one at a time the time it takes to open and close each connection is:

.031 SECONDS according to db monitor.


Mysql Oracle Client:

Code: Select all

using (var connnection = new MySql.Data.MySqlClient.MySqlConnection("User Id=xxx;Password=xxx;Host=192.168.1.2;Port=3306;Database=xxx;"))

            {
                try
                {


                    connnection.Open();
 
                    try
                    {
                        

                       
                    }
                    finally
                    {                       
                         
                        connnection.Close();
                    }


                }
                finally
                {

                }
            }

The longest running connection is 2.2 seconds using Oracles connector, Devarts is 38 seconds.

I've tried adjusting Devarts connection string to every permutation I can think of and still the same results. In the debugger it literally feels like Devarts mysqlconnection is waiting on something. This is not the same when using Oracles mysql connector client for .net:

Install-Package MySql.Data -Version 8.0.13

[Shalex]

You should use the same connection mode (e.g.: SSL protocol) when comparing the performance of two providers:

Code: Select all

        static void Main(string[] args)
        {

            TestConnector();

            TestDevart();

            Console.ReadKey();
        }

        private static void TestConnector()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            for (int i = 0; i < 30; i++)
            {
                using (var connnection = new MySql.Data.MySqlClient.MySqlConnection("User Id=root;Password=root;Host=localhost;Port=3306;Database=mysql;SslMode=Required;"))
                {
                    try
                    {
                        connnection.Open();
                        try
                        {

                        }
                        finally
                        {
                            connnection.Close();
                        }
                    }
                    finally
                    {

                    }
                }
            }

            watch.Stop();
            Console.WriteLine("Connector/NET executes {0} milliseconds", watch.ElapsedMilliseconds);
        }

        private static void TestDevart()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            for (int i = 0; i < 30; i++)
            {
                using (var connnection = new Devart.Data.MySql.MySqlConnection("User Id=root;Password=root;Host=localhost;Port=3306;Database=mysql;Protocol=Ssl;"))
                {
                    try
                    {
                        connnection.Open();
                        try
                        {

                        }
                        finally
                        {
                            connnection.Close();
                        }
                    }
                    finally
                    {

                    }
                }
            }
            watch.Stop();
            Console.WriteLine("Devart dotConnect for MySQL executes {0} milliseconds", watch.ElapsedMilliseconds);
        }
    }

Our output is:

Code: Select all

Connector/NET executes 471 milliseconds
Devart dotConnect for MySQL executes 232 milliseconds

[KW]

First, the server I'm using enforces SSL so the mode is not required for the client and adding it does not change the results.

require_secure_transport = ON

Second, you're not reproducing the problem. I acknowledge calling single is fine. But change to threading. Oracle provider open/close is fine - Devarts open/close is VERY slow.

Code: Select all

class Program
    {
        static void Main(string[] args)
        {

            TestConnector();

            TestDevart();

            Console.ReadKey();
        }

        private static void TestConnector()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

            for (int i = 0; i < 30; i++)
            {
                
                 
                    tasks.Add( Task.Factory.StartNew(() =>
                    {
                        using (var connnection = new MySql.Data.MySqlClient.MySqlConnection("User Id=root;Password=****;Host=localhost;Port=3306;Database=mysql;SslMode=Required;"))
                        {
                            try
                            {
                                connnection.Open();
                                try
                                {

                                }
                                finally
                                {
                                    connnection.Close();
                                }
                            }
                            finally
                            {

                            }
                        }

                    }));
                
                
                
           }

            Task.WaitAll(tasks.ToArray());

            watch.Stop();
            Console.WriteLine("Connector/NET executes {0} milliseconds", watch.ElapsedMilliseconds);
        }

        private static void TestDevart()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

            for (int i = 0; i < 30; i++)
            {


                tasks.Add( Task.Factory.StartNew(() =>
                {
                    using (var connnection = new Devart.Data.MySql.MySqlConnection("User Id=root;Password=****;localhost;Port=3306;Unicode=True;Compress=False;Protocol=Ssl;Persist Security Info=True;Connection Timeout=60;Pooling=True;Min Pool Size=0;Connection Lifetime=20;Validate Connection=False;Found Rows=True;SSL TLS Protocol=1.2"))
                    {
                        try
                        {
                            connnection.Open();
                            try
                            {

                            }
                            finally
                            {
                                connnection.Close();
                            }
                        }
                        finally
                        {

                        }
                    }

                }));


            }

            Task.WaitAll(tasks.ToArray());

            watch.Stop();   

            Console.WriteLine("Devart dotConnect for MySQL executes {0} milliseconds", watch.ElapsedMilliseconds);
        }
         
    }

Connector/NET executes 411 milliseconds
Devart dotConnect for MySQL executes 24815 milliseconds


24 seconds to open and close connection in a multi-threaded application is abysmal. All the MVC calls are potentially multi-threaded as multiple clients can hit those controllers at the same time.

To clarify, I've tried every connection string change to Devart mysqlconnection and still the same results.

Devart.Data.mysql version 8.12.1229.0

[Shalex]

Connector/NET executes 411 milliseconds
Devart dotConnect for MySQL executes 24815 milliseconds

1. You get these results in a simple console application, don't you? Tell us Target framework specified in the properties of your project.

2. There are two sets of assemblies:
a) .NET Framework Devart.* assemblies (provided with installation)
b) .NET Standard Devart.* assemblies (downloaded from https://www.nuget.org/packages/devart.data.mysql)
Are you working with a) or b)?

3. Describe your MySQL Server:
a) version
b) edition
c) the name of operating system and its capacity where MySQL Server is installed
d) if possible, send us your my.ini file

[KW]

1. You get these results in a simple console application, don't you? Tell us Target framework specified in the properties of your project.

Yes, I was able to reproduce in the above posted code in a simple console application. Target Framework is 4.7.1

2. There are two sets of assemblies:
a) .NET Framework Devart.* assemblies (provided with installation)We are using assemblies provided by installation, we have a professional license.

3. Describe your MySQL Server:
a) version 8.011
b) edition Community
c) the name of operating system and its capacity where MySQL Server is installed - Ubuntu Server 32gb of ram, 1tb SSD
d) if possible, send us your my.ini file

[Shalex]

1. Please add System.Threading.ThreadPool.SetMinThreads(100, 100); to your Main method:

Code: Select all

    class Program
    {
        static void Main(string[] args)
        {
            System.Threading.ThreadPool.SetMinThreads(100, 100);

            TestConnector();

            TestDevart();

            Console.ReadKey();
        }

        private static void TestConnector()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

            for (int i = 0; i < 30; i++)
            {


                tasks.Add(Task.Factory.StartNew(() =>
                {
                    using (var connnection = new MySql.Data.MySqlClient.MySqlConnection("User Id=root;Password=root;Host=dbfmylast;Port=3306;Database=mysql;SslMode=Required;"))
                    {
                        try
                        {
                            connnection.Open();
                            try
                            {

                            }
                            finally
                            {
                                connnection.Close();
                            }
                        }
                        finally
                        {

                        }
                    }

                }));



            }

            Task.WaitAll(tasks.ToArray());

            watch.Stop();
            Console.WriteLine("Connector/NET executes in {0} milliseconds", watch.ElapsedMilliseconds);
        }

        private static void TestDevart()
        {
            var watch = System.Diagnostics.Stopwatch.StartNew();

            List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

            for (int i = 0; i < 30; i++)
            {


                tasks.Add(Task.Factory.StartNew(() =>
                {
                    using (var connnection = new Devart.Data.MySql.MySqlConnection("User Id=root;Password=root;Host=dbfmylast;Port=3306;Database=mysql;Unicode =True;Compress=False;Protocol=Ssl;Persist Security Info=True;Connection Timeout=60;Pooling=True;Min Pool Size=0;Connection Lifetime=20;Validate Connection=False;Found Rows=True;SSL TLS Protocol=1.2"))
                    {
                        try
                        {
                            connnection.Open();
                            try
                            {

                            }
                            finally
                            {
                                connnection.Close();
                            }
                        }
                        finally
                        {

                        }
                    }

                }));


            }

            Task.WaitAll(tasks.ToArray());

            watch.Stop();

            Console.WriteLine("Devart dotConnect for MySQL executes in {0} milliseconds", watch.ElapsedMilliseconds);
        }

    }

Our result is:
Connector/NET executes in 4206 milliseconds
Devart dotConnect for MySQL executes in 9633 milliseconds

Tell us your output in this case.

2. Your MySQL Server is installed on a different workstation in your local network, isn't it?

3. JIC: the new (8.12.1278) build of dotConnect for MySQL is available for download.

[KW]

The results are much better

Connector/NET executes 544 milliseconds
Devart dotConnect for MySQL executes 376 milliseconds


However, I'm unsure this solves anything. How do you implement a fix in asp.net core where multiple threads are spun up for each request to a controller If you look at my first post there is no threading declared - it's all being handled by asp.net cores kestrel (I believe).

Also, why is this even necessary and why does this slow down oracles results?

[Shalex]

We will investigate the issue and notify you about the result.

[KW]

Any update on this?

[Shalex]

The investigation is in progress. As soon as we have any results, we will notify you.