MySQLTuner is a powerful open-source Perl script designed to help database administrators optimise the performance of MySQL servers. By analysing various metrics and configurations, MySQLTuner generates reports that provide valuable insights into the health and efficiency of your MySQL database. Use this article to initially tune your database, and then use a database observability tool such as DBmarlin to show you performance over time. This allows you to see the effects of your tuning efforts and help you understand when performance is meeting your goals.

The MySQLTuner Report

Image source https://github.com/major/MySQLTuner-perl/blob/master/README.md

1. General Overview:

   

   • MySQLTuner is very intuitive in the way it displays information. At the start of each line is a marker indicating
     • ‘for information only’ [--]
     • ‘no action needed’ [OK]
     • ‘attention required [!!]
   • The overview section provides details about the MySQL server, its version, and the operating system architecture. Make sure this is actually the database you think it is!

2. MySQL Storage Engine Statistics.

   

   • MySQLTuner presents general information about the storage engines in use for this database. Look for fragmented tables here as they can cause excess I/O slowing execution. Also watch out for auto increments nearing capacity as these will stop your application if they run out.

3. Security and CVE Recommendations.

   

   • MySQLTuner checks security risks on user accounts. It will look for anonymous users, users with weak or missing passwords and users without host restriction. If a list of vulnerabilities held in a a CVE list file is available to the server, MySQLTuner also checks and reports on these.

4. Performance Metrics:

   

   • This is where MySQLTuner provides really useful information that can be used to improve performance of your database and server. Ensure the database has been up for more than 24 hours in order to get a representative performance period. You can see the read/write balance and ensure that the database is being given enough memory without imposing too heavy a load on the server. Never allocate fmore than 85% of server memory to the database. Look for slow queries here, joins without indexes and temporary tables created on disk - all of which can impact performance.

5. Storage Engine Metrics:

   

   • MySQL supports an increasing number of storage engines. InnoDB is popular for transactional data. MyISAM is fast but not transactional. AriaDB for MariaDB databases, Galera for clusters and TokuDB for write-intensive applications. MySQLTuner evaluates storage engine related metrics specific to each of these. For InnoDB you can see advice for buffer pool and log file information along with read and write efficiency stats.
6. Query Performance:
   • The report will show you slow queries if the slow query log is enabled. Addressing your top 10 slow queries is essential for improving overall database performance. Consider optimizing these queries with a different join strategy. Using nested loops can be very quick when looking up specific records, whereas has joins for larger data sets can be quicker. Adding indexes to support queries and joins can help performance but beware of the increased index maintenance overhead. Lastly consider restructuring the database schema to enhance query efficiency.
7. Sort, Join and Temporary Table Metrics:
   • Evaluate the sort, join and temporary table metrics to ensure that temporary tables are being managed efficiently. Reducing the number of ‘in-query’ sorts can by done by making use of existing indexed columns. If spilling to disk is happening frequently, consider optimizing queries or increasing the tmp_table_size configuration variable. Increasing join_buffer_size can be used to improve join performance where indexes cannot be added, but this should be done at session level rather than global due to the amount of memory this can use.
8. Recommendations:
   • Performance gold can be found in the Recommendations section, but these must be interpreted and validated before changing the configuration. Ensure you use basic tuning tenets
     • change one thing at a time
     • measure the results
     • use change control
     • stop tuning when your tuning goal is reached

     Employing continuous improvement methods including regular running of the MySQLTuner report will ensure changes made are having a positive overall effect.

Does this replace the need for a true database observability tool?

In short, no. Although proactively implementing recommendations from MySQLTuner can help preempt potential performance issues, the only way to continually monitor database performance and observe changes over time is to use a solution designed for ongoing tracking.

Database observability tools like DBmarlin continuously monitor the performance of your MySQL and other databases every second to detect any new performance issues. When performance degrades, it can pinpoint the root cause, enabling you to rapidly restore normal operations. DBmarlin has built-in recommendations based on a curated knowledge base across multiple database technologies. Soon, it will also include AI capabilities to make tuning recommendations for even the most complex queries.

Conclusion

More information is available on the github.com/MySQLTuner-perl project where the INTERNALS.md breaks down the running of MySQLTuner in detail.

MySQLTuner is a valuable tool for database administrators seeking to optimise the performance of their MySQL servers. By carefully interpreting the generated reports and implementing the recommended changes, you can enhance the efficiency, stability, and overall performance of your MySQL database. Regularly running MySQLTuner and adjusting your MySQL configuration based on its insights will contribute to a well-tuned and responsive database system.

But remember that it isn’t a replacement for a good database observability solution 😊