fbpx

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/

Categories: General

Jonathan Adjei

Jon's expertise in web development is legendary and he oversees all technical aspects of our projects from development to hosting (all through the command line!)Jon is excited by the latest techniques and keeps the company on track by finding ways to adopt new practices into our workflow.