data size estimation for memory based table with MySQL Cluster 5.1

[Kenji HIROHAMA]

Hello,

I would like to ask the memory usage estimation with the formula, not
with the ndb_size.pl script.

The table schema is;
CREATE TABLE `t1` (
`id` varchar(32) NOT NULL,
`intcol1` int(32) DEFAULT NULL,
`intcol2` int(32) DEFAULT NULL,
`intcol3` int(32) DEFAULT NULL,
`intcol4` int(32) DEFAULT NULL,
`intcol5` int(32) DEFAULT NULL,
`charcol1` varchar(128) DEFAULT NULL,
`charcol2` varchar(128) DEFAULT NULL,
`charcol3` varchar(128) DEFAULT NULL,
`charcol4` varchar(128) DEFAULT NULL,
`charcol5` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`)

The number of rows is 10,000.

With VARCHAR, I inserted the max size of data such as ;
> mysql> select * from t1 limit 1\G
> *************************** 1. row ***************************
>       id: 9ea93eac-68b1-102b-b811-000c29b6
>  intcol1: 962033002
>  intcol2: 1047372231
>  intcol3: 1707746139
>  intcol4: 1372261796
>  intcol5: 2073785404
> charcol1:
> egILWisfxPwOc3nJx4frnAwgI539kr5EXFbupSZelM2MHqZEmD6ZNuEZzHib8fqYuHQbdrDND8lXqIdcNbAeWOBLZlpZOX5AoNlQFzpK7QjxcLP0wbWIriYGJLqksn
> charcol2:
> ug3YyANnWWDEJiRkiFC4a3e6KyJ2i3hSjksiuFLHlRXw9yhjDtnfoQd0OouyrcIbCB9zQWG4pf0yTZhaIT67nj7BY21FWJqaWrZxEh13Kt2hRbGl4MsrxsuLmvd8FJ
> charcol3:
> id3GaHpRC2L6jgirPm5AW3uGGgCloJ5Ww0eNHSiLWvS5bAxto23AxxR6TXr9qofeoAtxWcJsXnxzxmsdhvoekFc5mSES8tyxvsuPK5Hjs7ihtaJaLz5xEh2s1GCA22
> charcol4:
> zxutF6rOqjXYHHzSrKRwAhWCPXTdhNXYKQIRO9sEkFf1YeTGqw40Ta5u6QNfpvC1DWTTXDkFSFHtte9bbDSwgZjmryHglLhqjAKEF4MkJfT49eXcjzZNOG1F6BnsYT
> charcol5:
> i8X2EnycNH7sDHMltxcILtQE0ZPoPq9zyg24J0hiAgQNpg8jedtrWK5WtXIALR9B03FJ4ou6TCTAtWtN7fETzBzkiAmvTv6LrEZn2RtNfMaOkJfjytCp54ZfEJbb7Z
> 1 row in set (0.09 sec)

I didn't delete the data, and output of all dump 1000 is;

2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 2: Data usage is 6%(166
32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 2: Index usage is 1%(28
8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 0
max: 639 curr: 0
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 3: Data usage is 6%(166
32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 3: Index usage is 1%(28
8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 0
max: 639 curr: 0
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 4: Data usage is 6%(178
32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 4: Index usage is 1%(31
8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 4: Resource 0 min: 0
max: 639 curr: 0
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 5: Data usage is 6%(178
32K pages of total 2560)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 5: Index usage is 1%(31
8K pages of total 2336)
2008-05-07 14:45:43 [MgmSrvr] INFO     -- Node 5: Resource 0 min: 0
max: 639 curr: 0

Output of ndb_desc is here;

[root@h1 mysql-cluster]# /usr/local/mysql/bin/ndb_desc -c
192.168.47.128 t1 -d mysqlslap  -p
-- t1 --
Version: 1
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 11
Number of primary keys: 1
Length of frm data: 382
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
TableStatus: Retrieved
-- Attributes --
id Varchar(32;latin1_swedish_ci) PRIMARY KEY DISTRIBUTION KEY
AT=SHORT_VAR ST=MEMORY
intcol1 Int NULL AT=FIXED ST=MEMORY
intcol2 Int NULL AT=FIXED ST=MEMORY
intcol3 Int NULL AT=FIXED ST=MEMORY
intcol4 Int NULL AT=FIXED ST=MEMORY
intcol5 Int NULL AT=FIXED ST=MEMORY
charcol1 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol2 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol3 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol4 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
charcol5 Varchar(128;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

-- Per partition info --
Partition       Row count       Commit count    Frag fixed memory
 Frag varsized memory
0       2515    3107    131072  1966080
2       2572    3084    131072  1966080
1       2507    2891    131072  1966080
3       2406    2918    131072  1966080

NDBT_ProgramExit: 0 - OK


We have four data node, and each two is running on one server.
So, there are two servers with RedHat on VMWare.


Now, my calculation is here.

From this page,
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbd-definition.html

VARCHAR need 8 bytes overhead, so,

INT: 4*5=20
VARCHAR: (12+8)+5*(128+8)=776
Overhead for row: 16
Total  812 byte

FLOOR ((32*1024 - 128) / 812 ) +1 =41 rows per page

10,000 rows / 41 = 244 pages


The result of my calculation is differ from the output of all dump
1000 and ndb_desc command.

Does anyone have any comments on this?

Thanks,


-
kenji.hirohama@stripped
Kenji Hirohama