Use this guide to optimise all databases and configuration file
First, backup all databases
rsync -vrplogDtH /var/lib/mysql /home/MYSQL-BACKUP/
Run OPTIMIZE TABLE to defragment tables for better performance
mysqlcheck --auto-repair --check --optimize --all-databases
mysqlcheck -r -A
mysqlcheck -c -A
mysqlcheck -o -A
or simply run
Enable the slow query log to troubleshoot bad queries
>>echo “log-slow-queries” >> /etc/my.cnf
>>/sbin/service mysql restart
Adjust your join queries to always utilize indexes
This is and adjustment that would need to be made in the software making the queries to MySQL.
Increase table_cache gradually to avoid file descriptor limits
>>edit table_cache if it exists or add it if it doesn’t like this: “table_cache=2048” (or larger)
query_cache_size (> 128M)
>>Same as table_cache above but instead query_cache_size – this caches the queries made to the MySQL server to respond to the same queries in the future from ram instead of the disk for maximum performance. If your server is very RAM limited (as are most VPS) then you will want to be careful with this.
join_buffer_size (> 128.0K, or always use indexes with joins)
>>Same as the other two variables above – Generally you want this to be larger than the largest reasonable join that happens on the server to prevent temporary tables being written to the disk.
table_cache (> 2048)
>>Again, same as the other variables – just increase the value in the /etc/my.cnf
chmod +x mysqltuner.pl