MySQL Memory Usage - Revision history

Smsldoo at 18:00, 8 July 2014

2014-07-08T18:00:38Z

Smsldoo at 17:43, 17 February 2012

2012-02-17T17:43:12Z

Smsldoo: /* Script */

2012-01-20T19:55:03Z

‎Script

Smsldoo: /* Usage */

2012-01-20T19:54:45Z

‎Usage

10.30.6.195: /* Script */

2011-05-03T15:07:44Z

‎Script

10.30.6.195: /* Script */

2011-05-03T15:05:35Z

‎Script

10.30.6.195: /* Usage */

2011-05-03T15:03:39Z

‎Usage

10.30.6.195: /* Credits */

2011-05-03T14:54:16Z

‎Credits

10.30.6.195: /* What to change */

2011-05-03T14:53:20Z

‎What to change

10.30.6.195: /* Overview */

2011-05-03T14:50:32Z

‎Overview

@@ Line 8: / Line 8: @@
 ==Script==
-<code>[perl,n]
+<source lang='perl'>
 #!/usr/bin/perl
+#
@@ Line 95: / Line 95: @@
 	sprintf $prefix?"%.1f %s":"%d %s", $n, qw"Bytes KB MB GB TB"[$prefix];
+}
-</code>
+</source>
 ==Usage==
@@ Line 101: / Line 101: @@
 Direct download and execution of script (auto mode)
-<code>[bash,n]wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem.pl -O - | perl</code>
+<source lang='bash'>wget http://svn.jamesdooley.us/svn/JDooley/MySQLMemoryUsage/sqlmem.pl -O - | perl</source>
 Default
-<code>[bash,n]perl sqlmem.pl</code>
+<source lang='bash'>perl sqlmem.pl</source>
 Defaults will use the cpanel default MySQL locations.
@@ Line 112: / Line 112: @@
 Specify paths
-<code>[bash,n]perl sqlmem.pl [/path/to/my.cnf] [/path/to/mysqld]</code>
+<source lang='bash'>perl sqlmem.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==

@@ Line 8: / Line 8: @@
 <code>[perl,n]
-    #!/usr/bin/perl
+#!/usr/bin/perl
-−
+#
-    # calculate mysqld memory consumption from my.cnf
+# calculate mysqld memory consumption from my.cnf
-    # usage: mysql-memory-usage /path/to/my.cnf [/path/to/mysqld]
+# 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;
+my $mycnf= shift;
-    $mycnf= "/etc/my.cnf" unless $mycnf;
+$mycnf= "/etc/my.cnf" unless $mycnf;
-    die "cannot read $mycnf\n" unless (-r $mycnf);
+die "cannot read $mycnf\n" unless (-r $mycnf);
-−
-    my $mysqld= shift;
+my $mysqld= shift;
-    unless ($mysqld) {
+unless ($mysqld) {
-    # find mysqld relative to mysql
+	# find mysqld relative to mysql
-    $mysqld= `which mysqld` or $mysqld= `which mysql` or die;
+	$mysqld= `which mysqld` or $mysqld= `which mysql` or die;
-    chomp $mysqld;
+	chomp $mysqld;
-    $mysqld =~ s(bin/mysql$)(libexec/mysqld);
+	$mysqld =~ s(bin/mysql$)(libexec/mysqld);
-−
+}
-    die "cannot execute $mysqld\n" unless (-x $mysqld);
+die "cannot execute $mysqld\n" unless (-x $mysqld);
-−
-    # read configuration
+# read configuration
-    my %conf;
+my %conf;
-    open M, "$mysqld --defaults-file=$mycnf --help --verbose |" or die;
+open M, "$mysqld --defaults-file=$mycnf --help --verbose |" or die;
-    while (<M>) { # skip to parameter list
+while (<M>) { # skip to parameter list
-    last if (/^\-{5,} \-{5,}$/o);
+	last if (/^\-{5,} \-{5,}$/o);
-−
+}
-    while (<M>) {
+while (<M>) {
-    last if /^$/o;
+	last if /^$/o;
-    my ($var, $val)= split;
+	my ($var, $val)= split;
-    $conf{$var}= $val;
+	$conf{$var}= $val;
-−
+}
-    close M;
+close M;
-−
-    # static memory consumption
+# static memory consumption
-    my $static= $conf{'key_buffer_size'} + $conf{'query_cache_size'};
+my $static= $conf{'key_buffer_size'} + $conf{'query_cache_size'};
-    # has innodb
+# has innodb
-    $static+= $conf{'innodb-buffer-pool-size'} + $conf{'innodb-log-buffer-size'}
+$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'));
++ $conf{'innodb-additional-mem-pool-size'} if (($conf{'innodb'} eq 'TRUE') or ($conf{'innodb'} eq 'ON'));
-−
-    # per thread memory consumption
+# per thread memory consumption
-    my $thread= $conf{'thread_stack'}
+my $thread= $conf{'thread_stack'}
-    + 2*$conf{'net_buffer_length'} + $conf{'read_buffer_size'}
++ 2*$conf{'net_buffer_length'} + $conf{'read_buffer_size'}
-    + $conf{'read_rnd_buffer_size'} + $conf{'sort_buffer_size'};
++ $conf{'read_rnd_buffer_size'} + $conf{'sort_buffer_size'};
-−
-    # writes binlog
+# writes binlog
-    $thread += $conf{'binlog_cache_size'} if ($conf{'log-bin'} ne '(No');
+$thread += $conf{'binlog_cache_size'} if ($conf{'log-bin'} ne '(No');
-−
-    # MyISAM repair thread(s)
+# MyISAM repair thread(s)
-    my $repair= 0;
+my $repair= 0;
-    $repair+= $conf{'myisam_repair_threads'} * $conf{'myisam_sort_buffer_size'}
+$repair+= $conf{'myisam_repair_threads'} * $conf{'myisam_sort_buffer_size'}
-    if ($conf{'myisam-recover'} ne 'OFF');
+if ($conf{'myisam-recover'} ne 'OFF');
-−
-    # Memory (HEAP) tables
+# Memory (HEAP) tables
-    my $heaptables= $conf{'max_heap_table_size'};
+my $heaptables= $conf{'max_heap_table_size'};
-−
-    # temp. tables
+# temp. tables
-    my $tmptables= min($conf{'tmp_table_size'}, $conf{'max_heap_table_size'});
+my $tmptables= min($conf{'tmp_table_size'}, $conf{'max_heap_table_size'});
-−
-−
+#
-    # show result
+# show result
-−
+#
-    print "$mysqld will use at most:\n";
+print "$mysqld will use at most:\n";
-    printf "%-10s for global stuff\n", p($static);
+printf "%-10s for global stuff\n", p($static);
-    printf "%-10s per thread\n", p($thread);
+printf "%-10s per thread\n", p($thread);
-    printf "%-10s total (with %d active threads)\n",
+printf "%-10s total (with %d active threads)\n", p($static + $conf{'max_connections'}*$thread), $conf{'max_connections'};
-    p($static + $conf{'max_connections'}*$thread), $conf{'max_connections'};
+print "and additionally:\n";
-    print "and additionally:\n";
+printf "%-10s while recovering MyISAM tables\n", p($repair) if ($repair);
-    printf "%-10s while recovering MyISAM tables\n", p($repair) if ($repair);
+printf "%-10s for each HEAP table\n", p($heaptables);
-    printf "%-10s for each HEAP table\n", p($heaptables);
+printf "%-10s for each temporary table\n", p($tmptables);
-    printf "%-10s for each temporary table\n", p($tmptables);
+exit 0;
-    exit 0;
-−
 sub min {
-    sub min {
+	my ($a, $b)= @_;
-    my ($a, $b)= @_;
+	return $a <$b ? $a : $b;
-    return $a <$b ? $a : $b;
-−
 sub p {
-    sub p {
+	my $n= shift;
-    my $n= shift;
+	my $prefix= 0;
-    my $prefix= 0;
+	while ($n >= 1024 && $prefix < 5) {
-    while ($n >= 1024 && $prefix < 5) {
+		$n/= 1024;
-    $n/= 1024;
+		$prefix++;
-    $prefix++;
+	sprintf $prefix?"%.1f %s":"%d %s", $n, qw"Bytes KB MB GB TB"[$prefix];
-    sprintf $prefix?"%.1f %s":"%d %s", $n, qw"Bytes KB MB GB TB"[$prefix];
 </code>

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

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

← Older revision		Revision as of 15:03, 3 May 2011
Line 98:		Line 98:

	==Usage==		==Usage==
		+
		+	Direct download and execution of script (auto mode)
		+
		+	<code>[bash,n]wget http://wiki.jamesdooley.us/scripts/sqlmem -O - \| perl</code>
		+

	Default		Default

← Older revision		Revision as of 14:54, 3 May 2011
Line 110:		Line 110:

	==Credits==		==Credits==
		+
		+	Original code created by:
		+
		+	Axel Schwenke:
		+
		+	http://forge.mysql.com/tools/tool.php?id=266

← Older revision		Revision as of 14:50, 3 May 2011
Line 1:		Line 1:
	==Overview==		==Overview==

		+	Perl script to calculate MySQL max memory allotment.
		+
		+	Modified original code to search for cPanel defaults.

	==Script==		==Script==