Monitoring MySQL performance
If your MySQL queries are taking a while to run, or you generally have a very heavy database driven site, the first thing that you want to do is take a look at your slow MySQL queries. This will log all MySQL queries that take a long time to process such as any that are selecting a large amount of data. Optimizing your MySQL queries will not only speed up your site, but reduce the load on the server leaving you room for other things that may require those freed up resources.
Shared hosting accounts (Launch, Power, Pro)
Unfortunately, this would not be possible to do on your own as it will require root access to the server to enable slow query logging, but you can still view more information via PHPMyAdmin. To do so, you will follow these steps:
- Log into PHPMyAdmin.
- Click on the Status tab at the top
Within here, you will see plenty of information on the particular queries that are being executed
If you would like more information on your MySQL queries, our support team will be happy to assist you via a support ticket.
VPS and Dedicated hosting
If you are on a VPS or Dedicated hosting account and you want to monitor your MySQL queries, you can fully monitor them on your own, although you will need root access to enable slow query logging which will provide you with much more information on the queries being run.
Without enabling slow query logging by our support team, you may still review running MySQL queries the same as a user on a shared server would as stated above.
If you DON'T have root access
If you do not have root access, support will need to enable this for you. After slow query logging is enabled, you will see a file named "slowqueries" within your home directory. From there, you can view it using the file manager in cPanel or via SSH access.
If you have root access
If you have root access, you can enable MySQL slow query logging quite easily via SSH. We have written a simple script that will enable it for you:
echo;echo -n "username: ";read cpuser; \
echo "log-slow-queries = /var/log/slowqueries" >> /etc/my.cnf; \
echo "long_query_time = 3" >> /etc/my.cnf;touch /var/log/slowqueries; \
chown mysql:wheel /var/log/slowqueries;chmod 664 /var/log/slowqueries; \
usermod -a -G wheel $cpuser; \
ln -s /var/log/slowqueries /home/$cpuser/slowqueries && service mysql restart
slow_query_log = 1
slow_query_log_file = /var/log/slowqueries
long_query_time = 3
Simply copy and paste this full block of code into your SSH window as root. When it prompts for a username, add the cPanel user that you want slow query logging to be enabled for. It will allow you to create the same symlink to the slow query logs within your home directory as our support team would. From here, you can then view the file within your home directory via SSH or the cPanel file manager.
What am I looking for?
When looking through this file, you are looking for any queries that are taking an especially log time. MySQL queries such as ones that select a very large amount of data or search through a very large database can take some time as all of the information that it is pulling from will need to be processes. The best guideline to abide to when running your MySQL queries is to only select the data you need.
2014-06-24 4:52 am
Variable log-slow-queries is removed since MySQL 5.6.1. Use slow_query_log instead.