|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
We've been experiencing extremely slow response times on our forum for the past few days. At first I thought it was a routing issue, but now I'm not so sure. This is what our hosting company just posted to an ongoing ticket discussing thiese issues. Based on the "mtr" output you shared, the network connection appears stable and healthy, with no significant packet loss or latency issues. This suggests that the load issue is likely originating from the server itself. Upon further investigation, we noticed that the MySQL processes running on the server are contributing to the high load. It is recommended to optimize your database, as inefficient or long-running queries could be straining server resources. Please consult with your developer to review and optimize the database performance. Below is a screenshot showing the active MySQL processes contributing to the load. https://hw-screenshots.sea-proxy.wi...b48560c5-63a2-4334-910b-e8cfc50d9495.pngOnce these optimizations are applied, we advise monitoring the server's performance to ensure improvements. Let us know if you need assistance with any specific steps, and we’ll be happy to help. Feel free to reach out if you have further questions or concerns. Thank you for your continued patience and cooperation. So, I thought, this is easy to shoot down. I'll just load the home page, and when it takes forever, that's clearly not a mysql issue. So, I checked the home page, and a number of other static pages on the site. They all loaded instantly. Hmm... So, I ran top: top
top - 15:05:24 up 37 days, 21:14, 2 users, load average: 48.02, 48.80, 40.91
Tasks: 293 total, 11 running, 282 sleeping, 0 stopped, 0 zombie
%Cpu(s): 47.2 us, 52.6 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.2 hi, 0.0 si, 0.0 st
MiB Mem : 15718.4 total, 435.3 free, 2854.8 used, 12428.3 buff/cache
MiB Swap: 4000.0 total, 3495.8 free, 504.2 used. 12473.1 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2515 mysql 20 0 5019624 340772 12752 S 687.4 2.1 47144:23 mysqld
3070788 root 20 0 830548 563092 6004 R 16.2 3.5 0:16.22 awstats.pl
3061199 apache 20 0 762380 57404 21096 R 15.6 0.4 0:47.74 php-fpm
3066945 apache 20 0 762316 54616 19392 R 14.9 0.3 0:40.55 php-fpm
3066949 apache 20 0 762300 54704 19260 R 14.9 0.3 0:40.76 php-fpm
3066967 apache 20 0 761716 53916 19260 R 13.2 0.3 0:42.32 php-fpm
3061057 apache 20 0 755928 51632 21560 R 7.9 0.3 0:49.49 php-fpm
3061519 apache 20 0 759008 55248 25780 R 7.3 0.3 0:46.03 php-fpm
3061525 apache 20 0 752108 45632 21368 R 5.6 0.3 0:46.00 php-fpm
3061183 apache 20 0 750060 44752 21112 R 5.0 0.3 0:46.16 php-fpm
3061201 apache 20 0 748016 42852 21688 R 4.6 0.3 0:45.79 php-fpm
3061053 apache 20 0 733680 28952 20928 S 0.7 0.2 0:50.32 php-fpm
3070442 apache 20 0 2505452 16844 8840 S 0.7 0.1 0:00.74 httpd
907006 root 20 0 96112 3420 3304 S 0.3 0.0 0:35.54 master
3064357 root 20 0 0 0 0 I 0.3 0.0 0:02.52 kworker/1:1-events
3069873 apache 20 0 2505452 17644 8908 S 0.3 0.1 0:00.75 httpd
3070009 apache 20 0 2505452 17716 8916 S 0.3 0.1 0:00.72 httpd Then I restarted mysql and ran top again. ]# systemctl restart mysqld
[root@ded602 ~]# top
top - 15:17:08 up 37 days, 21:26, 2 users, load average: 36.99, 41.69, 41.16
Tasks: 291 total, 16 running, 275 sleeping, 0 stopped, 0 zombie
%Cpu(s): 42.4 us, 49.2 sy, 0.0 ni, 8.2 id, 0.0 wa, 0.2 hi, 0.0 si, 0.0 st
MiB Mem : 15718.4 total, 918.5 free, 2349.1 used, 12450.8 buff/cache
MiB Swap: 4000.0 total, 3809.3 free, 190.7 used. 12982.6 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3070998 mysql 20 0 5007348 499576 38548 S 665.6 3.1 73:49.84 mysqld
3071117 apache 20 0 750064 43064 19436 R 5.3 0.3 0:14.16 php-fpm
3061199 apache 20 0 748148 42212 21144 S 5.0 0.3 1:02.83 php-fpm
3066950 apache 20 0 748016 40772 19948 R 5.0 0.3 0:54.01 php-fpm
3066966 apache 20 0 748012 40884 19820 S 4.6 0.3 0:57.91 php-fpm
3061198 apache 20 0 745968 40736 21228 R 4.0 0.3 1:10.06 php-fpm
3061525 apache 20 0 745964 41180 21416 R 4.0 0.3 1:03.12 php-fpm
3066951 apache 20 0 745964 38972 19692 R 4.0 0.2 0:57.23 php-fpm
3071094 apache 20 0 748012 39752 19948 R 4.0 0.2 0:16.12 php-fpm
3066963 apache 20 0 745972 37960 19624 R 3.6 0.2 0:57.62 php-fpm
3067035 apache 20 0 745964 39580 19628 R 3.3 0.2 0:56.33 php-fpm
3071099 apache 20 0 745964 39196 19248 R 3.0 0.2 0:13.42 php-fpm
3066952 apache 20 0 743916 36092 19752 R 2.3 0.2 0:56.54 php-fpm
3066967 apache 20 0 743916 36612 19948 R 2.3 0.2 0:57.90 php-fpm
3067037 apache 20 0 743920 36200 19948 R 2.3 0.2 0:56.64 php-fpm
3071118 apache 20 0 743920 37388 19504 R 2.3 0.2 0:15.20 php-fpm
3071084 apache 20 0 739824 33800 20012 R 1.7 0.2 0:15.04 php-fpmCan someone smarter than me tell me I have a problem with mysql? How can I be using 665% of cpu? That doesn't make any sense to me. How do I optimize the database? And would that actually make a difference?
|
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
I just closed our forum. Stopped mysqld. Then restarted it. Then reopened the forum. The %CPU is varying between 1% and 790% (using top). I've also been noticing a much higher than normal view count on our website. Could this be some sort of a denial of service attack?
|
|
|
|
|
Joined: Jun 2006
Posts: 16,378 Likes: 129
|
|
Joined: Jun 2006
Posts: 16,378 Likes: 129 |
Optimization could help with a high load, but not if your being attacked it won't do a thing, if you have a cdn like Cloudflare you can lookup traffic by country then can any offenders; I just had to do this the other day for a client with Singapore, Hong Kong, and China..
Your host could be helpful here to determine if you're being attached if you don't utilize a cdn
|
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
I have an active open ticket with our hosting company. I think I need to look at where our traffic is coming from.
|
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
I blocked this entire network. NetRange: 47.74.0.0 - 47.87.255.255
CIDR: 47.76.0.0/14, 47.80.0.0/13, 47.74.0.0/15
NetName: AL-3
NetHandle: NET-47-74-0-0-1
Parent: NET47 (NET-47-0-0-0-0)
NetType: Direct Allocation
OriginAS:
Organization: Alibaba Cloud LLC (AL-3)
RegDate: 2016-03-17
Updated: 2017-04-26
Ref: https://rdap.arin.net/registry/ip/47.74.0.0 Their IPs were responsible for a tripling of our traffic, and I'm sure I'll find all sorts of bogus queries in the logs. grep -c 47.76.99.127 /var/log/httpd/stovebolt/ssl_access_log
382974 # grep -c 47.76.209.138 /var/log/httpd/stovebolt/ssl_access_log
382905 And, our performance has returned to normal.
Last edited by Baldeagle; 10/18/2024 5:58 PM.
|
|
|
|
|
Joined: Aug 2004
Posts: 480
Addict
|
|
Addict
Joined: Aug 2004
Posts: 480 |
I blocked this entire network. NetRange: 47.74.0.0 - 47.87.255.255
CIDR: 47.76.0.0/14, 47.80.0.0/13, 47.74.0.0/15
NetName: AL-3
NetHandle: NET-47-74-0-0-1
Parent: NET47 (NET-47-0-0-0-0)
NetType: Direct Allocation
OriginAS:
Organization: Alibaba Cloud LLC (AL-3)
RegDate: 2016-03-17
Updated: 2017-04-26
Ref: https://rdap.arin.net/registry/ip/47.74.0.0 Their IPs were responsible for a tripling of our traffic, and I'm sure I'll find all sorts of bogus queries in the logs. Did you pull that IP range from the server yourself or did the ISP provide you with that after opening up a ticket with support?
|
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
Did you pull that IP range from the server yourself or did the ISP provide you with that after opening up a ticket with support? I pulled that from our httpd logs after seeing the hits in awstats. That's what the greps were in my post.
|
|
|
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165 |
Did you pull that IP range from the server yourself or did the ISP provide you with that after opening up a ticket with support? About 2 weeks ago, i had to toss this in to my .htaccess file to get the "10-hits every second" to stop. They were hitting my servers for several days. About a week later, I commented out the deny, and they instantly showed up again from Singapore. so i uncommented it to block them again. The bad part about this, is that there is a huge server in Texas Colorado that also begins with 74 47, so their traffic is also blocked. But I'm fine with that  <Limit GET HEAD POST>
order allow,deny
allow from all
# Singapore
deny from 47.0.0.0/8
</Limit>
Last edited by isaac; 03/26/2025 10:31 PM.
|
|
1 member likes this:
Conrad |
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
The Texas server begins with 74? Or 47? If it begins with 47, it's part of the Alibaba cloud, so blocking it shouldn't cause undue problems. They hit us so hard the site was timing out at times. I wonder if the huge networks that are load balanced realize what's going on? Or if they even care. For us little guys, it's a huge problem.
Here's my .htaccess file (part of it).
# Block via IP Address
#Allow from 127.0.0.1
#Deny from 212.235.15.153
#Deny from 156.59.198.136
#Deny from 212.29.233
#Deny from 111.225.149
#Deny from 47.76.35.19
#Deny from 217.113.194
#Deny from 101.44
Deny from 47.76
Deny from 47.89
Deny from 47.74
The commented out ones were previous abusers. If they come back, I uncomment them.
|
|
1 member likes this:
Conrad |
|
|
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165 |
you have a Deny from 47.76. in your list twice. the commented-out first one is more specific. the second is broad ranged. lol @ Allow from 127.0.0.1 
|
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
Yeah, your block takes out an entire class A network. Mine don't even get the entire Alibaba network. Just the class Bs that were giving us fits.
|
|
|
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165 |
Yup. I've looked, and I'm not too worried about it.
|
|
|
|
|
Joined: Aug 2004
Posts: 480
Addict
|
|
Addict
Joined: Aug 2004
Posts: 480 |
Here's my .htaccess file (part of it).
# Block via IP Address
#Allow from 127.0.0.1
#Deny from 212.235.15.153
#Deny from 156.59.198.136
#Deny from 212.29.233
#Deny from 111.225.149
#Deny from 47.76.35.19
#Deny from 217.113.194
#Deny from 101.44
Deny from 47.76
Deny from 47.89
Deny from 47.74
The commented out ones were previous abusers. If they come back, I uncomment them. Currently my .htaccess file is completely blank but in light of unusually high data use I'd like to try blocking a few countries as a start. Is it just the "Deny from xxx.xxx.xxx.xxx" lines that I need to add to that file and nothing else? And then turn off the forums (mysql too?), reboot the server, and turn the forums back on again? Will this work considering I'm on Apache 2.2.27? Also, is there an easy way to encompass all or at least most IP addresses from the likes of China/Russia/India?
|
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
Deny x or Deny x.x or Deny x.x.x or Deny x.x.x.x
You don't need anything else in the file. I would caution against blocking Class A networks (Deny x). You're almost guaranteed to block something you didn't mean to.
|
|
|
|
|
Joined: Aug 2004
Posts: 480
Addict
|
|
Addict
Joined: Aug 2004
Posts: 480 |
Thanks, certainly don't want to block more than is necessary. What's the quickest way to encompass the likes of China/Russia without inadvertently blocking access to any Western countries?
|
|
|
|
|
Joined: Oct 2007
Posts: 478 Likes: 13
Addict
|
|
Addict
Joined: Oct 2007
Posts: 478 Likes: 13 |
I've never researched that. You might try asking Grok or ChatGPT.
|
|
|
|
|
Joined: Aug 2004
Posts: 480
Addict
|
|
Addict
Joined: Aug 2004
Posts: 480 |
Ok, no prob. I thought someone might have already tried blocking these countries using .htaccess as opposed to CloudFlare where you can select the country by name...
|
|
|
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165
|
|
Joined: Apr 2004
Posts: 1,997 Likes: 165 |
If you want to block the entire class a network, add the following to your .htaccess
<Limit GET HEAD POST>
order allow,deny
allow from all
# Broad violator range
deny from 47.0.0.0/8
</Limit>
If you want be more specific, add the following to your .htaccess
<Limit GET HEAD POST>
order allow,deny
allow from all
# Singapore common violator ranges
deny from 47.74
deny from 47.79
deny from 47.82
deny from 47.84
deny from 47.88
deny from 47.89
</Limit>
If you want be more near exact in what you limit, add the following to your .htaccess
<Limit GET HEAD POST>
order allow,deny
allow from all
# Singapore specific
deny from 47.74.128.0/17
deny from 47.79.0.0/20
deny from 47.79.48.0/20
deny from 47.79.96.0/19
deny from 47.79.192.0/18
deny from 47.82.0.0/18
deny from 47.84.0.0/16
deny from 47.88.128.0/17
deny from 47.89.88.0/24
deny from 47.89.91.0/24
deny from 47.128.0.0/14
deny from 47.235.1.0/24
deny from 47.235.4.0/24
deny from 47.235.6.0/23
deny from 47.235.19.0/24
deny from 47.235.21.0/24
deny from 47.235.30.0/24
deny from 47.236.0.0/15
deny from 47.241.0.0/16
deny from 47.245.64.0/18
deny from 47.246.1.0/24
deny from 47.246.39.0/24
deny from 47.246.58.0/24
deny from 47.246.62.0/23
deny from 47.246.106.0/24
deny from 47.246.157.0/24
deny from 47.246.158.0/23
deny from 47.246.160.0/20
deny from 47.246.196.0/22
</Limit>
source for country list IP2Location - Block Visitors by Country https://www.ip2location.com/free/visitor-blockerTO AVOID POSSIBLE HOST VIOLATIONS, DO NOT ADD TOO MANY DENY REQUESTS
|
|
|
|
3 members (Gismo der erste, Mender, 1 invisible),
135
guests, and
140
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
