Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I maintain webhosting servers and more often than not sometimes a server slows up as a result of increase in load or a large amount of CPU resources used up. I've observed that its the MySQL usage which results in the large increase in the load and the server coming to a crawl, however I'd like to find out which user is responsible for the high MySQL usage ?

share|improve this question
3  
Before you blame the users, make sure your database and the server on which it runs are halfway decently tuned. –  Michael Hampton Oct 6 '12 at 20:39
    
@MichaelHampton Is absolutely right. We constantly see the default query caches and such on MySQL deployments. Depending on your application tweaking the my.cnf may prove useful. –  WinkyWolly Oct 16 '12 at 19:47

3 Answers 3

This can be done with a fairly basic SHOW PROCESSLIST; from the MySQL CLI as a MySQL Administrative user.

If there's a process that needs to be terminated, a simple KILL <query Id>; should take care of that.

There's nothing going on for this dev box, but it's an example of what you'd see in the output:

mysql> show processlist;
+----+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host      | db     | Command | Time | State | Info             |
+----+------+-----------+--------+---------+------+-------+------------------+
|  7 | root | localhost | wikidb | Query   |    0 | NULL  | show processlist |
+----+------+-----------+--------+---------+------+-------+------------------+

As you can see, it displays useful information about the database running the query, runtime, query status, and the content of the query.

share|improve this answer

I do agree with @Michael Hampton and @Adrian’s responses, but I'd like to add my own suggestion- Try logging into the server and running the following:

[~]# watch mysqladmin pr

That should give you the databases that are being accessed and the queries that are executed, and the addition of ‘watch’ will monitor and refresh the list. If any database is overloading the server, you can try running the following:

service mysql stop

myisamchk -r /var/lib/mysql/dbname/*.MYI

service mysql start

If the issue persists after restart, you might need to optimize your queries or limit user access to MySQL server resources.

share|improve this answer

Another good suggestion is to enable query logs / set the slow query time low and take a peak at those / use a tool such percona-toolkit to parse them.

We personally utilize the MariaDB build of MySQL which has the userstats patch applied which modifies the information_schema to include useful data such as CPU time used, busy time and so forth.

Example:

+------------------+----------+-----------+--------------+--------------+
| USER             | CPU_TIME | BUSY_TIME | ROWS_FETCHED | ROWS_UPDATED |
+------------------+----------+-----------+--------------+--------------+
| xxx_agaipdf |   208850 |    219735 |    372405321 |       950924 |
| xxx_pdfseus |   179626 |    196981 |    145208779 |       771474 |
| xxx_gopdf   |   177303 |    191366 |    323861619 |       653246 |
| xxx_nextuse |   140624 |    153672 |     37374285 |       425618 |
| xxx_gospel  |   135814 |    142874 |     65301676 |       491579 |
+------------------+----------+-----------+--------------+--------------+
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.