Store S L O W. V2.2-MS2. How do I find the problem?

[Steve80]

I have a store that's been active for about 5 years with no major problems.  Quite a number of mods installed and working.  No measureable changes in php code in recent months(lots in the database).  Suddenly, about a week ago, the site went from <2 sec page loads to TWENTY FIVE SECOND page loads .. ALL of them.  It basically makes the store unuseable.  We have contacted the host (GoDaddy) and they ASSURE us that it's either a corrupted db or in OUR PHP CODE. (they ALWAYS say that, right ?).  At any rate, I've been working on this for the last 3 days solid and have NOTHING.  I have tested the db and it appears just fine.  I can access it and "play with it" at normal speeds with phpMyAdmin.  I have optimized all the tables.  I can also access the db through a 3rd party app that we've been using for years (Store Manager) and it also appears to be just fine.  It can download the entire db and display it in < 20 secs. (2500+ products)  Updates to products via SM are working just as they always have.

 

Today, I finally went back and overwrote EVERY PHP file from a backup from a month ago.  The result is the same S L O W operation.  I have searched all kinds of forums and googled myself silly trying to figure out what's going on.  The minimal profilers that I have found show me bascially nothing.  The best one shows about 35 "calls" for the index page (it's just as slow as every other page) at times varying from a few milliseconds to around 900 ms and ONE sitting there at 24 seconds - with no further break down.  The WAIT time (specified at the bottom of the report) is ~24 seconds for the whole page.

 

I can't think of anything else to try now.  I guess I could start over TRULY from scratch, but I would lose a LOT of mods that I've done to get things the way I want them - including a lot of SEO stuff.  Redoing all of that is WEEKS of work ... if I can find all the info.  I always figured that keeping a known good backup would be adequate .. apparently not always.

 

My deep down thought is that it is still a GoDaddy problem.  Something about the connection to the database.  I have a 'sister' site on the same server and same hosting area... just a different directory and it works just fine .. with no db access.  I even tried putting my backup into a new subdirectory on the same server... but I don't seem to be able to get the path settings in configure.php to work correctly.  Interestingly, I did get the index page to load .. and it loaded in <2 secs.  Sounded good, but any click would revert back to the base page and the 24 second delay - so in the end, it left me with nothing.

 

Is there a way to TEST the db access relatively simply.  I'm not a 'guru', but I have modded quite a few php files. I know a LITTLE about mySQL - just enough about both to get myself in trouble, but not enough to cobble together a test program from scratch.

 

If you want to see how bad it is, the site is http://www.SpruellMotorsport.com .

Begging for any ideas or thoughts :wacko: .

 

Steve

[MrPhil]

Any chance that shopping bots and store scrapers have suddenly found your store, and are sucking up all your cycles? Your host should be able to tell you how to look at visitor statistics. It could be nothing to do with your store, but external factors. You might have to deny access to some of the worst offenders via .htaccess, it it's crippling your store that much.

 

You might try making a copy of your store in a private directory, and see if it has the same problems (it sounds like you tried that, but were unable to fix the paths in configure.php). If it's bots pounding on your live store, you'll probably still have trouble. I don't know if making a test install of 2.3.4 or 2.3.4BS will show any difference, unless it really is a code problem. Did your host recently upgrade the server and/or PHP version? Do your site error logs show anything?

[Jack_mcs]

@@Steve80 Normally I would say it is data skimmers causing this, and that could still be the case. But that usually comes and goes and it seems to be consistent in your case. It still could be that they are relentlessly hitting the site but I think I would look at your categories first. It might be that you have reached a point where the number of categories and products are causing a problem. For a quick test, I suggest editing the column left file and removing the entry for the categories box. If you have cache enabled, also remove the line for the cache in that file. Upload the changed file and see if the speed changes drastically.

[BrockleyJohn]

I have a client site that's had very similar problems over a similar period, and have now got them back to reasonable response times. There were a few factors all acting together, but it was mostly spiders/bots.

 

Have a look at the raw web access logs, presumably accessible through your hosting panel. You can see in there if the majority of your traffic is bots - this is likely.

 

This is what I suggest looking for, based on this last week's experience
 
1. someone had uploaded very large (like approaching 1MB) images to a couple of the top level categories. So they load for every new home page access. Ouch! A great resource for finding stuff like this is http://gtmetrix.com
you can run off reports as long as your arm with prioritised recommendations for what to change

 

2. look at the sessions table - either you've added in a mod to delete sessions more than n months old or it's several gig big, if the latter delete all the ones with expiry less than a date you pick. There may well be a significant overhead on this table so optimise it if so.

 

3. make sure includes/spiders.txt is up to date - if you're still using the old one from 2007 it's fairly useless - take the one from the latest release, it's the same format, you can just overwrite

 

4. in admin > configuration > sessions set Prevent Spider Sessions to true

 

5. make sure robots.txt is sensible - most importantly, rule out search and images directory

 

6. throttle bingbot - I found that regularly 4 to 6 instances of bingbot were crawling the site simultaneously, give it a crawl delay directive and set up a crawl profile in bings webmaster tools so it stays away while your customers are trying to use the site

 

 

This has brought everything back to mostly quick response with the occasional unlucky slow one - although there are a couple of things I'm not entirely happy with:

- the sessions table is automatically trimmed to a month's worth, yet it has about 400,000 rows. This total is still going up by about 100 rows per day, maybe due to the second concern

- I think there's a bad bot interested in the site. In a twelve hour period yesterday there were 221 page accesses that look to me to be from a bot. Each has only a single GET for the page html without any of the other content. The user agent string, though, just indicates someone running Firefox 14 on windows 7 (Mozilla/5.0 (Windows NT 6.1; WOW64; rv:14.0) Gecko/20100101 Firefox/14.0.1) which I don't believe as I don't think it's possible to use it with just one GET. Every single one of these GETs is from a different IP address, but they all resolve to Amazon.com ISP in Ashburn VA. I'm thinking about how to close the door on this joker...

 

All of that said, there are some 2.2 addons with very badly written queries that will bring shops to a shuddering halt with lots of products and only a few simultaneously accessing. Some versions of Products Extra Fields, Bundled Products and the one that lets you have multiple specials on the same product (I don't remember the name) are ones that I have come across that had some very suboptimal queries (sometimes more in their combination with other addons than the original author's code).

[BrockleyJohn]

I forgot to say - the easiest way to check out what's happening with the database is to enable logging in admin > configuration

[Steve80]

OK.. Thanks very much for the inputs so far...

**This is a long one - so, if you want to jump to the end first .. look at the last few sentences (w00t) ****

Bots does NOT appear to the problem.  I have looked at page accesses and they are only running 500 or so per day and as far as I can see they ARE related to the store and show a pattern that I would expect from a prospective customer.  Nothing that looks like a bot to me.

I did try making a copy in another directory- it SHOULD have worked, but I couldn't get the settings in included/configure.php correct.  My real store runs from root .. so I just added the new directory following the '/' in the places it appeared to be needed .. but clicking would always take me back to the main store (the url in the window would NOT show the subdirectory).  Is there some other place where a path needs to be changed to have it function from the new directory ??

Going "over a cliff" in the categories list did occur to me, however, I've seen posts from people with stores with THOUSANDS of Categories and 10's of thousands of products.  So that seems somewhat unlikely to me.  I am on a shared server, but the store is still "small" by most standards.  I did edit the right and left columns as a test to see if maybe the facebook stuff or something else was causing issues .. I completely elminated everything except the categories box on both sides and it changed nothing with regards to the speed.  Removing the categories box would probably fix the speed issue .. but I'm not sure what that would tell me.  There is NOTHING you can "DO" on the site without that box.  I do recall that initially I set it up to "show counts" and wow - did that make a difference - but that was 6 years ago.  Taking counts back out brought the speed back to an acceptable level where it has remained until a few days ago.

Checking my error logs, shows no more than about 10 errors per day - nothing significant and nothing that jumps out at me - of course.. what do I know ??  One log file is shown here

[Mon Jul 13 12:29:10 2015] [7673476] [fcgid:warn] (104)Connection reset by peer: [client 157.55.39.44:10806] mod_fcgid: error reading data from FastCGI server
[Mon Jul 13 12:29:10 2015] [7673476] [core:error] [client 157.55.39.44:10806] End of script output before headers: product_reviews.php
[Mon Jul 13 12:29:11 2015] [7673476] [fcgid:warn] (104)Connection reset by peer: [client 100.43.81.143:39340] mod_fcgid: error reading data from FastCGI server
[Mon Jul 13 12:29:11 2015] [7673476] [core:error] [client 100.43.81.143:39340] End of script output before headers: product_info.php
[Tue Jul 14 14:08:56 2015] [7673476] [autoindex:error] [client 207.46.13.49:18440] AH01276: Cannot serve directory /var/chroot/home/content/76/7673476/html/psmain/motorsport/racecars/: No matching DirectoryIndex (default.html,default.htm,index.php,index.shtml,index.html,index.htm,home.html,home.htm,index.php5,welcome.html,welcome.htm) found, and server-generated directory index forbidden by Options directive
[Wed Jul 15 05:09:58 2015] [7673476] [fcgid:warn] (104)Connection reset by peer: [client 24.17.229.54:63375] mod_fcgid: ap_pass_brigade failed in handle_request_ipc function, referer http://www.bing.com/search?q=panasport+wheels&src=IE-TopResult&FORM=IETR02&conversationid=
[Wed Jul 15 13:14:30 2015] [7673476] [fcgid:warn] [client 108.2.195.249:3031] mod_fcgid: can't apply process slot for /web/cgi-bin/php-fcgi, referer http://spruellmotorsport.com/index.php?manufacturers_id=3&osCsid=850167d95e03b1a98720ce44c3a31747

I think that means that there is a page with a coding error (End of script before headers), but there are not a LOT of errors of any kind.  In other files, I see some different errors, but can attribute most of them to the thrashing I've been doing with file overwriting on a live store (restoring from the backups).
From John's post I'll cover each item..
1). Pictures.  There have been only 3 new pictures uploaded in the last couple of months and all of those are under 50k bytes.  I did use GTMetrix to analyze my site - it told me pretty much the same.  Lots of CALLS with small ms delays.. and ONE with 23 seconds (just listed the base URL- nothing else).

2). Sessions table - phpMyAdmin says it is 417.8k - including all overhead.  If I read the bottom line correctly the entire db is only 6.7 MB for a total of 36,435 records. Sounds reasonable to me.

3). Spiders.txt .. Here, I am somewhat lost. My file was from 2008. I downloaded the spiders file from the latest merchant 2.3.4 version and have uploaded it to my site.  However, as stated above, I'm pretty sure this is not a spiders issue.  I also wonder why the newest version is some 50% of the size of my old one?

4). Prevent Spider Session is set to true.. and always has been.

5). Robots.txt is from 2012, but not sure how to make sure it's "sensible".  It does rule out images and lots of specific files.  Don't see anything related to SEARCH. Disallows about 100 agents or so.

6). Thottle bingbot .. how do I do that ?  And, how did you "find" that instances of it were crawling your site?  I'm afraid that I don't know very much about how to do this stuff.  I don't do it for a living.  It's just a 'passtime' for me - I'm OLD and RETIRED :).

In the end... I'm not using any of the addons you mentioned and I've not added any recently anyway.
I did enable logging in Admin.  The only thing it would log is Page Parse Time, but I figured that might help.
****** PEOPLE ******
A truly amazing thing has just happened.  While I was typing this, I would occasionally go over and click on my home page to observer the load time.  When I started.. the load time was about 18 seconds.  In the last 10 minutes .. it has dropped from 18 .. to 15... to 11.. to 5 and now down to ~3.  WOW .. The site is almost useable again.  The last couple have even been down to around 1.5 secs.

I can't imagine that simply turning on the logging in admin had anything to do with it, but that's the ONLY thing I did today other than type this message.  Well.. I guess I did upload the new spiders.txt and dl a couple of files to look at them, but that's all.

I'm stunned!  I wonder how long it will last ??
Steve

[Hotclutch]

I did try making a copy in another directory- it SHOULD have worked, but I couldn't get the settings in included/configure.php correct.  My real store runs from root .. so I just added the new directory following the '/' in the places it appeared to be needed .. but clicking would always take me back to the main store (the url in the window would NOT show the subdirectory).  Is there some other place where a path needs to be changed to have it function from the new directory ??

 

.htaccess

 

RewriteBase /

 

 

Going "over a cliff" in the categories list did occur to me, however, I've seen posts from people with stores with THOUSANDS of Categories and 10's of thousands of products.  So that seems somewhat unlikely to me.  I am on a shared server, but the store is still "small" by most standards.  I did edit the right and left columns as a test to see if maybe the facebook stuff or something else was causing issues .. I completely elminated everything except the categories box on both sides and it changed nothing with regards to the speed.  Removing the categories box would probably fix the speed issue .. but I'm not sure what that would tell me.  There is NOTHING you can "DO" on the site without that box.  I do recall that initially I set it up to "show counts" and wow - did that make a difference - but that was 6 years ago.  Taking counts back out brought the speed back to an acceptable level where it has remained until a few days ago.

 

Your number of categories and products don't look like a problem, even on a shared server.

 

 

6). Thottle bingbot .. how do I do that ?  And, how did you "find" that instances of it were crawling your site?  I'm afraid that I don't know very much about how to do this stuff.  I don't do it for a living.  It's just a 'passtime' for me - I'm OLD and RETIRED :).

 

 

You don't need to throttle Bing and Googlebot normally. They are "stingy" with their crawl rates and won't cause your site a problem.

 

My advice would be to hire someone who can do a conversion for you to a new responsive type store and optimize it at the same time. Difference will be night and day.

[Steve80]

Thanks for the input.  The joy didn't last long.  The page load time has been all over the map today - varying from 20 secs down to as low as 1.

I'm thinking it's time to find a new host.  Looking at Arvixe since they seem to specialize in Oscommerce sites.  Anyone have input on them ?

Steve

[tgely]

Have you ever used firebug net panel?

You could see the loading times and the problems with your site. Its a very usefull tool.. (I dislike facebook)

[Steve80]

Have you ever used firebug net panel?

 

You could see the loading times and the problems with your site. Its a very usefull tool.. (I dislike facebook)

Thanks - I've had firebug for a while... but never realized it had this capability.  Almost all of the options were DISABLED.  It took some hunting around, but I finally figured out how to enable the Net Panel.  Now I can see basically the same report I found on a website somewhere.  Nice to have it 'in house' now.  However, as luck would have it, NOW is the time the site starts to work at its fastest.  I'll have to keep checking to catch it at a low moment, but I suspect it'll tell me the same thing as before - the base page doesn't takes the longest time and everything else is essentially irrelevant.  Even at this moment, the base page load shows 506ms WAITING and only 20 ms actually receiving anything.

 

Steve

[Hotclutch]

There's not much content on your home page. osCommerce has built in cache for the categories box and manufacturers box. Do you have cache turned on in Admin?

[Hotclutch]

The View All products link in your category box is broken. If you are no longer using code, you should uninstall it, they could be adding to your query load and slowing your site down.

[Hotclutch]

You can turn GZip compression on in the Admin if you don't already have it enabled. It's under Configuration - Gzip compression. Set the level to 4.

[Hotclutch]

More broken links in the Information box.

[Steve80]

There's not much content on your home page. osCommerce has built in cache for the categories box and manufacturers box. Do you have cache turned on in Admin?

Agreed - yes cache is turned on, but I can't really figure out how to tell if it's working.  There ARE files in the cache directory for both.  Is there a concise way to find out other than turning it on and then off and trying to see the difference?

 

The View All products link in your category box is broken. If you are no longer using code, you should uninstall it, they could be adding to your query load and slowing your site down.

 

More broken links in the Information box.

 

Agreed.. Thanks.  This is most likely due to the REVERSION of code I did trying to get the site to work correctly.  I'll have to look into all of them (more work to do :( ).  Personally, I think the view all products is a waste of time.  I'll probably just delete that one...

 

You can turn GZip compression on in the Admin if you don't already have it enabled. It's under Configuration - Gzip compression. Set the level to 4.

Thanks. I had GZip enabled in .htaccess but not within Admin.  I THOUGHT that item was strictly for downloads and we don't do any of that.

 

I also see from the profiling of Firebug (and other analyzers) that there is significant work that could be done in the images area.  A 'semi-non computer person' does more of the db updating, including new pictures.  I have stressed to them in the past the importance of minimizing graphic file sizes, but it appears that some additional training is in order.. and again .. more work for me to fix past transgressions :- .

Steve

[Hotclutch]

Yes i thought about mentioning the images needing optimization. You only have 4 on your home page, so i left it.

 

Cache only works if you upload a blank file initially to the working directory specified in Admin under cache. It should be

 

categories_box-english.cache

 

for the categories box.

 

You can get an idea if it works, if the file size changes after uploading it. That means cache contents are being written.

[♥toyicebear]

Have seen a slow down in some other oscommerce sites at Godaddy lately, so it might actually be something at your host. Some adjustments in the hosting settings can be done to improve it abit at least.

 

To have something to compare with you can try running a local copy of your site on your pc using something like easyphp, wamp or xampp.

 

Or just get an "test" domain and an account at another hosting company and run a copy of the site there to compare.

[Steve80]

I did put blank files in there and they DID get updated... well.. actually they DON'T get updated .. a new file with a suffix gets created.  Not sure why the blank file is needed, but it for sure didn't work before I added that.  The also-purchased cache has NOT updated .. I guess that just means that page hasn't come up. I think I might have disabled that option years ago.  Categories and Manufacturers are both there.

 

I just tried a couple of pages again and they loaded in UNDER ONE SECOND.

And GoDaddy ASSURED me the problem was mine and not theirs.

 

I can see that the files are created, but I can't see that they are READ and used in the page create .. can I?  I'd have to change something in the cat tree to see the cache size change, I guess.

Steve

[Steve80]

Have seen a slow down in some other oscommerce sites at Godaddy lately, so it might actually be something at your host. Some adjustments in the hosting settings can be done to improve it abit at least.

 

To have something to compare with you can try running a local copy of your site on your pc using something like easyphp, wamp or xampp.

 

Or just get an "test" domain and an account at another hosting company and run a copy of the site there to compare.

 

I already have an account under another domain - it's still at GoDaddy, but under a different account.  I could not get that to work either.  I copied the entire site with FileZilla over to the other hosting area, then changed configure.php to reflect the new paths leaving the old db access info.  I thought it should work, but got a bascially blank page that only said 'contact your administrator' :x

 

Steve

[Jack_mcs]

 

Bots does NOT appear to the problem.  I have looked at page accesses and they are only running 500 or so per day and as far as I can see they ARE related to the store and show a pattern that I would expect from a prospective customer.  Nothing that looks like a bot to me.

 

You can't fully know who is accessing your site without looking at the access log. When I posted previously, it looked like the slowdown was constant but your later posts show that it isn't. That is almost always due to data skimmers. Those are sites that visit your site and just bounce from page to page, gathering data. One can easily do that for hours. If you have a number of them going at the same time, the site slows down. They won't always show up In who's online, if that is what you are looking at. You shouldn't block legitimate search bots. While they will add to the load, your site should be able to handle them.

 

Also, it could be godaddy, no matter what they say. They just announced in the last week that an on-going problem, that would cause something like this, was resolved. But there's no way to confirm that short of moving to a different host so it would be the last thing to consider if all else fails.

[Steve80]

I am now quite confident that it WAS GoDaddy's problem.  Unfortunately, I spent 3 days screwing up my site that was working quite well :x .

 

Where would I find 'Access Logs' ??  Don't see that they are available at GoDaddy.  I do have some data from StatCounter that gives me a good idea .. and as said before, I saw nothing that looked like bot or data skimmers .. btw .. just what IS a data skimmer?  What are they trying to gain?

 

I'm going to attempt to restore my site again back to the way it was before this mess all started - but that's a project for later.  At least at the moment it seems to be fully functional although I had to drop off a couple links that weren't working -- they weren't really NEEDED anyway.  What a disaster..  But I did learn a lot :D

 

As for legit search bots - is there a place where I can find all this 'agent' stuff that tells me who's good and who's bad .. and what really SHOULD be in the Robots and Spiders files?  I have no idea where to go looking for that .. google hasn't been much help.  I guess I'm not using the correct search terms.

Steve

[♥toyicebear]

Look at: http://addons.oscommerce.com/info/5914

[Hotclutch]

...

 

As for legit search bots - is there a place where I can find all this 'agent' stuff that tells me who's good and who's bad .. and what really SHOULD be in the Robots and Spiders files?  I have no idea where to go looking for that .. google hasn't been much help.  I guess I'm not using the correct search terms.

Steve

 

A lot can be done to your site to improve it's optimization. As for cache, you can download the cache file and open it with notepad to view the contents. A better way of checking is to install an error and query output tool. If cache is working your query count and time will drop significantly.

[Jack_mcs]

Where would I find 'Access Logs' ??  Don't see that they are available at GoDaddy.  I do have some data from StatCounter that gives me a good idea .. and as said before, I saw nothing that looked like bot or data skimmers .. btw .. just what IS a data skimmer?  What are they trying to gain?

 

As for legit search bots - is there a place where I can find all this 'agent' stuff that tells me who's good and who's bad .. and what really SHOULD be in the Robots and Spiders files?  I have no idea where to go looking for that .. google hasn't been much help.  I guess I'm not using the correct search terms.

Steve

Godaddy has a log section in their control panel. It varies with the account and type of control panel that offer. If yours is cpanel, which they are switching to, there is a "Raw Access Log" in the logs section. Otherwise, you need to ask them where to find it.

 

Data Skimmers are sites/people that retrieve information about your site. Some are probably hackers but most, in my experience, are just sites gathering data. They do this all over the web. Then they compile the data and sell it to whoever has an interest in it, like marketing companies. Some may be search engines, like Yandex, which is Russia's equivalent of google. They should be blocked completely since they are one of the worse ones. You may also see many hits from Amazon. They are checking prices of products they offer and probably seeing how popular they are. They are another one that should be banned.

 

There's not a list of good bots. There is a fairly common one of bad bots floating around the web. But list provided is for the robots file which is just asking them not to visit. If it is a well-behaved bad bot, they may honor that. But others, like Yandex, will completely ignore it, even though their site says they will honor it.

 

You should update the spiders file if there is a newer one but it won't help with data skimmers, nor will the robots file. The only fix for them is to block them. That can be an on-going job but I've found that once some of the major ones are blocked, the rest don't cause too much trouble,

 

If you host has a way to block countries from your site, the best solution is to just completely block any you know you won't sell to. If they don't ask them to install the GEO IP country blocking package (you'll need to look up the location). Some hosts will - some won't. You can block countries yourself via the .htaccess file but to do more than a few causes the file to be large enough so that it starts slowing down the speed too, so this fix may not be much better than the cure.

[Steve80]

Look at: http://addons.oscommerce.com/info/5914

OK . . I Looked at it and it sounds interesting.  Do you vouch for this addon?  Anyone else used it?  I don't plan to do anything with it until I get the store back where I want it though. 

 

I should mention here that previously I said I was having trouble getting my configure.php file(s) set up correctly.  I found a post that does a GREAT job of explaining what is expected for each of those directory lines - it cleared up a LOT of questions that I had and I'm sure it will help others.

http://www.oscommerce.com/forums/topic/193738-a-guide-to-the-configurephp-files/

One important issue was that the path to the admin directory should NOT be in the includes/configure.php file .. only in the one that's inside the admin directory. (seems obvious, but when I started the original install, I had NO CLUE what I was doing .. now I just have the slightest CLUE :D

Steve