async Call MVC controller to open/close connection is slow

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
KW
Posts: 131
Joined: Tue 19 Feb 2008 19:12

async Call MVC controller to open/close connection is slow

Post by KW » Wed 14 Nov 2018 18:15

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
Site Admin
Posts: 8418
Joined: Thu 14 Aug 2008 12:44

Re: async Call MVC controller to open/close connection is slow

Post by Shalex » Mon 26 Nov 2018 17:09

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
Posts: 131
Joined: Tue 19 Feb 2008 19:12

Re: async Call MVC controller to open/close connection is slow

Post by KW » Mon 26 Nov 2018 19:57

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
Site Admin
Posts: 8418
Joined: Thu 14 Aug 2008 12:44

Re: async Call MVC controller to open/close connection is slow

Post by Shalex » Tue 27 Nov 2018 17:04

KW wrote:
Mon 26 Nov 2018 19:57
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
Posts: 131
Joined: Tue 19 Feb 2008 19:12

Re: async Call MVC controller to open/close connection is slow

Post by KW » Tue 27 Nov 2018 17:17

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
Site Admin
Posts: 8418
Joined: Thu 14 Aug 2008 12:44

Re: async Call MVC controller to open/close connection is slow

Post by Shalex » Thu 06 Dec 2018 14:08

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
Posts: 131
Joined: Tue 19 Feb 2008 19:12

Re: async Call MVC controller to open/close connection is slow

Post by KW » Thu 06 Dec 2018 17:52

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
Site Admin
Posts: 8418
Joined: Thu 14 Aug 2008 12:44

Re: async Call MVC controller to open/close connection is slow

Post by Shalex » Fri 07 Dec 2018 18:52

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

Post Reply