Tuning-primer.sh: Difference between revisions

From genomewiki
Jump to navigationJump to search
No edit summary
(grammar nit - removing errant apostrophe)
 
(One intermediate revision by one other user not shown)
Line 2: Line 2:


Run it at your own risk as root. It worked for me but otherwise comes with no guarantees except the usual one - if it breaks anything of yours, you still own all the pieces.
Run it at your own risk as root. It worked for me but otherwise comes with no guarantees except the usual one - if it breaks anything of yours, you still own all the pieces.
(Note: The script has been extended. Download from mysqltuner.com, link is on the right-hand side. --[[User:Max|Max]] 18:03, 25 February 2010 (UTC))


<code>
<code>
Line 100: Line 102:
  function check_for_plesk_passwords () {
  function check_for_plesk_passwords () {
   
   
  ## -- Check for the existance of plesk and login using it's credentials -- ##
  ## -- Check for the existance of plesk and login using its credentials -- ##
   
   
         if [ -f /etc/psa/.psa.shadow ] ; then
         if [ -f /etc/psa/.psa.shadow ] ; then

Latest revision as of 15:50, 10 March 2016

This is a shell script I found on the net. It uses MySQL internals to make recommendations about tuning your MySQL installation by adjusting /etc/my.cnf - the mysql startup file.

Run it at your own risk as root. It worked for me but otherwise comes with no guarantees except the usual one - if it breaks anything of yours, you still own all the pieces.

(Note: The script has been extended. Download from mysqltuner.com, link is on the right-hand side. --Max 18:03, 25 February 2010 (UTC))

#!/usr/bin/env bash

#########################################################################
#                                                                       #
#       MySQL performance tuning primer script                          #
#       Writen by: Matthew Montgomery <mmontgom@rackspace.com>          #
#       Inspired by: MySQLARd (http://gert.sos.be/demo/mysqlar/)        #
#       Version 1.2-r6                                                  #
#       Licenced under GPLv2                                            #
#                                                                       #
#########################################################################
#########################################################################
#                                                                       #
# Set this socket variable if you have multiple instances running or if # 
# we are unable to find your socket otherwise                           #
#                                                                       #
#########################################################################

socket=

function cecho ()

## -- Function to easliy print colored text -- ##

                                # Color-echo.
                                # Argument $1 = message
                                # Argument $2 = color
{
export black='\E[0m\c'
export boldblack='\E[1;0m\c'
export red='\E[31m\c'
export boldred='\E[1;31m\c'
export green='\E[32m\c'
export boldgreen='\E[1;32m\c'
export yellow='\E[33m\c'
export boldyellow='\E[1;33m\c'
export blue='\E[34m\c'
export boldblue='\E[1;34m\c'
export magenta='\E[35m\c'
export boldmagenta='\E[1;35m\c'
export cyan='\E[36m\c'
export boldcyan='\E[1;36m\c'
export white='\E[37m\c'
export boldwhite='\E[1;37m\c'

local default_msg="No message passed."
                                # Doesn't really need to be a local variable.

message=${1:-$default_msg}      # Defaults to default message.
color=${2:-$black}              # Defaults to black, if not specified.

  echo -e "$color"
  echo -e "$message"
  tput sgr0                     # Reset to normal.
  echo -e "$black"
  return
} 

function print_banner () {

## -- Banner -- ##

cecho "\t\c " $black
cecho "-- MYSQL PERFORMANCE TUNING PRIMER --" $boldblue
cecho "\t     - By: Matthew Montgomery -" $black

}

## -- Find the location of the mysql.sock file -- ##

function check_for_socket () {
        if [ -z "$socket" ] ; then
                if [ -S /var/lib/mysql/mysql.sock ] ; then
                        socket=/var/lib/mysql/mysql.sock
                elif [ -S /tmp/mysql.sock ] ; then
                        socket=/tmp/mysql.sock
                else
                        ps_socket=`netstat -ln | egrep "mysql(d)?\.sock" | awk '{ print $9 }'`
                        if [ "$ps_socket" ] ; then
                        socket=$ps_socket
                        fi
                fi
        fi
        if [ -S "$socket" ] ; then
                echo UP > /dev/null
        else
                cecho "No valid socket file "$socket" found!" $boldred
                cecho "mysqld is not running or it is installed in a custom location" $red
                cecho "Please set the $socket variable at the top of this script."
                exit 1
        fi
}


function check_for_plesk_passwords () {

## -- Check for the existance of plesk and login using its 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 check_mysql_login () {

## -- Test for running mysql -- ##

        is_up=`$mysqladmin ping 2>&1`
        if [ "$is_up" = "mysqld is alive" ] ; then
                echo UP > /dev/null
        elif [ "$is_up" != "mysqld is alive" ] ; then
                cecho "\n\c"
                cecho "- INITIAL LOGIN ATTEMPT FAILED -\n" $boldred
                if [ -z $prompted ] ; then
                find_webmin_passwords
                else
                        return 1
                fi

        else 
                cecho "Unknow exit status" $red
                exit -1
        fi
}

function final_login_attempt () {
        is_up=`$mysqladmin ping 2>&1`
        if [ "$is_up" = "mysqld is alive" ] ; then
                echo UP > /dev/null
        elif [ "$is_up" != "mysqld is alive" ] ; then
                cecho "- FINAL LOGIN ATTEMPT FAILED -\n" $boldred
                exit 1
        fi
}

function second_login_failed () {

## -- create a ~/.my.cnf and exit when all else fails -- ##

        cecho "- RETRY LOGIN ATTEMPT FAILED -\n" $boldred
        cecho "Could not auto detect login info!\n"
        read -p "Do you have your login handy ? [y/N] : "
        case $REPLY in 
                yes | y | Y | YES)
                answer1='yes'
                read -p "User: " user
                read -rsp "Password: " pass
                export mysql="$mysql -u$user -p$pass"
                export mysqladmin="$mysqladmin -u$user -p$pass"
                ;;
                *)
                cecho "\nPlease create a valid login to MySQL"
                cecho "Or, set correct values for  'user=' and 'password=' in ~/.my.cnf"
                ;;
        esac
        cecho "\n\c"
        read -p "Would you like me to create a ~/.my.cnf file for you? [y/N] : "
        case $REPLY in
                yes | y | Y | YES)
                answer2='yes'
                if [ ! -f ~/.my.cnf ] ; then
                        umask 077
                        echo -e "[client]\nuser=$user\npassword=$pass" > ~/.my.cnf
                        if [ "$answer1" != 'yes' ] ; then
                                exit 1
                        else
                                final_login_attempt
                                return 0
                        fi
                else
                        cecho "\n~/.my.cnf already exists!\n" $boldred
                        read -p "Replace ? [y/N] : "
                        if [ "$REPLY" = 'y' ] || [ "$REPLY = 'Y' " ] ; then 
                        echo -e "[client]\nuser=$user\npassword=$pass" > ~/.my.cnf
                                if [ "$answer1" != 'yes' ] ; then
                                        exit 1
                                else
                                        final_login_attempt
                                        return 0
                                fi
                        else
                                cecho "Please set the 'user=' and 'password=' values in ~/.my.cnf"
                                exit 1
                        fi
                fi
                ;;
                *)
                if [ "$answer1" != 'yes' ] ; then
                        exit 1
                else
                        final_login_attempt
                        return 0
                fi
                ;;
        esac
}

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
}

#########################################################################
#                                                                       #
#  Function to pull MySQL status variable                               #
#                                                                       #
#  Call using :                                                         #
#       mysql_status \'Mysql_status_variable\' bash_dest_variable       #
#                                                                       #
#########################################################################

function mysql_status () {
        local status=`$mysql -Bse "show /*!50000 global */ status like $1" | 
                awk '{ print $2 }'`
        export "$2"=$status
}

#########################################################################
#                                                                       #
#  Function to pull MySQL server runtime variable                       #
#                                                                       #
#  Call using :                                                         #
#       mysql_variable \'Mysql_server_variable\' bash_dest_variable     #
#       - OR -                                                          #
#       mysql_variableTSV \'Mysql_server_variable\' bash_dest_variable  #
#                                                                       #
#########################################################################

function mysql_variable () {
        local variable=`$mysql -e "show /*!500000 global */ variables like $1" | 
                grep -v Variable_name | awk '{ print $2 }'`
        export "$2"=$variable
}
function mysql_variableTSV () {
        local variable=`$mysql -e "show variables like $1" | 
                grep -v Variable_name | awk -F \t '{ print $2 }'`
        export "$2"=$variable
}

function divide () {

# -- Divide two intigers -- #

        usage="$0 dividend divisor '$variable' scale"
        if [ $1 -ge 1 ] ; then
                dividend=$1
        else
                cecho "Invalid Dividend" $red
                echo $usage
                exit 1
        fi
        if [ $2 -ge 1 ] ; then
                divisor=$2
        else
                cecho "Invalid Divisor" $red
                echo $usage
                exit 1
        fi
        if [ ! -n $3 ] ; then
                cecho "Invalid variable name" $red
                echo $usage
                exit 1
        fi
        if [ -z $4 ] ; then
                scale=2
        elif [ $4 -ge 0 ] ; then
                scale=$4
        else
                cecho "Invalid scale" $red
                echo $usage
                exit 1
        fi
        export $3=$(echo "scale=$scale; $dividend / $divisor" | bc -l)
}

function human_readable () {

#########################################################################
#                                                                       #
#  Convert a value in to human readable size and populate a variable    #
#  with the result.                                                     #
#                                                                       #
#  Call using:                                                          #
#       human_readable $value 'variable name'                           #
#                                                                       #
#########################################################################

        ## value=$1
        ## variable=$2
        scale=$3

        if [ $1 -gt 1048576 ] ; then
                if [ -z $3 ] ; then 
                        scale=0
                fi
                divide $1 1048576 "$2" $scale
                unit="M"
        elif [ $1 -gt 1024 ] ; then
                if [ -z $3 ] ; then
                        scale=2
                fi
                divide $1 1024 "$2" $scale
                unit="K"
        else
                export "$2"=$1
                unit="bytes"
        fi
        # let "$2"=$HR
}

function human_readable_time () {

########################################################################
#                                                                      #
#       Function to produce human readable time                        #
#                                                                      #
########################################################################

        usage="$0 seconds 'variable'"
        if [ -z $1 ] || [ -z $2 ] ; then
                cecho $usage $red
                exit 1
        fi
        days=$(echo "scale=0 ; $1 / 86400" | bc -l)
        remainder=$(echo "scale=0 ; $1 % 86400" | bc -l)
        hours=$(echo "scale=0 ; $remainder / 3600" | bc -l)
        remainder=$(echo "scale=0 ; $remainder % 3600" | bc -l)
        minutes=$(echo "scale=0 ; $remainder / 60" | bc -l)
        seconds=$(echo "scale=0 ; $remainder % 60" | bc -l)
        export $2="$days days $hours hrs $minutes min $seconds sec"
}

function check_mysql_version () {

## -- Print Version Info -- ##

        mysql_variable \'version\' mysql_version
        mysql_variable \'version_compile_machine\' mysql_version_compile_machine

        cecho "MySQL Version $mysql_version $mysql_version_compile_machine"
}

function post_uptime_warning () {

#########################################################################
#                                                                       #
#  Present a reminder that mysql must run for a couple of days to       #
#  build up good numbers in server status variables before these tuning #
#  suggestions should be used.                                          #
#                                                                       #
#########################################################################

        mysql_status \'Uptime\' uptime
        mysql_status \'Threads_connected\' threads
        let queries_per_sec=$questions/$uptime
        human_readable_time $uptime uptimeHR

        cecho "Uptime = $uptimeHR"
        cecho "Avg. qps = $queries_per_sec"
        cecho "Total Questions = $questions"
        cecho "Threads Connected = $threads"
        echo

        if [ $uptime -gt 172800 ] ; then
                cecho "Server has been running for over 48hrs."
                cecho "It should be safe to follow these recommendations"
        else
                cecho "Warning: \c" $boldred
                cecho "Server has not been running for at least 48hrs." $boldred
                cecho "It may not be safe to use these recommendations" $boldred

        fi
        echo ""
        cecho "To find out more information on how each of these" $red
        cecho "runtime variables effects performance visit:" $red
        if [ $major_version == '3.23' ] || [ $major_version == '4.0' ] || [ $major_version == '4.1' ]; then
        cecho "http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html" $boldblue
        elif [ $major_version == '5.0' ] || [ $major_version == '5.1' ] ; then
        cecho "http://dev.mysql.com/doc/refman/$major_version/en/server-system-variables.html" $boldblue
        else
        echo "UNSUPPORTED MYSQL VERSION"
        exit 1
        fi
}

function check_slow_queries () {

## -- Slow Queries -- ## 

        cecho "SLOW QUERIES" $boldblue

        mysql_status \'Slow_queries\' slow_queries
        mysql_variable \'long_query_time\' long_query_time
        mysql_variable \'log%queries\' log_slow_queries
        prefered_query_time=5
        if [ -e /etc/my.cnf ] ; then
                if [ -z $log_slow_queries ] ; then
                        log_slow_queries=`grep log-slow-queries /etc/my.cnf`
                fi
        fi
        cecho "Current long_query_time = $long_query_time sec."
        cecho "You have \c"
        cecho "$slow_queries \c" $boldred 
        cecho "out of \c"
        cecho "$questions \c" $boldred
        cecho "that take longer than $long_query_time sec. to complete"

        if [ "$log_slow_queries" = 'ON' ] ; then
                cecho "The slow query log is enabled."
        elif [ "$log_slow_queries" = 'OFF' ] ; then
                cecho "The slow query log is \c"
                cecho "NOT \c" $boldred
                cecho "enabled."
        elif [ -z $log_slow_queries ] ; then
                cecho "The slow query log is \c"
                cecho "NOT \c" $boldred
                cecho "enabled."
        else
                cecho "Error: $log_slow_queries" $boldred
        fi

        if [ $long_query_time -gt $prefered_query_time ] ; then
                cecho "Your long_query_time may be too high, I typically set this under $prefered_query_time sec." $red
        else
                cecho "Your long_query_time seems to be fine" $green
        fi 
}

function check_used_connections () {

## -- Used Connections -- ##

        mysql_variable \'max_connections\' max_connections
        mysql_status \'Max_used_connections\' max_used_connections
        mysql_status \'Threads_connected\' threads_connected

        let connections_ratio=$max_used_connections*100/$max_connections

        cecho "MAX CONNECTIONS" $boldblue
        cecho "Current max_connections = $max_connections"
        cecho "Current threads_connected = $threads_connected"
        cecho "Historic max_used_connections = $max_used_connections"
        cecho "The number of used connections is \c"
        if [ $connections_ratio -ge 85 ] ; then
                txt_color=$red
        else 
                txt_color=$green
        fi
        # cecho "$max_used_connections \c" $txt_color
        # cecho "which is \c"
        cecho "$connections_ratio% \c" $txt_color
        cecho "of the configured maximum."
        unset txt_color

        if [ $connections_ratio -ge 85 ] ; then
                cecho "You should raise max_connections" $red
        else 
                cecho "Your max_connections variable seems to be fine." $green
        fi
}

function check_threads() {

## -- Worker Threads -- ##

        cecho "WORKER THREADS" $boldblue

        mysql_status \'Threads_created\' threads_created1
        sleep 1
        mysql_status \'Threads_created\' threads_created2

        mysql_status \'Threads_cached\' threads_cached
        mysql_status \'Uptime\' uptime
        mysql_variable \'thread_cache_size\' thread_cache_size

        let historic_threads_per_sec=$threads_created1/$uptime
        let current_threads_per_sec=$threads_created2-$threads_created1;

        cecho "Current thread_cache_size = $thread_cache_size"
        cecho "Current threads_cached = $threads_cached"
        cecho "Current threads_per_sec = $current_threads_per_sec"
        cecho "Historic threads_per_sec = $historic_threads_per_sec"

        if [ $historic_threads_per_sec -ge 2 ] && [ $threads_cached -le 1 ] ; then
                cecho "Threads created per/sec are overrunning threads cached" $red
                cecho "You should raise thread_cache_size" $red
        elif [ $current_threads_per_sec -ge 2 ] ; then
                cecho "Threads created per/sec are overrunning threads cached" $red
                cecho "You should raise thread_cache_size" $red
        else
                cecho "Your thread_cache_size is fine" $green
        fi
}

function check_key_buffer_size () {

## -- Key buffer Size -- ##

        cecho "KEY BUFFER" $boldblue

        mysql_status \'Key_read_requests\' key_read_requests
        mysql_status \'Key_reads\' key_reads
        mysql_status \'Key_blocks_used\' key_blocks_used
        mysql_status \'Key_blocks_unused\' key_blocks_unused
        mysql_variable \'key_buffer_size\' key_buffer_size
        mysql_variable \'datadir\' datadir

        
        myisam_indexes=`$mysql -Bse "/*!50000 SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where ENGINE='MyISAM' 
*/"`
        OS=$(uname)

        if [ "$OS" == 'Darwin' ] || [ "$OS" == 'FreeBSD' ] || [ "$OS" == 'OpenBSD' ] ; then
                duflags=
        else
                duflags='-b'
        fi
        if [ -z "$myisam_indexes" ] ; then
                myisam_indexes=`find $datadir -name '*.MYI' -exec du $duflags '{}' \; | awk '{ s += $1 } END { 
printf("%i\n", s )}'`
        fi

        if [ $key_reads -eq 0 ] ; then
                cecho "No key reads?!" $boldred
                cecho "Seriously look into using some indexes" $red
                key_cache_miss_rate=0
                key_buffer_ratio=0
                key_buffer_ratioRND=0
        else
                let key_cache_miss_rate=$key_read_requests/$key_reads
                if [ ! -z $key_blocks_unused ] ; then
                        let key_blocks_total=$key_blocks_used+$key_blocks_unused
                        divide $key_blocks_used $key_blocks_total key_buffer_fill 2
                        key_buffer_ratio=$(echo "$key_buffer_fill * 100" | bc -l)
                        key_buffer_ratioRND=$(echo "scale=0; $key_buffer_ratio / 1" | bc -l)
                else
                        key_buffer_ratio='Unknown'
                        key_buffer_ratioRND=75
                fi
        fi

        human_readable $myisam_indexes myisam_indexes_HR 0
        cecho "Current MyISAM index space = $myisam_indexes_HR $unit" 

        human_readable  $key_buffer_size key_buffer_size_HR 0
        cecho "Current key_buffer_size = $key_buffer_size_HR $unit"
        cecho "Key cache miss rate is 1 / $key_cache_miss_rate"
        cecho "Key buffer fill ratio = $key_buffer_ratio %" 

        if [ $key_cache_miss_rate -le 100 ] && [ $key_cache_miss_rate -gt 0 ] && [ $key_buffer_ratioRND -ge 80 ]; then
                cecho "You could increase key_buffer_size" $boldred
                cecho "It is safe to raise this up to 1/4 of total system memory;"
                cecho "assuming this is a dedicated database server."
        elif [ $key_buffer_ratioRND -ge 80 ] && [ $key_buffer_size -le $myisam_indexes ] ; then
                cecho "You could increase key_buffer_size" $boldred
                cecho "It is safe to raise this up to 1/4 of total system memory;"
                cecho "assuming this is a dedicated database server."
        elif [ $key_cache_miss_rate -ge 10000 ] || [ $key_buffer_ratioRND -le 50  ] ; then
                cecho "Your key_buffer_size seems to be too high." $red 
                cecho "Perhaps you can use these resources elsewhere" $red
        else
                cecho "Your key_buffer_size seems to be fine" $green
        fi
}

function check_query_cache () {

## -- Query Cache -- ##

        cecho "QUERY CACHE" $boldblue

        mysql_variable \'version\' mysql_version
        mysql_variable \'query_cache_size\' query_cache_size
        mysql_status \'Qcache_free_memory\' qcache_free_memory
        mysql_status \'Qcache_lowmem_prunes\' qcache_lowmem_prunes

        if [ -z $query_cache_size ] ; then
                cecho "You are using MySQL $mysql_version, no query cache is supported. \nI recommend an upgrade to MySQL 
4.0 or better" $red
        elif [ $query_cache_size -eq 0 ] ; then
                cecho "Query cache is supported but not enabled" $red
                cecho "Perhaps you should set the query_cache_size" $red
        else
                let qcache_used_memory=$query_cache_size-$qcache_free_memory
                qcache_fill_ratio=$(echo "scale=2; $qcache_used_memory * 100 / $query_cache_size" | bc -l)
                qcache_fill_ratio_HR=$(echo "scale=0; $qcache_fill_ratio / 1" | bc -l)
                cecho "Query cache is enabled" $green
                human_readable $query_cache_size query_cache_size_HR
                cecho "Current query_cache_size = $query_cache_size_HR $unit"
                human_readable $qcache_used_memory qcache_used_memory_HR
                cecho "Current query_cache_used = $qcache_used_memory_HR $unit"
                cecho "Current Query cache fill ratio = $qcache_fill_ratio %"
                if [ $qcache_fill_ratio_HR -le 25 ] ; then
                        cecho "Your query_cache_size seems to be too high." $red
                        cecho "Perhaps you can use these resources elsewhere" $red
                fi
                if [ $qcache_lowmem_prunes -ge 50 ] && [ $qcache_fill_ratio_HR -ge 80 ]; then
                        cecho "However, \c"
                        cecho "$qcache_lowmem_prunes \c" $boldred
                        cecho "queries have been removed from the query cache due to lack of memory"
                        cecho "Perhaps you should raise query_cache_size" $boldred
                fi
        fi

}

function check_sort_operations () {

## -- Sort Operations -- ##

        cecho "SORT OPERATIONS" $boldblue

        mysql_status \'Sort_merge_passes\' sort_merge_passes
        mysql_status \'Sort_scan\' sort_scan
        mysql_status \'Sort_range\' sort_range
        mysql_variable \'sort_buffer%\' sort_buffer_size 
        mysql_variable \'read_rnd_buffer_size\' read_rnd_buffer_size 

        let total_sorts=$sort_scan+$sort_range
        if [ -z $read_rnd_buffer_size ] ; then
                mysql_variable \'record_buffer\' read_rnd_buffer_size
        fi

        ## Correct rounding error in mysqld where 512K != 524288 ##
        let sort_buffer_size=$sort_buffer_size+8
        let read_rnd_buffer_size=$read_rnd_buffer_size+8

        human_readable $sort_buffer_size sort_buffer_size_HR
        cecho "Current sort_buffer_size = $sort_buffer_size_HR $unit"

        human_readable $read_rnd_buffer_size read_rnd_buffer_size_HR
        cecho "Current record/read_rnd_buffer_size = $read_rnd_buffer_size_HR $unit"

        if [ $total_sorts -eq 0 ] ; then 
                cecho "No sort operations have been performed"
                passes_per_sort=0
        fi
        if [ $sort_merge_passes -ne 0 ] ; then
                let passes_per_sort=$sort_merge_passes/$total_sorts
        else
                passes_per_sort=0
        fi

        if [ $passes_per_sort -ge 2 ] ; then
                cecho "On average \c"
                cecho "$passes_per_sort \c" $boldred
                cecho "sort merge passes are made per sort operation"
                cecho "You should raise your sort_buffer_size"
                cecho "You should also raise your \c"
                if [ $major_version == '3.23' ] ; then 
                        cecho "record_rnd_buffer_size"
                else
                        cecho "read_rnd_buffer_size"
                fi
        else
                cecho "Sort buffer seems to be fine" $green
        fi
}

function check_join_operations () {

## -- Joins -- ##

        cecho "JOINS" $boldblue

        mysql_status \'Select_full_join\' select_full_join
        mysql_status \'Select_range_check\' select_range_check
        mysql_variable \'join_buffer%\' join_buffer_size

        human_readable $join_buffer_size join_buffer_size_HR

        cecho "Current join_buffer_size = $join_buffer_size_HR $unit"
        cecho "You have had $select_full_join queries where a join could not use an index properly"

        if [ $select_range_check -eq 0 ] && [ $select_full_join -eq 0 ] ; then
                cecho "Your joins seem to be using indexes properly" $green
        fi
        if [ $select_full_join -gt 0 ] ; then
                print_error='true'
        fi
        if [ $select_range_check -gt 0 ] ; then
                cecho "You have had $select_range_check joins without keys that check for key usage after each row" $red
                print_error='true'
        fi
        ## Debuging ##
        # print_error='true'
        if [ $print_error ] ; then 
                if [ $major_version == '3.23' ] || [ $major_version == '4.0' ] ; then
                        cecho "You should enable \"log-long-format\" "
                elif [ $major_version == '4.1' ] || [ $major_version == '5.0' ] || [ $major_version == '5.1' ] ; then
                        cecho "You should enable \"log-queries-not-using-indexes\""
                fi
                cecho "Then look for non indexed joins in the slow query log."
                cecho "If you are unable to optimize your queries you may want to increase your"
                cecho "join_buffer_size to accommodate larger joins in one pass."
        fi

        # XXX Add test for join_buffer_size 
}

check_tmp_tables () {

## -- Temp Tables -- ##

        cecho "TEMP TABLES" $boldblue

        mysql_status \'Created_tmp_tables\' created_tmp_tables 
        mysql_status \'Created_tmp_disk_tables\' created_tmp_disk_tables
        mysql_variable \'tmp_table_size\' tmp_table_size

        human_readable $tmp_table_size tmp_table_size_HR 

        if [ $created_tmp_tables -eq 0 ] ; then
                tmp_disk_tables=0
        else
                let tmp_disk_tables=created_tmp_disk_tables*100/created_tmp_tables
        fi
        cecho "Current tmp_table_size = $tmp_table_size_HR $unit"
        cecho "$tmp_disk_tables% of tmp tables created were disk based"
        if [ $tmp_disk_tables -ge 25 ] ; then
                cecho "Perhaps you should increase your tmp_table_size" $red
        else
                cecho "Created disk tmp tables ratio seems fine" $green
        fi
}

function check_table_cache () {

## -- Table Cache -- ##

        cecho "TABLE CACHE" $boldblue

        mysql_variable \'datadir\' datadir
        mysql_variable \'table_cache\' table_cache

        ## /* MySQL +5.1 version of table_cache */ ## 
        mysql_variable \'table_open_cache\' table_open_cache
        mysql_variable \'table_definition_cache\' table_definition_cache

        mysql_status \'Open_tables\' open_tables
        mysql_status \'Opened_tables\' opened_tables
        mysql_status \'Open_table_definitions\' open_table_definitions
 
#       socket_owner=`find $socket -printf '%u\n'`
        socket_owner=`ls -l $socket | awk '{ print $3 }'`
        script_runner=`whoami`

        table_count=`$mysql -Bse "/*!50000 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' 
*/"`

        if [ -z $table_count ] ; then
                if [ "$script_runner" != "$socket_owner" ] && [ "$script_runner" != "root" ] ; then
                        cecho "You are not '$socket_owner' or 'root'" $red
                        cecho "I am unable to determine the table_count!" $red
                else
                        table_count=`find $datadir 2>&1 | grep -c .frm$`
                fi
        fi
        if [ $table_open_cache ] ; then
                table_cache=$table_open_cache
        fi

        if [ $opened_tables -ne 0 ] && [ $table_cache -ne 0 ] ; then 
                let table_cache_hit_rate=$open_tables*100/$opened_tables
                let table_cache_fill=$open_tables*100/$table_cache
        elif [ $opened_tables -eq 0 ] && [ $table_cache -ne 0 ] ; then
                table_cache_hit_rate=100
                let table_cache_fill=$open_tables*100/$table_cache
        else
                cecho "ERROR no table_cache ?!" $boldred
                exit 1
        fi
        if [ $table_cache ] && [ ! $table_open_cache ] ; then
                cecho "Current table_cache value = $table_cache tables"
        fi
        if [ $table_open_cache ] ; then
                cecho "Current table_open_cache = $table_open_cache tables"
                cecho "Current table_definition_cache = $table_definition_cache tables"
        fi
        if [ $table_count ] ; then
        cecho "You have a total of $table_count tables"
        fi

        if  [ $table_cache_fill -lt 95 ] ; then
                cecho "You have \c"
                cecho "$open_tables \c" $green
                cecho "open tables." 
                cecho "The table_cache value seems to be fine" $green
        elif [ $table_cache_hit_rate -le 85 -o  $table_cache_fill -ge 95 ]; then
                cecho "You have \c"
                cecho "$open_tables \c" $boldred
                cecho "open tables."
                cecho "Current table_cache hit rate is \c" 
                cecho "$table_cache_hit_rate%\c" $boldred
                cecho ", while \c"
                cecho "$table_cache_fill% \c" $boldred
                cecho "of your table cache is in use"
                cecho "You should probably increase your table_cache" $red
        else
                cecho "Current table_cache hit rate is \c"
                cecho "$table_cache_hit_rate%\c" $green
                cecho ", while \c"
                cecho "$table_cache_fill% \c" $green
                cecho "of your table cache is in use"
                cecho "The table cache value seems to be fine" $green
        fi
        if [ $table_definition_cache ] && [ $table_definition_cache -le $table_count ] && [ $table_count -ge 100 ] ; then
                cecho "You should probably increase your table_definition_cache value." $red
        fi
}

function check_table_locking () {

## -- Table Locking -- ##

        cecho "TABLE LOCKING" $boldblue

        mysql_status \'Table_locks_waited\' table_locks_waited
        mysql_status \'Table_locks_immediate\' table_locks_immediate
        mysql_variable \'concurrent_insert\' concurrent_insert
        mysql_variable \'low_priority_updates\' low_priority_updates
        if [ "$concurrent_insert" = 'ON' ]; then
                let concurrent_insert=1
        elif [ "$concurrent_insert" = 'OFF' ]; then
                let concurrent_insert=0
        fi

        cecho "Current Lock Wait ratio = \c"
        if [ $table_locks_waited -gt 0 ]; then
                let immediate_locks_miss_rate=$table_locks_immediate/$table_locks_waited
                cecho "1 : $immediate_locks_miss_rate" $red 
        else
                let immediate_locks_miss_rate=99999 # perfect
                cecho "0 : $questions" $green
        fi
        if [ $immediate_locks_miss_rate -lt 5000 ] ; then
                cecho "You may benefit from selective use of InnoDB."
                if [ "$low_priority_updates" == 'OFF' ] ; then
                cecho "If you have long running SELECT's against MyISAM tables \c"
                cecho "and perform frequent updates consider setting 'low_priority_updates=1'"
                fi
                if [ $concurrent_insert -le 1 ] && [ $major_version == '5.0' ] ; then
                cecho "If you have a high concurrentcy of inserts on Dynamic row-lenght tables \c"
                cecho "consider setting 'concurrent_insert=2'."
                elif  [ $concurrent_insert -le 1 ] && [ $major_version == '5.1' ] ; then
                cecho "If you have a high concurrentcy of inserts on Dynamic row-lenght tables \c"
                cecho "consider setting 'concurrent_insert=2'."
                fi
        else
                cecho "Your table locking seems to be fine" $green
        fi
}

function check_table_scans () {

## -- Table Scans -- ##

        cecho "TABLE SCANS" $boldblue

        mysql_status \'Com_select\' com_select
        mysql_status \'Handler_read_rnd_next\' read_rnd_next
        mysql_variable \'read_buffer_size\' read_buffer_size

        if [ -z $read_buffer_size ] ; then
                mysql_variable \'record_buffer\' read_buffer_size
        fi

        human_readable $read_buffer_size read_buffer_size_HR
        cecho "Current read_buffer_size = $read_buffer_size_HR $unit"

        if [ $com_select -gt 0 ] ; then
                let full_table_scans=$read_rnd_next/$com_select 
                cecho "Current table scan ratio = $full_table_scans : 1"
                if [ $full_table_scans -ge 4000 ] && [ $read_buffer_size -le 2097152 ] ; then
                        cecho "You have a high ratio of sequential access requests to SELECTs" $red
                        cecho "You may benefit from raising \c" $red
                        if [ $major_version == '3.23' ] ; then 
                                cecho "record_buffer \c" $red
                        else
                                cecho "read_buffer_size \c" $red
                        fi
                        cecho "and/or improving your use of indexes." $red
                elif [ $read_buffer_size -gt 2097152 ] ; then 
                        cecho "read_buffer_size is over 2 MB \c" $red 
                        cecho "there is probably no need for such a large read_buffer" $red

                else
                        cecho "read_buffer_size seems to be fine" $green
                fi
        else
                cecho "read_buffer_size seems to be fine" $green
        fi
}


function check_innodb_status () {

## -- InnoDB -- ##

        mysql_variable \'have_innodb\' have_innodb

        if [ "$have_innodb" = "YES" ] ; then
                mysql_variable \'innodb_buffer_pool_size\' innodb_buffer_pool_size
                echo
                cecho "INNODB STATUS" $boldblue
                innodb_indexes=`$mysql -Bse "/*!50000 SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where 
ENGINE='InnoDB' */"`

                if [ ! -z "$innodb_indexes" ] ; then
                human_readable $innodb_indexes innodb_indexes_HR 0
                cecho "Current InnoDB index space = $innodb_indexes_HR $unit"
                else
                cecho "Cannot find InnoDB index space prior to 5.0.x" $red
                fi

                human_readable $innodb_buffer_pool_size innodb_buffer_pool_sizeHR
                cecho "Current innodb_buffer_pool_size = $innodb_buffer_pool_sizeHR $unit"
                cecho "Depending on how much space your innodb indexes take up it may be safe"  
                cecho "to increase this value to up to 1 / 3 of total system memory"
                echo
                $mysql -s -e "SHOW /*!50000 ENGINE */INNODB STATUS\G"
        else
                cecho "No InnoDB Support Enabled!" $boldred
        fi
}

function total_memory_used () {

## -- Total Memory Usage -- ##
        cecho "MEMORY USAGE" $boldblue

        mysql_variable \'read_buffer_size\' read_buffer_size
        mysql_variable \'sort_buffer_size\' sort_buffer_size
        mysql_variable \'thread_stack\' thread_stack
        mysql_variable \'max_connections\' max_connections
        mysql_status \'Max_used_connections\' max_used_connections

        let per_thread_buffers=($read_buffer_size+$sort_buffer_size+$thread_stack)*$max_used_connections
        let per_thread_max_buffers=($read_buffer_size+$sort_buffer_size+$thread_stack)*$max_connections

        mysql_variable \'innodb_buffer_pool_size\' innodb_buffer_pool_size
        if [ -z $innodb_buffer_pool_size ] ; then
        innodb_buffer_pool_size=0
        fi

        mysql_variable \'innodb_additional_mem_pool_size\' innodb_additional_mem_pool_size
        if [ -z $innodb_additional_mem_pool_size ] ; then
        innodb_additional_mem_pool_size=0
        fi

        mysql_variable \'innodb_log_buffer_size\' innodb_log_buffer_size
        if [ -z $innodb_log_buffer_size ] ; then
        innodb_log_buffer_size=0
        fi

        mysql_variable \'key_buffer_size\' key_buffer_size

        mysql_variable \'query_cache_size\' query_cache_size
        if [ -z $query_cache_size ] ; then
        query_cache_size=0
        fi


        let 
global_buffers=$innodb_buffer_pool_size+$innodb_additional_mem_pool_size+$innodb_log_buffer_size+$key_buffer_size+$query_cache_size

        let total_memory=$global_buffers+$per_thread_buffers
        let max_memory=$global_buffers+$per_thread_max_buffers
        human_readable $total_memory total_memoryHR 0
        cecho "Max Memory Ever Allocated : $total_memoryHR $unit" $boldred
        human_readable $max_memory max_memoryHR 0
        cecho "Configured Max Memory Limit : $max_memoryHR $unit" $boldred

        total_system_memory=`free -b | grep -v buffers |  awk '{ s += $2 } END { printf("%ld\n", s ) }'`
        human_readable $total_system_memory total_system_memoryHR 0
        cecho "Total System Memory : $total_system_memoryHR $unit" $boldred
}

function snarky () {

## -- Be Snarky -- ##

        fortune=`which fortune 2>/dev/null` 
        if [ -z $fortune ] ; then
                echo "What the hell sort of straight-lace bastard doesn't have fortune installed?"
        else
                $fortune
        fi
}

## Required Functions  ## 

function login_validation () {
        check_for_socket                # determine the socket location -- 1st login
        check_for_plesk_passwords       # determine the login method -- 2nd login
        check_mysql_login               # determine if mysql is accepting login -- 3rd login
        export major_version=`$mysql -Bse 'select substring_index(version(), ".", +2)'`
        export OS=`uname`
        mysql_status \'Questions\' questions
}

## Optional Components Groups ##

function banner_info () {
        print_banner            ; echo
        check_mysql_version     ; echo
        post_uptime_warning     ; echo
}

function misc () {
        check_slow_queries      ; echo
        check_used_connections  ; echo
        check_threads           ; echo
}

function memory () {
        total_memory_used       ; echo
        check_key_buffer_size   ; echo
        check_query_cache       ; echo
        check_sort_operations   ; echo
        check_join_operations   ; echo
}

function file () {
        check_table_cache       ; echo
        check_tmp_tables        ; echo
        check_table_scans       ; echo
        check_table_locking     ; echo
}

function all () {
        banner_info
        misc
        memory
        file
#       snarky
}

function prompt () {
        prompted='true'
        read -p "Username [anonymous] : " user
        read -rsp "Password [<none>] : " pass
        cecho "\n\c"
        read -p "Socket [ /var/lib/mysql/mysql.sock ] : " socket
        if [ -z $socket ] ; then
                export socket='/var/lib/mysql/mysql.sock'
        fi
        if [ -n $pass ] ; then
                pass_flag='-p'
        fi
        mysql="mysql -S $socket -u$user $pass_flag$pass"
        mysqladmin="mysqladmin -S $socket -u$user $pass_flag$pass"
        echo $mysql
        check_for_socket
        check_mysql_login
        if [ $? = 1 ] ; then
                exit 1
        fi
        read -p "Mode to test (see usage:) [all] : " pmode
        case $pmode in
                banner )
                banner_info 
                ;;
                misc )
                misc
                ;;
                memory )
                memory
                ;; 
                file )
                file
                ;;
                innodb )
                innodb
                ;;
                all | *)
                all
                ;;
        esac 
}

if [ -z $1 ] ; then
        login_validation
        mode='ALL'
elif [ "$1" != "prompt" ] || [ "$1" != "PROMPT" ] ; then
        login_validation
        mode=$1
elif [ "$1" = "prompt" ] || [ "$1" = "PROMPT" ] ; then
        mode=$1
fi

case $mode in 
        ALL | all )
        cecho "\n\c"
        all
        ;;
        mem | memory |  MEM | MEMORY )
        cecho "\n\c"
        memory
        ;;
        file | FILE | disk | DISK )
        cecho "\n\c"
        file
        ;;
        banner | BANNER | header | HEADER | head)
        banner_info
        ;;
        misc | MISC | miscelaneous )
        cecho "\n\c"
        misc
        ;;
        innodb | INNODB )
        banner_info
        check_innodb_status ; echo
        ;;
        prompt | PROMPT )
        prompt
        ;;
        *)
        cecho "usage: $0 [ all | banner | file | innodb | memory | misc | promp ]" $boldred
        exit 1
        ;;
esac