MySQL Memory Usage Profile Script 2

Posted in: Technical Track

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: http://www.pythian.com/blogs/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

Interested in working with Tim? Schedule a tech call.

9 Comments. Leave new

Ouch! That got mangled pretty heavily in the posting, and the download is 404. Mind fixing one of those?

Thanks for sharing btw.

Steve

Reply

Hi Steve, the script should clean up if you copy-paste it. and I’ve fixed the download. Thanks for noticing.

Reply

Awesome, thanks. For some reason the copy and paste didn’t work, but the download is fine, so thats great. I’ve got a small patch to support FreeBSD as well as Linux if you want it. Mail me. Very useful script, thanks for sharing.

Steve

Reply

Hi! When you figure out the licensing, feel free to consider adding this to MySQL Forge snippets :) http://forge.mysql.com/snippets/

Cheers!

jay

Reply

Thanks Jay, I’ve referred to that site myself.

I’m posting a reponse from Steve, with his permission.

>>> >>Awesome, thanks. For some reason the copy and paste didn’t work, but the download is fine, so thats great. I’ve got a small patch to support FreeBSD as well as Linux if you want it. Mail me. Very useful script, thanks for sharing.
>>> Many of our clients are linux-based, so I’m curious about what failed. I did make some changes to it to make it independent from our internal software, so maybe I broke that.

> Steve Wills wrote:
>> FreeBSD doesn’t have a /proc/meminfo, you do “sysctl hw.physmem” instead. I just added a call to “uname -s” to get the OS then if/elsif/else it to DTRT, etc. Also, I added a "<html><body><pre>" and "</pre></body></html>"
at the end to generate a complete valid HTML file, just as a niceity.
>> I am curious about one thing. I’m not sure if the numbers are off or I’m misunderstanding, but I notice on one server I ran this on (a Linux box):
>> Per connection memory usage:
>> …
>> TOTAL: 0.13 M
>>
>> Then in the summary:
>> Potential Connection Memory: 13 M
>>
>> Am I misunderstanding or is one of those off?
>> (output here: http://www.pythian.com/blogs/steves-mysql-report/ ) (Copied from original location -tp)
>> Thanks,
>> Steve

> The total takes into account the max_connections variable (100), since if all connections are used, they would each consume the per-connection amount.

Gotcha, I think I was just thrown since per connection was .13 and the total was 13, but of course that is 100 times… I was just in a hurry reading it….

> -> ::key_buffer usage: 100 % (high-water: 0 %)
> Those numbers seem unusual. The high-water mark can sometimes be wrong, but 100 % usage is rare. You may see better performance by increasing the buffer size.

Yeah, I was thinking about increasing it before I found your script, but just wasn’t sure the system had enough total memory for it.

> Is this a dedicated server, or do you have other apps running on it?
It’s running the whole LAMP stack…

Reply

No updates on the missing file, or steve’s linux version?

Reply

Thanks for pointing out that it went missing again. It seems to be a recurring problem.

I’ve put the file back up there, and I’ll be investigating the case of the missing files in more depth. I also emailed Steve and asked if he would post his changes.

Reply

It was recently pointed out that the link was broken again, and I apologize for that. The link is working as of now, and I think we’ve corrected the problem. Please let me know via comment or email if you notice any other issues.

Reply

This is really handy and would definitely use it if it were GPL’d. Please consider it! Cheers.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *