MySQL umstellen von myISAM auf InnoDB

part 2

Code: 
#########################################################################
#									#
#  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 /*!50000 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' [ 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
	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
		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

        
	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 [ $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_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." $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_fill_ratio=$(echo "scale=2; $qcache_used_memory * 100 / $query_cache_size" | bc -l)
		qcache_fill_ratioHR=$(echo "scale=0; $qcache_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_cach_limit = $query_cache_limitHR $unit"
		cecho "Current Query cache fill ratio = $qcache_fill_ratio %"
		if [ $qcache_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_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 concurrentcy 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_variable \'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_variable \'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 () {
 
part3


Code: 
## -- 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/$total_system_memory" | bc -l)

	if [ $pct_of_sys_mem -gt 85 ] ; 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 $total_system_memory total_system_memoryHR 2
	cecho "Total System Memory : $total_system_memoryHR $unit" $txt_color
	if [ $error -eq 1 ] ; then
		cecho "\nMax memory limit exceeds 85% of total system 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)'`
}

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
        export total_system_memory=`sysctl -n hw.memsize`
	export duflags=''
    elif [ "$OS" = 'FreeBSD' ] || [ "$OS" == 'OpenBSD' ]; then
        export total_system_memory=`sysctl -n hw.realmem`
	export duflags=''
    elif [ "$OS" = 'Linux' ] ; then
        export total_system_memory=`free -b | grep -v buffers |  awk '{ s += $2 } END { printf("%.0f\n", s ) }'`
	export duflags='-b'
    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
	if [ -n $pass ] ; then
		pass_flag='-p'
	fi

	cecho "\n\c"
	read -p "Socket [ /var/lib/mysql/mysql.sock ] : " socket
	if [ -z $socket ] ; then
		export socket='/var/lib/mysql/mysql.sock'
	fi

        export mysql="mysql -S $socket -u$user $pass_flag$pass"
        export mysqladmin="mysqladmin -S $socket -u$user $pass_flag$pass"

	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
 
Endlich den lästigen Amerkaner vom Telefon los bekommen.

Also, bei dem was Du schilderst bin ich auch gerade ratlos.
Irgendjemand beschert sich über zu wenig RAM obwohl kaum etwas davon genutzt wird.
Das müsste ich mir nun quasi selbst ansehen.

-> Logfiles durchsuchen z.B. /var/log/messages etc.
um herauszufinden wer denn hier eine Problem hat.
Meine Erfahrung auf vServer hält sich jedoch in grenzen.
Was da nun wirklich schief geht kann ich so nicht sagen und auch nicht vermuten. An Speicherverbauch von mysql und den Diensten liegt es augenscheinlich nicht.

Was mich zumindest ein gewisses Stück weit wundert dass auch kein SWAP existiert. Kenne dazu aber die Stratokonfiguration der vServer auch nicht.
Das hat aber meiner Meinung nach nach erstmal nichts mit dem Problem zu tun.
Das Problem trat erst ein, nachdem Du auf Innodb umgestellt hast?
 
Last edited by a moderator:
Hi,

so weiter gehts. Also aus den Logfiles ist nichts ersichtlich. Man sieht zwar dass der Meckert das kein Speicher mehr frei ist, aber nichts was passiert ist.

Ich habe nun Deine my.cnf hergenommen mit der hat sich der MySQL Dienst nicht starten lassen. Dann habe ich den Teil der innodb rauskopiert und in die original my.cnf eingefügt damit gings.

Mit dem scheint jetzt auch keine Speicherprobleme mehr zu geben (vielleicht dauert es aber nur länger) mal shen.

ABER jetzt habe ich ein anderes Problem nun starter der Apache nicht beim booten. Erst wenn ich den Apache restarte geht er dann.
Das ist doch zum Kotzen.
 
@meddie:
Dein Speicher-Problem dürfte auf die Virtualisierungsumgebung zurück zu führen sein. Ich tippe mal auf eine OpenVZ da die Buffers bei 0 stehen. Hier ist die Frage, ob die 5GB "garantierter Speicher" oder nur "maximaler Speicher" darstellen. Das steht in der Beschreibung Deines vServer-Angebots. Grundsätzlich solltest Du versuchen nicht über den garantierten Speicher hinaus zu verbrauchen. Der Rest kann zeitweise nicht vorhanden sein.

PS für alle: Die aktuelle tuning-primer-Version (v1.5-r5) hat bereits die ausgebaute InnoDB-Unterstützung drin.
Download in der Console mit:
Code: 
wget http://launchpad.net/mysql-tuning-primer/trunk/1.5-r5/+download/tuning-primer.sh

huschi.
 
Hallo Huschi,

die 5 GB sind nicht garantiert (Leider). Das Problem ist den V-Server den ich habe gibt es bei Strato nicht mehr im Programm.

Wenn ich mich richtig erinnere dann waren es nur 128 MB garantiert und bis 500 MB Möglich. Aber so wie es ausshiet sind die 500 auf 5 GB erhöht worden. Aber wie gesagt das was garantiert ist sind 128

Ich habe jetzt die Aktion abgebrochen. Ich restore alles zurück. Entweder starte ich den Vorgang später nochmal, ode ich warte bis von Zarafa endlich eine richtige SuSE unterstützung kommt.

Ich überlege mir in letzter Zeit von Strato weg zu gehen, aber so richtig schlüssig bin ich mir nicht wohin. Im Moment zahle ich 9 Euro im Monat bei 500 GB Monatstraffic, und 10 Gb HDD. Hat jemand von Euch ein Tipp.


Gruss Eddie
 
Hallo!
meddie said:
Wenn ich mich richtig erinnere dann waren es nur 128 MB garantiert und bis 500 MB Möglich.
Click to expand...
Gewissheit verschafft dir hier vzfree (Forensuche bemühen) oder ein cat /proc/user_beancounters.

mfG
Thorsten
 
Also wenn es sich tatsächlich nur um 128MB garantieten Speicher handelt, dann ist meine Konfiguration insbeondere der innodb Buffer Pool zu hoch ausgelegt.
-> Setzte es auf die Werte der alten Konfig.

Spamassassin wäre dann einer der dienste, welcher vermeidbar bzw. verzichtbar ist. Dieser braucht schon ein wenig Speicher.
-> policyd-weight bringt an der Stelle mehr, bei weniger Resourcenlast.
Zusätzlich müsste man sich den Apachen und PHP ansehen.
Hier ggf. unnötige Module raus werfen.
Max_Servers etc. -> siehe die tuning.conf reduzieren.

Aber allein spamassassin zu deaktivieren dürfte dir erstmal helfen.
Dann sollte man es zum laufen bekommen.

Hand Hand aufs Herz, wenns wirklich 128 MB garantiert sind, dann hol dir einen neuen Server mit garantierten 2GB RAM.
Denn mit so wenig Arbeitspeicher machst Du keine grossen Sprünge.
 
You must log in or register to reply here.
Back
Top