UBB.threads PHP Forum Software Community Forums UBB.threads 7 Version 7 Support General MySQL question

#!/usr/bin/env bash

#########################################################################
#									#
#	MySQL performance tuning primer script				#
#	Writen by: Matthew Montgomery <mmontgomery@mysql.com>		#
#	Inspired by: MySQLARd (http://gert.sos.be/demo/mysqlar/)	#
#	Version: 1.3-r7		Released: 2007-12-20			#
#	Licenced under GPLv2                                            #
#									#
#########################################################################

#########################################################################
#									#
# Little known feature: 1st argument is execution mode			#
#									#
#	Usage: ./tuning-primer.sh [ mode ] 				#
#									#
#	Available Modes: 						#
#		all : 		perform all checks (default)		#
#		prompt : 	prompt for login credintials and socket	#
#				and execution mode			#
# 		mem, memory : 	run checks for tunable options which	#
#				effect memory usage			#
#		disk, file :	run checks for options which effect	#
#				i/o performance or file handle limits	#
#		innodb :	run InnoDB checks /* to be improved */	# 
#		misc : 		run checks for that don't categorise	#
#				well Slow Queries, Used Connections,	#
#				and Worker Threads			#
#########################################################################
#									#
# Set this socket variable ONLY if you have multiple instances running	# 
# or we are unable to find your socket, and you don't want to to be	#
# prompted for input each time you run this script.			#
#									#
#########################################################################
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
			if [ -S "$ps_socket" ] ; then
			socket=$ps_socket
			fi
		fi
	fi
	if [ -S "$socket" ] ; then
		echo UP > /dev/null
	else
		cecho "\c"
		cecho "No valid socket file \"$socket\" found!" $boldred
		cecho "The mysqld process is not running or it is installed in a custom location." $red
		cecho "If you are sure mysqld is running, execute script in \"prompt\" mode or set " $red
		cecho "the socket= variable at the top of this script" $red
		exit 1
	fi
}


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 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
		cecho "Unable to log into socket: $socket" $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
		if [ -z $pass ] ; then
		export mysql="$mysql -u$user"
		export mysqladmin="$mysqladmin -u$user"
		else
		export mysql="$mysql -u$user -p$pass"
		export mysqladmin="$mysqladmin -u$user -p$pass"
		fi
		;;
		*)
		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 -Bse "show /*!50000 global */ variables like $1" | awk '{ print $2 }')
	export "$2"=$variable
}
function mysql_variableTSV () {
        local variable=$($mysql -Bse "show /*!50000 global */ variables like $1" | awk -F \t '{ print $2 }')
        export "$2"=$variable
}

function float2int () {
        local variable=$(echo "$1 / 1" | bc -l)
        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' [ places of precision]	#
#									#
#########################################################################

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

	if [ $1 -ge 1073741824 ] ; then
		if [ -z $3 ] ; then
			scale=2
		fi
		divide $1 1073741824 "$2" $scale
		unit="G"
	elif [ $1 -ge 1048576 ] ; then
		if [ -z $3 ] ; then 
			scale=0
		fi
		divide $1 1048576 "$2" $scale
	        unit="M"
	elif [ $1 -ge 1024 ] ; then
		if [ -z $3 ] ; then
			scale=0
		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
	
if [ $major_version == '3.23' ] || [ $major_version == '4.0' ] ; then
	cecho "MySQL Version $mysql_version $mysql_version_compile_machine is EOL please upgrade to MySQL 4.1 or later" $boldred
else
	cecho "MySQL Version $mysql_version $mysql_version_compile_machine"
fi


}

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
	cecho "UNSUPPORTED MYSQL VERSION" $boldred
	exit 1
	fi
	cecho "Visit http://www.mysql.com/products/enterprise/advisors.html" $boldblue
	cecho "for info about MySQL's Enterprise Monitoring and Advisory Service" $boldblue
}

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 [ "$major_version" = '5.1' ]; then
		float2int long_query_time long_query_timeInt
	else
		let long_query_timeInt=$long_query_time
	fi

	if [ $long_query_timeInt -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
		error=1
	elif [ $connections_ratio -le 10 ] ; then
		txt_color=$red
		error=2
	else
		txt_color=$green
		error=0
	fi
	# cecho "$max_used_connections \c" $txt_color
	# cecho "which is \c"
	cecho "$connections_ratio% \c" $txt_color
	cecho "of the configured maximum."

	if [ $error -eq 1 ] ; then
		cecho "You should raise max_connections" $txt_color
	elif [ $error -eq 2 ] ; then
		cecho "You are using less than 10% of your configured max_connections." $txt_color
		cecho "Lowering max_connections could help to avoid an over-allocation of memory" $txt_color
		cecho "See \"MEMORY USAGE\" section to make sure you are not over-allocating" $txt_color
	else 
		cecho "Your max_connections variable seems to be fine." $txt_color
	fi
	unset txt_color
}

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
        mysql_variable \'version_compile_machine\' mysql_version_compile_machine
	myisam_indexes=$($mysql -Bse "/*!50000 SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where ENGINE='MyISAM' */")

	if [ -z $myisam_indexes ] ; then
		myisam_indexes=$(find $datadir -name '*.MYI' -exec du $duflags '{}' \; 2>&1 | awk '{ s += $1 } END { printf("%.0f\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_indexesHR 0
	cecho "Current MyISAM index space = $myisam_indexesHR $unit" 

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

	if [ "$major_version" == '5.1' ] && [ $mysql_version_num -lt '5123' ] ; then
		if [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then
			cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.1.23 " $boldred
			cecho "See Bug#5731, Bug#29419, Bug#29446" $boldred
		fi
	fi
	if [ "$major_version" == '5.0' ] && [ $mysql_version_num -lt '5052' ] ; then
		if [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then
			cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52 " $boldred
			cecho "See Bug#5731, Bug#29419, Bug#29446" $boldred
		fi
	fi
	if [ "$major_version" == '4.1' -o "$major_version" == '4.0' ] && [ $key_buffer_size -ge 4294967296 ] && ( echo "x86_64 ppc64 ia64 sparc64 i686" | grep -q $mysql_version_compile_machine ) ; then
		cecho "Using key_buffer_size > 4GB will cause instability in versions prior to 5.0.52 " $boldred
		cecho "Reduce key_buffer_size to a safe value" $boldred
		cecho "See Bug#5731, Bug#29419, Bug#29446" $boldred
	fi

	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_variable \'query_cache_limit\' query_cache_limit
	mysql_variable \'query_cache_min_res_unit\' query_cache_min_res_unit
	mysql_status \'Qcache_free_memory\' qcache_free_memory
	mysql_status \'Qcache_total_blocks\' qcache_total_blocks
	mysql_status \'Qcache_free_blocks\' qcache_free_blocks
	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." $red
		cecho "I recommend an upgrade to MySQL 4.1 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_mem_fill_ratio=$(echo "scale=2; $qcache_used_memory * 100 / $query_cache_size" | bc -l)
		qcache_mem_fill_ratioHR=$(echo "scale=0; $qcache_mem_fill_ratio / 1" | bc -l)

		cecho "Query cache is enabled" $green
		human_readable $query_cache_size query_cache_sizeHR
		cecho "Current query_cache_size = $query_cache_sizeHR $unit"
		human_readable $qcache_used_memory qcache_used_memoryHR
		cecho "Current query_cache_used = $qcache_used_memoryHR $unit"
		human_readable $query_cache_limit query_cache_limitHR
		cecho "Current query_cache_limit = $query_cache_limitHR $unit"
		cecho "Current Query cache Memory fill ratio = $qcache_mem_fill_ratio %"
		human_readable $query_cache_min_res_unit query_cache_min_res_unitHR 
		cecho "Current query_cache_min_res_unit = $query_cache_min_res_unitHR $unit"
		if [ $qcache_free_blocks -gt 2 ] && [ $qcache_total_blocks -gt 0 ] ; then
			qcache_percent_fragmented=$(echo "scale=2; $qcache_free_blocks * 100 / $qcache_total_blocks" | bc -l)
			qcache_percent_fragmentedHR=$(echo "scale=0; $qcache_percent_fragmented / 1" | bc -l)
			if [ $qcache_percent_fragmentedHR -gt 20 ] ; then
				cecho "Query Cache is $qcache_percent_fragmentedHR % fragmented" $red
				cecho "Run \"FLUSH QUERY CACHE\" periodically to defragment the query cache memory" $red 
				cecho "If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation." $red
			fi
		fi

		if [ $qcache_mem_fill_ratioHR -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_mem_fill_ratioHR -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
		cecho "MySQL won't cache query results that are larger than query_cache_limit in size" $yellow
	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 for 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_sizeHR
	cecho "Current sort_buffer_size = $sort_buffer_sizeHR $unit"

	human_readable $read_rnd_buffer_size read_rnd_buffer_sizeHR
	cecho "Current record/read_rnd_buffer_size = $read_rnd_buffer_sizeHR $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
	
	## Some 4K is dropped from join_buffer_size adding it back to make sane ##
	## handling of human-readable conversion ## 

	let join_buffer_size=$join_buffer_size+4096

	human_readable $join_buffer_size join_buffer_sizeHR 2

	cecho "Current join_buffer_size = $join_buffer_sizeHR $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'
		raise_buffer='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'
		raise_buffer='true'
	fi

	## For Debuging ##
	# print_error='true'
	if [ $join_buffer_size -ge 4194304 ] ; then
		cecho "join_buffer_size >= 4 M" $boldred
		cecho "This is not advised" $boldred
		raise_buffer=
	fi

	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."
		if [ $raise_buffer ] ; then
		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.\n"
		cecho "Note! This script will still suggest raising the join_buffer_size when" $boldred
		cecho "ANY joins not using indexes are found." $boldred
		fi
	fi

	# XXX Add better tests for join_buffer_size XXX #
}

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
	mysql_variable \'max_heap_table_size\' max_heap_table_size


	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
	human_readable $max_heap_table_size max_heap_table_sizeHR
	cecho "Current max_heap_table_size = $max_heap_table_sizeHR $unit"

	human_readable $tmp_table_size tmp_table_sizeHR 
	cecho "Current tmp_table_size = $tmp_table_sizeHR $unit"

	cecho "Of $created_tmp_tables temp tables, $tmp_disk_tables% were created on disk"
	if [ $tmp_table_size -gt $max_heap_table_size ] ; then
		cecho "Effective in-memory tmp_table_size is limited to max_heap_table_size." $yellow
	fi
	if [ $tmp_disk_tables -ge 25 ] ; then
		cecho "Perhaps you should increase your tmp_table_size and/or max_heap_table_size" $boldred
		cecho "to reduce the number of disk-based temporary tables" $boldred
		cecho "Note! BLOB and TEXT columns are not allow in memory tables." $yellow
		cecho "If you are using these columns raising these values might not impact your " $yellow
		cecho  "ratio of on disk temp tables." $yellow
	else
		cecho "Created disk tmp tables ratio seems fine" $green
	fi
}

function check_open_files () {

## -- Open Files Limit -- ## 
	cecho "OPEN FILES LIMIT" $boldblue

	mysql_variable \'open_files_limit\' open_files_limit
	mysql_status   \'Open_files\' open_files
	
	if [ -z $open_files_limit ] || [ $open_files_limit -eq 0 ] ; then
		open_files_limit=$(ulimit -n)
		cant_override=1
	else
		cant_override=0
	fi
	cecho "Current open_files_limit = $open_files_limit files"
	
	let open_files_ratio=$open_files*100/$open_files_limit

	cecho "The open_files_limit should typically be set to at least 2x-3x" $yellow 
	cecho "that of table_cache if you have heavy MyISAM usage." $yellow
	if [ $open_files_ratio -ge 75 ] ; then
		cecho "You currently have open more than 75% of your open_files_limit" $boldred
		if [ $cant_override -eq 1 ] ; then
			cecho "You should set a higer value for ulimit -u in the mysql startup script then restart mysqld" $boldred
			cecho "MySQL 3.23 users : This is just a guess based upon the current shell's ulimit -u value" $yellow
		elif [ $cant_override -eq 0 ] ; then
			cecho "You should set a higher value for open_files_limit in my.cnf" $boldred
		else
			cecho "ERROR can't determine if mysqld override of ulimit is allowed" $boldred
			exit 1
		fi
	else
		cecho "Your open_files_limit value seems to be 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
 
	table_count=$($mysql -Bse "/*!50000 SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' */")

	if [ -z "$table_count" ] ; then
		if [ "$UID" != "$socket_owner" ] && [ "$UID" != "0" ] ; 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"
	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 and perform"
		cecho "frequent updates consider setting 'low_priority_updates=1'"
		fi
		if [ $concurrent_insert -le 1 ] && [ $major_version == '5.0' -o $major_version == '5.1' ] ; then
		cecho "If you have a high concurrency of inserts on Dynamic row-length tables"
		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_sizeHR
	cecho "Current read_buffer_size = $read_buffer_sizeHR $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 8388608 ] ; then 
			cecho "read_buffer_size is over 8 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
		mysql_variable \'innodb_additional_mem_pool_size\' innodb_additional_mem_pool_size
		mysql_variable \'innodb_fast_shutdown\' innodb_fast_shutdown
		mysql_varaible \'innodb_flush_log_at_trx_commit\' innodb_flush_log_at_trx_commit
		mysql_variable \'innodb_locks_unsafe_for_binlog\' innodb_locks_unsafe_for_binlog
		mysql_varaible \'innodb_log_buffer_size\' innodb_log_buffer_size
		mysql_variable \'innodb_log_file_size\' innodb_log_file_size
		mysql_variable \'innodb_log_files_in_group\' innodb_log_files_in_group
		mysql_variable \'innodb_safe_binlog\' innodb_safe_binlog
		mysql_variable \'innodb_thread_concurrency\' innodb_thread_concurrency

		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

		mysql_status \'Innodb_buffer_pool_pages_data\' innodb_buffer_pool_pages_data
		mysql_status \'Innodb_buffer_pool_pages_misc\' innodb_buffer_pool_pages_misc
		mysql_status \'Innodb_buffer_pool_pages_free\' innodb_buffer_pool_pages_free
		mysql_status \'Innodb_buffer_pool_pages_total\' innodb_buffer_pool_pages_total

		mysql_status \'Innodb_buffer_pool_read_ahead_seq\' innodb_buffer_pool_read_ahead_seq
		mysql_status \'Innodb_buffer_pool_read_requests\' innodb_buffer_pool_read_requests

		mysql_status \'Innodb_os_log_pending_fsyncs\' innodb_os_log_pending_fsyncs
		mysql_status \'Innodb_os_log_pending_writes\'	innodb_os_log_pending_writes
		mysql_status \'Innodb_log_waits\' innodb_log_waits

		mysql_status \'Innodb_row_lock_time\' innodb_row_lock_time
		mysql_status \'Innodb_row_lock_waits\' innodb_row_lock_waits

		human_readable $innodb_indexes innodb_indexesHR 0
		cecho "Current InnoDB index space = $innodb_indexesHR $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 \'read_rnd_buffer_size\' read_rnd_buffer_size
	mysql_variable \'sort_buffer_size\' sort_buffer_size
	mysql_variable \'thread_stack\' thread_stack
	mysql_variable \'max_connections\' max_connections
	mysql_variable \'join_buffer_size\' join_buffer_size
	mysql_variable \'tmp_table_size\' tmp_table_size
	mysql_variable \'max_heap_table_size\' max_heap_table_size
	mysql_variable \'log_bin\' log_bin
	mysql_status \'Max_used_connections\' max_used_connections

	if [ "$major_version" = "3.23" ] ; then
		mysql_variable \'record_buffer\' read_buffer_size
		mysql_variable \'record_rnd_buffer\' read_rnd_buffer_size
		mysql_variable \'sort_buffer\' sort_buffer_size
	fi

	if [ "$log_bin" == "ON" ] ; then
		mysql_variable \'binlog_cache_size\' binlog_cache_size
	else
		binlog_cache_size=0
	fi

	if [ $max_heap_table_size -le $tmp_table_size ] ; then
		effective_tmp_table_size=$max_heap_table_size
	else
		effective_tmp_table_size=$tmp_table_size
	fi


	per_thread_buffers=$(echo "($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_connections" | bc -l)
	per_thread_max_buffers=$(echo "($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_used_connections" | bc -l)

        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

	global_buffers=$(echo "$innodb_buffer_pool_size+$innodb_additional_mem_pool_size+$innodb_log_buffer_size+$key_buffer_size+$query_cache_size" | bc -l)


	max_memory=$(echo "$global_buffers+$per_thread_max_buffers" | bc -l)
	total_memory=$(echo "$global_buffers+$per_thread_buffers" | bc -l)

	pct_of_sys_mem=$(echo "scale=0; $total_memory*100/$physical_memory" | bc -l)

	if [ $pct_of_sys_mem -gt 90 ] ; then
		txt_color=$boldred
		error=1
	else
		txt_color=
		error=0
	fi	

	human_readable $max_memory max_memoryHR 0
	cecho "Max Memory Ever Allocated : $max_memoryHR $unit" $txt_color
	human_readable $per_thread_buffers per_thread_buffersHR 0
	cecho "Configured Max Per-thread Buffers : $per_thread_buffersHR $unit" $txt_color
	human_readable $global_buffers global_buffersHR 0
	cecho "Configured Max Global Buffers : $global_buffersHR $unit" $txt_color
	human_readable $total_memory total_memoryHR 0
	cecho "Configured Max Memory Limit : $total_memoryHR $unit" $txt_color
#	human_readable $effective_tmp_table_size effective_tmp_table_sizeHR 0
#	cecho "Plus $effective_tmp_table_sizeHR $unit per temporary table created"
	human_readable $physical_memory physical_memoryHR 2
	cecho "Physical Memory : $physical_memoryHR $unit" $txt_color
	if [ $error -eq 1 ] ; then
		cecho "\nMax memory limit exceeds 90% of physical memory" $txt_color
	else
		cecho "Max memory limit seem to be within acceptable norms" $green
	fi
	unset txt_color
}

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 mysql_version_num=$($mysql -Bse "SELECT REPLACE(SUBSTRING_INDEX(VERSION(), '-', +1), '.', '')" )
}

function shared_info () {
	export major_version=$($mysql -Bse 'select substring_index(version(), ".", +2)')
	mysql_status \'Questions\' questions
#	socket_owner=$(find -L $socket -printf '%u\n')
	socket_owner=$(ls -nH $socket | awk '{ print $3 }')
}
	

function get_system_info () {

    export OS=$(uname)
    
    # Get information for various UNIXes
    if [ "$OS" = 'Darwin' ]; then
	ps_socket=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }')
        export physical_memory=$(sysctl -n hw.memsize)
	export duflags=''
    elif [ "$OS" = 'FreeBSD' ] || [ "$OS" == 'OpenBSD' ]; then
	## On FreeBSD must be root to locate sockets.
	ps_socket=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }')
        export physical_memory=$(sysctl -n hw.realmem)
	export duflags=''
    elif [ "$OS" = 'Linux' ] ; then
	## Includes SWAP
        ## export physical_memory=$(free -b | grep -v buffers |  awk '{ s += $2 } END { printf("%.0f\n", s ) }')
	ps_socket=$(netstat -ln | awk '/mysql(d)?\.sock/ { print $9 }')
	export physical_memory=$(awk '/^MemTotal/ { printf("%.0f", $2*1024 ) }' < /proc/meminfo)
	export duflags='-b'
    elif [ "$OS" = 'SunOS' ] ; then
	ps_socket=$(netstat -a | awk '/mysql(d)?.sock/ { print $5 }')
	export physical_memory=$(prtconf | awk '/^Memory\ size:/ { print $3*1048576 }')
    fi
}


## Optional Components Groups ##

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

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

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

function file () {
	shared_info
	check_open_files	; echo
	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 [ -z $pass ] ; then
	export mysql="mysql -S $socket -u$user"
	export mysqladmin="mysqladmin -S $socket -u$user"
	else
        export mysql="mysql -S $socket -u$user -p$pass"
        export mysqladmin="mysqladmin -S $socket -u$user -p$pass"
	fi

	check_for_socket
	check_mysql_login

	if [ $? = 1 ] ; then
		exit 1
	fi
	read -p "Mode to test (see usage:) [all] : " pmode
	if [ -z $pmode ] ; then
		pmode='all'
	fi
	case $pmode in
	        banner | BANNER | header | HEADER | head | HEAD)
		banner_info 
		;;
		misc | MISC | miscelaneous )
		misc
		;;
        	mem | memory |  MEM | MEMORY )
		memory
		;; 
		file | FILE | disk | DISK )
		file
		;;
		innodb | INNODB )
		innodb
		;;
		all | ALL )
		cecho "\n\c"
		all
		;;
		* )
		cecho "Invalid Mode!  Valid options are 'banner', 'misc', 'memory', 'file', 'innodb' or 'all'" $boldred
		exit 1
		;;
	esac 
}

## Address environmental differences ##
get_system_info


if [ -z "$1" ] ; then
	login_validation
	mode='ALL'
elif [ "$1" = "prompt" ] || [ "$1" = "PROMPT" ] ; then
	mode=$1
elif [ "$1" != "prompt" ] || [ "$1" != "PROMPT" ] ; then
	login_validation
	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 | 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