Optimize your MySQL Server with MySQLTuner

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


wget 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.

Related Posts

• How-to install eAccelerator?

  I’m using eAccelerator on two servers now, for both I’m using DirectAdmin as control panel. [...]