MySQL Memory Usage Verbose - Revision history

Smsldoo at 18:25, 8 July 2014

2014-07-08T18:25:57Z

Smsldoo at 17:43, 17 February 2012

2012-02-17T17:43:19Z

Smsldoo: /* Usage */

2012-01-20T19:54:17Z

‎Usage

Smsldoo: /* Script */

2012-01-20T19:52:20Z

‎Script

Show changes

Smsldoo: /* Script */

2012-01-20T19:48:23Z

‎Script

Smsldoo: /* Script */

2012-01-20T19:42:27Z

‎Script

Smsldoo: 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 ..."

2012-01-20T19:41:47Z

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 ..."

New page

==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==

<c #!/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];
}
ode>[perl,n]

</code>

==Usage==

Direct download and execution of script (auto mode)

<code>[bash,n]wget http://wiki.jamesdooley.us/scripts/sqlmem-verbose -O - | perl</code>

Default

<code>[bash,n]perl sqlmem-verbose.pl</code>

Defaults will use the cpanel default MySQL locations.

Specify paths

<code>[bash,n]perl sqlmem-verbose.pl [/path/to/my.cnf] [/path/to/mysqld]</code>

==Credits==

Original code created by:

Axel Schwenke:

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

@@ Line 10: / Line 10: @@
 ==Script==
-<code>[perl,n]
+<source lang='perl'>
 #!/usr/bin/perl
+#
@@ Line 119: / Line 119: @@
 	sprintf $prefix?"%.1f %s":"%d %s", $n, qw"Bytes KB MB GB TB"[$prefix];
+}
-</code>
+</source>
 ==Usage==
@@ Line 125: / Line 125: @@
 Direct download and execution of script (auto mode)
-<code>[bash,n]wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem-verbose.pl -O - | perl</code>
+<source lang='bash'>wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem-verbose.pl -O - | perl</source>
 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.
@@ Line 136: / Line 136: @@
 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==

← Older revision		Revision as of 17:43, 17 February 2012
Line 1:		Line 1:
		+	[[Category:Misc]]
	==Overview==		==Overview==

← Older revision		Revision as of 19:54, 20 January 2012
Line 124:		Line 124:
	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>	+	<code>[bash,n]wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem-verbose.pl -O - \| perl</code>

@@ Line 9: / Line 9: @@
 ==Script==
-<c    #!/usr/bin/perl
+<code>[perl,n]
+     #
      # calculate mysqld memory consumption from my.cnf