Jul 3, 2008
Andy Pace

The most important aspect of any MySQL-Driven web application is tuning MySQL to perform at it’s peak. Typically the SingleHop Technicians do this all manually, which takes a lot of time and perseverance. However, recently Igor from the Development Team introduced this very easy to use script — mysql-tuning-primer.sh

The script was developed by Matthew Montgomery from MySQL Inc. — not bad for a “Novice” MySQL Technical Support Specialist. I’d love to see what MySQL’s Senior technicians can do! Anyway, onto the script…

Currently the script handles recommendations for the following:

  • Slow Query Log
  • Max Connections
  • Worker Threads
  • Memory Usage
  • Key Buffer
  • Query Cache
  • Sort Buffer
  • Joins
  • Temp Tables
  • Table (Open & Definition) Cache
  • Table Scans (read_buffer)
  • Table Locking
  • Innodb Status

The script can be easily downloaded to any linux-based server running MySQL, and is Plesk and WebMin friendly!

function check_for_plesk_passwords () {

## — Check for the existance of plesk and login using it’s credentials — ##

if [ -f /etc/psa/.psa.shadow ] ; then
mysql=”mysql -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)”
mysqladmin=”mysqladmin -S $socket -u admin -p$(cat /etc/psa/.psa.shadow)”
else
mysql=”mysql -S $socket”
mysqladmin=”mysqladmin -S $socket”
fi
}

function find_webmin_passwords () {

## — populate the .my.cnf file using values harvested from Webmin — ##

cecho “Testing Stored for passwords:\c”
if [ -f /etc/webmin/mysql/config ] ; then
user=$(grep ^login= /etc/webmin/mysql/config | cut -d “=” -f 2)
pass=$(grep ^pass= /etc/webmin/mysql/config | cut -d “=” -f 2)
if [ $user ] && [ $pass ] && [ ! -f ~/.my.cnf ] ; then
cecho “Setting login info as User: $user Password: $pass”
touch ~/.my.cnf
chmod 600 ~/.my.cnf
echo -e “[client]\nuser=$user\npassword=$pass” > ~/.my.cnf
cecho “Retrying login”
is_up=$($mysqladmin ping 2>&1)
if [ "$is_up" = "mysqld is alive" ] ; then
echo UP > /dev/null
else
second_login_failed
fi
echo
else
second_login_failed
echo
fi
else
cecho ” None Found\n” $boldred
second_login_failed
fi
}

After downloaded, simply run it from the command line and read the output:

Looks like I have some work to do! Until next time, happy hosting!

Leave a Comment