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.
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.