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
1.2483 06/06/07 01:37:29 stewart@stripped +2 -0
WL#3310 Update ndb_size.pl for 5.1
the "If I had a hammer" patch.
Had to answer the question, did it in code.
Much more accurrate results for 5.1
ndb/tools/ndb_size.tmpl
1.4 06/06/07 01:37:12 stewart@stripped +16 -2
Comment on 5.1-dd exp support.
Display:
- nr varsized attributes
- actual row overhead depending on version
- overhead for varsized attributes
ndb/tools/ndb_size.pl
1.7 06/06/07 01:37:12 stewart@stripped +50 -30
Add experimental 5.1-dd support to ndb_size.pl
Improve calculations with 5.1 and variable sized attributes.
Now much more closely resembles reality.
# 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.6/ndb/tools/ndb_size.pl 2006-01-19 22:50:51 +11:00
+++ 1.7/ndb/tools/ndb_size.pl 2006-06-07 01:37:12 +10:00
@@ -57,7 +57,7 @@
$template->param(dsn => $dsn);
}
-my @releases = ({rel=>'4.1'},{rel=>'5.0'},{rel=>'5.1'});
+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");
@@ -91,12 +91,14 @@
# We now work out the DataMemory usage
- # sizes for 4.1, 5.0, 5.1
- my @totalsize= (0,0,0);
+ # sizes for 4.1, 5.0, 5.1 and 5.1-dd
+ my @totalsize= (0,0,0,0);
+ my $nrvarsize= 0;
foreach(keys %$info)
{
- my @realsize = (0,0,0);
+ my @realsize = (0,0,0,0);
+ my @varsize = (0,0,0,0);
my $type;
my $size;
my $name= $_;
@@ -112,53 +114,56 @@
}
if($type =~ /tinyint/)
- {@realsize=(1,1,1)}
+ {@realsize=(1,1,1,1)}
elsif($type =~ /smallint/)
- {@realsize=(2,2,2)}
+ {@realsize=(2,2,2,2)}
elsif($type =~ /mediumint/)
- {@realsize=(3,3,3)}
+ {@realsize=(3,3,3,3)}
elsif($type =~ /bigint/)
- {@realsize=(8,8,8)}
+ {@realsize=(8,8,8,8)}
elsif($type =~ /int/)
- {@realsize=(4,4,4)}
+ {@realsize=(4,4,4,4)}
elsif($type =~ /float/)
{
if($size<=24)
- {@realsize=(4,4,4)}
+ {@realsize=(4,4,4,4)}
else
- {@realsize=(8,8,8)}
+ {@realsize=(8,8,8,8)}
}
elsif($type =~ /double/ || $type =~ /real/)
- {@realsize=(8,8,8)}
+ {@realsize=(8,8,8,8)}
elsif($type =~ /bit/)
{
my $a=($size+7)/8;
- @realsize = ($a,$a,$a);
+ @realsize = ($a,$a,$a,$a);
}
elsif($type =~ /datetime/)
- {@realsize=(8,8,8)}
+ {@realsize=(8,8,8,8)}
elsif($type =~ /timestamp/)
- {@realsize=(4,4,4)}
+ {@realsize=(4,4,4,4)}
elsif($type =~ /date/ || $type =~ /time/)
- {@realsize=(3,3,3)}
+ {@realsize=(3,3,3,3)}
elsif($type =~ /year/)
- {@realsize=(1,1,1)}
+ {@realsize=(1,1,1,1)}
elsif($type =~ /varchar/ || $type =~ /varbinary/)
{
- my $fixed= 1+$size;
+ my $fixed=$size+ceil($size/256);
my @dynamic=$dbh->selectrow_array("select avg(length(`"
.$name
."`)) from `".$table.'`');
$dynamic[0]=0 if !$dynamic[0];
- @realsize= ($fixed,$fixed,ceil($dynamic[0]));
+ $dynamic[0]+=ceil($dynamic[0]/256); # size bit
+ $nrvarsize++;
+ $varsize[3]= ceil($dynamic[0]);
+ @realsize= ($fixed,$fixed,ceil($dynamic[0]),$fixed);
}
elsif($type =~ /binary/ || $type =~ /char/)
- {@realsize=($size,$size,$size)}
+ {@realsize=($size,$size,$size,$size)}
elsif($type =~ /text/ || $type =~ /blob/)
{
- @realsize=(256,256,1);
+ @realsize=(256,256,256,256);
$NoOfTables[$_]{val} += 1 foreach 0..$#releases; # blob uses table
- } # FIXME check if 5.1 is correct
+ }
@realsize= align(4,@realsize);
@@ -212,20 +217,20 @@
type=>'bigint',
size=>8,
key=>'PRI',
- datamemory=>[{val=>8},{val=>8},{val=>8}],
+ datamemory=>[{val=>8},{val=>8},{val=>8},{val=>8}],
};
$columnsize{'HIDDEN_NDB_PKEY'}= [8,8,8];
}
- my @IndexDataMemory= ({val=>0},{val=>0},{val=>0});
- my @RowIndexMemory= ({val=>0},{val=>0},{val=>0});
+ my @IndexDataMemory= ({val=>0},{val=>0},{val=>0},{val=>0});
+ my @RowIndexMemory= ({val=>0},{val=>0},{val=>0},{val=>0});
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});
- my @dm = ({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},
@@ -233,13 +238,22 @@
indexmemory=>\@im,
datamemory=>\@dm,
};
- $IndexDataMemory[$_]{val}+=$dm[$_]{val} foreach 0..2;
- $RowIndexMemory[$_]{val}+=$im[$_]{val} foreach 0..2;
+ $IndexDataMemory[$_]{val}+=$dm[$_]{val} foreach 0..$#releases;
+ $RowIndexMemory[$_]{val}+=$im[$_]{val} foreach 0..$#releases;
}
# total size + 16 bytes overhead
my @TotalDataMemory;
- $TotalDataMemory[$_]{val}=$IndexDataMemory[$_]{val}+$totalsize[$_]+16 foreach 0..2;
+ 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});
+ {
+ my @a= align(4,$nrvarsize*2);
+ $nrvarsize_mem[2]{val}+=$a[0]+$nrvarsize*4;
+ }
+
+ $TotalDataMemory[$_]{val}=$IndexDataMemory[$_]{val}+$totalsize[$_]+$RowOverhead[$_]{val}+$nrvarsize_mem[$_]{val} foreach 0..$#releases;
my @RowDataMemory;
push @RowDataMemory,{val=>$_} foreach @totalsize;
@@ -260,12 +274,18 @@
my @counts;
$counts[$_]{val}= $count foreach 0..$#releases;
+ my @nrvarsize_rel= ({val=>0},{val=>0},
+ {val=>$nrvarsize},{val=>0});
+
push @table_size, {
table=>$table,
indexes=>\@indexes,
columns=>\@columns,
count=>\@counts,
+ RowOverhead=>\@RowOverhead,
RowDataMemory=>\@RowDataMemory,
+ nrvarsize=>\@nrvarsize_rel,
+ nrvarsize_mem=>\@nrvarsize_mem,
releases=>\@releases,
IndexDataMemory=>\@IndexDataMemory,
TotalDataMemory=>\@TotalDataMemory,
--- 1.3/ndb/tools/ndb_size.tmpl 2005-11-16 19:47:00 +11:00
+++ 1.4/ndb/tools/ndb_size.tmpl 2006-06-07 01:37:12 +10:00
@@ -15,6 +15,8 @@
<p>This information should be valid for MySQL 4.1 and 5.0. Since 5.1 is not a final release yet, the numbers should be used as a guide only.</p>
+<p>5.1-dd is for tables stored on disk. The ndb_size.pl estimates are <b>experimental</b> and should not be trusted. Notably we don't take into account indexed columns being in DataMemory versus non-indexed on disk.</p>
+
<h2>Parameter Settings</h2>
<p><b>NOTE</b> the configuration parameters below do not take into account system tables and other requirements.</p>
<table>
@@ -129,9 +131,21 @@
</TMPL_LOOP>
</tr>
<tr>
+ <th>Nr Varsized Attributes</th>
+ <TMPL_LOOP NAME=nrvarsize>
+ <td><TMPL_VAR NAME=val></td>
+ </TMPL_LOOP>
+</tr>
+<tr>
<th>Row Overhead</th>
- <TMPL_LOOP NAME=releases>
- <td>16</td>
+ <TMPL_LOOP NAME=RowOverhead>
+ <td><TMPL_VAR NAME=val></td>
+ </TMPL_LOOP>
+</tr>
+<tr>
+ <th>Varsized Overhead</th>
+ <TMPL_LOOP NAME=nrvarsize_mem>
+ <td><TMPL_VAR NAME=val></td>
</TMPL_LOOP>
</tr>
<tr>
| Thread |
|---|
| • bk commit into 4.1 tree (stewart:1.2483) | Stewart Smith | 6 Jun |
