Today we will discuss how you can easily optimize the MySQL performance of your VPS and Dedicated Server via the WHM or SSH.

MySQL Optimization
=======================

You can edit your MySQL configuration by editing the following file: /etc/my.cnf. You can open it with either vi or nano, depending on your preferences.

You need to tweak the following variables:

1. max_connections
2. wait_timeout
3. thread_cache_size
4. table_cache
5. key_buffer_size
6. query_cache_size
7. tmp_table_size

Via SSH you can type ‘mysqladmin variables’ to see their current values. You can also see a refreshed process list by using the command ‘mysqladmin –i10 processlist extended-status’ or by running the command

mysql

then

show processlist;

The settings below are designed to work for a server with 512MB RAM (leaving you enough space for traffic spikes). You can increase the number of max_connections to 400 & possibly the key_buffer to 64M, but be careful, since this can cause your server to overload if there is an extensive MySQL usage.

1. [mysqld]
2. max_connections = 300
3. key_buffer = 32M
4. myisam_sort_buffer_size = 32M
5. join_buffer_size = 1M
6. read_buffer_size = 1M
7. sort_buffer_size = 2M
8. table_cache = 4000
9. thread_cache_size = 286
10. interactive_timeout = 25
11. wait_timeout = 7000
12. connect_timeout = 10
13. max_allowed_packet = 16M
14. max_connect_errors = 10
15. query_cache_limit = 2M
16. query_cache_size = 12M
17. query_cache_type = 1
18. tmp_table_size = 16M
19. skip-innodb
20. [mysqld_safe]
21. open_files_limit = 8192
22. [mysqldump]
23. quick
24. max_allowed_packet = 16M
25. [myisamchk]
26. key_buffer = 64M
27. sort_buffer = 64M
28. read_buffer = 16M
29. write_buffer = 16M
30. [mysqlhotcopy]
31. interactive-timeout

When you are finished, restart the MySQL service. You can do that via the WHM or via the command line

Restart MySQL: /etc/init.d/mysql restart

Under WHM, type “restart” in the search box and you will be presented with highlighted results from all the sentences that contain that word.

Monitoring Changes
=======================

Use the ‘top’ command, ‘free – m’ & ‘ps aux’, to get an idea of how the server load is working. Pay attention to how much RAM you have left & also to how much CPU the MySQL service is using. If you see that you are running out of RAM and MySQL is to blame, try to revise the MySQL settings and reduce their values.