How to find MySQL abusers?

[Rahxie]

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]

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:

# 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":

# /scripts/ensurerpm procps

For FreeBSD the equivalent utility "cmdwatch" is included with the package (or "FreeBSD port") named "cmdwatch":

# /scripts/ensurepkg cmdwatch

 

[Eric]

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

 

[Rahxie]

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]

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