Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
Despite putting CloudFlare in place and turning on all the options to mitigate AI bots, my site is still having severe performance issues at times. When the site stops responding, in Task Manager, the CPU usage stays over 98% for extended periods. I'll generally see the W3WP.exe process in the 10-20% range for CPU usage and SQL Server in the 60-90% range. I have 4 virtual CPUs. CloudFlare shows that I'm serving over 10,000 requests in a 24 hour period from my server. 236,000 overall but most of those are mitigated by CloudFlare. If I put CouldFlare into "I'm Under Attack" mode, CPU usage falls to around 5% on average. So, I could just throw more hardware at it (and probably will) but I'm guessing doing some SQL index tuning could help a lot. Does anyone have recommendations for AbleCommerce SQL Index tuning that would help? Edited by user Wednesday, July 9, 2025 12:27:19 PM(UTC)
| Reason: added additional detail
|
|
|
|
|
|
Rank: Advanced Member
Groups: System, Administrators, Developers, Registered, HelpDesk
Joined: 10/29/2018(UTC)
Posts: 489
Thanks: 4 times
Was thanked: 36 time(s) in 35 post(s)
|
Hi, Before using SQL tuning, it may help to look at some common issues that I run across daily. Check to see if some of the data needs to be cleanup up. Tables like ac_PageViews and ac_ErrorMessages can grow huge when these attacks happen. It's safe to delete all the data in these tables. Also check ac_SearchHistory - I've seen cases where an SQL injection attack happens on the Search form. The next thing I would recommend is to defrag any tables that are greater > than 50% Run this query (see below) and then go through each table that meets this criteria. In SSMS, expand the table, select the Indexes folder and then right-click to Rebuild All. Once everything is defragged, I will usually shrink files and reduce the size of the transaction log file. You can shrink the database as well, but generally not good to do this too often. Also, good idea to make sure to backup the database first.
To check to see how fragmented the database is: Quote:
SELECT
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE
indexstats.database_id = DB_ID()
ORDER BY
indexstats.avg_fragmentation_in_percent DESC;
|
Thanks for your support!
Katie
Secure eCommerce Software and Hosting |
 1 user thanked Katie S for this useful post.
|
|
|
|
Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
Thanks Katie! That was very helpful. I have been monitoring my ac_PageViews and ac_ErrorMessages over the years and clearing those out every so often as they grew enormous. But, I'm not sure I've ever rebuilt the indexes and they were VERY fragmented. I ended up using the scripts in the first response of this StackOverflow question: https://stackoverflow.co...agmentation-for-a-databaAfter doing that, the site performance is the best it has been in a long time. The SQL Server CPU usage is still spiking near 90% but rather than staying up there for several minutes, it is only spiking for 1-2 seconds and then dropping back down to 5% utilization.
|
|
|
|
|
|
Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
And, nevermind... back to extended 100% CPU usage and pages taking 30 seconds to load if they load at all... Edited by user Wednesday, July 9, 2025 3:03:54 PM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Advanced Member
Groups: System, Administrators, Developers, Registered, HelpDesk
Joined: 10/29/2018(UTC)
Posts: 489
Thanks: 4 times
Was thanked: 36 time(s) in 35 post(s)
|
I'm sorry. This must be very frustrating. I haven't used Cloudflare myself, but I have heard that it can be tricky to configure properly. Quote:CloudFlare shows that I'm serving over 10,000 requests in a 24 hour period from my server. This is crazy. Do they offer any support that might help you to confirm the configurations are working properly? Do you know if the traffic is coming from one or two IPs that you can block? I'm hoping you are on a Cloud VM so you can temporarily add some CPU and memory to compensate until this is under control. AbleCommerce is surprisingly good at handling large increases in traffic, so if you can add more resources to the server, it should keep the site running. |
Thanks for your support!
Katie
Secure eCommerce Software and Hosting |
|
|
|
|
|
Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
I've blocked any abusive IPs already. I haven't been able to get a very good understanding of the traffic from CloudFlare yet as they don't make a lot of the info available (or I haven't figured it out very well).
I am on a virtual machine and will be going from 4 vCPUs to 6 vCPUs tonight to see what effect that has. I might also disable CloudFlare so I can get a better sense of the actual traffic to see if there are IPs I should be blocking.
|
|
|
|
|
|
Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
Increasing the virtual CPUs to 6 did not resolve the issue. Still getting bogged down. Ultimately, I think the problem is that we use the product template capabilities of AbleCommerce rather extensively as you can see on this page: https://www.crmagnetics.com/digital-transducersThis allows users that know what they are doing to filter down to exactly what they are looking for. But for bots, they just stupidly combine them all together and that results in a huge number of hits for every bot. Is there a way to prevent the crawlers from using the Product Finder links? Edited by user Thursday, July 10, 2025 12:09:46 AM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Advanced Member
Groups: Developers
Joined: 11/7/2018(UTC)
Posts: 309
Thanks: 21 times
Was thanked: 6 time(s) in 6 post(s)
|
I worked on a site that used the ShopBy and I had warned them there would be performance problems, but they didn't take the warning seriously. Since they have the source code, I changed one location in the code (don't remember where right now) from using "like" to using "=" in a sql query. I might have done something else that I don't remember, but the site is OK now.
|
1 user thanked judy e for this useful post.
|
|
|
|
Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
This is some kind of broad scale attack/exploit. We've now identified that any URL that includes the "srsltid" QueryString is part of the attack/exploit. e.g. /analog-transducers?ManufacturerId=1&ShopBy=33%2C36%2C57%2C61%2C80%2C73&srsltid=AfmBOoo0NnLuZE7mmaPsueCTCQO-lss4hr_awdKswDX8y3X4NRVTL89O In the last 4 hours, out of 73,000 requests, 70,000 included that QueryString parameter and most came from outside the US. This attack appears to be coming from over 70,000 different IP addresses. A little less than half are from Brazil. We added code to the Global.asax.cs to block those requests and now the server is fine. Edited by user Thursday, July 10, 2025 10:26:47 AM(UTC)
| Reason: more detail
|
|
|
|
|
|
Rank: Advanced Member
Groups: System, Administrators, Developers, Registered, HelpDesk
Joined: 10/29/2018(UTC)
Posts: 489
Thanks: 4 times
Was thanked: 36 time(s) in 35 post(s)
|
Imho, I don't this can be solved by code in AbleCommerce, database tuning, or hardware. You are under attack and this needs to be handled at the network level. Can you get in touch with Cloudflare support and have them help, or at least check your configurations? Quote:CloudFlare shows that I'm serving over 10,000 requests in a 24 hour period from my server. 236,000 overall but most of those are mitigated by CloudFlare. You probably have some baseline for number of requests before this all started. (The IIS logs would be a good source.) The whole point of using CloudFlare is to prevent this from happening to your website. They are the firewall. |
Thanks for your support!
Katie
Secure eCommerce Software and Hosting |
|
|
|
|
|
Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
Agreed. Just sharing an update on what we found the cause to be.
|
|
|
|
|
|
Rank: Advanced Member
Groups: System, Administrators, Developers, Registered, HelpDesk
Joined: 10/29/2018(UTC)
Posts: 489
Thanks: 4 times
Was thanked: 36 time(s) in 35 post(s)
|
Please keep us updated when you can. Thank you! |
Thanks for your support!
Katie
Secure eCommerce Software and Hosting |
|
|
|
|
|
Rank: Newbie
Groups: Developers
Joined: 4/17/2020(UTC)
Posts: 2
Thanks: 3 times
|
The solution for this particular case ended up being a Custom WAF (Web Application Firewall) rule in CloudFlare (Free Version) that sends any request that includes "srsltid=" in the QueryString and is from outside the US to a Managed Challenge. In the last 40 minutes, this has blocked 8000 requests.
But, I'm dealing with this type of thing (massive spikes in traffic) every month or two. Sometimes it involves blocking a AI or crawler bot based on the User Agent, sometimes blocking an IP range. This is the first time I've dealt with an attack that came from a massive number of IPs (over 70,000) a wide variety of countries, and could only be identified as an attack by the querystring and being outside the US.
|
|
|
|
|
|
Rank: Advanced Member
Groups: System, Administrators, Developers, Registered, HelpDesk
Joined: 10/29/2018(UTC)
Posts: 489
Thanks: 4 times
Was thanked: 36 time(s) in 35 post(s)
|
I think these AI attacks will be the new normal. We'll just have to take measures to block them.
Thanks for the info on Cloudflare. I'm still debating whether to implement Cloudflare or Cloud Armor for our hosting division. It will probably come down to cost.
Hope things will settle down for you now!
|
Thanks for your support!
Katie
Secure eCommerce Software and Hosting |
|
|
|
|
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
Important Information:
The AbleCommerce Forums uses cookies. By continuing to browse this site, you are agreeing to our use of cookies.
More Details
Close
