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

[jols]

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.

 

[mohit]

have you seen Apache status from WHM during that time to see if any particular IP is opening multiple or large number of connections ?

Have you checked those wordpress and other open-source scripts are updated, else they can give sleepless nights to a host easily.

implement mod_security, limit mysql connections per user.

it can also be comment/post/registartion spam to any user-site which spammers may be flooding.

 

[jols]

have you seen Apache status from WHM during that time to see if any particular IP is opening multiple or large number of connections ?

Have you checked those wordpress and other open-source scripts are updated, else they can give sleepless nights to a host easily.

implement mod_security, limit mysql connections per user.

it can also be comment/post/registartion spam to any user-site which spammers may be flooding.

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.

 

[madaboutlinux]

This does not seem to be an attack because attack don't cause the load to jump so high. It could be a badly written script OR a query that when accessed causing the load to jump high.

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

watch mysqladmin processlist

Also what is the CPU usage when it happens?

top c

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

netstat -alntp | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n

 

[jols]

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

 

[fujipadam]

I had the same issue - after searching around looks like the issue was with eAccelerator (see bug #381 (Deadlock in mm_do_lock for spinlock with PHP 5.3.1 under load) - eAccelerator - Trac)

This has been fixed in the latest version of eaccelerator so you can try upgrading or disabling eaccelerator (I chose the upgrade path and havent had any load spikes) Hope this fixes it for you

Fuji

 

[jols]

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?

 

[Spiral]

I can think of hundreds of reasons for your issue ....

The first place I would look is your /etc/my.cnf file though

 

[wizzy420]

You should probably be limiting total number of allowed connections to the SQL server, and total connections allowed by specific mysql user ID if you are not.

set-variable = max_connections=150
set-variable = max_user_connections=10

This will cap off connections at 150 total, and prevent any one MySQL user ID from opening more than 10 connections.

 

[wizzy420]

To help read over the SQL process list you can use a sort function to help spot users with a lot of queries, or long running queries.

echo "show processlist" | mysql | sort +1 -2

will sort by the SQL username.

echo "show processlist" | mysql | sort +5 -6 -n | tail -n 20

Will sort by total time the process has taken, with higher numbers at the bottom of the output. Tail will then display the bottom 20 lines. Keep in mind those may be long running due to your load being 260, and not the cause. But then again .... they may be the cause.

You can also use "show full processlist" to get the entire query, but that can muddy the output. Usually that comes when you think you've found the offender.

 

[Spiral]

This is kind of indirectly related but also a curiosity ....

How much memory do you have available on your server?

Insufficient memory is the #1 biggest plague to MySQL performance ...

 

[lehels]

You should also check the specific dbs within phpMyAdmin and check for the Overhead-ed tables, then index/optimize them. See also the size of each tables and you should find out what is happenning there.

Cheers,
Lehel

 

[rakuni]

watch mysqladmin processlist

top c

netstat -alntp | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n

Thats, helped to me. Very thanks..