Page 1 of 1
async Call MVC controller to open/close connection is slow
Posted: Wed 14 Nov 2018 18:15
by 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
{
}
}
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
Re: async Call MVC controller to open/close connection is slow
Posted: Mon 26 Nov 2018 17:09
by 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
Re: async Call MVC controller to open/close connection is slow
Posted: Mon 26 Nov 2018 19:57
by 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
Re: async Call MVC controller to open/close connection is slow
Posted: Tue 27 Nov 2018 17:04
by Shalex
KW wrote: ↑Mon 26 Nov 2018 19:57Connector/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
Re: async Call MVC controller to open/close connection is slow
Posted: Tue 27 Nov 2018 17:17
by 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
Re: async Call MVC controller to open/close connection is slow
Posted: Thu 06 Dec 2018 14:08
by 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.
Re: async Call MVC controller to open/close connection is slow
Posted: Thu 06 Dec 2018 17:52
by 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?
Re: async Call MVC controller to open/close connection is slow
Posted: Fri 07 Dec 2018 18:52
by Shalex
We will investigate the issue and notify you about the result.
Re: async Call MVC controller to open/close connection is slow
Posted: Sat 05 Jan 2019 01:35
by KW
Any update on this?
Re: async Call MVC controller to open/close connection is slow
Posted: Fri 11 Jan 2019 18:30
by Shalex
The investigation is in progress. As soon as we have any results, we will notify you.