Sever load spikes related to MySQL access, please help with this.

This is a very serious question which really effects all of our cPanel servers from time to time. Briefly as follows:

What we are seeing is a very severe load spike e.g. the top utility will go from a load average of 2, to up over 260 or more in a very brief period.

During such an episode, if we are able to run the following at shell:

mysqladmin processlist

.. we see pages and pages, many hundreds of entries, looking similar to the following brief example (I have made the user IDs generic):

-------------
| 106 | usera_wrdp4 | localhost | usera_wrdp4 | Sleep | 10 | | |
| 107 | userb | localhost | userb_baby | Query | 0 | Opening tables | SELECT id, title, module, position, content, showtitle, control, params FROM jos_modules AS m LEFT J |
| 108 | userc_volume | localhost | userc_volume | Sleep | 18 | | |
| 109 | userc_volume | localhost | userc_volume | Sleep | 18 | | |
| 111 | userb | localhost | userb_baby | Sleep | 11 | | |
| 112 | userd_wrdp1 | localhost | userd_wrdp1 | Query | 0 | Opening tables | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (114,111,107,97) |
| 113 | userf_Foley | localhost | userf_dingle | Query | 5 | closing tables | SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' |
| 114 | userg_drupal | localhost | userg_drupaldb | Query | 2 | closing tables | SELECT data, created, headers, expire, serialized FROM cache WHERE cid = 'variables' |
| 115 | userb | localhost | userb_exchange | Query | 1 | closing tables | update ebp_n_day_u set i10 = i10 + 1 where user = '92' and size = '1' |
-------------

Nothing unusual with any of the individual entries, it's just that the normal 5 or 10 that we see, shoot up well into the hundreds, and in the "blink of an eye", i.e. very very fast.

Sometimes I will see typical Yahoo slurp bots, etc. in the individual access logs of the users/accounts (who's user IDs I see in the mysql process list.) And I will block some IPs by hand and then the load goes down. But here lately I have seen fewer and fewer bot entries in the access logs, and therefore we have fewer and fewer countermeasures that we can take.

Question - If these very severe spikes due to MySQL accesses are just due to web bots, then why aren't the other servers we have hit at the same time?

Question - If the sudden spike is due to some sort of DoS attack, then why, or how is it that only sites with MySQL script packages are hit? (e.g. mostly WordPress and e-commerce sites).

Question - If these seemingly attacks are just the result of sudden and coincidental spurts of traffic, then why is it that this will typically happen in the dead of night, when there is otherwise very little activity on the server?

Question - What possible countermeasures could we employ against these seeming attacks? We already use some very good firewall rules (CSF), and we use relatively up to date mod_security rules along with several other installed security measures. So what more can we do?

This last spike actually damaged one of our servers, and brought it down for several hours as we were performing an FSCK (so the damaged blocks on the drive could be repaired), so for sure, this is hurting our business.

And again, this is intermittently effecting all of our servers, the issue is not isolated to just one.

 

Thanks for your response, to your points/questions:

1 -- No, but I understand what you are saying. There must be 80 WordPress and perhaps hundreds of open-source scripts on this server. Which of course makes checking every one down-right tough. Which is especially true for WordPress because, so far at least I have not found a good method to determine which version of WP is installed.

Also, please keep in mind that the pattern here is that seemingly EVERY WordPress (and other similar) site is hit all at once, all on the same server. Also, at the same time, none of our other servers seem to be effected.

2 -- Yes, we have mod_security installed, with some relatively recently rules. With regard to limiting MySQL per user, Thanks, I'll look into this, should be a setting in my.cnf, yes?

3 -- Regarding comment spam, I don't think so. Not when the server load goes from 2 to 200 in a blink.

-------------

I have a sneaking suspicion we are being hit with something called a slowloris attack, see Slowloris HTTP DoS in which case, none of these common remedies for dealing with CPU spikes would work. I am researching this angle now.

Thanks again.

 

Hi, thanks so much for your reply.

Regarding:
Do the same databases are accessed and same queries are executed when there is a spike in the load average?

We do see the usual suspects of approx 6 sites, all WordPress, but we also see many others interdispersed in the mysqladmin processlist

In short, this seems to be an across-the-board effect for nearly any site on the server that has scripts connecting to mysql.

Regarding:
And just to take the attack issue out of the question, what is the output of the following command when the load spikes?

Yes, I've been running this line, and during the spike there are certainly more IP that connecting in the double-digit range, some as high as 120 or so. But most often the highest number (with the most number of connects) is blank for some reason, i.e. it shows no IP address. For example, here's that reading taken of the server, but the load is very low for the moment. Check out the strange 46 connects entry and the blank one below it:


netstat -alntp | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n
1 114.76.85.198
1 117.16.81.198
1 117.201.19.110
1 117.207.51.157
1 119.155.66.234
1 124.82.100.199
1 174.37.205.85
1 196.25.255.250
1 203.100.231.79
1 209.191.82.245
1 210.217.181.4
1 213.153.13.138
1 217.164.209.2
1 218.248.80.60
1 59.165.251.107
1 59.97.185.208
1 61.247.227.152
1 66.249.71.115
1 66.249.71.71
1 66.249.71.85
1 70.173.199.218
1 79.141.29.6
1 83.244.100.38
1 85.158.139.99
1 85.243.81.52
1 88.122.70.175
1 94.178.57.106
1 94.96.201.205
1 97.125.197.125
1 Address
1 and
2 218.248.80.59
2 38.99.97.185
2 61.11.102.224
2 92.251.255.17
3 115.109.226.29
3 69.107.105.99
3 98.239.115.107
4 122.167.82.104
5 59.100.117.187
6 59.94.14.158
8 123.238.45.4
8 78.151.49.100
9 41.215.117.173
9 86.40.234.78
35 127.0.0.1
46 0.0.0.0
99

 

Thanks very much Fuji. However I never installed eAccelerator.

I'm still curious about the top number/the one with the most connections showing up after that netstat command being blank where there should be an IP. Anyone know anything about this one?