What happens if we realise that a series of databases that we thought were optimised through the use of indexes, have begun to consume CPU usage time of a server to unsuspected limits?
Even users have stopped being able to access the server when it had a certain number of visits because the timeout or timeout for the execution of scripts was exceeded.
You had several projects hosted on the server and the big problem was finding the problematic query or queries?
Luckily, there are different methods by which you can find the problematic query. In this tutorial we will try to compile them, starting with the one we found most useful.
#1 Find the slowest queries
You won't always be able to get to the cause of the problem right off the bat. In fact, sometimes you may find it after you first hit the problematic query. However, it never hurts to apply some common sense to understand what has happened to your databases. But common sense doesn't usually give much of itself when you find yourself in a problematic situation and the company you work for is losing customers.
We will explain the steps to follow to find the problematic MySQL queries that cause high CPU usage, even close to 100%, or that can slow down your server. We will look at several methods that can help you.
#1.1 Query all MySQL processes
The first thing you could do is to check the list of MySQL processes. Queries are queued to be executed if the maximum number of queries that can be executed in parallel is exceeded. If you see that a query is repeated many times, it is probably the problematic query.
To see the running queries, connect to MySQL from the command prompt as root using the following command:
mysql -u root
Then use the following command, which will display a list of running MySQL processes, along with the query being executed:
SHOW FULL PROCESSLIST;
This other command could also be useful, showing the status of the MySQL InnoDB engine:
SHOW ENGINE INNODB STATUS;
Estos comandos suelen bastar para encontrar las consultas MySQL problemáticas. Eso sí, todavía tendrás que investigar acerca del archivo en el que se ejecuta la consulta.
#1.2 Check the MySQL slow query log
MySQL incorporates a feature that allows you to create a log of slow queries. In addition to saving these queries in a log, they can also be stored in a MySQL table, although it is generally recommended to use a simple file as a log. You can configure the duration of the queries after which they will be stored in the log. This way, a quick glance at the log will allow you to find the slowest queries in your database.
Once you have activated the log, you will be able to find the date of execution of the queries, their duration or the number of times they have been executed. Once you have studied the log, you will have to use some common sense and, based on the tables used, think about the script where the problem lies.
#1.3 Check the list of MySQL logged in users
If there are several user accounts on your server and you do not have control over some of them, it is possible that a user is running some unwanted script, or it could also be the case that his account has been hacked. These last two scenarios tend to happen if you sell shared hosting accounts.
To find out who the potential problem users are, follow the steps below:
1. First connect to MySQL as root user from the command terminal:
mysql -u root
If you are prompted for a password, try logging in from your server's root account or use the following command, which will prompt you for the password:
mysql -u root -p
2. Then, once connected to MySQL, run the following query:
SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
GROUP_CONCAT(DISTINCT user) AS users,
COUNT(*) AS threads
FROM information_schema.processlist
GROUP BY host_short
ORDER BY COUNT(*), host_short;
The above query will display the users with active connections, as well as the number of threads they are using.
#1.4 Disable MySQL Persistent Connections
The idea of persistent connections is that the same connection between a client and a MySQL database can be reused, instead of being created and destroyed with every query.
The problem with persistent connections is that they may cause the maximum number of connections to be reached too soon, causing some of your services to fail to connect to MySQL. Therefore, this option should be used with caution.
If for example you use PHP, you can disable persistent connections by editing the php.ini file, which is the PHP configuration file.
Once you are editing the file, you must disable the mysql.allow_persistent option to disable persistent connections:
mysql.allow_persistent = Off
#2 Fix slower MySQL queries
In many cases you can optimise your queries using indexes, which can speed up your queries exponentially. However, it could also be the case that they are already optimised, with the problem being your server's own resources or architectural problems. If your traffic has recently increased, you may need to upgrade your server's CPU or memory, or if the traffic is considerable, you may need to use a load balancer.
#3 Prevents MySQL from excessive CPU usage
If you don't want this problem to catch you by surprise, you can always use a monitoring application such as NewRelic, AppDynamics or EverSQL. These tools include functionalities that allow you to do things like:
- Detect the slowest queries.
- Automatically optimise queries.
- Prioritise those queries that need to be optimised.
- Detection of redundant indexes.
- Detection of those tables that should have an index but do not.
Depending on the application or framework you use, you can always use particular tools to monitor your applications. If for example you use Laravel, you can use tools like Laravel Flare to get a notification when an error occurs in your system.
In other words, prevention is better than cure.