Difference between revisions of "MySQL Memory Usage"

1. !/usr/bin/perl
3. calculate mysqld memory consumption from my.cnf
4. usage: mysql-memory-usage /path/to/my.cnf [/path/to/mysqld]
6. $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);

1. 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;

1. static memory consumption

my $static= $conf{'key_buffer_size'} + $conf{'query_cache_size'};

1. 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'));

1. 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'};

1. writes binlog

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

1. MyISAM repair thread(s)

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

1. Memory (HEAP) tables

my $heaptables= $conf{'max_heap_table_size'};

1. temp. tables

my $tmptables= min($conf{'tmp_table_size'}, $conf{'max_heap_table_size'});

2. 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; }