How to Optimize MySQL Performance using MySQLTuner?

To handle the exponentially increasing data, a growing business often requires a Database Management System that is scalable, effective, reliable, and secure. 

MySQL is one of the popular choices as an Open Source Relational Database Management System. With Standard SQL support, you can quickly query, manipulate & add data to your MySQL Tables.

In order to get the best performance out of your MySQL Database according to your use case, you can use the MySQLTuner command. This is a PERL script that provides you with suggestions to enhance your MySQL performance, security, etc. 

In this article, you will learn how to effectively use the MySQLTuner command to optimize your MySQL performance.

Table of Contents

• What is MySQL?
  • Key Features of MySQL
• How to use MySQLTuner for MySQL Performance Optimization?
  • Installing MySQLTuner
  • Running MySQLTuner
  • MySQLTuner Recommendations
• Frequently Asked Questions (FAQ)
• Conclusion

What is MySQL?

MySQL is a popular Open-Source Relational Database Management System. Data in MySQL is stored in tables consisting of rows and columns. It is fully developed, distributed, and maintained by the Oracle Corporation. 

From a technical point of view, MySQL is written in C and C++ language. It is compatible with several platforms such as Microsoft Windows, Oracle Solaris, AIX, Symbian, Linux, MAC OS, etc. 

MySQL is also an integral part of the Modern LAMP Stack consisting of a Linux-based Operating System, the Apache Web Server, a MySQL Database, and PHP for processing. 

Using the Standard SQL(Standard Query Language) commands you can easily define, manipulate, control, and query your data in MySQL tables. 

Client-Side GUIs(Graphical User Interface) such as MySQL WorkBench, SequelPro, or DBVisualizer can be used to type in the SQL commands and the server will respond with the requested information. 

MySQL also offers a paid Enterprise version that comes with premium support services as well as a series of extensions that can be installed as Server Plugins. 

Key Features of MySQL

MySQL is used to store and retrieve data in a wide variety of popular applications, websites, and services. It offers a collection of eye-catching features that makes it one of the top-performing RDMS:

• Ease of Use: MySQL supports a wide variety of programming languages ​​such as PHP, PERL, C, C ++, and JAVA. The MySQL environment also provides a set of tools to facilitate tasks such as Server Management, Reporting, and Data Analysis. To enable you to work with a wider range of datasets, MySQL fully supports multiple Data structures, JSON and Geospatial data, as well as logical, Numeric, Alphanumeric, Date, and Time data types. 
• Best-in-class Performance: MySQL assures a top-notch query performance with a wide range of clustered servers. It provides fast-loading utilities with distinct memory caches as well as Table Index Partitioning. MySQL can also effectively handle fluctuating workloads and process large amounts of data at optimal speeds. It allows you to store data in 50 million rows or more in a table with a default file size limit for a table of 4GB. However, depending on your Operating System configuration, you can increase it to the theoretical limit of 8 million terabytes (TB). You can also improve your performance by viewing the list of recommendations given by the MySQLTuner command. 
• Open Source: MySQL is licensed under the GNU General Public License (GPL). In other words, MySQL is always free to use. Based on Oracle’s Open-Source MySQL Codebase, you can customize it as per your needs. Because it is Open-Source software, a large public community has been developed that regularly enriches the MySQL documentation and online support culture. 
• Localization: MySQL supports several different character sets including latin1 (cp1252), german, big5, ujis, and more. You can also set the language of error messages that the server provides to clients. The MySQL Server Time can also be changed dynamically, so you can set a specific time zone for each client. 
• Data Security & Protection: SQL allows you to configure data access control settings. You can use powerful mechanisms such as the Access Privilege System and User Account Management to determine who can view or use MySQL data. MySQL sets the bar with Host-based Validation and Password Encryption. 

How to use MySQLTuner for MySQL Performance Optimization?

MySQLTuner is a read-only script written in PERL. It analyses your MySQL Installation and quickly provides you with a list of recommendations and adjustments for improving performance and stability. 

It retrieves the current Configuration Variables and Status Data and displays a complete list of statistics & suggestions associated with performance, security, etc. 

It is continuously maintained and supports 300+ Indicator Configurations such as Galera Cluster, TokuDB, Performance schema, Linux OS Metrics, InnoDB, MyISAM, Aria, etc.

To start using MySQLTuner, you can go through the following aspects given below:

1. Installing MySQLTuner
2. Running MySQLTuner
3. MySQLTuner Recommendations

1) Installing MySQLTuner

You can follow the following steps to download and install MySQLTuner:

• Step 1: You can directly download by executing any of the following commands given below:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

You can also download the complete repository by using git clone or git clone –depth 1 -b master followed by the cloning URL above.

• Step 2: To run MySQLTuner without calling Perl you can run the following command for changing the script’s permissions to be executable.

chmod +x mysqltuner.pl 

2) Running MySQLTuner

Now that you have installed MySQLTuner, you can run it by following the simple steps given below:

• Step 1: Execute the following command to run MySQLTuner. After this, MySQL will ask you for the administrative login and password.

./mysqltuner.pl

You can also run MySQLTuner using any existing Perl executable on your system

perl mysqltuner.pl

• Step 2: You can also apply MySQLTuner according to the following Use Cases:

• Operating Remotely for Minimal Usage.

perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password

• Operating Locally for Minimal Usage.

perl mysqltuner.pl --host 127.0.0.1

• Getting the Maximum Output Information around MySQL without Debugging.

perl mysqltuner.pl --verbose
perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat

• Enable CVE(Common Vulnerabilities and Exposures) check for your MySQL Version.

perl mysqltuner.pl --cvefile=vulnerabilities.csv

• Save your MySQLTuner Output in a file with the Information displayed.

perl mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt

• Save your MySQLTuner Result in a file without displaying Information.

perl mysqltuner.pl --silent --outputfile /tmp/result_mysqltuner.txt

• Based on the Text::Template syntax, Customize your Reporting File using the Template Model.

perl mysqltuner.pl --silent --reportfile /tmp/result_mysqltuner.txt --template=/tmp/mymodel.tmpl

• Toggle On Debugging Information.

perl mysqltuner.pl --debug

• Updating MySQLTuner and the Data Files (password and cve) when necessary.

perl mysqltuner.pl --checkversion --updateversion

3) MySQLTuner Recommendations

The configuration file of MySQL is generally located at /etc/mysql/my.cnf. It is always a good practice to create a backup for your configuration file my.cnf by executing the following command:

cp /etc/mysql/my.cnf ~/my.cnf.backup

You can start making small changes one at a time based on the suggestions and check the server after each change. Restarting the server after every modification can help you detect anomalies. 

systemctl restart mysqld

If your system has different init systems, then use the following command:

service mysql restart

MySQLTuner can provide you with suggestions associated with the following parameters to increase performance & stability:

• Key_buffer: By modifying this, you can allot more memory to MySQL, thereby speeding up your databases. The preferred size should not exceed 25% of the total system memory when using the MyISAM Table Engine, and up to 70 percent for InnoDB. MySQL Documentation suggests setting 64M for servers with 256MB of RAM with many tables. Whereas, the default size of 16M is recommended for Servers with fewer tables and 128MB of RAM.
• Max_allowed_packet: This allows you to set the maximum size of a sendable packet. This packet can be a single SQL state, a single row being sent to a client, or even a log being sent from a source database to a replica. For situations when your MySQL Server is going to process large packets, you can increase this to the size of your largest packet.
• Thread_stack: Though MySQL sets the default stack size for each thread, you can increase the size if an error related to the thread_stack is logged.
• Thread_cache_size: For instance, when you are receiving hundreds of connections per minute, you can increase it so that the majority of connections can be made on cached threads.
• Max_connections: Using this, you can set the maximum number of concurrent connections. This number can be estimated based on past usage so that there is a buffer between that past upper number and the max_connections parameter.
• tmp_table_size or max_heap_table_size: While changing this, ensure that you modify both of them and set them equal. Based on the Memory Availability, you can increase them by large chunks since these are global values.
• Join_buffer_size: It is a good practice to increase it in small increments as it gets multiplied by the max_connections.
• Innodb_buffer_pool_size: If MySQLTuner suggests increasing this, you can make it large enough to accommodate all your InnoDB databases as this parameter significantly affects your performance.

Always run MySQLTuner after 24 hrs of starting MySQL for accurate results. For cases when the recommendations are not clear, it is advised to consult your DBA or a System Administrator that you trust.

Frequently Asked Questions (FAQs)

What is MySQL Tuning?

MySQL tuning refers to the process of improving the performance of SQL statements in the database for better, more accurate, and faster results. MySQLTuner can be used to improve the query performances as well as verify the installation.

What is Table_Open_Cache?

Table_open_cache defines the maximum number of tables that can be kept open in the cache. This parameter tunes the performance by allowing to re-open tables frequently with ease. Also, the newer tables created will receive the cache memory, hence saving the efforts of memory allocation.

What is Key_Buffer_Size?

The key_buffer_size variable determines the amount of memory that can be readily available for the MySQL index. The more the memory, the faster the performance, and this tunes the performance of MySQL queries.  

What is Wait_timeout in MySQL?

It is the time for which the server waits for inactivity, before closing the connection. It can be defined as the number of seconds the server waits for before receiving a bad handshake.

Conclusion

In this article, you have learned how to install, run & effectively use MySQLTuner to optimize your MySQL performance. MySQLTuner reviews your MySQL Installation and provides a list of suggestions to increase your performance & stability. 

Before making any changes to your MySQL settings, you can always create a backup of your configuration file to easily revert back to the initial setup in case of an error.

After you have enhanced your MySQL Performance, you can start querying your data at a faster rate. To get a complete picture of your business health & performance, you need to consolidate data from MySQL and all the other applications used across your business for Marketing, Customer Relationship Management, Accounting, Sales, etc. 

To achieve this you need to assign a portion of your Engineering Bandwidth to Integrate Data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse or a destination of your choice for further Business Analytics. 

All of these challenges can be comfortably solved by a Cloud-Based ETL tool such as Hevo Data.  

If you are using MySQL as your Database Management System and searching for a no-fuss alternative to Manual Data Integration, then Hevo can effortlessly automate this for you. 

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline can seamlessly transfer data from a vast sea of 100+ sources such as MySQL to a Data Warehouse or a Destination of your choice to be visualized in a BI Tool. It is a reliable, completely automated, and secure service that doesn’t require you to write any code!  

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process.

Now that you know how to use MySQLTuner to optimize the performance of your MySQL Database you can also check out this article on Optimizing MySQL Tables and manage the arrangement of data within the database.

Tell us about your experience of Optimising MySQL using MySQLTuner! Share your thoughts with us in the comments below.