Difference between revisions of "MySQL Memory Usage"

#!/usr/bin/perl
#
# calculate mysqld memory consumption from my.cnf
# usage: mysql-memory-usage /path/to/my.cnf [/path/to/mysqld]
#
# $Id: mysql-memory-usage 228 2010-05-31 10:53:33Z schwenke $

my $mycnf= shift;
$mycnf= "/etc/my.cnf" unless $mycnf;
die "cannot read $mycnf\n" unless (-r $mycnf);

my $mysqld= shift;
unless ($mysqld) {
	# find mysqld relative to mysql
	$mysqld= `which mysqld` or $mysqld= `which mysql` or die;
	chomp $mysqld;
	$mysqld =~ s(bin/mysql$)(libexec/mysqld);
}
die "cannot execute $mysqld\n" unless (-x $mysqld);

# read configuration
my %conf;
open M, "$mysqld --defaults-file=$mycnf --help --verbose |" or die;
while (<M>) { # skip to parameter list
	last if (/^\-{5,} \-{5,}$/o);
}
while (<M>) {
	last if /^$/o;
	my ($var, $val)= split;
	$conf{$var}= $val;
}
close M;

# static memory consumption
my $static= $conf{'key_buffer_size'} + $conf{'query_cache_size'};
# has innodb
$static+= $conf{'innodb-buffer-pool-size'} + $conf{'innodb-log-buffer-size'}
+ $conf{'innodb-additional-mem-pool-size'} if (($conf{'innodb'} eq 'TRUE') or ($conf{'innodb'} eq 'ON'));

# per thread memory consumption
my $thread= $conf{'thread_stack'}
+ 2*$conf{'net_buffer_length'} + $conf{'read_buffer_size'}
+ $conf{'read_rnd_buffer_size'} + $conf{'sort_buffer_size'};

# writes binlog
$thread += $conf{'binlog_cache_size'} if ($conf{'log-bin'} ne '(No');

# MyISAM repair thread(s)
my $repair= 0;
$repair+= $conf{'myisam_repair_threads'} * $conf{'myisam_sort_buffer_size'}
if ($conf{'myisam-recover'} ne 'OFF');

# Memory (HEAP) tables
my $heaptables= $conf{'max_heap_table_size'};

# temp. tables
my $tmptables= min($conf{'tmp_table_size'}, $conf{'max_heap_table_size'});

#
# show result
#
print "$mysqld will use at most:\n";
printf "%-10s for global stuff\n", p($static);
printf "%-10s per thread\n", p($thread);
printf "%-10s total (with %d active threads)\n", p($static + $conf{'max_connections'}*$thread), $conf{'max_connections'};
print "and additionally:\n";
printf "%-10s while recovering MyISAM tables\n", p($repair) if ($repair);
printf "%-10s for each HEAP table\n", p($heaptables);
printf "%-10s for each temporary table\n", p($tmptables);
exit 0;


sub min {
	my ($a, $b)= @_;
	return $a <$b ? $a : $b;
}

sub p {
	my $n= shift;
	my $prefix= 0;
	while ($n >= 1024 && $prefix < 5) {
		$n/= 1024;
		$prefix++;
	}
	sprintf $prefix?"%.1f %s":"%d %s", $n, qw"Bytes KB MB GB TB"[$prefix];
}

wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem.pl -O - | perl

perl sqlmem.pl

perl sqlmem.pl [/path/to/my.cnf] [/path/to/mysqld]