MySQL Performance Tuning
Guide

MySQL is a powerful and widely-used open-source relational database management system (RDBMS). To unlock its full potential, it is crucial for database administrators (DBAs) to optimize and fine-tune their MySQL servers. This comprehensive guide walks through eight critical steps for effective MySQL tuning that can help improve the performance, efficiency, and stability of MySQL databases.

MySQL has excellent documentation resources that are useful for all, even veteran database administrators with years of experience. MySQL provides server reference manuals for each currently supported version:

• MySQL 8.0 Reference Manual;
• MySQL 5.7 Reference Manual;
• MySQL 5.6 Reference Manual (MySQL 5.6 has not been supported since February 2021.);
• MariaDB Reference Manual

It's helpful for database administrators to be intimately familiar with these resources. And it's highly recommended to take the time to work through the documentation to better understand how MySQL works and how different parameter settings affect database performance.

We published the full list of MySQL variables which impact on database performance.

There are an array of resources available both online and in print to learn how to configure MySQL. MySQL has hundreds of configuration options, but for many servers, only a handful are critical. The tuning will vary depending on workload and hardware specifications, but DBAs that familiarize themselves with best practices (for their specific version of MySQL) will be better equipped to understand and solve performance issues in a timely manner.

Releem has assembled a useful list of articles and resources that are related to MySQL/ MariaDB/Percona configuration. Here are some general articles for database administrators interested in studying or refreshing MySQL best practices:

• Ten MySQL performance tuning settings after installation;
• MySQL Server and SQL Performance Tuning
• MySQL Performance Cheat Sheet
• Performance Tuning and Configurations for your MySQL Server
• MAKING IT BETTER: BASIC MYSQL PERFORMANCE TUNING (MYSQLD)
• InnoDB Performance Optimization Basics
• MySQL 101: Tuning MySQL After Upgrading Memory
• How MySQL Opens and Closes Tables

After studying MySQL documentation and learning best practices, DBAs can use monitoring software to analyze data from the MySQL server. These tools will help monitor server health while providing unique ways to visualize metrics and handle alerts.

Database engineers should examine various indicators of database health to guarantee that databases operate smoothly and efficiently. Resource utilization is one of the most important health check categories. Each operation within a server primarily relies on four main system resources:

• The CPU is the powerhouse behind the system;
• The memory encodes, stores, and retrieves information;
• Disk I/O is the input and output process for data moving from storage to other hardware components;
• The network consists of the client connections to the server;

When these resources are not optimized, this can lead to performance degradation of both the operating system and database. Ultimately, the most critical metric is the speed at which a query is received and the data returned by the server. The following MySQL metrics are associated with the four system resources:

• Database Connection Utilization;
• MySQL Latency;
• MySQL Throughput (Queries per Second);
• Memory Utilization;
• Disk Space Usage;
• CPU Utilization.

By monitoring resource utilization, database administrators can identify potential bottlenecks or capacity issues and make informed decisions about resource allocation or scaling. High resource utilization may signify the need for hardware upgrades, resource reallocation, or query optimizations to ensure optimal database performance.

Before utilizing tools like Releem or other performance optimization techniques, it's essential for database administrators to ensure that their MySQL server has been running for at least 24 hours. This allows the server to accumulate a sufficient amount of data and provide more accurate insights into its performance.

To analyze MySQL server status and detect any variables that require configuration adjustments, database administrators can query the server using the SHOW GLOBAL STATUS; command. This command will deliver various metrics that reflect the current performance and status of the MySQL server.

These metrics are crucial for understanding the health of the database system and can be categorized into different areas, such as cache performance, database efficiency, and temporary data metrics. With Releem, database administrators can easily manage and monitor these health checks. Each of the metrics noted below is tracked by Releem.

These metrics evaluate the efficiency of cache systems in the database, identifying bottlenecks and optimization opportunities. By measuring hit rate and fragmentation across various cache types (e.g., thread, table, MyISAM, and InnoDB), they help ensure data accessibility and optimized cache usage.

1. Thread Cache Hit Rate – Measures the efficiency of thread caching by calculating the percentage of threads reused from the cache instead of creating new ones.
2. Thread Cache Ratio – Represents the proportion of threads created and cached, which helps evaluate the effectiveness of the thread cache configuration.
3. MyISAM Cache Hit Rate – Calculates the percentage of key reads served from the key buffer in MyISAM tables, indicating the efficiency of the key buffer configuration.
4. InnoDB Cache Hit Rate – Determines the percentage of InnoDB data served from the buffer pool, reflecting the efficiency of the InnoDB buffer pool size.
5. Table Cache Hit Rate – Measures the effectiveness of table cache configuration by calculating the percentage of table open requests served from the cache.
6. QCache Fragmentation – Assesses the level of fragmentation within the query cache, which can impact cache efficiency and query performance.

Monitoring overall database efficiency and performance, these metrics track key write ratios, log file sizes, and sort merge passes. They assess the database's management of data writes, storage, and sorting, helping pinpoint areas where there's room for optimization.

1. MyISAM Key Write Ratio – Indicates the proportion of key buffer writes in relation to key writes requested, which helps assess the effectiveness of the key buffer size in MyISAM tables.
2. InnoDB Log File Size – Evaluates the appropriateness of the InnoDB log file size, which can affect transaction processing and recovery times.
3. Sort Merge Passes Ratio – Computes the proportion of merge passes required during sort operations, with a lower ratio indicating better performance.
4. Flushing Logs – Tracks the frequency of log flushes, which can impact database performance and durability.

Focusing on the creation and management of temporary data during database operations, these metrics help detect issues with temporary storage systems or query execution inefficiencies that cause excessive temporary data creation.

• Temporary Disk Data – Monitors the amount of temporary data created on disk during query execution, which can impact performance if it's too high.

Tuning MySQL to improve performance requires continuous monitoring and adjustments to the database configuration. One effective approach to achieve this is by using scripts and tools that provide recommendations for improving performance. Let's take a look at some of these tools:

MySQLTuner is a Perl script that analyzes a server's MySQL configuration and provides suggestions for improving performance. By reviewing various server settings and status variables, MySQLTuner identifies potential issues and recommends adjustments to optimize the database.

Some of the key features of MySQLTuner include:

• Analyzing MySQL configuration file (my.cnf or my.ini);
• Evaluating server status variables and performance metrics;
• Providing recommendations for adjusting key performance-related settings;
• Highlighting potential security vulnerabilities and suggesting remediation;

To use MySQLTuner, download the script, ensure that Perl is installed on the system, and execute the script. The output will include a summary of the server's status, along with specific recommendations for improving performance.

This script uses data from "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..." commands to generate reasonable recommendations for optimizing server variables. The original script is no longer maintained, but a Tuning-primer version on Github fully supports MariaDB.

This toolkit consists of advanced open-source command-line tools designed to simplify complex or challenging MySQL tasks, allowing DBAs to focus on tasks that contribute to business goals.

The Advisor system offers suggestions on server variables by analyzing MySQL status variables. phpMyAdmin is a free PHP-based tool designed for administering MySQL through a web interface.

Mysqlreport converts values from SHOW STATUS into an easily understandable report, providing a detailed overview of MySQL performance. It is a superior and virtually the only alternative to manually interpreting SHOW STATUS.

In order to optimize MySQL performance, it's essential to understand how to properly calculate the values of various MySQL settings. This process, commonly referred to as MySQL tuning, involves adjusting parameters to improve database efficiency, increase server speed, and enhance read and write performance.

By utilizing Releem, DBAs can essentially skip the manual process of this step because Releem automatically calculates and tunes all of the following variables:

• Thread_cache_size: Controls the number of threads to be cached for reuse.
• Query_cache_type: Determines the type of query caching mechanism used.
• query_cache_size: Sets the size of the query cache in bytes.
• query_cache_limit: Defines the maximum size of a single query that can be cached.
• query_cache_min_res_unit: Specifies the minimum result size in bytes for caching.
• key_buffer_size: Sets the size of the buffer used for index blocks in MyISAM tables.
• max_allowed_packet: Defines the maximum size of a packet that can be sent between the client and server.
• max_heap_table_size: Determines the maximum size of a heap table in bytes.
• tmp_table_size: Sets the maximum size of internal in-memory temporary tables.
• innodb_file_per_table: Controls whether InnoDB creates a separate file for each table.
• sort_buffer_size: Specifies the buffer size for sorting operations.
• read_rnd_buffer_size: Sets the buffer size for random read operations.
• bulk_insert_buffer_size: Controls the buffer size for bulk insert operations.
• myisam_sort_buffer_size: Specifies the buffer size for sorting MyISAM indexes during repair.
• innodb_buffer_pool_chunk_size: Determines the size of each chunk in the InnoDB buffer pool.
• join_buffer_size: Sets the buffer size for join operations.
• table_open_cache: Controls the number of open tables that can be cached.
• table_definition_cache: Determines the number of table definitions to be cached.
• innodb_flush_log_at_trx_commit: Controls when logs are flushed during a transaction commit.
• innodb_log_buffer_size: Specifies the size of the InnoDB log buffer. innodb_write_io_threads: Sets the number of I/O threads for writing to the InnoDB buffer pool.
• innodb_read_io_threads: Sets the number of I/O threads for reading from the InnoDB buffer pool.
• innodb_flush_method: Determines the method used for flushing data to InnoDB data files.
• innodb_thread_concurrency: Controls the number of user threads allowed inside InnoDB concurrently.
• optimizer_search_depth: Specifies the depth of the search tree for the query optimizer.
• innodb_purge_threads: Sets the number of threads used for purging operations in InnoDB.
• thread_handling: Determines how threads are managed by the server.
• max_connections: Controls the maximum number of concurrent connections to the server.
• innodb_buffer_pool_size: Sets the size of the InnoDB buffer pool. innodb_log_file_size: Specifies the size of the InnoDB log file.
• thread_pool_size: Determines the number of threads in the thread pool.

After using tuner tools to analyze a database's performance and gather recommendations for improvements, the next step is to create a new configuration file that incorporates these changes.

When MySQL is first installed, a standard configuration file (my.cnf or my.ini) is created in the base directory. This file contains various settings that control the behavior and performance of the MySQL server. By updating this file with the recommended changes, database administrators can optimize server performance and ensure that the database runs efficiently.

Here are the essential steps for creating a new configuration file:

• Backup the original configuration file – Before making any changes, database administrators should create a backup of the original configuration file. This will allow them to revert to the previous settings in case of any issues or unexpected behavior;

• Create a new configuration file – To create a new configuration file, open the original my.cnf or my.ini file in a text editor. Start making the manual calculated or recommended changes based on the analysis performed using Releem, MySQLTuner, mysqlslap, or other performance analysis tools;

• Update the parameters – In the new configuration file, update the relevant parameters with the recommended values. These adjustments can include changes to buffer sizes, cache settings, query optimizations, and other performance-related settings. Ensure that these changes are made in the [mysqld] sections of the configuration file;

• Save and close the new configuration file – Once all the necessary changes have been made, save the new configuration file, ensuring that the original file extension (i.e., .cnf or .ini) is maintained. Close the text editor once the file is saved;

Once the new configuration file has been created and saved, it can be applied to the MySQL server as follows:

• Restart the MySQL server – To apply the changes made in the new configuration file, restart the MySQL server. This can be done by restarting the MySQL service through the system's control panel or using the appropriate command for the operating system;

Releem has been uniquely developed to help DBAs skip many of the more time-intensive steps outlined above, accelerating the process and allowing database administrators to efficiently and easily monitor and improve MySQL server performance. Releem can provide SQL query optimization and new recommended MySQL configuration by analyzing workload, server, and database information and detecting areas of performance degradation.