How to optimize MySQL performance using MySQLTuner?
Have no idea on how to optimize MySQL performance using MySQLTuner? We can help you.
The MySQLTuner is a script that gives suggestions for increasing the server’s performance and stability.
At Bobcares, we often get requests to optimize MySQL, as a part of our Server Management Services.
Today, let’s see how our Support Engineers do this for our customers.
What is MySQLTuner?
High server loads can be handled by running the MySQL at optimal settings. And it prevents server slowdown.
MySQLTuner is one such which helps in optimizing MySQL by tuning it at its best.
The script accesses the MySQL installation and outputs the recommendations for improving the performance accordingly.
MySQLTuner’s suggestions are one of the safer ways of improving our database performance.
We can install this in our server in few steps. We usually do this for our customers.
Our Engineers are here for any kind of help for fixing our customer’s problems.
How we optimize MySQL performance using MySQLTuner?
Recently, one of our customers having their servers with us came with a request that their portals are not responding.
When we checked in detail, we found that the load on the server was very high. So, our Engineers did the following in the server in order to control the load.
Firstly, we checked the error logs. Nothing was evident from there.
So, we ran the below command in MySQL
It showed many sleep queries in it.
So, our Engineers reduced the wait_timeout value accordingly and restarted the service using the command below:
service mysql restart
This reduced the load.
In another case, restarting the service or rebooting the server did not reduce the load. Changing the wait_timeout value also didn’t make a difference.
So, we thought of optimizing the MySQL using the MySQLtuner script as there was some continuous load spike on the server.
When checked we found that it was giving some suggestions on values like query_cache_size and join_buffer_size, wait_timeout, etc as shown above.
So, we changed them accordingly as shown in /etc/mysql/my.cnf.
And, restarted the service using the command below:
service mysql restart
Finally, this way we optimized the MySQL with the usage of MySQLTuner. And, this way the load reduced to normal.
[Having trouble in fixing high load on servers? – Our Experts are available 24/7.]
In conclusion, we can use MySQLTuner for optimizing MySQL and in order to control the high load on servers. Today, we saw how our Support Engineers fix this for our customers.