How to track MySQL Load


Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"


Top Level » MySQL / MariaDB

How to track MySQL LoadLast Modified: Feb 7, 2011, 2:37 pm
If you notice that the mysqld processes are using a lot of cpu load, there is a way to see which mysql user is causing it.

1) Display the user/pass by running:

cat /usr/local/directadmin/conf/mysql.conf



2) Login to /phpMyAdmin through apache (any website)

3) Click the "Processes" tab (near top right), or if you don't have Processes tab, click the "SQL" tab, and run the query

SHOW PROCESSLIST



You'll be shown a list of all current queries, who's running it, on which DB, and how long it's been going.
A query shouldn't take anymore than few seconds.  Queries that take more than that are likely the source of the high load on the box.

The "Processes" tab has the option to kill a process, if needed.
Using it may cause corrupted databases, so use sparingly.



If you find a specific User which is using more load than he should, newer MySQL versions have the ability to limit resources.  As of DA 1.37.0, this feature is not in the interface, but if you login to /phpMyAdmin again (see #2 above), go to the database:
mysql

then the table:
user

Edit the user in question, and adjust the options:

max_queries
max_updates
max_connections
max_user_connections

as needed.  See the MySQL documentation for the correct usage of these values.  Incorrect usage will likely upset your client and break their website.
 
Related Helpfiles
How to Optimize MySQL
How to track which site is using the apache processes and causing load

© 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST