ASP.net Very slow connection to MySQL database, what can I do?

John_Mc · 27-04-2008 12:12pm #1

Hi,

I'm rewriting an existing ASP.net 1.1 website to use .Net 2.0 with AJAX.

There's a lot of functionality which was completely rewritten to be faster & more efficient, however it's running extremely slow on my development machine. I have isolated it down to the functions querying & Updating the MySQL database, which is running on the webhost server.

I have tried using both the ODBC and .Net Connectors but they both run at about the same speed.

What could cause this slowness? I'm pretty puzzled because the code is probably the cleanest & most structured I've ever come up with

Suggestions welcome!

Bluefrog · 27-04-2008 2:16pm

Have you done pings and traceroutes from your development machine to the webhosts MySQL server to rule out some network bottleneck?

<snip E.P>

John_Mc · 27-04-2008 7:32pm

Bluefrog wrote: »

Have you done pings and traceroutes from your development machine to the webhosts MySQL server to rule out some network bottleneck?

If you're hosting with Hosting365, I and others have had a number of issues with lost connections and dropped connections to their MySQL servers and I wouldn't tend to host this particular architecture with them again.

My ping results are:

Minimum: 143ms
Maximum: 162ms
Average: 151ms

My Trace Route is:

15 160ms 143ms 143ms [Server IP]

Could these results explain the low speed?

Thanks
John

Ginger · 27-04-2008 7:39pm

Have you tried simple queries to eliminate the database system ...

John_Mc · 27-04-2008 10:30pm

Ginger wrote: »

Have you tried simple queries to eliminate the database system ...

It's the same database that the current website is using which has no performance problems so I don't think it's the DB.

Are those Ping and Trace route results slow?

stevenmu · 28-04-2008 10:13am

Is it running the same queries as the old system, maybe some query change is forcing some full table scans ? Try some simple queries like Ginger suggests, possibly even create a (very temporary

) page that lets you type in a query and just displays the results.

Also, are you actually timing the query responses, or the page display times? There could be a problem somewhere else instead.

John_Mc · 28-04-2008 10:19am

Yeah, they're pretty much the same queries but I've streamlined them somewhat in this version.

When I comment out the calls for the functions that query the database, the page loads almost immediately so this leads me to assume that it's the DB queries.

I'll try some simple queries later on tonight and report back.

Thanks for the advice, but can anyone comment on the ping and trace route speeds?

Sposs · 28-04-2008 12:16pm

Bluefrog wrote: »

Have you done pings and traceroutes from your development machine to the webhosts MySQL server to rule out some network bottleneck?

<snip E.P.>

We have not had any issues with our Mysql DB servers, can you drop me a PM with your ticket number?

Ginger · 28-04-2008 12:26pm

A timeout can happen because the amount of time the database is taking to process the query is longer than the timeout allowed on the connection that you are using.

Sooo... hence my question. Have you tried for example, a 1 row table and a simple select on that, to knock out the possiblity of connection issues. So if it returns quickly you can say well then its possibly the query that is causing the issue that you are experiencing

stevenmu · 28-04-2008 2:19pm

Those pings don't seem too bad to me on their own, depending on the type of queries you are doing. But they do make me wonder about how you are connecting to the DB, is it hosted on the net somewhere and you are connecting from home/office, or possibly at another location within your company (considering your trace has 16 hops I take it thats a yes)? Is the existing system which runs fine connecting the same way or is it hosted in the same location as the DB server?

I said the pings don't sound too bad on their own, but they could explain a big speed difference if the existing system is co-located with the server. It's very dependant on the type of queries you are doing. And you have to take overall bandwidth into consideration aswell, aswell as other details of how the connection to the hosts network works.

Bluefrog · 28-04-2008 4:55pm

Sposs wrote: »

We have not had any issues with our Mysql DB servers, can you drop me a PM with your ticket number?

<snip E.P.>

The ping and traceroute values look fine. If you make a simple query page is there any difference between the execution time on your development machine and the production server? Do multiple requests cause any changes in execution time?

Evil Phil · 28-04-2008 5:18pm

Folks, I don't want to hear any complaints about companies on this forum. I don't care what kind of experience you've had. Its only your word for it and boards.ie could be held liable for your claims.

I've updated the charter to reflect this and as far as I'm concerned the new rule is in place for existing posts as well as future posts.

At this stage I'd have thought regular users would know better.

Also user Sposs banned for requesting a pm in thread and for commercial posting. This ban is permanent.

John_Mc · 28-04-2008 10:12pm

stevenmu wrote: »

Those pings don't seem too bad to me on their own, depending on the type of queries you are doing. But they do make me wonder about how you are connecting to the DB, is it hosted on the net somewhere and you are connecting from home/office, or possibly at another location within your company (considering your trace has 16 hops I take it thats a yes)? Is the existing system which runs fine connecting the same way or is it hosted in the same location as the DB server?

I said the pings don't sound too bad on their own, but they could explain a big speed difference if the existing system is co-located with the server. It's very dependant on the type of queries you are doing. And you have to take overall bandwidth into consideration aswell, aswell as other details of how the connection to the hosts network works.

Yes, at the moment I am developing the application on my home computer. I have a DB connection string pointing to the shared webhosting server in my web.config file, and I'm using the ASPX MySQL .Net connector to make the connection (I have also tried the ODBC connectors but there was little difference in speed). When deploying to the live server I will modify the connection string to use "localhost" as the server path as this is what the Web Hosting company says in their knowledge base.

The script which is giving me the worst performance has a lot of DB transactions to complete with each page load. There's also a fair bit of processing to do with the information, so I'm not surprised that it's a bit slow. The strange thing is, I decided to totally rewrite the current version because it was messy & inefficient. That is running without any problems so I would expect the same or better out of the new version

Ginger wrote: »

A timeout can happen because the amount of time the database is taking to process the query is longer than the timeout allowed on the connection that you are using.

Sooo... hence my question. Have you tried for example, a 1 row table and a simple select on that, to knock out the possiblity of connection issues. So if it returns quickly you can say well then its possibly the query that is causing the issue that you are experiencing

I opened up MySQL Query Browser on my development machine and ran a select all from the main table. It returned 1119 rows in 3.4055 seconds. I'm certainly no expert but that sounds on the high side?

Bluefrog wrote: »

<snip E.P.>

The ping and traceroute values look fine. If you make a simple query page is there any difference between the execution time on your development machine and the production server? Do multiple requests cause any changes in execution time?

Yes they do, I have other pages with only a few DB queries and their performance is within normal ranges.

stevenmu · 28-04-2008 10:34pm

When you say a lot of DB transactions do you mean many small queries or one/few queries with a lot of transactions in it?

The former can be very inefficient when connecting to a remote database. It can be the same even with a more local database, but it really shows up with remote databases, and that's where your ping times really come into affect. With a ping average of 150ms, you basically have a minimum cost of 0.3secs for each query you run, 0.15 to send your query and 0.15 to get a response. In reality it will be much more than that because your query and result sets will most likely be much larger than the ICMP packets sent for a ping. Add to that the processing time on the database server, the processing between each query on your web server (including the fact that while each server is idle waiting for it's next message the OS will probably switch the CPU off to something else), and any potential network delays and throttling on the side of your host, and each individual query becomes very expensive.

You might be able to improve the situation hugely by trying to combine the multiple queries into one/few big query that just gets sent to the server and processed and the results sent back. It's usually possible to do this when there's a lot of transactions to be done. Alternately, if that's not possible, try running the queries asynchronously so at least you don't have to wait for one to return to start the next. That might be kinda complicated programming wise though.

Of course you'll almost certainly see a huge boost when you deploy to the hosts servers and you're accessing the DB locally, so none of that might matter, can you try deploying some code to test that? (you might not be able to mix .net 1.1 and .net 2)

John_Mc · 28-04-2008 11:01pm

stevenmu wrote: »

When you say a lot of DB transactions do you mean many small queries or one/few queries with a lot of transactions in it?

The former can be very inefficient when connecting to a remote database. It can be the same even with a more local database, but it really shows up with remote databases, and that's where your ping times really come into affect. With a ping average of 150ms, you basically have a minimum cost of 0.3secs for each query you run, 0.15 to send your query and 0.15 to get a response. In reality it will be much more than that because your query and result sets will most likely be much larger than the ICMP packets sent for a ping. Add to that the processing time on the database server, the processing between each query on your web server (including the fact that while each server is idle waiting for it's next message the OS will probably switch the CPU off to something else), and any potential network delays and throttling on the side of your host, and each individual query becomes very expensive.

You might be able to improve the situation hugely by trying to combine the multiple queries into one/few big query that just gets sent to the server and processed and the results sent back. It's usually possible to do this when there's a lot of transactions to be done. Alternately, if that's not possible, try running the queries asynchronously so at least you don't have to wait for one to return to start the next. That might be kinda complicated programming wise though.

Of course you'll almost certainly see a huge boost when you deploy to the hosts servers and you're accessing the DB locally, so none of that might matter, can you try deploying some code to test that? (you might not be able to mix .net 1.1 and .net 2)

Yes, what you are saying makes complete sense to me, and is what I am hoping the problem is because it will go away when I deploy it to the live server

You are correct about not being able to mix 1.1 & 2.0, so I have been unable to test it properly. I'm going back over the code now and trying to improve it where I can, but it seems alright to me so far.

I'm going to have to get some new webhosting to test it I think

ASP.net Very slow connection to MySQL database, what can I do?

Comments