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.