mycnf_check.pl / InnoDBの最適な設定値を見つける為のスクリプト

mycnf_check.pl

#how to use, mysql -u DBUSERNAME -p -e 'show variables' |perl mycnf_check.pl
use strict;
my @THREAD_BUFFER_KEY = qw(
        join_buffer_size
        read_buffer_size
        read_rnd_buffer_size
        sort_buffer_size
        );

my @GLOBAL_BUFFER_KEY = qw(
        innodb_additional_mem_pool_size
        innodb_buffer_pool_size
        innodb_log_buffer_size
        innodb_log_file_size
        key_buffer_size
        net_buffer_length
        );

my @MAX_CONNECTIONS = qw(
        max_connections
        );

my %list;
while (<STDIN>){
    (my $result = $_) =~  s/\s/\t/;
    $result =~  s/\n//;
    my @data = split(/\t/,$result);

    $list{$data[0]} = $data[1];
}

print "==GLOBAL_BUFFER==\n";
my $total_size_global = get_total(\@GLOBAL_BUFFER_KEY, \%list);
print "\n";
print "total_size_global, ".get_human_readable_num($total_size_global)." byte\n";
print "\n";

print "==THREAD_BUFFER==\n";
my $total_size_thread = get_total(\@THREAD_BUFFER_KEY, \%list);
print "\n";
print "total_size_thread, ".get_human_readable_num($total_size_thread)." byte\n";
print "\n";

print "==MAX_CONNECTIONS==\n";
my $max_connections = get_total(\@MAX_CONNECTIONS, \%list);
print "\n";
print "max_connections, ".get_human_readable_num($max_connections)." connections\n";
print "\n";

my $total_size = $total_size_global + $total_size_thread * $max_connections;

print "\n";
print "==TOTAL==\n";
print "total_size, ".get_human_readable_num($total_size)." byte\n";


sub get_human_readable_num {
    my ($num) = @_;
    $num =~ s/(\d{1,3})(?=(?:\d\d\d)+(?!\d))/$1,/g;
    return $num;
}

sub get_total {
    my ($key_list, $value_list) = @_;

    my $total_size = 0;
    foreach my $key(@{$key_list}){
        my $size = %{$value_list}->{$key};
        print $key.", ".get_human_readable_num($size)."\n";
        $total_size += $size;
    }
    return $total_size;
}