How to Optimise MySQL Performance using MySQLTuner February 10, 2025

MySQL performance optimisation is crucial for ensuring database efficiency, stability, and speed. MySQLTuner is a powerful script that analyses your MySQL server’s configuration and provides recommendations for performance improvements.

This guide will walk you through installing, running, and interpreting MySQLTuner results to optimise your database.

Prerequisites

Before proceeding, ensure you have:

• Root or sudo access to the server.
• MySQL is installed and running.
• Perl installed (required for running MySQLTuner).

1. Install MySQLTuner:
   To install MySQLTuner, follow these steps:
   1. On Debian/Ubuntu:
      

      sudo apt update
      
      sudo apt install mysqltuner -y

   2. On CentOS/RHEL:
      

      sudo yum install epel-release -y
      
      sudo yum install mysqltuner -y

      Alternatively, you can download it manually:

      wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
      
      chmod +x mysqltuner.pl

2. Run MySQLTuner
   Execute the following command:

   sudo mysqltuner

   Alternatively, if you downloaded it manually:

   perl mysqltuner.pl

   MySQLTuner will prompt you for the MySQL root password and begin analysing your server’s performance.

3. Interpret the Results
   After running MySQLTuner, you will receive a report with key insights:
   • General Recommendations: Security, version updates, and uptime information.
   • Performance Metrics: Buffer sizes, query cache usage, temporary table handling, and slow queries.
   • Storage Engine Advice: Recommendations for InnoDB or MyISAM configurations.
4. Optimise MySQL Settings
   Based on MySQLTuner’s recommendations, modify the MySQL configuration file (/etc/mysql/my.cnf or /etc/my.cnf). Below are common optimisations:
   1. Increase InnoDB Buffer Pool Size (For InnoDB Databases)
      

      [mysqld]
      
      innodb_buffer_pool_size = 1G

   2. Optimise Query Cache (For MySQL Versions < 8.0)
      

      [mysqld]
      
      query_cache_size = 64M
      
      query_cache_type = 1

   3. Adjust Max Connections
      

      [mysqld]
      
      max_connections = 200

      After making changes, restart MySQL:

      sudo systemctl restart mysql

5. Re-run MySQLTuner
   To check the impact of optimisations, re-run MySQLTuner:

   sudo mysqltuner

   Review the new recommendations and fine-tune the configurations accordingly.

Using MySQLTuner regularly helps maintain an optimised and efficient MySQL database. By analysing its recommendations and making necessary adjustments, you can significantly improve database performance and stability. If issues persist, consider deeper profiling with tools like EXPLAIN, slow query logs, or performance_schema.

For further assistance, consult the MySQL documentation or seek expert advice.

Need to manage database access? Check out our guide on How to add a user to a MySQL database in cPanel