Difference between revisions of "MySQL Memory Usage"
(→Usage) |
(→Script) |
||
| Line 8: | Line 8: | ||
<code>[perl,n] | <code>[perl,n] | ||
| − | + | #!/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]; | |
| − | + | } | |
| − | |||
</code> | </code> | ||
Revision as of 19:55, 20 January 2012
Contents
Overview
Perl script to calculate MySQL max memory allotment.
Modified original code to search for cPanel defaults.
Script
[perl,n]
- !/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]; }
Usage
Direct download and execution of script (auto mode)
[bash,n]wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem.pl -O - | perl
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:
