Optimize your MySQL Server with MySQLTuner

Login to your server’s console and download MySQLTuner (I use my user’s home directory)

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Optional
If you don’t like to enter the user details for every test it’s possible to create a .my.cnf file in the same directory and add a mysql admin user to access your database

Use this kind of settings:

[client]
user=mysqladminuser
password=dbpassword

Run the tool the first time and follow the advice you get. Create or locate the mysql config file (/etc/my.cnf), don’t use the client file you have created for mysqltuner.
Note MySQL is using a lot of default settings, that will say you don’t need to replace them all, just add the settings you like to change:

[mysqld]
local-infile=0
max_connections = 150
wait_timeout = 1800
interactive_timeout = 30
query_cache_size = 12M
join_buffer_size = 4M
read_buffer_size = 128K
thread_cache_size = 4
table_cache = 384
tmp_table_size = 64M
max_heap_table_size = 64M
log-slow-queries=/var/log/mysql-slow-queries.log

The setting above are fine for a server with 2Gb of memory.
If you enable the the “slow-query-log” setting, you need to create the file in the given directory:

# touch /var/log/mysql-slow-queries.log
# chown mysql:root /var/log/mysql-slow-queries.log
# chmod 660 /var/log/mysql-slow-queries.log

What’s next?
Restart the MySQL service and re-check the performance using MySQLTuner 24-48 hours later and maybe you need to tweak some more settings.

Leave a Reply

Your email address will not be published. Required fields are marked *