magento服务器优化 -- 估算MySQL的内存消耗值

源文:[[Estimate MySQL Memory Consumption ]]

magento强大的功能背后是magento自身从代码到数据库的臃肿,吃内存是没商量,如何优化服务器使之更适合运行magento网站成为越来越多人考虑的首选问题.

Estimate MySQL Memory Consumption

#!/usr/bin/perl -w # # calculate mysqld memory consumption from my.cnf # usage: mysql-memory-usage /path/to/my.cnf [/path/to/mysqld] my $mycnf= shift or die "usage: mysql-memory-usage /path/to/my.cnf [/path/to/mysqld]/n"; die "cannot read $mycnf/n" unless (-r $mycnf); my $mysqld= shift; unless ($mysqld) { # find mysqld relative to mysql $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 (/^help/s+TRUE$/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'); # 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]; }