Boards.ie uses cookies. By continuing to browse this site you are agreeing to our use of cookies. Click here to find out more x
Post Reply  
 
Thread Tools Search this Thread
27-04-2008, 12:12   #1
John_Mc
Registered User
 
Join Date: May 2005
Posts: 1,809
ASP.net Very slow connection to MySQL database, what can I do?

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!
John_Mc is offline  
Advertisement
27-04-2008, 14:16   #2
Bluefrog
Registered User
 
Bluefrog's Avatar
 
Join Date: Oct 2005
Location: Glasnevin 54m asl
Posts: 993
Send a message via Yahoo to Bluefrog Send a message via Skype™ to Bluefrog
Have you done pings and traceroutes from your development machine to the webhosts MySQL server to rule out some network bottleneck?

<snip E.P>

Last edited by Evil Phil; 28-04-2008 at 17:32.
Bluefrog is offline  
27-04-2008, 19:32   #3
John_Mc
Registered User
 
Join Date: May 2005
Posts: 1,809
Quote:
Originally Posted by Bluefrog View Post
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
John_Mc is offline  
27-04-2008, 19:39   #4
Ginger
Registered User
 
Ginger's Avatar
 
Join Date: Jan 2003
Location: Stavanger, Norway
Posts: 2,824
Have you tried simple queries to eliminate the database system ...
Ginger is offline  
27-04-2008, 22:30   #5
John_Mc
Registered User
 
Join Date: May 2005
Posts: 1,809
Quote:
Originally Posted by Ginger View Post
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?
John_Mc is offline  
Advertisement
28-04-2008, 10:13   #6
stevenmu
Moderator
 
Join Date: Jul 2003
Posts: 9,174
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.
stevenmu is offline  
28-04-2008, 10:19   #7
John_Mc
Registered User
 
Join Date: May 2005
Posts: 1,809
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?
John_Mc is offline  
28-04-2008, 12:16   #8
Sposs
Registered User
 
Sposs's Avatar
 
Join Date: Jun 2002
Location: Longitude: -6.2489 , Latitude: 53.3331
Posts: 2,422
Quote:
Originally Posted by Bluefrog View Post
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?

Last edited by Evil Phil; 28-04-2008 at 17:36.
Sposs is offline  
28-04-2008, 12:26   #9
Ginger
Registered User
 
Ginger's Avatar
 
Join Date: Jan 2003
Location: Stavanger, Norway
Posts: 2,824
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
Ginger is offline  
Advertisement
28-04-2008, 14:19   #10
stevenmu
Moderator
 
Join Date: Jul 2003
Posts: 9,174
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.
stevenmu is offline  
28-04-2008, 16:55   #11
Bluefrog
Registered User
 
Bluefrog's Avatar
 
Join Date: Oct 2005
Location: Glasnevin 54m asl
Posts: 993
Send a message via Yahoo to Bluefrog Send a message via Skype™ to Bluefrog
Quote:
Originally Posted by Sposs View Post
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?

Last edited by Evil Phil; 28-04-2008 at 17:33.
Bluefrog is offline  
28-04-2008, 17:18   #12
Evil Phil
Moderator
 
Evil Phil's Avatar
 
Join Date: Mar 2001
Location: West Indies
Posts: 7,123
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.

Last edited by Evil Phil; 28-04-2008 at 17:54.
Evil Phil is offline  
28-04-2008, 22:12   #13
John_Mc
Registered User
 
Join Date: May 2005
Posts: 1,809
Quote:
Originally Posted by stevenmu View Post
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

Quote:
Originally Posted by Ginger View Post
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?

Quote:
Originally Posted by Bluefrog View Post
<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.

Last edited by John_Mc; 28-04-2008 at 22:15.
John_Mc is offline  
28-04-2008, 22:34   #14
stevenmu
Moderator
 
Join Date: Jul 2003
Posts: 9,174
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)
stevenmu is offline  
28-04-2008, 23:01   #15
John_Mc
Registered User
 
Join Date: May 2005
Posts: 1,809
Quote:
Originally Posted by stevenmu View Post
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
John_Mc is offline  
Post Reply

Quick Reply
Message:
Remove Text Formatting
Bold
Italic
Underline

Insert Image
Wrap [QUOTE] tags around selected text
 
Decrease Size
Increase Size
Please sign up or log in to join the discussion

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Share Tweet