MySQL Memory Usage Profile Script 2

Here is the perl script referred to by mysql-memory-usage-profile.

Downloadable: mysql_memory_profile.pl

#!/usr/bin/perl
# Version: 1 Last Change by: procter.pythian. 20070419 14:14:39
# Copyright 2007, The Pythian Group, Inc. All rights reserved.
# Hey, we work hard on this stuff!

# Feel free to use this script, un-modified, as much as you like 
#on any system. If you find issues or have comments, we would 
#welcome the opportunity to collaborate with you on it. But we 
#haven't yet decided on any formal licensing (GPL etc.) and 
#public interest will be a factor on whether we go that route.

# MySQL Memory Profiling Script

# Reference: https://blog.pythian.coms/431/mysql-memory-usage-profile

#MD5 Checksum 5D713DCA3702EBC91D3B1AA997E68F07
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#============================================#
#			Includes						 #
#============================================#
use DBI;
use strict;

#============================================#
#			Subroutine Prototypes			 #
#============================================#
sub pdebug($);
sub println($);
sub safe_div_p($$);
sub min($$);
sub dbi_connect();

#use these script parameters
sub usage();
sub get_args();

#============================================#
#			Global Variables				 #
#============================================#
use vars qw(%args);
my $dbh;	#database handle
my $sth;	#statement handle
my $sh_variables;
my $sh_status;
my $OStype;

my $debug = 0;

#############========================================############
#############				Setup					 ############
#############========================================############
get_args();
if (defined($args{d})){
	$debug = 1;
}
if (defined($args{h})){
	usage();
}

if ($^O eq 'MSWin32') {
	$OStype = "windows";
} else {
	$OStype = "linux";
}
my $threshhold;
if(defined $args{t}){
        $threshhold = $args{t};
}
else{
        $threshhold = 0.9;
}

$dbh=dbi_connect();

#############========================================############
#############			  Main Program				 ############
#############========================================############

$sh_variables = $dbh->prepare("SHOW VARIABLES LIKE ?");

#version-dependent global keyword *****************>
$sh_variables->execute('version');
my $version = $sh_variables->fetchrow();

$version =~ /^(\d+)\.(\d+)\.(\d+).*/;
my @ver=($1,$2,$3);

if ($ver[0] <= 4 || ($ver[1] == 0 && $ver[2] < 2)) {
	#pdebug "mysql_version 4 or less\n"; 
	#$sth = $dbh_avail->prepare("SHOW status LIKE ?") or die "Unable to prepare.";
	$sh_status = $dbh->prepare("SHOW STATUS LIKE ?");
}
else {
	#$sth = $dbh_avail->prepare("SHOW global status LIKE ?") or die "Unable to prepare.";
	$sh_status = $dbh->prepare("SHOW GLOBAL STATUS LIKE ?");
	#pdebug "mysql_version > 5.0.2\n";
}

########################################################<<

println "Analyze current database variables for memory settings and compare to recommendations for engine types and resource availability.";

############ get engine stats
my $db_list = $dbh->prepare("SHOW DATABASES");
my $table_status = $dbh->prepare("SHOW TABLE STATUS");

$db_list->execute();

my $glo_inno_cnt = 0;
my $glo_myism_cnt = 0;
my %other_cnts;

######engine types
println "Engine types:";
while (my $db_name = $db_list->fetchrow()) {
        $dbh->do("use `$db_name`");
        $table_status->execute();

        my $loc_inno_cnt = 0;
        my $loc_myism_cnt = 0;

        while( my $table_info = $table_status->fetchrow_hashref()){
                if($table_info->{'Engine'} eq 'MyISAM'){
                        $loc_myism_cnt++;
                }
                elsif($table_info->{'Engine'} eq 'InnoDB'){
                        $loc_inno_cnt++;
                }
                elsif($table_info->{'Type'} eq 'MyISAM'){ #4.0 compatibility
                        $loc_myism_cnt++;
                }
                elsif($table_info->{'Type'} eq 'InnoDB'){ #4.0 compatibility
                        $loc_inno_cnt++;
                }
                else{
                        println "Other engine type: $db_name\.$table_info->{'Name'} -

$table_info->{‘Engine’}

";
                        $other_cnts{$table_info->{'Engine'}} = $other_cnts{$table_info->{'Engine'}} + 1;
                }
        }
        println "$db_name: $loc_inno_cnt [

InnoDB

] $loc_myism_cnt [

MyISAM

]";

        $glo_inno_cnt += $loc_inno_cnt;
        $glo_myism_cnt += $loc_myism_cnt;

}
$table_status->finish();
$db_list->finish();

println "\nGlobal engine stats: $glo_inno_cnt [

InnoDB

] $glo_myism_cnt [

MyISAM

]";
while ( my ($ckey, $cvalue) = each(%other_cnts) ) {
	print "$cvalue [

$ckey

], ";
  	}
println "";

############ General configs
#query_cache_size       = 32M -->HighFluctuating
$sh_variables->execute('query_cache_size');
my $query_cache_size = $sh_variables->fetchrow();
$sh_status->execute('qcache_free_memory');
my $qcache_free_memory = $sh_status->fetchrow();
$sh_status->execute('qcache_lowmem_prunes');
my $qcache_lowmem_prunes = $sh_status->fetchrow();
$sh_status->execute('qcache_hits');
my $qcache_hits = $sh_status->fetchrow();
$sh_status->execute('qcache_inserts');
my $qcache_inserts = $sh_status->fetchrow();
$sh_status->execute('qcache_not_cached');
my $qcache_not_cached = $sh_status->fetchrow();
$sh_status->execute('qcache_queries_in_cache');
my $qcache_queries_in_cache = $sh_status->fetchrow();
$sh_variables->execute('tmp_table_size');
my $tmp_table_size = $sh_variables->fetchrow();
$sh_variables->execute('binlog_cache_size');
my $binlog_cache_size = $sh_variables->fetchrow();
$sh_variables->execute('log_bin');
my $log_bin = $sh_variables->fetchrow();
if($log_bin eq 'OFF') {$binlog_cache_size = 0;}
#max_heap_table_size  -->When Needed
$sh_variables->execute('max_heap_table_size');
my $max_heap_table_size = $sh_variables->fetchrow();
my $tot_gen_mem = int(($query_cache_size + $binlog_cache_size)/1024/1024*100)/100;
#my $qcache_divisor = $qcache_hits+$qcache_inserts+$qcache_not_cached;
my $qcache_hit_rate = safe_div_p($qcache_hits,$qcache_hits+$qcache_inserts+$qcache_not_cached);

######tmp
$sh_status->execute('created_tmp_disk_tables');
my $created_tmp_disk_tables = $sh_status->fetchrow();
$sh_status->execute('created_tmp_tables');
my $created_tmp_tables = $sh_status->fetchrow();
println "\nTemp Space:";
println "max_heap_table_size: $max_heap_table_size";
my $real_tmp_table_size = min($max_heap_table_size, $tmp_table_size);
if ($real_tmp_table_size eq $tmp_table_size){
	println "tmp_table_size: $tmp_table_size";
}
else{
	println "tmp_table_size ($tmp_table_size) reduced to max_heap_table_size ($max_heap_table_size)";
}
println "::created_tmp_disk_tables / created_tmp_tables: $created_tmp_disk_tables / $created_tmp_tables";
println ":: if large, consider increasing tmp_table_size (current: $tmp_table_size)";

######open tables
$sh_variables->execute('table_cache');
my $table_cache = $sh_variables->fetchrow();
$sh_status->execute('open_tables');
my $open_tables = $sh_status->fetchrow();
$sh_status->execute('opened_tables');
my $opened_tables = $sh_status->fetchrow();
println "\nOpen Tables:";
println "::opened_tables: $opened_tables - current $open_tables open";
println ":: if large, consider increasing table_cache (current: $table_cache)";

######handler types /
$sh_status->execute('handler_read_key');
my $handler_read_key = $sh_status->fetchrow();
$sh_status->execute('handler_read_first');
my $handler_read_first = $sh_status->fetchrow();
$sh_status->execute('handler_read_next');
my $handler_read_next = $sh_status->fetchrow();
$sh_status->execute('handler_read_prev');
my $handler_read_previous = $sh_status->fetchrow();
$sh_status->execute('handler_read_rnd');
my $handler_read_rnd = $sh_status->fetchrow();
$sh_status->execute('handler_update');
my $handler_update = $sh_status->fetchrow();
$sh_status->execute('handler_delete');
my $handler_delete = $sh_status->fetchrow();
$sh_status->execute('handler_write');
my $handler_write = $sh_status->fetchrow();
$sh_status->execute('handler_rollback');
my $handler_rollback = $sh_status->fetchrow();
println "\nHandler Types - suggests scan types:";
println "::handler_read_key: \t$handler_read_key";
println "::handler_read_first: \t$handler_read_first";
println "::handler_read_next: \t$handler_read_next";
println "::handler_read_previous: \t$handler_read_previous";
println "::handler_read_rnd: \t$handler_read_rnd";
println "::handler_update: \t$handler_update";
println "::handler_delete: \t$handler_delete";
println "::handler_write: \t$handler_write";
println "::handler_rollback: \t$handler_rollback";

######com... values
$sh_status->execute('com_alter_table');
my $com_alter_table = $sh_status->fetchrow();
$sh_status->execute('com_commit');
my $com_commit = $sh_status->fetchrow();
$sh_status->execute('com_create_table');
my $com_create_table = $sh_status->fetchrow();
$sh_status->execute('com_delete');
my $com_delete = $sh_status->fetchrow();
$sh_status->execute('com_drop_table');
my $com_drop_table = $sh_status->fetchrow();
$sh_status->execute('com_flush');
my $com_flush = $sh_status->fetchrow();
$sh_status->execute('com_insert');
my $com_insert = $sh_status->fetchrow();
$sh_status->execute('com_insert_select');
my $com_insert_select = $sh_status->fetchrow();
$sh_status->execute('com_lock_tables');
my $com_lock_tables = $sh_status->fetchrow();
$sh_status->execute('com_purge');
my $com_purge = $sh_status->fetchrow();
$sh_status->execute('com_replace');
my $com_replace = $sh_status->fetchrow();
$sh_status->execute('com_rollback');
my $com_rollback = $sh_status->fetchrow();
$sh_status->execute('com_select');
my $com_select = $sh_status->fetchrow();
$sh_status->execute('com_update');
my $com_update = $sh_status->fetchrow();
$sh_status->execute('com_truncate');
my $com_truncate = $sh_status->fetchrow();

println "\nCOM_... Values:";
println "::com_alter_table: \t$com_alter_table";
println "::com_commit: \t\t$com_commit";
println "::com_create_table: \t$com_create_table";
println "::com_delete: \t\t$com_delete";
println "::com_drop_table: \t$com_drop_table";
println "::com_flush: \t\t$com_flush";
println "::com_insert: \t\t$com_insert";
println "::com_insert_select: \t$com_insert_select";
println "::com_purge: \t\t$com_purge";
println "::com_replace: \t\t$com_replace";
println "::com_rollback: \t$com_rollback";
println "::com_select: \t\t$com_select";
println "::com_truncate: \t$com_truncate";
println "::com_update: \t\t$com_update";

######general memory
println "\nGeneral memory usage:";
println "binlog_cache_size: $binlog_cache_size";
println "query_cache_size: $query_cache_size";
println "::holding $qcache_queries_in_cache queries with $qcache_free_memory free memory";
println "::qcache hit ratio: $qcache_hit_rate %";
println "::qcache_lowmem_prunes can suggest qcache too small - $qcache_lowmem_prunes";
println "TOTAL: $tot_gen_mem M";

############ MyISAM - fixed
#key_buffer_size -->PreAlloc  -->max 4GB
$sh_variables->execute('key_buffer_size');
my $key_buffer_size = $sh_variables->fetchrow();
$sh_status->execute('key_read_requests');
my $key_read_requests = $sh_status->fetchrow();
$sh_status->execute('key_reads');
my $key_reads = $sh_status->fetchrow();
$sh_status->execute('key_write_requests');
my $key_write_requests = $sh_status->fetchrow();
$sh_status->execute('key_writes');
my $key_writes = $sh_status->fetchrow();
$sh_status->execute('key_blocks_unused');
my $key_blocks_unused = $sh_status->fetchrow();
$sh_status->execute('key_blocks_used');
my $key_blocks_used = $sh_status->fetchrow();
$sh_variables->execute('key_cache_block_size');
my $key_cache_block_size = $sh_variables->fetchrow();
$sh_variables->execute('delay_key_write');
my $delay_key_write = $sh_variables->fetchrow();
#myisam_sort_buffer_size = 64M
$sh_variables->execute('myisam_sort_buffer_size');
my $myisam_sort_buffer_size = $sh_variables->fetchrow();
my $tot_myisam = int(($key_buffer_size + $myisam_sort_buffer_size)/1024/1024*100)/100;
my $key_disk_read_rate = safe_div_p($key_reads,$key_read_requests);
my $key_disk_write_rate = safe_div_p($key_writes, $key_write_requests);
my $key_buffer_use;
my $key_buffer_high_water;
if($key_buffer_size > 0) {
	$key_buffer_use = (1 - (($key_blocks_unused * $key_cache_block_size) / $key_buffer_size))*100;
	$key_buffer_high_water = (($key_blocks_used * $key_cache_block_size) / $key_buffer_size)*100;
}
else{
	$key_buffer_use = 'UNDEF';
	$key_buffer_high_water = 'UNDEF';
}

println "\nMyISAM fixed memory usage:";
println "key_buffer_size: $key_buffer_size";
println "::key_buffer usage: $key_buffer_use % (high-water: $key_buffer_high_water %)";
println "::Read ratio to disk instead of buffer: $key_disk_read_rate % (ideally less than 1%)";
println "::Write ratio to disk instead of buffer: $key_disk_write_rate % (may be high if lots of deletes/updates)";
println "\tdelay_key_write is$delay_key_write, lowers key_disk_write ratio.";
println "myisam_sort_buffer_size: $myisam_sort_buffer_size\t\t(for sorting MyISAM indexes)";
println "TOTAL: $tot_myisam M";

############ InnoDB - fixed
#innodb_buffer_pool_size = 7000M
$sh_variables->execute('innodb_buffer_pool_size');
my $innodb_buffer_pool_size = $sh_variables->fetchrow();
#innodb_additional_mem_pool_size = 32M
$sh_variables->execute('innodb_additional_mem_pool_size');
my $innodb_additional_mem_pool_size = $sh_variables->fetchrow();
#innodb_log_buffer_size = 8M
$sh_variables->execute('innodb_log_buffer_size');
my $innodb_log_buffer_size = $sh_variables->fetchrow();
$sh_variables->execute('have_innodb');
my $have_innodb = $sh_variables->fetchrow();

my $tot_innodb = int(($innodb_buffer_pool_size + $innodb_additional_mem_pool_size + $innodb_log_buffer_size)/1024/1024*100)/100;

println "\nInnoDB fixed memory usage:";
println "have_innodb?:

$have_innodb

";
println "innodb_buffer_pool_size: $innodb_buffer_pool_size";
println "innodb_additional_mem_pool_size: $innodb_additional_mem_pool_size";
println "innodb_log_buffer_size: $innodb_log_buffer_size";
println "TOTAL: $tot_innodb M";

############ Per Connection
#sort_buffer_size = 2M
$sh_variables->execute('sort_buffer_size');
my $sort_buffer_size = $sh_variables->fetchrow();
#read_buffer_size = 2M -->When Needed
$sh_variables->execute('read_buffer_size');
my $read_buffer_size = $sh_variables->fetchrow();
#read_rnd_buffer_size = 8M -->When Needed
$sh_variables->execute('read_rnd_buffer_size');
my $read_rnd_buffer_size = $sh_variables->fetchrow();
$sh_variables->execute('net_buffer_length');
my $net_buffer_length = $sh_variables->fetchrow();
#join_buffer_size
$sh_variables->execute('join_buffer_size');
my $join_buffer_size = $sh_variables->fetchrow();
$sh_variables->execute('max_allowed_packet');
my $max_allowed_packet = $sh_variables->fetchrow();
$sh_variables->execute('thread_stack');
my $thread_stack = $sh_variables->fetchrow();
$sh_variables->execute('bulk_insert_buffer_size');
my $bulk_insert_buffer_size = $sh_variables->fetchrow();

$sh_status->execute('connections');
my $connections = $sh_status->fetchrow();

my $tmp_per_conn = $created_tmp_tables / $connections;
my $tot_pcon = int((($net_buffer_length*2) + $thread_stack)/1024/1024*100)/100;
#my $adjust_pcon = int((($net_buffer_length*2)) + $sort_buffer_size + $read_buffer_size + $read_rnd_buffer_size + $tot_gen_mem + $join_buffer_size + ($tmp_table_size*$tmp_per_conn))/1024/1024*100)/100;

println "\nPer connection memory usage:";
println "thread stack: $thread_stack";
println "net_buffer_length: $net_buffer_length\t\t(alloc. per client, grows to $max_allowed_packet)";
#println "tmp_table_size: $tmp_table_size\t\t(alloc per all temp tables";
println "TOTAL: $tot_pcon M";
#println "Adjusted estimate with $tmp_per_conn tmp tables per connection: $adjust_pcon";

println "\nPer allocation memory usage:";
println "bulk_insert_buffer_size: $bulk_insert_buffer_size\t(alloc. for insert..select, load data infile, insert value exteded)";
println "sort_buffer_size: $sort_buffer_size\t\t(allocated for sorts, order by)";
println "read_buffer_size: $read_buffer_size\t\t(allocated for sequential scans)";
println "read_rnd_buffer_size: $read_rnd_buffer_size\t\t(pre-alloc. for sorted reads)";
println "join_buffer_size: $join_buffer_size\t\t(using full-table-scan, alloc. per join)";
my $tot_per_alloc = int(($bulk_insert_buffer_size + $sort_buffer_size + $read_buffer_size + $read_rnd_buffer_size + $join_buffer_size)/1024/1024*100)/100;
println "TOTAL: $tot_per_alloc M";

############ Summation
#set-variable=max_connections=700
$sh_variables->execute('max_connections');
my $max_connections = $sh_variables->fetchrow();
$sh_status->execute('threads_connected');
my $threads_connected = $sh_status->fetchrow();
$sh_status->execute('max_used_connections');
my $max_used_connections = $sh_status->fetchrow();
$sh_status->execute('threads_running');
my $threads_running = $sh_status->fetchrow();

$sh_status->execute('threads_cached');
my $threads_cached = $sh_status->fetchrow();
$sh_status->execute('threads_created');
my $threads_created = $sh_status->fetchrow();
my $thread_hit_rate = 100-safe_div_p($threads_created,$connections);

my $pot_cmem = $max_connections * $tot_pcon;
my $tot_pot_cons = $pot_cmem + $tot_myisam + $tot_innodb;
#my $adj_pot_cons = ($max_connections * $adjust_pcon) + $tot_myisam + $tot_innodb;

println "\nSummary:";
println "\nmax_connections: $max_connections";
println "Potential Connection Memory: $pot_cmem M";
println "::Current Connections - $threads_connected ($threads_running active)";
println "::Max_Used_Connections - $max_used_connections";
println "::Thread Cache ($threads_cached) hit ratio - $thread_hit_rate %";

println "\nTheoretical Potential Consumption: $tot_pot_cons M";
#println "\nAdjusted Estimate Potential Consumption: $adj_pot_cons M";

###disconnect
$sh_variables->finish();
$sh_status->finish();
$dbh->disconnect();

############ Resources available
my $physical_mem=0;
if ( $OStype eq "windows") {
	my $dbh_wmi = DBI->connect('dbi:WMI:') or die "$!";
	my $sth = $dbh_wmi->prepare("SELECT * FROM Win32_PhysicalMemory") or die "$!";
	$sth->execute() or die "$!";

	while (my @row = $sth->fetchrow()) {
		my $mem_obj = $row[0];
		$physical_mem+=int($mem_obj->{Capacity}/1024);
	}
} 
else {
	$physical_mem = `cat /proc/meminfo | grep MemTotal`;
	chomp($physical_mem);
	$physical_mem=~s/MemTotal://;
}
$physical_mem =int($physical_mem/1024*100)/100;

println "MySQL formula: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections";
println "\t = $key_buffer_size + ($read_buffer_size + $sort_buffer_size) * $max_connections = ".(int(($key_buffer_size + ($read_buffer_size + $sort_buffer_size) * $max_connections)/1024/1024*100)/100)." M";
println "Physical memory available: $physical_mem M";

if ( $tot_pot_cons > $physical_mem * $threshhold) {
    println "

Warning

: estimated potential memory may be too high for resources available\n";
}
else{
	println "

OK

 Physical memory within threshold for theoretical memory usage.";
}

#############========================================############
#############			   Tear-Down				 ############
#############========================================############
$dbh->disconnect();
exit;

#================================================================#
##--------------------------------------------------------------##
##                          Subroutines                         ##
##--------------------------------------------------------------##
#================================================================#

#########################
#
# Sub usage
#################################
#use this function with get_args() for scripts needing advanced parameters
#treat this as a manpage
sub usage()
{
   print STDOUT <<EOF_HELP;

   MySQL/System Statistics Monitoring:
   Monitors mysql and sar data

   usage: $0 [-u X] [-p X] [-P X | -S X] [-db X] [-t X] -d]

	-u			: username
    -p			: password
    -db         : database to connect to
    -P			: mysql port
    -S			: mysql socket file
    -t			: % threshold of theoretical usage to physical memory for warning
    				-default 0.9

    -d			: debug

    -h			: this listing

    *NOTES
   example: $0 -c CLIENT_ID -p2 X -p1 Y 

EOF_HELP
   exit 1;
}

#########################
#
# Sub get_args
#################################
sub get_args()
{
       my $last_arg;
       while (@ARGV) {
               #print "ARG: $ARGV[0] \n";
               if($ARGV[0] =~ /^-(.+)/) {
                       $last_arg = $1;
                       $args{$last_arg} = "existencial";
               }
               else {
                       $args{$last_arg} = $ARGV[0];
                        #print "$args{$last_arg} = $ARGV[0]\n";
               }
               shift @ARGV;
       }
}

#########################
#
# Sub pdebug
#################################
#use pdebug statement to print output when in debug mode
sub pdebug($){	
	if("$debug" == 1 ){
		print "  -- DEBUG: @_\n";
	}
}

#########################
#
# Sub dbi_connect
#################################
sub dbi_connect() {
	#----------------------------------------------------------
	my $dbh;

	if ($OStype ne "windows") {
		$SIG{ALRM} = sub {
			die "dbi_connect timeout at 30 s.\n";
		};
		alarm(30);
	}

	#Connect to mysql
	my $connectstring = "";
	if(defined($args{P})){
		$connectstring = ";host=127.0.0.1;port=$args{P}";
	}
	elsif(defined($args{S})){
		$connectstring = ";mysql_socket=$args{S}";
	}
	my $dsn = "dbi:mysql:database=$args{db}$connectstring";			
	$dbh = DBI->connect($dsn, $args{u}, $args{p}, { RaiseError => 1, AutoCommit => 0 });

	if ($OStype ne "windows") {
		alarm(0);
	}

	return $dbh;
}

#########################
#
# Utilities
#################################
sub println($) {
   print "@_\n";
}

sub safe_div_p($$) {
	my ($divd,$divr) = @_;
	if ($divr == 0){
		return 'UNDEF';
	}
	else{
		return int(($divd / $divr)*10000)/100;
	}
}

sub min($$) {
	my ($v1,$v2) = @_;
	if($v1 < $v2){
		return $v1;
	}
	return $v2;
}
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
#End MD5 Checksum