Site response is extremely slow

[Guest]

Our site - http://www.acehighsupply.com

 

Yesterday I posted asking if the problem could be the hosting or osCommerce.

 

Well, I am beginning to believe it is osCommerce.

 

The hosting company is constantly telling me there is nothing wrong with the server, etc. I have had the site monitored by three different website monitoring services. Basically, what they do is go to the homepage at www.acehighsupply.com and check for a response. I have received notices that the site has been down 13 times over the last 24 hours. And, the graph showing response times is horrible (at certain hours, it hovers around 5-15 seconds!).

 

I was assuming it was the hosting, but I may be changing my mind. The reason? If I click within our site, there is a long response time (right now, it is around 3-5 seconds). This is where the browser will be saying "waiting for acehighsupply.com" and nothing happens for around 5 seconds. Once it hears from the the site, the page loads very fast. However, if I try to open up any file except for a PHP file, it doesn't have the "waiting" period. It loads instantly. Such as http://acehighsupply.com/images/products/c...tom_pile001.jpg

 

I have not made any changes to the code for a long time. The only changes I have made are updates to the news and adding new products (both through osCommerce admin - not touching any actual code).

 

This started to be very bad over the last week. To the point where our site was essentially inaccessible yesterday. See the response time graph here:

 

https://www.alertsite.com/tmp/[email protected]

 

Anyone know what could be causing this? Could it still be something on the server - such as the database server being slow, etc.

 

What can I do to troubleshoot the problem and find out what it is?

 

Thanks for your help!

[TAPAKAH]

start with this contribution:

http://www.oscommerce.com/community/contributions,2575

[Guest]

Thanks TAPAKAH,

 

I will do that during lunchtime. Do you think the output would provide some clues about the problem?

 

I have just talked to our hosting company and they are just blaming the PHP code, so they won't help at all. The tech guy I spoke to pulled up http://acehighsupply.com/index.html and since there was no significant response time from the server, he says everything is fine on their end.

 

Before I install that contribution, is there anyone that has any clues as to why this started happening? As I mentioned, this started about a week ago with no changes to the osCommerce code.

[Guest]

Mr. Psychotic,

 

You must first identify the underlying problem before trying to dissect the reason it manisfested recently and also implement preventive measures for the future.

 

I would suggest you start debugging ASAP to identify the root problem and then look into methods of correcting those issues.

 

I would not only look at the MySQL query time but also the PHP parse time. Create a custom class to record execution time break points in the code. Also, tie this into the database execution time. Once you do this you'll get a performance output of your store from top to bottom and should be able to pinpoint the problem within a few minutes.

 

Bobby

[Guest]

Thanks everyone! I am going to need some help deciphering the output of the debug. I have it installed now so the information is in the (edit) footer.

For me, it said "Current Parse Time: 5.309 s with 46 queries"

 

Then, it seems like there is a time for every query. The problem is that the sum of those times do not come close to 5.309 s.

 

I am good at following directions, but I can't quite figure out how to troubleshoot this on my own. Know that I have the debugging contribution turned on, can someone see a problem?

 

Thanks again!

[Guest]

Okay, I am playing around going from page to page. It now seems like adding the query time in the array may come up to the total time. However, there isn't a certain query that is taking up all the time. For instance, I just got, "Current Parse Time: 18.169 s with 56 queries", but there are several that are 1+ seconds each. When I go to a different page, different queries will take a long time.

 

Any clues from the results?

[Guest]

The times listed are how long it took to execute that particular query.

 

It does not look like there are any certain performance killers on the script side of the house.

 

Assessment: your server is shit.

 

As a frame of reference, all those times are off by a factor of 10. The queries that are taking .005 seconds to execute should be taking .0005 seconds. The load time is 1.79 seconds and should be around .179

 

That is a sure sign that your hosting company sucks ass and has you on an overloaded server.

 

GENERAL RULE #1: if you pay more for your internet connection than your website hosting expect problems

 

Bobby

[Guest]

Okay, I am playing around going from page to page.  It now seems like adding the query time in the array may come up to the total time.  However, there isn't a certain query that is taking up all the time.  For instance, I just got, "Current Parse Time: 18.169 s with 56 queries", but there are several that are 1+ seconds each.  When I go to a different page, different queries will take a long time. 

 

Any clues from the results?

<{POST_SNAPBACK}>

The page parse time is related to the query execution time but they are not the same.

 

The page parse time is how long it took to get from the top of the script to the bottom by the PHP parser.

 

The time listed in the debug output is the time it took to execute each query.

 

So, the page parse time = PHP time + MySQL time

 

My assessment still stands...you server is to blame for slow load times.

 

Bobby

[Guest]

Well, just got off the phone with our hosting company. They keep wanting to just say, "you are using PHP, so that is the problem"!! The tech turned out to be fairly nice and understanding, even though he initially gave the reply above. The good thing is that I have three different accounts with them - all three are running osCommerce with one getting much more traffic than the site I am having problems with. Therefore, I have some leverage to make them come to action (by the way, each account is costing me about the same as my internet service - this is not $5/month hosting).

 

Anyway, I installed the query debug contrib on my heavy traffic site and showed the tech how this site was averaging 2-5 second parse time and the heavy traffic site was averaging 0.1-0.3 second parse time. He said they will be looking at it and if worst comes to worst, would consider moving me to a different server.

 

. . . I will update, if needed, but right now it just looks like I have to fight with my hosting company.

 

Thanks Chemo!

[Guest]

This is sort of off-topic, but if my database server gets switched, what do I have to do? If I have a backup saved (on my computer, not the server) from the "Database Backup Manager" in admin do I just run that through phpMyAdmin and then change the configure file? I just want to be prepared in case this happens. Our site is just beginning to get some significant traffic so I want the least amount of downtime. Thanks.

[Guest]

I'm glad my contribution was of some service... :)

 

Bobby

[Guest]

I'm experiencing very similar problems and the hosting company is saying very similar things. It is clearly a php problem with the sql queries as written in oscommerce. My sites ran very smoothly until I reach about 20,000 orders. Once that happened, my sites came to a crawl.

 

I disabled the "best seller" feature and hard coded it. This helped tremendously. I was getting sql queries that were looking at over 90,000 records for almost every page load and response times over 11 seconds. CRAZY!

 

Unfortunately, as orders have increased well beyond that point, the sql queries as written in the default oscommerce php are again causing me great trouble. My hosting company has reviewed the slow queries log (they are also very familiar with oscommerce) and have suggested that I remove all functions that require a "count". I'm not sure what exactly this will entail, but if anyone has alternate suggestions I would very much appreciate the advice.

 

Thanks,

[Guest]

When you're dealing with high volume sites you need a high volume optimization specialist...not a webhost that knows a thing or two about osCommerce.

 

The easiest method of getting around the high order slow down is to add another field onto a few tables to denote archived/(default) not archived and set this column as an index. It won't be a large index since it will only have 2 values (0/1). Then, you'll have to rewrite certain queries on the catalog side to incorporate that indexed column (you've already found one, the bestsellers box).

 

To complete the optimization you'll need to create an admin script to set the archive flag for old orders.

 

These measures will enable a store owner to use only a certain range of orders for their queries and will restore a "500 order" performance level.

 

Bobby

[Guest]

When you're dealing with high volume sites you need a high volume optimization specialist...not a webhost that knows a thing or two about osCommerce.

 

The easiest method of getting around the high order slow down is to add another field onto a few tables to denote archived/(default) not archived and set this column as an index.  It won't be a large index since it will only have 2 values (0/1).  Then, you'll have to rewrite certain queries on the catalog side to incorporate that indexed column (you've already found one, the bestsellers box).

 

To complete the optimization you'll need to create an admin script to set the archive flag for old orders. 

 

These measures will enable a store owner to use only a certain range of orders for their queries and will restore a "500 order" performance level.

 

Bobby

<{POST_SNAPBACK}>

 

Bobby,

 

Thanks a bunch for your hard work and insight. I have installed a lot of your contributions and they have really helped. The above information you posted sounds brilliant! Have you added these indexes and scripts on your site? I would really like to implement your suggestions but quite frankly I know barely enough to be dangerous. I'm certain if I attempted your suggestions on my own, I would quickly be out of business.

 

If you have done scripts and sql changes like the ones you mention, would you mind posting them here?

[Guest]

I've posted the guts of how to do it...I have to keep the rest for paying clients :)

 

It's not hard to implement. Make a backup of your database and source files. Move the source to another directory and treat that like your "sandbox". Restore the database backup and use that instead of the actual store database. Now you have a complete mirror copy of the store and any changes you make will not break the live store. Play around until you get it done...this is the only way to learn.

 

Bobby