NoOfReplicas=2

mysql> SELECT VERSION();
+---------------------------------+
| VERSION()                       |
+---------------------------------+
| 5.1.24-ndb-6.3.16-telco-1hardy1 | 
+---------------------------------+

mysql> CREATE TABLE `vartest` (
    `id` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING HASH
    ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

zsh> for i in {1..262144}; do echo $i ; done > /tmp/i.txt

mysql> LOAD DATA INFILE '/tmp/i.txt' INTO TABLE vartest;

ndb_mgm> ALL DUMP 1000

2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 2: Data usage is 9%(250 32K pages of total 2560)
2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2308 max: 3199 curr: 252
2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 252
2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 3: Data usage is 9%(250 32K pages of total 2560)
2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2308 max: 3199 curr: 252
2008-09-12 16:44:29 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 252

mysql> ALTER TABLE vartest ADD a VARCHAR(50);

2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 2: Data usage is 9%(250 32K pages of total 2560)
2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2308 max: 3199 curr: 252
2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 252
2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 3: Data usage is 9%(250 32K pages of total 2560)
2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2308 max: 3199 curr: 252
2008-09-12 16:45:56 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 252

mysql> UPDATE vartest SET a='abcd' WHERE ID MOD 2 = 1;
Query OK, 131072 rows affected (10.46 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 2: Data usage is 13%(334 32K pages of total 2560)
2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2224 max: 3199 curr: 336
2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 336
2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 3: Data usage is 13%(334 32K pages of total 2560)
2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2224 max: 3199 curr: 336
2008-09-12 16:49:31 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 336

>>> (334-250)*32*1024/131072.0
21.0

# So the total amount used here is on average 21 bytes/row. 8 bytes
  should be for the according to the manual.

mysql> UPDATE vartest SET a=NULL WHERE id MOD 2 = 1;
Query OK, 131072 rows affected (8.53 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 2: Data usage is 13%(334 32K pages of total 2560)
2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2224 max: 3199 curr: 336
2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 336
2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 3: Data usage is 13%(334 32K pages of total 2560)
2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2224 max: 3199 curr: 336
2008-09-12 16:55:09 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 336

# We didn't get back any memory when resetting the values to NULL
  again. AFAIK the memory will still be allocated to the table for
  later use so this should be ok.

mysql> update vartest set a='abcd' where id mod 2 = 0;
Query OK, 131072 rows affected (7.75 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 2: Data usage is 14%(382 32K pages of total 2560)
2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2176 max: 3199 curr: 384
2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 384
2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 3: Data usage is 14%(382 32K pages of total 2560)
2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2176 max: 3199 curr: 384
2008-09-12 16:58:39 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 384

>>> (382-334)*32*1024/131072.0
12.0

# So when 131072 other rows was set to NULL we needed 12 bytes
  more/row than in the first case. If this 12 bytes is a general
  overhead for the fact that a VARCHAR is used in the row there is
  only 1 byte left unexplained for the 21 instead of 8 bytes above.

mysql> update vartest set a='abcd' where id mod 2 = 1;
Query OK, 131072 rows affected (14.53 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 2: Data usage is 16%(422 32K pages of total 2560)
2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2136 max: 3199 curr: 424
2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 424
2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 3: Data usage is 16%(422 32K pages of total 2560)
2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2136 max: 3199 curr: 424
2008-09-12 17:03:00 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 424

>>> (422-382)*32*1024/131072.0
10.0

# So setting the other 131072 rows back to 'abcd' again 10 bytes more
  is used per row, not just 8.

>>> (422-250)*32*1024/(131072.0*2)
21.5

# So the average number of bytes/row for all VARCHARs set to 'abcd' is
  now 21.5 bytes.

# Lets go adding one more column.

mysql> ALTER TABLE vartest ADD b VARCHAR(50);

ndb_mgm> ALL DUMP 1000

2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 2: Data usage is 16%(422 32K pages of total 2560)
2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2136 max: 3199 curr: 424
2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 424
2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 3: Data usage is 16%(422 32K pages of total 2560)
2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2136 max: 3199 curr: 424
2008-09-12 23:30:56 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 424

mysql> UPDATE vartest SET b='' WHERE id MOD 2 = 1;

ndb_mgm> ALL DUMP 1000

2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 2: Data usage is 16%(422 32K pages of total 2560)
2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2136 max: 3199 curr: 424
2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 424
2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 3: Data usage is 16%(422 32K pages of total 2560)
2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2136 max: 3199 curr: 424
2008-09-12 23:35:56 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 424

# So no more memory used yet

mysql> UPDATE vartest SET b='1' WHERE id MOD 2 = 1;
Query OK, 131072 rows affected (17.39 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 2: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 442
2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 3: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:40:21 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 442

>>> (440-422)*32*1024/131072.0
4.5

# So now an average of 4.5 extra bytes was used for each row.

mysql> UPDATE vartest SET b=NULL WHERE id MOD 2 = 1;
Query OK, 131072 rows affected (7.74 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 2: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 442
2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 3: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:45:43 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 442

# No memory back, but that should be fine.

mysql> UPDATE vartest SET b='1' WHERE id MOD 2 = 0;
Query OK, 131072 rows affected (17.47 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 2: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 442
2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 3: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:47:44 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 442

# Now still the same amount of memory is needed when the other half
  number of the rows was set to '1' as for the first half.

mysql> UPDATE vartest SET b='' WHERE id MOD 2 = 1;
Query OK, 131072 rows affected (7.52 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 2: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 442
2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 3: Data usage is 17%(440 32K pages of total 2560)
2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2118 max: 3199 curr: 442
2008-09-12 23:53:08 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 442

# No additional memory used. This is the same case as when setting
  b='' on half of the rows before.

mysql> UPDATE vartest SET b='1' WHERE id MOD 2 = 1;
Query OK, 131072 rows affected (17.66 sec)
Rows matched: 131072  Changed: 131072  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 2: Data usage is 17%(458 32K pages of total 2560)
2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2100 max: 3199 curr: 460
2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 460
2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 3: Data usage is 17%(458 32K pages of total 2560)
2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2100 max: 3199 curr: 460
2008-09-13 00:01:59 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 460

>>> (458-440)*32*1024/131072.0
4.5

# So same 4.5 bytes is needed in this case.

# Looking at what we have on each row now.

# a='abcd'
# b='1'

# Let's set b=NULL again and add 8 bytes to a, a='abcd12345678'.

mysql> UPDATE vartest SET b=NULL;
Query OK, 262144 rows affected (46.78 sec)
Rows matched: 262144  Changed: 262144  Warnings: 0

mysql> UPDATE vartest SET a='abcd12345678';
Query OK, 262144 rows affected (32.44 sec)
Rows matched: 262144  Changed: 262144  Warnings: 0

ndb_mgm> ALL DUMP 1000

2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 2: Data usage is 19%(496 32K pages of total 2560)
2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 2: Index usage is 21%(513 8K pages of total 2336)
2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 2062 max: 3199 curr: 498
2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 2: Resource 3 min: 2560 max: 2560 curr: 498
2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 3: Data usage is 19%(496 32K pages of total 2560)
2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 3: Index usage is 21%(513 8K pages of total 2336)
2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 2062 max: 3199 curr: 498
2008-09-13 00:24:46 [MgmSrvr] INFO     -- Node 3: Resource 3 min: 2560 max: 2560 curr: 498

>>> (496-458)*32*1024/262144.0
4.75

# This should have resulted in 8 bytes more used for a since 4 byte
  alignment. On the other hand the b that should have been 4 bytes
  before is now NULL. This all together gives that the NULL Value
  might use 0.75 bytes in this case. But the column b did take 4.5
  bytes before. So maybe this half byte is also used for the NULL
  Value, or the fact that the column is there at all.

>>> (496-422)*32*1024/262144.0
9.25

# Compared to before the column b was added this 8 bytes extra in a
  and the NULL Value in b gives 9.25 bytes. So 1.25 bytes have gone
  somewhere else, maybe column b.

# All this is a bit confusing. It might also be the case that the two
  VARCHAR columns are aligned together.