From: Date: July 26 2006 3:44am Subject: bk commit into 4.1 tree (stewart:1.2484) BUG#21204 List-Archive: http://lists.mysql.com/commits/9568 X-Bug: 21204 Message-Id: <20060726014455.953FB14085E4@localhost.localdomain> Below is the list of changes that have just been committed into a local 4.1 repository of stewart. When stewart does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2006-07-26 11:44:52+10:00, stewart@willster.(none) +2 -0 BUG#21204 ndb_size.pl doesn't take extended BLOB/TEXT size into account ndb/tools/ndb_size.pl@stripped, 2006-07-26 11:44:47+10:00, stewart@willster.(none) +76 -31 disable 5.1-dd reporting as it's not ready yet. support BLOB tables with mostly accurate space estimate (our estimates will be slightly higher due to the fact we also calculate an ORDERED index on the BLOB table, something that NDB doesn't have). now have subroutine do_table that does the calculations for a table. We call this with a "fake" blob table to get estimates for blob usage. ndb/tools/ndb_size.tmpl@stripped, 2006-07-26 11:44:47+10:00, stewart@willster.(none) +2 -0 Add column in columns table for if column is VARSIZED # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: stewart # Host: willster.(none) # Root: /home/stewart/Documents/MySQL/4.1/ndb_size_51 --- 1.7/ndb/tools/ndb_size.pl 2006-07-26 11:44:55 +10:00 +++ 1.8/ndb/tools/ndb_size.pl 2006-07-26 11:44:55 +10:00 @@ -57,7 +57,7 @@ $template->param(dsn => $dsn); } -my @releases = ({rel=>'4.1'},{rel=>'5.0'},{rel=>'5.1'},{rel=>'5.1-dd'}); +my @releases = ({rel=>'4.1'},{rel=>'5.0'},{rel=>'5.1'}); #,{rel=>'5.1-dd'}); $template->param(releases => \@releases); my $tables = $dbh->selectall_arrayref("show tables"); @@ -81,18 +81,19 @@ return @aligned; } -foreach(@{$tables}) -{ - my $table= @{$_}[0]; +sub do_table { + my $table= shift; + my $info= shift; + my %indexes= %{$_[0]}; + my @count= @{$_[1]}; + my @columns; - my $info= $dbh->selectall_hashref('describe `'.$table.'`',"Field"); - my @count = $dbh->selectrow_array('select count(*) from `'.$table.'`'); my %columnsize; # used for index calculations - # We now work out the DataMemory usage # sizes for 4.1, 5.0, 5.1 and 5.1-dd my @totalsize= (0,0,0,0); + @totalsize= @totalsize[0..$#releases]; # limit to releases we're outputting my $nrvarsize= 0; foreach(keys %$info) @@ -102,6 +103,7 @@ my $type; my $size; my $name= $_; + my $is_varsize= 0; if($$info{$_}{Type} =~ /^(.*?)\((\d+)\)/) { @@ -154,6 +156,7 @@ $dynamic[0]=0 if !$dynamic[0]; $dynamic[0]+=ceil($dynamic[0]/256); # size bit $nrvarsize++; + $is_varsize= 1; $varsize[3]= ceil($dynamic[0]); @realsize= ($fixed,$fixed,ceil($dynamic[0]),$fixed); } @@ -161,10 +164,38 @@ {@realsize=($size,$size,$size,$size)} elsif($type =~ /text/ || $type =~ /blob/) { - @realsize=(256,256,256,256); - $NoOfTables[$_]{val} += 1 foreach 0..$#releases; # blob uses table + @realsize=(8+256,8+256,8+256,8+256); + + my $blobhunk= 2000; + $blobhunk= 8000 if $type=~ /longblob/; + $blobhunk= 4000 if $type=~ /mediumblob/; + + my @blobsize=$dbh->selectrow_array("select SUM(CEILING(". + "length(`$name`)/$blobhunk))". + "from `".$table."`"); + $blobsize[0]=0 if !defined($blobsize[0]); + #$NoOfTables[$_]{val} += 1 foreach 0..$#releases; # blob uses table + do_table($table."\$BLOB_$name", + {'PK'=>{Type=>'int'}, + 'DIST'=>{Type=>'int'}, + 'PART'=>{Type=>'int'}, + 'DATA'=>{Type=>"binary($blobhunk)"} + }, + {'PRIMARY' => { + 'unique' => 1, + 'comment' => '', + 'columns' => [ + 'PK', + 'DIST', + 'PART', + ], + 'type' => 'HASH' + } + }, + \@blobsize); } + @realsize= @realsize[0..$#releases]; @realsize= align(4,@realsize); $totalsize[$_]+=$realsize[$_] foreach 0..$#totalsize; @@ -175,6 +206,7 @@ push @columns, { name=>$name, type=>$type, + is_varsize=>$is_varsize, size=>$size, key=>$$info{$_}{Key}, datamemory=>\@realout, @@ -188,24 +220,10 @@ # Firstly, we assemble some information about the indexes. # We use SHOW INDEX instead of using INFORMATION_SCHEMA so # we can still connect to pre-5.0 mysqlds. - my %indexes; - { - my $sth= $dbh->prepare("show index from `".$table.'`'); - $sth->execute; - while(my $i = $sth->fetchrow_hashref) - { - $indexes{${%$i}{Key_name}}= { - type=>${%$i}{Index_type}, - unique=>!${%$i}{Non_unique}, - comment=>${%$i}{Comment}, - } if !defined($indexes{${%$i}{Key_name}}); - - $indexes{${%$i}{Key_name}}{columns}[${%$i}{Seq_in_index}-1]= - ${%$i}{Column_name}; - } - } if(!defined($indexes{PRIMARY})) { + my @usage= ({val=>8},{val=>8},{val=>8},{val=>8}); + @usage= @usage[0..$#releases]; $indexes{PRIMARY}= { type=>'BTREE', unique=>1, @@ -217,20 +235,22 @@ type=>'bigint', size=>8, key=>'PRI', - datamemory=>[{val=>8},{val=>8},{val=>8},{val=>8}], + datamemory=>\@usage, }; $columnsize{'HIDDEN_NDB_PKEY'}= [8,8,8]; } my @IndexDataMemory= ({val=>0},{val=>0},{val=>0},{val=>0}); my @RowIndexMemory= ({val=>0},{val=>0},{val=>0},{val=>0}); + @IndexDataMemory= @IndexDataMemory[0..$#releases]; + @RowIndexMemory= @RowIndexMemory[0..$#releases]; my @indexes; foreach my $index (keys %indexes) { my $im41= 25; $im41+=$columnsize{$_}[0] foreach @{$indexes{$index}{columns}}; - my @im = ({val=>$im41},{val=>25},{val=>25},{val=>25}); - my @dm = ({val=>10},{val=>10},{val=>10},{val=>10}); + my @im = ({val=>$im41},{val=>25},{val=>25}); #,{val=>25}); + my @dm = ({val=>10},{val=>10},{val=>10}); #,{val=>10}); push @indexes, { name=>$index, type=>$indexes{$index}{type}, @@ -244,10 +264,10 @@ # total size + 16 bytes overhead my @TotalDataMemory; - my @RowOverhead = ({val=>16},{val=>16},{val=>16},{val=>24}); + my @RowOverhead = ({val=>16},{val=>16},{val=>16}); #,{val=>24}); # 5.1 has ptr to varsize page, and per-varsize overhead my @nrvarsize_mem= ({val=>0},{val=>0}, - {val=>8},{val=>0}); + {val=>8}); #,{val=>0}); { my @a= align(4,$nrvarsize*2); $nrvarsize_mem[2]{val}+=$a[0]+$nrvarsize*4; @@ -275,7 +295,7 @@ $counts[$_]{val}= $count foreach 0..$#releases; my @nrvarsize_rel= ({val=>0},{val=>0}, - {val=>$nrvarsize},{val=>0}); + {val=>$nrvarsize}); #,{val=>0}); push @table_size, { table=>$table, @@ -301,6 +321,31 @@ $dbIndexMemory[$_]{val} += $IndexMemory[$_]{val} foreach 0..$#releases; $NoOfAttributes[$_]{val} += @columns foreach 0..$#releases; $NoOfIndexes[$_]{val} += @indexes foreach 0..$#releases; +} + +foreach(@{$tables}) +{ + my $table= @{$_}[0]; + my $info= $dbh->selectall_hashref('describe `'.$table.'`',"Field"); + my @count = $dbh->selectrow_array('select count(*) from `'.$table.'`'); + + my %indexes; + { + my $sth= $dbh->prepare("show index from `".$table.'`'); + $sth->execute; + while(my $i = $sth->fetchrow_hashref) + { + $indexes{${%$i}{Key_name}}= { + type=>${%$i}{Index_type}, + unique=>!${%$i}{Non_unique}, + comment=>${%$i}{Comment}, + } if !defined($indexes{${%$i}{Key_name}}); + + $indexes{${%$i}{Key_name}}{columns}[${%$i}{Seq_in_index}-1]= + ${%$i}{Column_name}; + } + } + do_table($table, $info, \%indexes, \@count); } my @NoOfTriggers; --- 1.4/ndb/tools/ndb_size.tmpl 2006-07-26 11:44:55 +10:00 +++ 1.5/ndb/tools/ndb_size.tmpl 2006-07-26 11:44:55 +10:00 @@ -71,6 +71,7 @@ Column Type + VARSIZE Size Key @@ -81,6 +82,7 @@ + YES