Looking to eke out some more performance from my shared server sites running on CENTos, I decided to re-investigate the methods I’ve been using to optimise mysql.
I’ve found another new tool to me that is a bit more verbose than the somewhat laconic mysqltuner.. very valid though it still is.
You’ll need to know a few things about your hardware and OS.
See how many processors your server holds…
cat /proc/cpuinfo
See how many open file descriptors your OS allows… per process and system wide
ulimit -n cat /proc/sys/fs/file-max
I was able to get hold of the mysqltuner script through my system’s package management system, and downloaded and moved the more recently discovered tuning-primer.sh into my executable path.
yum install mysqltuner wget http://launchpad.net/mysql-tuning-primer/trunk/1.5-r5/+download/tuning-primer.sh chmod +x tuning-primer.sh mv tuning-primer /usr/bin/tuning-primer
Optimise all tables in all databases in your mysql installation…
service psmon stop service httpd stop mysqlcheck -u root -p --auto-repair --check --optimize --all-databases service httpd start service psmon start
References:
https://launchpad.net/mysql-tuning-primer
http://www.puschitz.com/TuningLinuxForOracle.shtml
http://blog.taragana.com/index.php/archive/mysql-tip-how-to-check-repair-optimize-all-tables-in-all-databases/
