Install MySQL Tuner

Use this guide to optimise all databases and configuration file /etc/my.cnf

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

or

mysqlcheck -r -A
mysqlcheck -c -A
mysqlcheck -o -A

or simply run mysqlcheck -Aor

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
>>nano /etc/my.cnf
>>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

wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Author: Christopher Smith

I managed websites.

Leave a Reply

Your email address will not be published. Required fields are marked *