High MySQL load: Difference between revisions
No edit summary |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 7: | Line 7: | ||
===mytop=== | ===mytop=== | ||
mytop will show what MySQL is doing in real time. The most useful mode is accessed by pressing ''Q'' which is the real time query mode. Press ''i'' to hide inactive connections. mytop can be started by running ''mytop -d mysql'' with a ''-s'' flag to specify the refresh delay. | [http://jeremy.zawodny.com/mysql/mytop/ mytop] will show what MySQL is doing in real time. The most useful mode is accessed by pressing ''Q'' which is the real time query mode. Press ''i'' to hide inactive connections. mytop can be started by running ''mytop -d mysql'' with a ''-s'' flag to specify the refresh delay. | ||
===innotop=== | ===innotop=== | ||
A useful program called [http://code.google.com/p/innotop/ innotop] (installed in /root on cPanel servers) can be used to see InnoDB MySQL queries in realtime. You can run this command with the ''-d'' flag to specify how often it should update. | A useful program called [http://code.google.com/p/innotop/ innotop] (installed in /root on cPanel servers) can be used to see InnoDB MySQL queries in realtime. You can run this command with the ''-d'' flag to specify how often it should update. | ||
==Limiting User Resources== | |||
With MySQL 5.1 and above (used by default on cPanel servers) it's possible to limit resource usage per server. This might be required if one user is using a lot of resources. To perform this limit: | |||
# Log into phpMyAdmin on the relevant server | |||
# Click on ''Users'' | |||
# Click ''Edit Privileges'' on the user | |||
# Set suitable values in the ''Resource Limits'' box. The most important field is the ''MAX USER_CONNECTIONS'' one, and setting this to ''5'' would usually be suitable. | |||
Latest revision as of 18:37, 22 April 2013
Verify that MySQL is causing high load
If you notice high load on a cPanel server, it's worth running atop -dl to see whether it could be MySQL performing a lot of reads and writes to the database. If it looks like a likely culprit, then it's worth investigating what user queries are performing.
Real time MySQL monitoring
mytop
mytop will show what MySQL is doing in real time. The most useful mode is accessed by pressing Q which is the real time query mode. Press i to hide inactive connections. mytop can be started by running mytop -d mysql with a -s flag to specify the refresh delay.
innotop
A useful program called innotop (installed in /root on cPanel servers) can be used to see InnoDB MySQL queries in realtime. You can run this command with the -d flag to specify how often it should update.
Limiting User Resources
With MySQL 5.1 and above (used by default on cPanel servers) it's possible to limit resource usage per server. This might be required if one user is using a lot of resources. To perform this limit:
- Log into phpMyAdmin on the relevant server
- Click on Users
- Click Edit Privileges on the user
- Set suitable values in the Resource Limits box. The most important field is the MAX USER_CONNECTIONS one, and setting this to 5 would usually be suitable.