1. Home
  2. How To
  3. How to tune and optimise MySQL using MySQLTuner

How to tune and optimise MySQL using MySQLTuner

MySQLtuner is an open source perl script which analyzes your MySQL performance / collect necessary information and will give recommendations to adjust variables/parameters that you should adjust in order to increase performance. This MySQL tuning will helps to handle larger server load and prevent from server slow down.

Steps to Optimize MySQL configuration using MySQLTuner

1. Download MySQL tuner 

You can download MySQLTuner perl script using below pasted url.

wget http://mysqltuner.com/mysqltuner.pl

2. Update permission

Make the script executable.

chmod +x mysqltuner.pl

3. Run MySQL tuner

./mysqltuner.pl

Enter the root password and mysql root password if it ask for the passwords.

The output shows exactly which variables you should adjust in the [mysqld] section on your my.cnf – MySQL configuration file.

Tuning and update configuration

Next, update the variables in my.cnf file as per recommendations provided by the mysql tuner.

Here is one sample my.cnf file.

root@ssdweb2 [~]# cat /etc/my.cnf

[mysqld]

innodb_file_per_table=1 innodb_buffer_pool_size=134217728 open_files_limit = 1024000 query_cache_size=1G join_buffer_size=16M tmp_table_size=1024M max_heap_table_size=2G thread_cache_size=8 table_open_cache=4096 innodb_buffer_pool_size=4G key_buffer_size=512M query_cache_limit=1G max_allowed_packet=268435456 default-storage-engine=MyISAM

[mysqldump]

max_allowed_packet = 500M

5. Restart mysql server

You need to restart the mysql server for the changes to take effect.

service mysql restart

You can run MySQLTuner multiple times to check if it has further recommendations to improve the MySQL performance.

Updated on May 31, 2019

Was this article helpful?

Related Articles