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.

Monitoring MySQL Performance on 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:

1. Log into PHPMyAdmin.
2. Click on the Status tab at the top.

Within here, you will see plenty of information on the particular queries that are being executed. You can also kill database processes.

If you would like more information on your MySQL queries, our support team will be happy to assist you via a support ticket.

Monitoring MySQL Performance on 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. You will need root access to enable slow query logging. The resulting report 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 MySQL queries the same as a user on a shared server as per the instructions above.

How to Enable Slow Query Logging 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.

How to Enable Slow Query Logging if you have root access

If you have root access, you can enable MySQL slow query logging 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

In order to use this code to enable MySQL Slow Query logging:

1. Login to SSH as the root user
2. Copy and paste code (provided above) into your SSH window as a Root user.
3. Run the code. When it prompts for a username add the cPanel user that you want slow query logging to be enabled for.
4. For easier access you can optionally create a 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.