How to find MySQL abusers?

Rahxie

Member
Aug 21, 2005
17
0
151
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.
 

cPanelDon

cPanel Quality Assurance Analyst
Staff member
Nov 5, 2008
2,544
13
268
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
 

Rahxie

Member
Aug 21, 2005
17
0
151
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.
 

mohit

Well-Known Member
Jul 12, 2005
553
0
166
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


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