Skip to content

Instantly share code, notes, and snippets.

@Craftworks
Created December 8, 2010 15:14
Show Gist options
  • Save Craftworks/733390 to your computer and use it in GitHub Desktop.
Save Craftworks/733390 to your computer and use it in GitHub Desktop.
MySQL memory configuration check tool
#!/usr/bin/env perl
use strict;
use warnings;
my @GLOBAL_BUFFERS = qw(
key_buffer_size
innodb_buffer_pool_size
innodb_log_buffer_size
innodb_additional_mem_pool_size
net_buffer_length
);
my @THREAD_BUFFERS = qw(
sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_rnd_buffer_size
);
my @HEAP_LIMIT = qw(
innodb_buffer_pool_size
key_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
);
my @INNODB_LOG_FILE = qw(innodb_buffer_pool_size innodb_log_files_in_group);
my @OTHER_VARIABLES = qw(max_connections);
my %DEFAULT_VALUES = (
'key_buffer_size' => 8388600,
'innodb_buffer_pool_size' => 8388608,
'innodb_log_buffer_size' => 1048576,
'innodb_additional_mem_pool_size' => 1048576,
'net_buffer_length' => 16384,
'sort_buffer_size' => 8388608,
'myisam_sort_buffer_size' => 8388608,
'read_buffer_size' => 131072,
'join_buffer_size' => 131072,
'read_rnd_buffer_size' => 262144,
'innodb_log_files_in_group' => 2,
'max_connections' => 100,
);
my $TEMPLATE = get_data_section();
MAIN: {
my $variables = read_variables();
report_minimal_memory($variables);
report_heap_limit($variables) if ~0 == 2**32-1;
report_innodb_log_file($variables);
exit 0;
}
sub read_cnf {
my $filename = shift;
open my $fh, '<', $filename or die qq{open: $! "$filename"};
my @content;
while (<$fh>) {
if ( my ($dir) = /!includedir\s+(\S+)/ ) {
opendir my $fh, $dir or die qq{opendir: $! "$dir"};
my @cnf = map "$dir/$_", sort grep /\.cnf$/, readdir $fh;
push @content, read_cnf($_) for @cnf;
}
elsif ( /!include\s+(\S+)/ ) {
push @content, read_cnf($1);
}
else {
push @content, $_;
}
}
return @content;
}
sub read_variables {
my $var;
my @content;
if ( defined $ARGV[0] && -r $ARGV[0] ) {
@content = read_cnf($ARGV[0]);
}
else {
@content = <STDIN>;
}
for ( @content ) {
next if /^#/;
chomp;
s/^\|\s+//;
if ( my ($name, $value) = split /[\s=|]+/ ) {
next unless defined $value;
$value =~ s/\s*\|\s*$//;
$value = to_byte($value) if $value =~ /[KMG]$/;
$name .= '_size' if $name =~ /buffer$/;
$var->{ $name } = $value;
}
}
for my $name ( keys %DEFAULT_VALUES ) {
unless ( exists $var->{ $name } ) {
$var->{ $name } = $DEFAULT_VALUES{ $name };
}
}
return $var;
}
sub report_minimal_memory {
my $var = shift;
my $global_buffer_size;
for my $key ( @GLOBAL_BUFFERS ) {
$global_buffer_size += $var->{ $key };
}
my $thread_buffer_size;
for my $key ( @THREAD_BUFFERS ) {
$thread_buffer_size += $var->{ $key };
}
my $minimal_memory = $global_buffer_size
+ $thread_buffer_size * $var->{'max_connections'};
my $global_buffers = '';
for my $key ( @GLOBAL_BUFFERS ) {
$global_buffers .= sprintf " %-32s %12d %12s\n",
$key, $var->{ $key }, add_unit($var->{ $key });
}
my $thread_buffers = '';
for my $key ( @THREAD_BUFFERS ) {
$thread_buffers .= sprintf " %-32s %12d %12s\n",
$key, $var->{ $key }, add_unit($var->{ $key });
}
my $max_connections = $var->{'max_connections'};
printf $TEMPLATE->{'minimal_memory'},
$global_buffers, add_unit($global_buffer_size),
$thread_buffers, add_unit($thread_buffer_size), $max_connections,
add_unit($global_buffer_size), add_unit($thread_buffer_size * $max_connections),
$minimal_memory, add_unit($minimal_memory);
}
sub report_heap_limit {
my $var = shift;
my $stack_size = 2 * 1024 ** 2;
my $heap_limit_size =
$var->{'innodb_buffer_pool_size'}
+ $var->{'key_buffer_size'}
+ $var->{'max_connections'} * ( $var->{'sort_buffer_size'}
+ $var->{'read_buffer_size'}
+ $var->{'binlog_cache_size'}
+ $stack_size );
my $result = ( 2147483648 <= $heap_limit_size ) ? 'LIMIT OVER!!' : 'safe';
printf $TEMPLATE->{'heap_limit'},
@$var{qw(innodb_buffer_pool_size key_buffer_size max_connections)},
@$var{qw(sort_buffer_size read_buffer_size read_rnd_buffer_size max_connections)},
$stack_size, $heap_limit_size, add_unit($heap_limit_size), add_unit($heap_limit_size),
$result;
}
sub report_innodb_log_file {
my $var = shift;
my $max_innodb_log_file_size =
int($var->{'innodb_buffer_pool_size'} / $var->{'innodb_log_files_in_group'});
my $result = $var->{'innodb_log_file_size'} > $max_innodb_log_file_size
? 'LIMIT OVER!!' : 'safe';
printf $TEMPLATE->{'innodb_log_file'},
$var->{'innodb_buffer_pool_size'}, $var->{'innodb_log_files_in_group'},
$max_innodb_log_file_size, add_unit($max_innodb_log_file_size),
$var->{'innodb_log_file_size'}, $max_innodb_log_file_size,
add_unit($var->{'innodb_log_file_size'}),
add_unit($max_innodb_log_file_size),
$result;
}
sub to_byte {
my $str = shift;
my ($number, $unit) = $str =~ /^(\d+)([KMG])$/ or return;
my $power = ( $unit eq 'G' ) ? 3 : ( $unit eq 'M' ) ? 2 : ( $unit eq 'K' ) ? 1 : 0;
return $number * (1024 ** $power);
}
sub add_unit {
my $number = shift;
my $base = 0;
my $unit = '';
if ( $number > 1073741824 ) {
$base = 1073741824;
$unit = 'G';
}
elsif ( $number > 1048576 ) {
$base = 1048576;
$unit = 'M';
}
elsif ( $number > 1024 ) {
$base = 1024;
$unit = 'K';
}
else {
$base = 1;
$unit = '';
}
return sprintf '%.3f%s', $number / $base, $unit;
}
sub get_data_section {
my $content = join '', <DATA>;
my @data = split /^@@\s+(.+?)\s*\r?\n/m, $content;
shift @data; # trailing whitespaces
my $all = {};
while ( @data ) {
my ($name, $content) = splice @data, 0, 2;
$all->{ $name } = $content;
}
return $all;
}
=head1 NAME
mymemcheck - check configuration parameter of MySQL
=head1 SYNOPSIS
mymemcheck /etc/mysql/my.cnf
mymemcheck ~/my.cnf
mysql -e 'SHOW VARIABLES' | mymemcheck
=head1 SEE ALSO
L<http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html>
L<http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html>
L<http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html>
=head1 AUTHOR
HIROSE, Masaaki E<lt>hirose at klab dot orgE<gt>
Craftworks E<lt>craftwork at cpan dot orgE<gt>
=cut
__DATA__
@@ minimal_memory
[ minimal memory ]============================================================
ref
* ¡ØHigh Performance MySQL¡Ù, Solving Memory Bottlenecks, p125
global_buffers
%s -----------------------------------------------------------
total %12s
thread_buffers
%s -----------------------------------------------------------
total %12s
max_connections %12d
min_memory_needed = global_buffers + (thread_buffers * max_connections)
= %s + %s
= %lu (%s)
@@ heap_limit
[ 32bit Linux x86 limitation ]================================================
ref
* http://dev.mysql.com/doc/mysql/en/innodb-configuration.html
* need to include read_rnd_buffer.
* no need myisam_sort_buffer because allocate when repair, check alter.
2G > process heap
process heap = innodb_buffer_pool + key_buffer
+ max_connections * (sort_buffer + read_buffer + read_rnd_buffer)
+ max_connections * stack_size
= %d + %d
+ %d * (%d + %d + %d)
+ %d * %d
= %lu (%s)
2G > %s ... %s
@@ innodb_log_file
[ maximum size of innodb_log_file_size ]======================================
ref
* http://dev.mysql.com/doc/mysql/en/innodb-start.html
1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB
MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group
= %d * 1 / %d
= %lu (%s)
innodb_log_file_size < MAX_innodb_log_file_size
%24d < %lu
%24s < %s ... %s
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment