Site de-activated by Host

[kenkja]

Hi all, my site has been de-activated by the host as it is/was causing performance problems for their server

 

heres a quote passed from their admin team to the support assistant I was talking to

 

Customer is highest mysql abuser on the server and should probably do something to fix it. They have a lot of slow queries running as well as using a HUGE amount of MySQL resources. They need to optimize their queries, index their site and maybe look into caching.

 

example of slow queries:

# Fri Nov 30 10:49:59 2012

# Query_time: 2.819797 Lock_time: 0.000145 Rows_sent: 0 Rows_examined: 27831

SET timestamp=1354297799;

select

c.categories_id

FROM

products p,

products_to_categories pc,

categories c,

categories_description cd

WHERE

p.products_id = pc.products_id AND c.categories_id = pc.categories_id AND

p.products_status = '1' AND

c.categories_id = 986 AND

c.categories_id = cd.categories_id AND

cd.language_id = '1' LIMIT 1

 

They gave advice on the lines of

 

1. Is the database very large, full of old logs etc, try decreasing database size. So I asked if it was large, answer no but there is a problem with the manner in which you use it.

 

2. Are you getting a lot of traffic, trying implementing caching to the your web pages - answer no usually 2500 to 3000 vistors per month

 

3. Problems like this may well be caused by poorly optimised mysql queries - does not seem likely as the site is v2.3.3 with only well regarded add ons

 

4. Do you have plug ins which are causing extra mysql queries - Not sure but I think not

 

Anyway so decided to have a look at that slow query

 

27831 is the amount of lines in the products table

 

Category 986 is a parent category which represents a type of motor cycle, it has 11 sub categories each of which represents a year in which the bike was manufactured. 8 of these have not yet had any products added. The 3 earliest years have 26, 42 and 40 child categories, each representing an area of the motor cycle, for example all the components which make up the "Front Wheel". All together these 108 categories hold 4240 products, but they are not necessarily individual lines from the product table, as components may have been used in more than year, so a product id may be linked to numerous categories (41618 lines in products to categories table)

 

Is it possible that the "manner in which i am using the database" that there support agent mentioned is that in order to assimilate and display all the items for category 986, the mysql is having to trawl through to much info to display quickly, I don't know enough about it to say, but I sort of find that difficult to believe.

 

Probably should also mention this change to product_listing.php, it displays an image above the actual product listing, which in the example of the "Front Wheel" would be the component diagram (parts fiche), each component having a reference number which becomes the product_model, so for example if the tyre is shown as part (1), the model number would be say "Gas Gas EC 2003 Part Ref (01)" These images are usually 940p wide by varying heights (the site has fluid grid 1280 wide)

 

In the actual product listing theres is no main_image, just the product_model, product_name, price and buy now button.

 

Heres the code

 

<?php

$category_query = tep_db_query("select cd.categories_name, c.cat_bigimage from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and cd.categories_id = '" . (int)$current_category_id . "' and cd.language_id = '" . (int)$languages_id . "'");

$category = tep_db_fetch_array($category_query);

echo tep_image( DIR_WS_IMAGES . $category['cat_bigimage'], $category['categories_name'] );

?>

 

It sits just after the first lines

 

$listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

?>

<div class="contentText">

 

Any clues folks

 

thanks

 

Ken

[kenkja]

just thought I should add (tried to edit above but could not)

 

1. host is Just Host and is a shared server

 

2. each product_description has at least some and somethimes quite a lost of html, for line breaks, font size and colour changes

 

3. the slow loading is always worse after i have uploaded new products or as was the case yesterday when i added 980 odd lines to products_to_categories (in this case they were not to category 986 or any of its child categories), I invariably do this by importing an excel csv file to the relevant table

[kenkja]

[30-Nov-2012 10:28:56] PHP Warning: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User myusername already has more than 'max_user_connections' active connections in /home4/mysite/public_html/includes/functions/database.php on line 19

 

According to cPanel error log, this happened about 30 times yesterday, may just be coincidence but was just after uploading the csv file to products_to_categories

 

and prior to this last time I have the same error message was 22nd August

[Jack_mcs]

The last error means the account exceeded the limit for connecting to the database. That, and the statement by your host of, "Do you have plug ins which are causing extra mysql queries" indicates this is due to your account being bombarded by search bots. Take a look at your raw access log and you will most likely see many entries for the same IP's in a short amount of time. If so, that is what is causing the problem.

[Juto]

Hi, Jack that will be the cause of the problem. I had the very same issue a couple of years ago. It turned out to be a crawler, sorry I can't remeber the name, but after stopping that one using htaccess ... "deny from ...", I haven't had any issues since then. So the log will definetly show the IP and the name of that bot.

 

Sara

[Guest]

TIP: A change to a better hosting provider would eliminate such issues.

 

 

 

Chris

[Jack_mcs]

Sara - Yes, it usually fixes such problems though t suspect there will be more than one search bot, especially at this time of the year.

 

Chris - If this is the problem, changing hosts won't make any difference because they are just visiting the site. There's nothing a host can do about it, short of banning them, which most won't due to the time involved.

[Guest]

@@Jack_mcs

 

Chris - If this is the problem, changing hosts won't make any difference because they are just visiting the site. There's nothing a host can do about it, short of banning them, which most won't due to the time involved.

 

A good hosting provider has unlimited bandwidth allowances and although we all know there is no such thing as it truly being unlimited, I can name SEVERAL that I deal with that I have never received overage of bandwidth warnings from.

 

 

Chris

[Jack_mcs]

It's not a matter of having unlimited bandwidth. These aren't really intentional attacks but the result is the same as a DDOS attack. They can bog a server down because they are visiting hundreds of pages in minutes. I've seen as many as twenty such bots active at one time. On a shared server, if there are enough of them, they can make it almost un-useable, which is why hosts will ban sites for it.

[kenkja]

Hi all

 

Thanks for ur input, sorry have not replied earlier but was birthday yesterday, so went to a pre arranged party, which under the circumstances could have done without, but anyway was well good.

 

@@DunWeb - point taken - you pay for what you get and to be honest host was very cheap and have changed there terms of service during about 2 years into the five years signed up for, my guess is they have realised that what they were originally offering is/was not commercially viable, hence terms have become more restrictive.

 

@@Jack_mcs & @@Juto - I think you may be correct about the bots, we had recently run a google add for an end of season bike, which obviously increased traffic and I had been from admin watching quite closely the who's online, on a couple of occasions I found well over 100 guest online, but closer examination in both cases, showed that the vast majority of the ip's where the same. In the first case I did an ip check and traced it to, apparently an "email server" in California, which seemed a little odd to me as it was looking at least 80 pages on the site.

 

I did not attempt to trace the second IP which appeared numerous times, as was not really sure what I could do with the info when I found it ( as in the first occasion)

 

Anyway I paused the google add, on the evening of 28th Uk time, all these incidents of "max-user" occurred around between 5/6 o'clock (pm) uk time on the 30th. According to Google analytics throughout the 30th only had 60 visitors, 48 of which were unique, and the pageviews were only 409. For sure some of these visitors/page views were me checking my upload of existing product_ids to the new category_ids to see how many many products where listed in each new category and checking my spreadsheets to see how many additional I needed to upload to complete the category.

 

So unless I am missing something here that you "brighter" guys than me know, I keep coming back to the csv import to products_to_categories, as I dont appear to have been suffering a bot attack at the time.

 

Perhaps I should be optimising the database after each upload of this type before I browse the site to check the number of products in each new category.

 

Quote from host again "your database is not too big just the way you are using it", keeps coming back to me all the time, that may well be a "red herring" but aside from my changes to product_listing.php this is (i think) a reasonably sound osc 2.3,3 install

 

thanks again

 

Ken

[kenkja]

Hello Folks

 

Host has re-activated account, I told them had run repair and optimise database in phpmyadmin, they say the database is now "significantly" reduced in size, so good to go.

 

Given that I think we have only about 20% of the products we need listed, probably time to start search for new host.

 

@@Jack_mcs will check the raw access log as you suggested, I guess you and @@Juto mean add deny from in catalog .htaccess

 

thanks

 

ken

[♥Biancoblu]

Hi Ken

 

not sure if you completely fixed your problem yet?

Although I don't know your host nor your site, it is worth remembering that some hosts will make up any stories in order not to admit that their servers are overcrowded therefore unable to cope at busy times. Then they will try to force you to get an expensive dedicated server plan.

 

This happened to me as well, and without going into boring details, I'll tell you that I went from a host that claimed my site (as it currently is) was constantly crashing their servers to another host that claims (to this day) that my site is amongst those using the less resources. Note that I have made no changes whatsoever between my move from the old host to the new host. Food for thought.

 

I tend to agree with the fact that you often pay for what you get, although that isn't always a guarantee either.

However moving to a new host might eventually be a good idea.

Alternatively, you could move to a dedicated server but that comes at a cost, so you have to determine if you really need it in the first place.

 

There's a caching option in admin, configuration>cache, you could try turning it on.

 

Have a look at FWR Media's debugging addon: http://addons.oscommerce.com/info/7217

 

This thread contains some useful tips as well: http://www.oscommerce.com/forums/topic/119077-a-store-speed-optimization-in-progress/

 

If you see some weird bots bombarding your site, ban them in .htaccess

SetEnvIfNoCase User-Agent "Bad Bot Name" bad_bot
order Allow,Deny
Allow from all
Deny from env=bad_bot

 

 

If Google's crawl rate on your site is too high, you can lower it in Google Webmaster Tools.

http://support.google.com/webmasters/bin/answer.py?hl=en&answer=48620

 

 

Hope that helps you some, I am no expert, the above is just some info I came across whilst researching the problem at the time I had issues with my ex host.

[Jack_mcs]

Hello Folks

 

Host has re-activated account, I told them had run repair and optimise database in phpmyadmin, they say the database is now "significantly" reduced in size, so good to go.

 

Given that I think we have only about 20% of the products we need listed, probably time to start search for new host.

 

@@Jack_mcs will check the raw access log as you suggested, I guess you and @@Juto mean add deny from in catalog .htaccess

Running the repair and optimize on the database may help a little but I doubt it is the problem in this case. If it is, it will just need it again soon because databases almost always need to be optimized. You can install the Database Optimizer contribution to do that automatically. But, more likely, the bots that were hitting the site just took a break. When they, or others, come back, you'll have the same problem. The server keeps logs of the IP's visiting it. That is where you need to look. Ask your host how to access them. They can be difficult to interpret if you are not used to them but that is where you need to look.

[kenkja]

@@Biancoblu

 

Hi Isa you okay.

 

Like you I dont think that the problem is resolved at all, and I'm convinced it will come back when I upload the next tranch of products. I had already been up the dedicated server track, gone to their upgrade page, but unless I was just too stressed by the whole downtime thing, they no longer offer that option.

 

I have some ideas about moving to another host which I am investigating.

 

Re the caching option, thanks, have turned it on in admin.

 

FWR's debugging downloaded, will check out, thanks

 

Thanks Isa

 

Re a store spped optimization in progress, had already been there, but again was prob too stressed to find the posts that related to 2.3.1/2/3 so now we are reactivated will go back

 

Presume you mean put the bad_bot code in catalog/.htaccess

 

Re the google crawl rate, how do I determine if the crawl rate is too high

[kenkja]

@@Jack_mcs

 

I agree with you, I dont think the repair optimize was the problem, but for future reference will optimise after each upload of atranch of new product_id's or products_to_categories links.

 

Will try my best to ban those bots tonight !!!

 

thanks

 

ken

[♥Biancoblu]

@@kenkja

 

I'm fine thanks Ken

 

 

 

 

Presume you mean put the bad_bot code in catalog/.htaccess

 

If your store is in www.yoursite.com, then put it in www.yoursite.com/.htaccess, if your store is in www.yoursite.com/catalog, then put it in www.yoursite.com/catalog/.htaccess

 

 

Re the google crawl rate, how do I determine if the crawl rate is too high

 

If you look at your raw access log files in cpanel, you can see who accessed the site at the time the site was busiest or at the times mysql hits the max number of connections, if you see the google bot was there, it is possible that the google bot, in addition to the bad bots trolling the site at the time was just too much for the server.

So it can help to lower the google bot crawl rate temporarily whilst you fix the other issues, so you have less load on the server, and hopefully less crashes.

[kenkja]

Okay folks, thanks very much for help so far and the pm's

 

Now looking at alternative host, but there are issues I'm unsure about.

 

Accordingly to existing host cPanel an using 2,394.53 Mb of webspace, I presume if the site was uploaded like for like to another host this would be the same number ??

 

How do I find out how much bandwith my site uses ??

 

ken

[Jack_mcs]

For the disk space, my guess is that alarge amount of the is in emails. Look at the default account (at the very bottome of the email section). If there is any figure there, it is wasted space. You can also use the disk usage tool to see where the disk is being used. Bandwidth usage is shown in the left column or you can use the AWStats program (or one of the other log programs) to view past usage.

[♥joli1811]

Hi ken,

 

have a reseller account give a guy a while ago.......

 

a site bandwidth 200000 MB he wasn't doing so many sales but getting a lot of hits which was good for internet presence and his business...

 

so there should be no problem with band width normally.

 

2,394.53 mb is a lot of space disk space probably well over 2.3 gigabyte how come you are using so much space??

 

last site I done was 1300 product few hundred mega byte.

 

Videos / flash (live streaming) can take a lot of space.....

 

Disk Space Usage is left hand side cpanel

 

Monthly Bandwidth Transfer is left hand side cpanel

 

would suggest average oscommerce site 1000 products maxes out at 300 mb

 

happy to be corrected on this just my opinion

 

John

[kenkja]

@@Jack_mcs @@Juto @@Biancoblu

 

I've been watching the error logs each day since we went back online.

 

Today is the first day we've had any instances of the more than max user mysql error message.

 

Looking at the Raw Access log for today, we have had 4747 "accesses", the majority of which are bots. msnbot. bingbot & googlebot account for a great percentage of the raw access log.

 

So, i think, as you predicted the problem is with the bots.

 

The 3 mentioned above, unless I am missing the point altogether, are "friendly" bots, if so is there a way I can suggest to them that they don't all visit on the same day ?? or are we saying that a "better" host would cope with them no matter what

 

There were some others which I have now blocked via .htaccess

 

thanks again

 

ken

[kenkja]

@@Jack_mcs & @ joli1811

 

regarding the site space, yes it is very large, but having deleted the test site now down to 1,374.32MB, which I know is still large.

 

I don't think that email accounts for this as it has 117.27MB, I think the issue may be the amount of supporting documents we have uploaded and perhaps quite a number of fairly HQ images.

 

anyway will be investigating further

 

thanks

 

ken

[burt]

Ken, my host moaned at me for exceeding my limit (of 100MB). It turned out that the Awstats were taking up over 90MB !

 

You might want to check to ensure that your total is without server files that should not be taken into account when totalising the usage.

[Jack_mcs]

So, i think, as you predicted the problem is with the bots.

 

The 3 mentioned above, unless I am missing the point altogether, are "friendly" bots, if so is there a way I can suggest to them that they don't all visit on the same day ?? or are we saying that a "better" host would cope with them no matter what

 

There were some others which I have now blocked via .htaccess

I wasn't referring to the normal search bots, like google, but it sounds like you figured that out. For those, you can add the following to your robots.txt file, adjusting the numbers as you wish

Crawl-delay: 2
Request-rate: 1/2
Visit-time: 0300-0900

[♥joli1811]

@@Jack_mcs & @ joli1811

 

regarding the site space, yes it is very large, but having deleted the test site now down to 1,374.32MB, which I know is still large.

 

I don't think that email accounts for this as it has 117.27MB, I think the issue may be the amount of supporting documents we have uploaded and perhaps quite a number of fairly HQ images.

 

anyway will be investigating further

 

thanks

 

ken

 

Forget who it was on the forum who give the tip but

 

http://www.smushit.com/ysmush.it/

 

is an interesting tool for optimizing image size worth checking

regards

 

Joli