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.