root/mysqltuner.pl

#!/usr/bin/perl -w
# mysqltuner.pl - Version 0.9.8
# High Performance MySQL Tuning Script
# Copyright (C) 2006-2008 Major Hayden - major@mhtx.net
#
# For the latest updates, please visit http://mysqltuner.com/
# Subversion repository available at http://tools.assembla.com/svn/mysqltuner/
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# This project would not be possible without help from:
#   Matthew Montgomery
#   Paul Kehrer
#   Dave Burgess
#   Jonathan Hinds
#   Mike Jackson
#   Nils Breunese
#   Shawn Ashlee
#   Luuk Vosslamber
#   Ville Skytta
#   Trent Hornibrook
#   Jason Gill
#
# Inspired by Matthew Montgomery's tuning-primer.sh script:
# http://forge.mysql.com/projects/view.php?id=44
#
use strict;
use warnings;
use diagnostics;
use Getopt::Long;

# Set up a few variables for use in the script
my $tunerversion = "0.9.8";
my (@adjvars, @generalrec);

# Set defaults
my %opt = (
                "nobad" => 0,
                "nogood" => 0,
                "noinfo" => 0,
                "nocolor" => 0,
                "forcemem" => 0,
                "forceswap" => 0,
                "host" => 0,
                "port" => 0,
                "user" => 0,
                "pass" => 0,
                "skipsize" => 0,
                "checkversion" => 0,
        );
        
# Gather the options from the command line
GetOptions(\%opt,
                'nobad',
                'nogood',
                'noinfo',
                'nocolor',
                'forcemem=i',
                'forceswap=i',
                'host=s',
                'port=i',
                'user=s',
                'pass=s',
                'skipsize',
                'checkversion',
                'help',
        );

if (defined $opt{'help'} && $opt{'help'} == 1) { usage(); }

sub usage {
        # Shown with --help option passed
        print "\n".
                "   MySQLTuner $tunerversion - MySQL High Performance Tuning Script\n".
                "   Bug reports, feature requests, and downloads at http://mysqltuner.com/\n".
                "   Maintained by Major Hayden (major\@mhtx.net) - Licensed under GPL\n".
                "\n".
                "   Important Usage Guidelines:\n".
                "      To run the script with the default options, run the script without arguments\n".
                "      Allow MySQL server to run for at least 24-48 hours before trusting suggestions\n".
                "      Some routines may require root level privileges (script will provide warnings)\n".
                "      You must provide the remote server's total memory when connecting to other servers\n".
                "\n".
                "   Connection and Authentication\n".
                "      --host <hostname>    Connect to a remote host to perform tests (default: localhost)\n".
                "      --port <port>        Port to use for connection (default: 3306)\n".
                "      --user <username>    Username to use for authentication\n".
                "      --pass <password>    Password to use for authentication\n".
                "\n".
                "   Performance and Reporting Options\n".
                "      --skipsize           Don't enumerate tables and their types/sizes (default: on)\n".
                "                             (Recommended for servers with many tables)\n".
                "      --checkversion       Check for updates to MySQLTuner (default: don't check)\n".
                "      --forcemem <size>    Amount of RAM installed in megabytes\n".
                "      --forceswap <size>   Amount of swap memory configured in megabytes\n".
                "\n".
                "   Output Options:\n".
                "      --nogood             Remove OK responses\n".
                "      --nobad              Remove negative/suggestion responses\n".
                "      --noinfo             Remove informational responses\n".
                "      --nocolor            Don't print output in color\n".
                "\n";
        exit;
}

# Setting up the colors for the print styles
my $good = ($opt{nocolor} == 0)? "[\e[00;32mOK\e[00m]" : "[OK]" ;
my $bad = ($opt{nocolor} == 0)? "[\e[00;31m!!\e[00m]" : "[!!]" ;
my $info = ($opt{nocolor} == 0)? "[\e[00;34m--\e[00m]" : "[--]" ;

# Functions that handle the print styles
sub goodprint { print $good." ".$_[0] unless ($opt{nogood} == 1); }
sub infoprint { print $info." ".$_[0] unless ($opt{noinfo} == 1); }
sub badprint { print $bad." ".$_[0] unless ($opt{nobad} == 1); }
sub redwrap { return ($opt{nocolor} == 0)? "\e[00;31m".$_[0]."\e[00m" : $_[0] ; }
sub greenwrap { return ($opt{nocolor} == 0)? "\e[00;32m".$_[0]."\e[00m" : $_[0] ; }

# Calculates the parameter passed in bytes, and then rounds it to one decimal place
sub hr_bytes {
        my $num = shift;
        if ($num >= (1024**3)) { #GB
                return sprintf("%.1f",($num/(1024**3)))."G";
        } elsif ($num >= (1024**2)) { #MB
                return sprintf("%.1f",($num/(1024**2)))."M";
        } elsif ($num >= 1024) { #KB
                return sprintf("%.1f",($num/1024))."K";
        } else {
                return $num."B";
        }
}

# Calculates the parameter passed in bytes, and then rounds it to the nearest integer
sub hr_bytes_rnd {
        my $num = shift;
        if ($num >= (1024**3)) { #GB
                return int(($num/(1024**3)))."G";
        } elsif ($num >= (1024**2)) { #MB
                return int(($num/(1024**2)))."M";
        } elsif ($num >= 1024) { #KB
                return int(($num/1024))."K";
        } else {
                return $num."B";
        }
}

# Calculates the parameter passed to the nearest power of 1000, then rounds it to the nearest integer
sub hr_num {
        my $num = shift;
        if ($num >= (1000**3)) { # Billions
                return int(($num/(1000**3)))."B";
        } elsif ($num >= (1000**2)) { # Millions
                return int(($num/(1000**2)))."M";
        } elsif ($num >= 1000) { # Thousands
                return int(($num/1000))."K";
        } else {
                return $num;
        }
}

# Calculates uptime to display in a more attractive form
sub pretty_uptime {
        my $uptime = shift;
        my $seconds = $uptime % 60;
        my $minutes = int(($uptime % 3600) / 60);
        my $hours = int(($uptime % 86400) / (3600));
        my $days = int($uptime / (86400));
        my $uptimestring;
        if ($days > 0) {
                $uptimestring = "${days}d ${hours}h ${minutes}m ${seconds}s";
        } elsif ($hours > 0) {
                $uptimestring = "${hours}h ${minutes}m ${seconds}s";
        } elsif ($minutes > 0) {
                $uptimestring = "${minutes}m ${seconds}s";
        } else {
                $uptimestring = "${seconds}s";
        }
        return $uptimestring;
}

# Retrieves the memory installed on this machine
my ($physical_memory,$swap_memory,$duflags);
sub os_setup {
        sub memerror {
                badprint "Unable to determine total memory/swap; use '--forcemem' and '--forceswap'\n";
                exit;
        }
        my $os = `uname`;
        $duflags = ($os =~ /Linux/) ? '-b' : '';
        if ($opt{'forcemem'} > 0) {
                $physical_memory = $opt{'forcemem'} * 1048576;
                infoprint "Assuming $opt{'forcemem'} MB of physical memory\n";
                if ($opt{'forceswap'} > 0) {
                        $swap_memory = $opt{'forceswap'} * 1048576;
                        infoprint "Assuming $opt{'forceswap'} MB of swap space\n";
                } else {
                        $swap_memory = 0;
                        badprint "Assuming 0 MB of swap space (use --forceswap to specify)\n";
                }
        } else {
                if ($os =~ /Linux/) {
                        $physical_memory = `free -b | grep Mem | awk '{print \$2}'` or memerror;
                        $swap_memory = `free -b | grep Swap | awk '{print \$2}'` or memerror;
                } elsif ($os =~ /Darwin/) {
                        $physical_memory = `sysctl -n hw.memsize` or memerror;
                        $swap_memory = `sysctl -n vm.swapusage | awk '{print \$3}' | sed 's/\..*\$//'` or memerror;
                } elsif ($os =~ /NetBSD/) {
                        $physical_memory = `sysctl -n hw.physmem` or memerror;
                        $swap_memory = `swapctl -l | grep '^/' | awk '{ s+= \$2 } END { print s }'` or memerror;
                } elsif ($os =~ /BSD/) {
                        $physical_memory = `sysctl -n hw.realmem`;
                        $swap_memory = `swapinfo | grep '^/' | awk '{ s+= \$2 } END { print s }'`;
                }
        }
        chomp($physical_memory);
}

# Checks to see if a MySQL login is possible
my ($mysqllogin,$doremote,$remotestring);
sub mysql_setup {
        $doremote = 0;
        $remotestring = '';
        my $command = `which mysqladmin`;
        chomp($command);
        if (! -e $command) {
                badprint "Unable to find mysqladmin in your \$PATH.  Is MySQL installed?\n";
                exit;
        }
        # Are we being asked to connect to a remote server?
        if ($opt{host} ne 0) {
                chomp($opt{host});
                $opt{port} = ($opt{port} eq 0)? 3306 : $opt{port} ;
                # If we're doing a remote connection, but forcemem wasn't specified, we need to exit
                if ($opt{'forcemem'} eq 0) {
                        badprint "The --forcemem option is required for remote connections\n";
                        exit;
                }
                infoprint "Performing tests on $opt{host}:$opt{port}\n";
                $remotestring = " -h $opt{host} -P $opt{port}";
                $doremote = 1;
        }
        # Did we already get a username and password passed on the command line?
        if ($opt{user} ne 0 and $opt{pass} ne 0) {
                $mysqllogin = "-u $opt{user} -p'$opt{pass}'".$remotestring;
                my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
                if ($loginstatus =~ /mysqld is alive/) {
                        goodprint "Logged in using credentials passed on the command line\n";
                        return 1;
                } else {
                        badprint "Attempted to use login credentials, but they were invalid\n";
                        exit 0;
                }
        }
        if ( -r "/etc/psa/.psa.shadow" and $doremote == 0 ) {
                # It's a Plesk box, use the available credentials
                $mysqllogin = "-u admin -p`cat /etc/psa/.psa.shadow`";
                my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
                unless ($loginstatus =~ /mysqld is alive/) {
                        badprint "Attempted to use login credentials from Plesk, but they failed.\n";
                        exit 0;
                }
        } else {
                # It's not Plesk, we should try a login
                my $loginstatus = `mysqladmin $remotestring ping 2>&1`;
                if ($loginstatus =~ /mysqld is alive/) {
                        # Login went just fine
                        $mysqllogin = "";
                        # Did this go well because of a .my.cnf file or is there no password set?
                        my $userpath = `ls -d ~`;
                        chomp($userpath);
                        unless ( -e "$userpath/.my.cnf" ) {
                                badprint "Successfully authenticated with no password - SECURITY RISK!\n";
                        }
                        return 1;
                } else {
                        print STDERR "Please enter your MySQL administrative login: ";
                        my $name = <>;
                        print STDERR "Please enter your MySQL administrative password: ";
                        system("stty -echo");
                        my $password = <>;
                        system("stty echo");
                        chomp($password);
                        chomp($name);
                        $mysqllogin = "-u $name";
                        if (length($password) > 0) {
                                $mysqllogin .= " -p'$password'";
                        }
                        $mysqllogin .= $remotestring;
                        my $loginstatus = `mysqladmin ping $mysqllogin 2>&1`;
                        if ($loginstatus =~ /mysqld is alive/) {
                                print STDERR "\n";
                                if (! length($password)) {
                                        # Did this go well because of a .my.cnf file or is there no password set?
                                        my $userpath = `ls -d ~`;
                                        chomp($userpath);
                                        unless ( -e "$userpath/.my.cnf" ) {
                                                badprint "Successfully authenticated with no password - SECURITY RISK!\n";
                                        }
                                }
                                return 1;
                        } else {
                                print "\n".$bad." Attempted to use login credentials, but they were invalid.\n";
                                exit 0;
                        }
                        exit 0;
                }
        }
}

# Populates all of the variable and status hashes
my (%mystat,%myvar,$dummyselect);
sub get_all_vars {
        # We need to initiate at least one query so that our data is useable
        $dummyselect = `mysql $mysqllogin -Bse "SELECT VERSION();"`;
        my @mysqlvarlist = `mysql $mysqllogin -Bse "SHOW /*!50000 GLOBAL */ VARIABLES;"`;
        foreach my $line (@mysqlvarlist) {
                $line =~ /([a-zA-Z_]*)\s*(.*)/;
                $myvar{$1} = $2;
        }
        my @mysqlstatlist = `mysql $mysqllogin -Bse "SHOW /*!50000 GLOBAL */ STATUS;"`;
        foreach my $line (@mysqlstatlist) {
                $line =~ /([a-zA-Z_]*)\s*(.*)/;
                $mystat{$1} = $2;
        }
}

# Checks for updates to MySQLTuner
sub validate_tuner_version {
        print "\n-------- General Statistics --------------------------------------------------\n";
        if ($opt{checkversion} eq 0) {
                infoprint "Skipped version check for MySQLTuner script\n";
                return;
        }
        my $update;
        my $url = "http://mysqltuner.com/versioncheck.php?v=$tunerversion";
        if (-e "/usr/bin/curl") {
                $update = `/usr/bin/curl --connect-timeout 5 '$url' 2>/dev/null`;
                chomp($update);
        } elsif (-e "/usr/bin/wget") {
                $update = `/usr/bin/wget -e timestamping=off -T 5 -O - '$url' 2>/dev/null`;
                chomp($update);
        }
        if ($update eq 1) {
                badprint "There is a new version of MySQLTuner available\n";
        } elsif ($update eq 0) {
                goodprint "You have the latest version of MySQLTuner\n";
        } else {
                infoprint "Unable to check for the latest MySQLTuner version\n";
        }
}

# Checks for supported or EOL'ed MySQL versions
my ($mysqlvermajor,$mysqlverminor);
sub validate_mysql_version {
        ($mysqlvermajor,$mysqlverminor) = $myvar{'version'} =~ /(\d)\.(\d)/;
        if ($mysqlvermajor < 5) {
                badprint "Your MySQL version ".$myvar{'version'}." is EOL software!  Upgrade soon!\n";
        } elsif ($mysqlvermajor == 5) {
                goodprint "Currently running supported MySQL version ".$myvar{'version'}."\n";
        } else {
                badprint "Currently running unsupported MySQL version ".$myvar{'version'}."\n";
        }
}

# Checks for 32-bit boxes with more than 2GB of RAM
my ($arch);
sub check_architecture {
        if ($doremote eq 1) { return; }
        if (`uname -m` =~ /64/) {
                $arch = 64;
                goodprint "Operating on 64-bit architecture\n";
        } else {
                $arch = 32;
                if ($physical_memory > 2147483648) {
                        badprint "Switch to 64-bit OS - MySQL cannot currenty use all of your RAM\n";
                } else {
                        goodprint "Operating on 32-bit architecture with less than 2GB RAM\n";
                }
        }
}

# Start up a ton of storage engine counts/statistics
my (%enginestats,%enginecount,$fragtables);
sub check_storage_engines {
        if ($opt{skipsize} eq 1) {
                print "\n-------- Storage Engine Statistics -------------------------------------------\n";
                infoprint "Skipped due to --skipsize option\n";
                return;
        }
        print "\n-------- Storage Engine Statistics -------------------------------------------\n";
        infoprint "Status: ";
        my $engines;
        $engines .= (defined $myvar{'have_archive'} && $myvar{'have_archive'} eq "YES")? greenwrap "+Archive " : redwrap "-Archive " ;
        $engines .= (defined $myvar{'have_bdb'} && $myvar{'have_bdb'} eq "YES")? greenwrap "+BDB " : redwrap "-BDB " ;
        $engines .= (defined $myvar{'have_federated'} && $myvar{'have_federated'} eq "YES")? greenwrap "+Federated " : redwrap "-Federated " ;
        $engines .= (defined $myvar{'have_innodb'} && $myvar{'have_innodb'} eq "YES")? greenwrap "+InnoDB " : redwrap "-InnoDB " ;
        $engines .= (defined $myvar{'have_isam'} && $myvar{'have_isam'} eq "YES")? greenwrap "+ISAM " : redwrap "-ISAM " ;
        $engines .= (defined $myvar{'have_ndbcluster'} && $myvar{'have_ndbcluster'} eq "YES")? greenwrap "+NDBCluster " : redwrap "-NDBCluster " ;      
        print "$engines\n";
        if ($mysqlvermajor >= 5) {
                # MySQL 5 servers can have table sizes calculated quickly from information schema
                my @templist = `mysql $mysqllogin -Bse "SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') GROUP BY ENGINE HAVING SUM(DATA_LENGTH) > 0 ORDER BY ENGINE ASC;"`;
                foreach my $line (@templist) {
                        my ($engine,$size,$count);
                        ($engine,$size,$count) = $line =~ /([a-zA-Z_]*)\s+(\d+)\s+(\d+)/;
                        $enginestats{$engine} = $size;
                        $enginecount{$engine} = $count;
                }
                $fragtables = `mysql $mysqllogin -Bse "SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0"`;
                chomp($fragtables);
        } else {
                # MySQL < 5 servers take a lot of work to get table sizes
                my @tblist;
                # Now we build a database list, and loop through it to get storage engine stats for tables
                my @dblist = `mysql $mysqllogin -Bse "SHOW DATABASES"`;
                foreach my $db (@dblist) {
                        chomp($db);
                        if ($db eq "information_schema") { next; }
                        if ($mysqlvermajor == 3 || ($mysqlvermajor == 4 && $mysqlverminor == 0)) {
                                # MySQL 3.23/4.0 keeps Data_Length in the 6th column
                                push (@tblist,`mysql $mysqllogin -Bse "SHOW TABLE STATUS FROM \\\`$db\\\`" | awk '{print \$2,\$6,\$9}'`);
                        } else {
                                # MySQL 4.1+ keeps Data_Length in the 7th column
                                push (@tblist,`mysql $mysqllogin -Bse "SHOW TABLE STATUS FROM \\\`$db\\\`" | awk '{print \$2,\$7,\$10}'`);
                        }
                }
                # Parse through the table list to generate storage engine counts/statistics
                $fragtables = 0;
                foreach my $line (@tblist) {
                        chomp($line);
                        $line =~ /([a-zA-Z_]*)\s+(\d+)\s+(\d+)/;
                        my $engine = $1;
                        my $size = $2;
                        my $datafree = $3;
                        if ($size !~ /^\d+$/) { $size = 0; }
                        if (defined $enginestats