The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Workarounds and Optimization' started by jols, Mar 6, 2010.

  1. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    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.
     
  2. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sticky On Internet
    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.
     
  3. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    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.
     
  4. madaboutlinux

    madaboutlinux Well-Known Member

    Joined:
    Jan 24, 2005
    Messages:
    1,052
    Likes Received:
    2
    Trophy Points:
    38
    Location:
    Earth
    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?

    Also what is the CPU usage when it happens?

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

     
  5. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    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
     
  6. fujipadam

    fujipadam Member

    Joined:
    Jun 25, 2009
    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    1
  7. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    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?
     
  8. Spiral

    Spiral BANNED

    Joined:
    Jun 24, 2005
    Messages:
    2,023
    Likes Received:
    7
    Trophy Points:
    0
    I can think of hundreds of reasons for your issue ....

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

    wizzy420 Well-Known Member

    Joined:
    Nov 13, 2007
    Messages:
    125
    Likes Received:
    2
    Trophy Points:
    18
    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.
     
  10. wizzy420

    wizzy420 Well-Known Member

    Joined:
    Nov 13, 2007
    Messages:
    125
    Likes Received:
    2
    Trophy Points:
    18
    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.
     
  11. Spiral

    Spiral BANNED

    Joined:
    Jun 24, 2005
    Messages:
    2,023
    Likes Received:
    7
    Trophy Points:
    0
    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 ...
     
  12. lehels

    lehels Well-Known Member

    Joined:
    Jul 10, 2006
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  13. rakuni

    rakuni Registered

    Joined:
    Apr 2, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    istanbul
    watch mysqladmin processlist

    top c

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

    Thats, helped to me. Very thanks..
     
Loading...

Share This Page