MySQL Memory Usage

Overview

Perl script to calculate MySQL max memory allotment.

Modified original code to search for cPanel defaults.

Script

[perl,n]

   #!/usr/bin/perl -w
   #
   # 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 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];
   }

Usage

Default

[bash,n]perl sqlmem.pl

Defaults will use the cpanel default MySQL locations.

Specify paths

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

Credits

Original code created by:

Axel Schwenke:

http://forge.mysql.com/tools/tool.php?id=266