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.

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.

4) Adjusting MySQL Settings

The following variables should be considered while adjusting your MySQL settings:

• innodb_buffer_pool_size
• max_connections

It’s crucial to create a copy of your mysql.cnf  file before mankind any changes in case you want to return it to its default setting:

cd /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf_original

While making direct changes to the mysql.cnf, only make a single change at a time and then run the following command to stop and start MySQL:

gp mysql stop

gp mysql start

Below are the details on how you can make changes using GP-CLI. 

INNODB_BUFFER_POOL_SIZE

Ideally, the innodb_buffer_pool_size should be the same as the size of your combined WordPress databases. You can alter it with the following command:

gp stack mysql -innodb-buffer-pool-size {value}

Example:

gp stack mysql -innodb-buffer-pool-size 2048

MAX_CONNECTIONS

You can alter the max_connections using the following command:

gp stack mysql -max-connections {value}

Example:

gp stack mysql -max-connections 151

Note: MySQL Tuner Linux installation is different from MySQLTuner Windows installation. Use this GitHub Repository for a better understanding.

For a smooth installation of MySQL on Ubuntu, read this.

MySQLTuner Additonal Usage

Important Note: Disabling metadata statistic updates with innodb_stats_on_metadata=0 can improve performance but might affect data accuracy.

Basic Usage:

• Locally: perl mysqltuner.pl --host 127.0.0.1 (make the script executable with chmod +x mysqltuner.pl if needed)
• Remotely: perl mysqltuner.pl --host target_ip --user username --pass password

Advanced Usage:

• Verbose Output: perl mysqltuner.pl --verbose (shows more details)
• Specific Statistics:
  • --buffers: Analyze buffer usage
  • --dbstat: Analyze database statistics
  • --idxstat: Analyze index statistics
  • --sysstat: Analyze system statistics
  • --pfstat: Analyze processlist information
  • --tbstat: Analyze table statistics
• Security Check: perl mysqltuner.pl --cvefile=vulnerabilities.csv (checks for vulnerabilities based on a CSV file)
• Output to File:
  • --outputfile /path/to/file.txt: Saves results with information
  • --silent --outputfile /path/to/file.txt: Saves results silently (no console output)
• Custom Reports: perl mysqltuner.pl --silent --reportfile /path/to/report.txt --template /path/to/template.tmpl (create reports with custom templates)
• Debugging: perl mysqltuner.pl --debug (for troubleshooting)
• Update: perl mysqltuner.pl --checkversion --updateversion (checks for script and data updates)

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

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.