Community Forums
Connect with us on LinkedIn
+ Reply to Thread
Results 1 to 5 of 5
  1. #1
    Member
    Join Date
    Aug 2005
    Posts
    17

    Default How to find MySQL abusers?

    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. #2
    cPanel Quality Assurance Analyst cPanelDon's Avatar
    Join Date
    Nov 2008
    Location
    Houston, Texas, U.S.A.
    Posts
    2,555
    cPanel/Enkompass Access Level

    DataCenter Provider

    Default

    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. #3
    Support Manager cPanelEric's Avatar
    Join Date
    Nov 2007
    Location
    Texas
    Posts
    488
    cPanel/Enkompass Access Level

    Root Administrator

    Default

    Just a note, I'd second MyTop, and MySQL logging.

  4. #4
    Member
    Join Date
    Aug 2005
    Posts
    17

    Thumbs up

    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. #5
    Member
    Join Date
    Jul 2005
    Location
    Sticky On Internet
    Posts
    555

    Default

    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


    Every 5.0s: mysqladmin proc stat Tue Mar 23 10:43:03 2010

    +-------+---------------+-----------+-----------------+----------------+------+--------------------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +-------+---------------+-----------+-----------------+----------------+------+--------------------+------------------+
    | 13 | DELAYED | localhost | knowbin_wrdp2 | Delayed insert | 13 | Waiting for INSERT | |
    | 39613 | eximstats | localhost | eximstats | Sleep | 1 | | |
    | 39691 | abcdmntc_manx | localhost | abcdmntc_chatdb | Sleep | 3 | | |
    | 39706 | root | localhost | | Query | 0 | | show processlist |
    +-------+---------------+-----------+-----------------+----------------+------+--------------------+------------------+
    Uptime: 18973 Threads: 4 Questions: 1486577 Slow queries: 83 Opens: 3760 Flush tables: 1 Open tables: 3624 Queries per second avg: 78.352
    Learn atleast A word Daily

    7+1 Dedicated Boxes with cPanel...

Similar Threads & Tags
Similar threads

  1. How to find MySQL abusers?
    By Rahxie in forum cPanel and WHM Discussions
    Replies: 4
    Last Post: 03-23-2010, 10:48 AM
  2. How can I find out what my mySQL host's name is?
    By jjpriest25 in forum New User Questions
    Replies: 2
    Last Post: 10-18-2007, 02:45 PM
  3. How to find MYSQL Process ID?
    By rajajagadesh in forum New User Questions
    Replies: 3
    Last Post: 03-11-2007, 09:33 PM
  4. Cant find mysql user?
    By jackie46 in forum cPanel and WHM Discussions
    Replies: 2
    Last Post: 10-10-2005, 11:45 PM
  5. Replies: 1
    Last Post: 12-24-2003, 01:43 AM
Linkedin       Facebook       Twitter       RSS       Flickr       YouTube