WHM MySQL Optimization
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. Originally published Wednesday, February 24th, 2010 at 3:30 pm, updated February 25, 2010 and is filed under Dedicated Hosting Services.