Extremely high database load after upgrading to 3.8 - did i do something wrong? Need help ASAP

chadwixk wrote:

You maybe right, but one thing we know is that Google is not malicious, on the other hand this visitor from China might be.

I pretty sure this visitor from China is not finding our site via google as the URL he is on is not indexed by Google, and actually the URL he is currently on doesn't exist.

I'm reviewing security features in Azure web apps, but currently it is beyond my understanding.

Do you have a visitor from China in your "Online customers" list? Mine has been around continuously for a number of days, possibly weeks (possibly since the issue started - I wish I had monitored better). Normally he is near the top of the list. His IP address normally starts with 180.76

Yes! Have that Chinese visitor as well. Can maybe set up a firewall rule to block Chinese IP addresses in Azure?

Ah, no that was for a VM only where you can create a network security group, not with web apps....will have to investigate further.
chadwixk wrote:

The spec urls show on anchors even if you use Ajax filters i think. This is so google actually knows what to index. They navigate only with hrefs on anchors.

My findings about specs in google webmaster tools is the same. 2 of my sites with 2000+ products had over 1 million specs urls.

But, the chinese customer, what ip is it from? If the customer were a google or bing crawler it should use the precreated seo customer, and should not get a new one.

We did not have the same problem.

I know for a fact that google was the reason for my high dtu usage. I was logging every request made to productsearch and i could see 3 requests per second from google ips.

But i still dont know why this became a problem suddendly. Could it be 3.8,or did google suddendly start indexing our site a lot more? That said, 3 requests per second is not much, and nopcommerce sure require a lot of hardware if thats the case.

chadwixk wrote:

Let me know if you find away to block this in Azure. I will do the same.

lintho wrote:

The IP address at the moment is 180.76.15.153. Always at the top of the "Online customers" list. Right now below is the last page visited, but this changes with every refresh, as does the IP address (new refresh new IP address 180.76.15.23, 180.76.15.148, 180.76.15.24...and so on.

https://www.adventuregear.ca/gear?specs=107,951,1264

Can you provide the steps on how to "I was logging every request made to productsearch and i could see 3 requests per second from google ips". I will try and do the same so that we can compare.

Hi guys,

because I'm seeing that our plugin ( Ajax Filters ) is mentioned quite a few times, I would like to clear the thing up with it.

When a URL with "?specs=220,715,700" in it, is loaded, nopCommerce is using its own stored procedure ( from which the mentioned slow query is ) to load the products. Our plugin does not call its own stored procedure until any filtering is applied by the customers ( calling "?specs=***" would not trigger anything in our plugin ). Also, when you filter with the Ajax Filters plugin, we are appending the applied filters after the hashtag symbol ( computers#/specFilters=2m!#-!6, for example ), which is only client-side ( only on customer's machine ) and it cannot be indexed by the search engines.

Keep in mind that if you really have a search engine, which is refreshing your site a few times in a second, then it is normal that you have 100% DTU usage. In the end, requesting this slow query so frequently is really "expensive" and this is keeping the DTU usage at maximum levels. My suggestion is to blacklist those Chinese IPs, which the "search engine" is using.

Hope this helps!

Regards,
Hristian

Nop-Templates.com wrote:

But what if its google, bing or some other useful crawler? I know that google WANT to search the website about 3 times per second. Atleast according to my google webmaster tools. Probably because the website has over 2 million spec urls. If they want to get through every url, they have to crawl very often. How can the DTU go to 100% with "only" 3 requests per second?

And i know its not noptemplates fault, as i think i mentioned in a post earlier in this thread.

[quote=GreatAdventure.ca]

But, the chinese customer, what ip is it from? If the customer were a google or bing crawler it should use the precreated seo customer, and should not get a new one.

We did not have the same problem.

I know for a fact that google was the reason for my high dtu usage. I was logging every request made to productsearch and i could see 3 requests per second from google ips.



The IP address at the moment is 180.76.15.153. Always at the top of the "Online customers" list. Right now below is the last page visited, but this changes with every refresh, as does the IP address (new refresh new IP address 180.76.15.23, 180.76.15.148, 180.76.15.24...and so on.

https://www.adventuregear.ca/gear?specs=107,951,1264

Can you provide the steps on how to "I was logging every request made to productsearch and i could see 3 requests per second from google ips". I will try and do the same so that we can compare.[/quote

ProductService.cs

ProductSearch right after this line:

filterableSpecificationAttributeOptionIds = new List<int>();

do _logger.insertlog(whatever). you can include customer information in the logging message. You can also log _workContext.CurrentCustomer.IsSearchEngineAccount() in the message to see if its a crawler for seo.

You can do the rest, right?

chadwixk wrote:

I'm thinking about trying ModSecurity, does anyone have an opinion on ModSecurity? I'm surprised Azure web apps doesn't have a built in Firewall solution.


Nop-Templates.com wrote:

Thank you Hristian for clarifying. I agree, It's not a Nop-Templates issue, but an issue with a bot stuck on a Nopcommerce long running query. In my case that bot is a Chinese bot, whereas Lintho's bot is from Google. Thank you for the suggestion.