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.

How to find MySQL abusers?

Discussion in 'Database Discussions' started by Rahxie, Mar 4, 2010.

  1. Rahxie

    Rahxie Member

    Joined:
    Aug 21, 2005
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    I'm having a problem with MySQL always using over 10 - 15% CPU. On other servers the CPU usage for MySQL is around 0.8 - 1.0% which is much better.

    Is there a way to find the SQL user causing this high CPU problem? I've tried watching the SQL processes but as the connections to the database come and go, it's hard to find the exact user causing this high load.

    The CPU usage for MySQL is always showing as red under Daily Processes. I've optimised it as much as possible but still can't seem to figure this out.

    Thanks in advance.
     
  2. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    Please note that the Daily Process Log in WHM is an average for the current day; the percentage reported may not indicate a severe problem or any problem at all depending on the exact activity taking place and how it may be affecting overall server load.

    As a start I would ensure to regularly monitor MySQL processes using a third-party program such as "mytop" and or by manually checking via a command like the following examples:
    Code:
    # mysqladmin proc stat
    # watch -n 5 'mysqladmin proc stat'
    Using "watch", as seen above, a specific command can be executed periodically while having the output displayed full-screen for monitoring purposes; the interval is specified using the switch "-n" as seen by the number 5 in my example, representing an interval of 5 seconds (while the default interval is every 2 seconds).

    If the utility "watch" (for Linux) or "cmdwatch" (for FreeBSD) is not available, it may be found via the following from the OS distribution:
    For Linux (RHEL/CentOS) the utility "watch" is included with the RPM named "procps":
    Code:
    # /scripts/ensurerpm procps
    For FreeBSD the equivalent utility "cmdwatch" is included with the package (or "FreeBSD port") named "cmdwatch":
    Code:
    # /scripts/ensurepkg cmdwatch
     
  3. Eric

    Eric Administrator
    Staff Member

    Joined:
    Nov 25, 2007
    Messages:
    746
    Likes Received:
    11
    Trophy Points:
    18
    Location:
    Texas
    cPanel Access Level:
    Root Administrator
    Just a note, I'd second MyTop, and MySQL logging.
     
  4. Rahxie

    Rahxie Member

    Joined:
    Aug 21, 2005
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    Thank you for your help. From logging slow queries and watching MySQL, I was able to find an old forum that was being hit with hundreds of spam each hour (over 200,000+ spam posts). I disabled that database and the load has now decreased.
     
  5. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Sticky On Internet
    I am having exactly the same issue.

    mysql is giving me sleepless nights now.

    I just Ran the command suggested by cpaneldon and heres the output

    running centos with 4GB ram + stable cpanel on this dedicated box


     
Loading...

Share This Page