Every database system operation on a server will have 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 using of these resources aren’t optimized, they can cause performance degradation of the operating system and database system. Fine-tuning the parameters of MySQL is vital for DBAs and DevOps engineers that want to prevent and quickly solve performance issues causing SQL server slowdowns. Ultimately the most important metric, is how quickly the query is received and the data returned by the server. The following results can be achieved for database systems with tuned and configured system parameters:
- Improve application performance.
- Improve the efficiency of server resource utilization.
- Reduce costs.
Adjust MySQL parameters
Difference situations and events may require recalculating and adjusting MySQL system parameters. Instead of a DBA spending resources troubleshooting server performance issues.
We recommend adjust MySQL parameters in the following cases:
- First-time server setup.
- Low applications performance.
- Changing of server resources (RAM, CPU).
- Changing the application or application load like the count of visitors.
Tuning MySQL Parameters
To get the most out of your server and curating the ultimate workflow we created a step-by-step guide to doing so:
1. MySQL Documentation
MySQL has excellent documentation resources useful to even veteran database system administrators. 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 will not be supported after February 2021.
It’s helpful for all users of MySQL to be familiar with these resources. And it’s highly recommended to spend time working through the documentation to better understand how the database server works.
2. Study MySQL Configuration Best Practices
There are an array of resources available to learn about MySQL configuration. MySQL has hundreds of configuration options, but for many server needs, only a handful are critical. The tuning will vary depending on workload and hardware, but DBAs that familiarize themselves with best practices (for their specific version of MySQL) will better be able to understand and solve performance issues.
Releem has collected an amazing list of articles and resources that are related to MySQL / MariaDB / Percona configuration.
3. Analyze Monitoring Data
Next, monitoring software should be used to continually monitor and analyze data from the MySQL server. These tools will help monitor serve health while providing unique ways to visualize metrics and handle alerts. Open-source as well as licensed software is available. Below are some of the most highly recommended options:
Zabbix is an open-source monitoring tool capable of monitoring networks, servers, cloud, applications, and services. Zabbix is highly secure and easily scalable.
Prometheus is open-source monitoring software marketing it’s simplicity and visualization tools.
Percona Monitoring and Management is an open-source monitoring solution aimed at helping improve database performance and improving data security
Nagios XI is a premium monitoring software but offers a free trial for new users. Nagios XI promises to be limitlessly scalable and highly customizable.
Using any of these monitoring tools will provide critical data on how MySQL uses server resources and history values of MySQL status.
4. Analyze MySQL Status
Make sure the server has been running for at least 24 hours. Analyze MySQL status to detect any variables that need configuration. Querying with "SHOW GLOBAL STATUS;" will deliver various metrics.
5. Use Scripts to get Configuration Recommendations
Using an automated script will save the incredible hassle and time of searching through the metrics. A script will check the metrics from the server and compare them against expected reasonable values. Anything operating outside the expected values will be flagged for review.
MySQLTuner is a powerful configuration tuner that is open-source and available on Github.
MySQL Tuning-Primer.sh is another strong open-source option on Github. This tool is older and should be used only with MySQL versions 5.5 - 5.7.
6. Calculate Values of MySQL Performance Settings
Now armed with the monitoring data, MySQL status, and configuration recommendations from steps 3 through 5, the specific changes and values can finally be determined to minimize bottlenecks and inefficiencies. The MySQL documentation and configuration best practices that were studied in steps 1 and 2 will show their value during this process. Refer to the documentation, resources, and internet as needed.
7. Create New Configuration File
When MySQL is first installed a standard configuration file is installed in the base directory. The new configuration file can be created to reflect the parameter changes made based on the previous steps.
8. Apply New Configuration File
The new performance settings can be applied incrementally to test stability and performance or the configuration file can be applied to update all changes at once. The server should now be optimized and ready for queries!
Streamline the MySQL Tuning
Without experience, fine-tuning the parameters of MySQL requires a significant investment to learn the surrounding information. DBAs and DevOps engineers need to understand the process otherwise, it’s an inefficient use of time. Thankfully MySQL database server tuning doesn’t need to be completed very often. But expect to revisit this process when a new server is setup, performance issues arise, system resources are changed, or an application is changed.
Top comments (0)