Difference between revisions of "MySQL Memory Usage Verbose"

From James Dooley's Wiki
Jump to: navigation, search
(Created page with "==Overview== Perl script to calculate MySQL max memory allotment. Modified original code to search for cPanel defaults. This version also give a more verbose output then the ...")
 
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
[[Category:Misc]]
 
==Overview==
 
==Overview==
  
Line 9: Line 10:
 
==Script==
 
==Script==
  
−
<c    #!/usr/bin/perl
+
<source lang='perl'>
−
    #
+
#!/usr/bin/perl
−
    # calculate mysqld memory consumption from my.cnf
+
#
−
    # usage: mysql-memory-usage /path/to/my.cnf [/path/to/mysqld]
+
# 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 $
+
#
 +
# $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'));
 +
print "Static Memory Consumption\n";
 +
printf " %-10s key_buffer_size\n", p($conf{'key_buffer_size'});
 +
printf "+%-10s query_cache_size\n", p($conf{'query_cache_size'});
 +
if (($conf{'innodb'} eq 'TRUE') or ($conf{'innodb'} eq 'on')) {
 +
printf "+%-10s innodb-buffer-pool-size\n", p($conf{'innodb-buffer-pool-size'});
 +
printf "+%-10s innodb-log-buffer-size\n", p($conf{'innodb-log-buffer-size'});
 +
printf "+%-10s innodb-additional-mem-pool-size\n", p($conf{'innodb-additional-mem-pool-size'});
 +
}
 +
printf "%-5s Total Static Memory\n\n", p($static);
 +
 +
# 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'};
 +
print "Per thread memory consumption\n";
 +
printf " %-10s thread_stack\n", p($conf{'thread_stack'});
 +
printf "+%-10s net_buffer_length 2x (%s)\n", p($conf{'net_buffer_length'}), p(2*$conf{'net_buffer_length'});
 +
printf "+%-10s read_buffer_size\n", p($conf{'read_buffer_size'});
 +
printf "+%-10s read_rnd_buffer_size\n", p($conf{'read_rnd_buffer_size'});
 +
printf "+%-10s sort_buffer_size\n", p($conf{'sort_buffer_size'});
 +
printf "%-5s Total Memory Per Thread\n\n", p($thread);
 +
 +
# writes binlog
 +
$thread += $conf{'binlog_cache_size'} if ($conf{'log-bin'} ne '(No');
 +
printf " %-10s binlog_cache_size\n\n", p($conf{'binlog_cache_size'}) if ($conf{'log-bin'} ne '(No');
 +
 +
 +
# MyISAM repair thread(s)
 +
my $repair= 0;
 +
if ($conf{'myisam-recover'} ne 'OFF') {
 +
$repair+= $conf{'myisam_repair_threads'} * $conf{'myisam_sort_buffer_size'};
 +
print "MyISAM Repair Thread(s)\n";
 +
printf " %-10s myisam_repair_threads\n", p($conf{'myisam_repair_threads'});
 +
printf "*%-10s myisam_sort_buffer_size\n", p($conf{'myisam_sort_buffer_size'});
 +
printf "%-5s Total MyISAM Repair Thread(s)\n\n", p($repair);
 +
};
 +
 +
# Memory (HEAP) tables
 +
my $heaptables= $conf{'max_heap_table_size'};
 +
 +
# temp. tables
 +
my $tmptables= min($conf{'tmp_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;
  
−
    my $mycnf= shift;
+
sub min {
−
    $mycnf= "/etc/my.cnf" unless $mycnf;
+
my ($a, $b)= @_;
−
    die "cannot read $mycnf\n" unless (-r $mycnf);
+
return $a <$b ? $a : $b;
−
 
+
}
−
    my $mysqld= shift;
+
−
    unless ($mysqld) {
+
sub p {
−
    # find mysqld relative to mysql
+
my $n= shift;
−
    $mysqld= `which mysqld` or $mysqld= `which mysql` or die;
+
my $prefix= 0;
−
    chomp $mysqld;
+
while ($n >= 1024 && $prefix < 5) {
−
    $mysqld =~ s(bin/mysql$)(libexec/mysqld);
+
$n/= 1024;
−
    }
+
$prefix++;
−
    die "cannot execute $mysqld\n" unless (-x $mysqld);
+
}
−
 
+
sprintf $prefix?"%.1f %s":"%d %s", $n, qw"Bytes KB MB GB TB"[$prefix];
−
    # read configuration
+
}
−
    my %conf;
+
</source>
−
    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'));
 
−
    print "Static Memory Consumption\n";
 
−
    printf " %-10s key_buffer_size\n", p($conf{'key_buffer_size'});
 
−
    printf "+%-10s query_cache_size\n", p($conf{'query_cache_size'});
 
−
    if (($conf{'innodb'} eq 'TRUE') or ($conf{'innodb'} eq 'on')) {
 
−
        printf "+%-10s innodb-buffer-pool-size\n", p($conf{'innodb-buffer-pool-size'});
 
−
        printf "+%-10s innodb-log-buffer-size\n", p($conf{'innodb-log-buffer-size'});
 
−
        printf "+%-10s innodb-additional-mem-pool-size\n", p($conf{'innodb-additional-mem-pool-size'});
 
−
        }
 
−
    printf "%-5s Total Static Memory\n\n", p($static);
 
−
 
 
−
    # 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'};
 
−
    print "Per thread memory consumption\n";
 
−
    printf " %-10s thread_stack\n", p($conf{'thread_stack'});
 
−
    printf "+%-10s net_buffer_length 2x (%s)\n", p($conf{'net_buffer_length'}), p(2*$conf{'net_buffer_length'});
 
−
    printf "+%-10s read_buffer_size\n", p($conf{'read_buffer_size'});
 
−
    printf "+%-10s read_rnd_buffer_size\n", p($conf{'read_rnd_buffer_size'});
 
−
    printf "+%-10s sort_buffer_size\n", p($conf{'sort_buffer_size'});
 
−
    printf "%-5s Total Memory Per Thread\n\n", p($thread);
 
−
 
 
−
    # writes binlog
 
−
    $thread += $conf{'binlog_cache_size'} if ($conf{'log-bin'} ne '(No');
 
−
    printf " %-10s binlog_cache_size\n\n", p($conf{'binlog_cache_size'}) if ($conf{'log-bin'} ne '(No');
 
−
 
 
−
 
 
−
    # MyISAM repair thread(s)
 
−
    my $repair= 0;
 
−
    if ($conf{'myisam-recover'} ne 'OFF') {
 
−
      $repair+= $conf{'myisam_repair_threads'} * $conf{'myisam_sort_buffer_size'};
 
−
      print "MyISAM Repair Thread(s)\n";
 
−
      printf " %-10s myisam_repair_threads\n", p($conf{'myisam_repair_threads'});
 
−
      printf "*%-10s myisam_sort_buffer_size\n", p($conf{'myisam_sort_buffer_size'});
 
−
      printf "%-5s Total MyISAM Repair Thread(s)\n\n", p($repair);
 
−
    };
 
−
 
 
−
    # Memory (HEAP) tables
 
−
    my $heaptables= $conf{'max_heap_table_size'};
 
−
 
 
−
    # temp. tables
 
−
    my $tmptables= min($conf{'tmp_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];
 
−
    }
 
−
ode>[perl,n]
 
−
 
 
−
</code>
 
  
 
==Usage==
 
==Usage==
Line 127: Line 125:
 
Direct download and execution of script (auto mode)
 
Direct download and execution of script (auto mode)
  
−
<code>[bash,n]wget http://wiki.jamesdooley.us/scripts/sqlmem-verbose -O - | perl</code>
+
<source lang='bash'>wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem-verbose.pl -O - | perl</source>
  
  
 
Default
 
Default
  
−
<code>[bash,n]perl sqlmem-verbose.pl</code>
+
<source lang='bash'>perl sqlmem-verbose.pl</source>
  
 
Defaults will use the cpanel default MySQL locations.
 
Defaults will use the cpanel default MySQL locations.
Line 138: Line 136:
 
Specify paths
 
Specify paths
  
−
<code>[bash,n]perl sqlmem-verbose.pl [/path/to/my.cnf] [/path/to/mysqld]</code>
+
<source lang='bash'>perl sqlmem-verbose.pl [/path/to/my.cnf] [/path/to/mysqld]</source>
  
 
==Credits==
 
==Credits==

Latest revision as of 18:25, 8 July 2014

Overview

Perl script to calculate MySQL max memory allotment.

Modified original code to search for cPanel defaults.

This version also give a more verbose output then the original.

Script

#!/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'));
print "Static Memory Consumption\n";
printf " %-10s key_buffer_size\n", p($conf{'key_buffer_size'});
printf "+%-10s query_cache_size\n", p($conf{'query_cache_size'});
if (($conf{'innodb'} eq 'TRUE') or ($conf{'innodb'} eq 'on')) {
	printf "+%-10s innodb-buffer-pool-size\n", p($conf{'innodb-buffer-pool-size'});
	printf "+%-10s innodb-log-buffer-size\n", p($conf{'innodb-log-buffer-size'});
	printf "+%-10s innodb-additional-mem-pool-size\n", p($conf{'innodb-additional-mem-pool-size'});
}
printf "%-5s Total Static Memory\n\n", p($static);
 
# 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'};
print "Per thread memory consumption\n";
printf " %-10s thread_stack\n", p($conf{'thread_stack'});
printf "+%-10s net_buffer_length 2x (%s)\n", p($conf{'net_buffer_length'}), p(2*$conf{'net_buffer_length'});
printf "+%-10s read_buffer_size\n", p($conf{'read_buffer_size'});
printf "+%-10s read_rnd_buffer_size\n", p($conf{'read_rnd_buffer_size'});
printf "+%-10s sort_buffer_size\n", p($conf{'sort_buffer_size'});
printf "%-5s Total Memory Per Thread\n\n", p($thread);
 
# writes binlog
$thread += $conf{'binlog_cache_size'} if ($conf{'log-bin'} ne '(No');
printf " %-10s binlog_cache_size\n\n", p($conf{'binlog_cache_size'}) if ($conf{'log-bin'} ne '(No');
 
 
# MyISAM repair thread(s)
my $repair= 0;
if ($conf{'myisam-recover'} ne 'OFF') {
	$repair+= $conf{'myisam_repair_threads'} * $conf{'myisam_sort_buffer_size'};
	print "MyISAM Repair Thread(s)\n";
	printf " %-10s myisam_repair_threads\n", p($conf{'myisam_repair_threads'});
	printf "*%-10s myisam_sort_buffer_size\n", p($conf{'myisam_sort_buffer_size'});
	printf "%-5s Total MyISAM Repair Thread(s)\n\n", p($repair);
};
 
# Memory (HEAP) tables
my $heaptables= $conf{'max_heap_table_size'};
 
# temp. tables
my $tmptables= min($conf{'tmp_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

Direct download and execution of script (auto mode) 

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


Default 

perl sqlmem-verbose.pl

Defaults will use the cpanel default MySQL locations.

Specify paths 

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

Credits

Original code created by:

Axel Schwenke:

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

Retrieved from "http://wiki.jamesdooley.us/index.php?title=MySQL_Memory_Usage_Verbose&oldid=587"