In this tutorial, we'll learn how to monitor database performance with MySQLTuner and tune the MySQL or MariaDB database.
You’ll learn how to monitor and optimize your MySQL database performance using MySQLTuner, a Perl script that helps database administrators quickly analyze a MySQL or MariaDB installation and suggest improvements for better performance and stability.
MySQLTuner provides recommendations based on the current status of your database, helping you tweak configuration settings and optimize performance.
Prerequisites
Before starting, ensure you have:
- A MySQL or MariaDB dedicated server or KVM VPS running.
- Root or sudo access to the server.
- Perl installed on your system.
Step 1: Install MySQLTuner
MySQLTuner is available as a Perl script, so you don’t need to install it using a package manager. Follow these steps:
Update Your Package List: Open a terminal and update your system’s package index:
sudo apt update # For Debian/Ubuntu
sudo yum update # For CentOS/AlmaLinux
Install Perl (if not already installed): MySQLTuner requires Perl, which is usually installed by default. If Perl is not installed, you can install it using:
sudo apt install perl # For Debian/Ubuntu
sudo yum install perl # For CentOS/AlmaLinux
Download MySQLTuner: You can download MySQLTuner directly from GitHub:
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
Make the Script Executable: After downloading, make the script executable:
chmod +x mysqltuner.pl
Now, you're ready to run MySQLTuner.
Step 2: Run MySQLTuner
Once you have MySQLTuner installed, you can begin to analyze your MySQL or MariaDB instance.
Run MySQLTuner with the following command:
sudo ./mysqltuner.pl
If your MySQL/MariaDB instance uses a non-standard configuration (like a different port or socket), you can specify it in the command:
sudo ./mysqltuner.pl --port 3307 --socket /path/to/socket
Enter MySQL Root Password: You will be prompted to enter the MySQL root user password for connecting to the database.
If you don’t want to input the password each time, you can use the --pass option:
sudo ./mysqltuner.pl --pass yourpassword
Wait for the Results: The script will collect various performance metrics and configuration settings. It can take a few seconds to complete.
Step 3: Review the MySQLTuner Output
After running MySQLTuner, you will receive a detailed analysis of your MySQL server’s performance. The output will look something like this:
-------- General Statistics --------
[--] Up for: 5 days 13 hours 7 minutes
[--] MySQL started within last 24 hours - recommendations may be inaccurate
[--] Performance schema disabled for this session.
[--] 100 connections used out of 151 available
[OK] Currently running with 75 max connections, no need for adjustment.
[!!] Slow queries: 10% (15K/150K)
The output consists of several sections, including:
General Information:
Uptime, number of connections, and general stats about your MySQL instance.
Memory Usage:
How much memory your MySQL instance is consuming and whether the allocated memory is optimal.
Slow Queries:
Percentage of slow queries. High numbers indicate performance bottlenecks.
InnoDB Metrics:
Analysis of your InnoDB storage engine performance, including buffer pool efficiency.
MyISAM Metrics:
If you’re using the MyISAM storage engine, this section will provide performance metrics.
Recommendations:
MySQLTuner provides a list of recommendations for optimizing your database. These can include increasing buffer sizes, adjusting max connections, or enabling specific features.
Step 4: Apply MySQLTuner Recommendations
MySQLTuner typically suggests adjustments in the following areas:
Memory Usage Tuning:
Example recommendation:
[OK] Maximum reached memory usage: 450MB (28% of installed RAM)
[OK] Maximum possible memory usage: 1.2GB (76% of installed RAM)
If MySQL uses more memory than it should, the script may suggest increasing or decreasing buffer sizes.
To apply this recommendation: Edit the my.cnf configuration file, typically located at /etc/mysql/my.cnf or /etc/my.cnf. Adjust the memory parameters, such as:
innodb_buffer_pool_size = 512M
key_buffer_size = 256M
After making changes, restart the MySQL service:
sudo systemctl restart mysql
Increase Max Connections:
If you get a recommendation regarding max connections, adjust the max_connections parameter.
Example recommendation:
[!!] Maximum reached connections: 75
[!!] Increase max_connections variable from 100 to 200.
To change this:
max_connections = 200
Optimize Slow Queries:
If the percentage of slow queries is high, you can enable slow query logging to identify problematic queries:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
The slow query log will help you identify the queries causing the delay.
InnoDB Buffer Pool Tuning:
If you’re using the InnoDB storage engine, a recommendation to increase the innodb_buffer_pool_size may appear. This buffer pool holds frequently accessed data in memory, reducing disk I/O.
Example:
[!!] InnoDB buffer pool size / data size: 128.0M/2.0G
Increase the buffer pool size:
innodb_buffer_pool_size = 1G
Query Cache Tuning:
If MySQLTuner suggests changes to the query cache, you can tweak the query cache size:
Example:
query_cache_size = 64M
Step 5: Monitor Performance After Changes
Once you’ve made the recommended changes, monitor your MySQL server to see the improvements. You can run MySQLTuner periodically to assess the effectiveness of the changes and get new recommendations.
- Analyze Slow Query Log: Use the slow query log to optimize problematic queries.
- Track Memory Usage: Keep an eye on memory usage using tools like htop or MySQLTuner to ensure your adjustments are effective.
- Monitor Database Uptime: Check uptime and system resource consumption to confirm stable performance.
Conclusion
MySQLTuner is a powerful tool to analyze and optimize MySQL database performance. By regularly running the tool and applying the recommended changes, you can improve the speed, efficiency, and reliability of your database.
This guide covered the steps how to monitor database performance with MySQLTuner and tune the MySQL or MariaDB database. Keep tuning your database as your workload evolves, and remember to monitor performance periodically.
