Every query seem to be delayed

Posts   
 
    
JanRHansen
User
Posts: 37
Joined: 02-Jan-2019
# Posted on: 01-Sep-2022 15:20:17   

Strange things ahead, I'm not expecting a solution, but rather advice how to proceed...

The scenario is that when my code runs locally (asp.net website) and connects to a (test) database (mysql 8.0.30) everything runs fine. The test database is hosted in our datacenter, so the connection is "potentially slow", but its fine. When the code is deployed to the test webhost, and connects to the very same test database server, which is now located on the same internal network, and thus should be faster, if any change at all - then every single query seem to take an extra second. So my grid refreshing takes no longer < 2 seconds, but more like 5-8 secons, as roughly 6 queries are executed.

This seem to be a problem that has come after switching to a newer database. We were on a myslq 5.0 before, and have now upgraded to 8.0. At least that is my impression. Others says it was as slow before also, but Im not sure.

As it works from my local machine to the database server, its not the server "as such". It has plenty of memory and cpu and hardly uses any ressources. So it must be something else.

We thought we had found the error in not having enabled skip-name-resolve in the mysql configuration, hence having mysql looking up the hostname for the connecting IP on each query, but thats now fixed (we believe) and its still equally slow.

Its like a) the database server treats connections from the webserver differently than connections from my developer machine. Not the name-resolve-thing, but then something else? or b) the webserver processes the application code differently, all of a sudden. or c) the dotconnect layer somehow delays queries, but only on the webserver, as I use the same on my dev machine

How would you go about analyzing this issue? I've enabled MySqlDQE tracing (level 4) but all I really get is the SQL statements, and timestamps that confims that the queries take some time.

LLBLGen pro 5.9.1 asp.net webiste, framework 4.6 Devart dotConnect for MySQL express v. 8.19 MySQL server community edition v. 8.0.30

Ideas are most welcome simple_smile

Thanks in advance.

Best regards, Jan

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 02-Sep-2022 06:50:29   

Some guessing... What I can think of is that, it's either a network related issue, or a web server environment issue.

Can you VPN from your machine to the database internal network and try to run the same queries? If it's till as fast, then it's not a network issue and it's a server environment issue, so you need to trace what's different om your dev machine than on the test server. Otherwise it's still a network related issue (ip resolving).

You could also change the connection string to use the database ip address instead of the database server name to role out ip resolving and see if this makes a difference.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Sep-2022 08:57:55   

If that doesn't, additionally you could create a console app with native ADO components and see how fast it opens a connection, runs a query etc. Our code doesn't have different code paths for mysql 5/8. And perhaps ask Devart too? It's their set of ADO.NET components after all (e.g. perhaps they know of some issue with their stuff and mysql 8 )

Frans Bouma | Lead developer LLBLGen Pro