Re: Row Size Calculations - Adam Dixon - com.mysql.lists.cluster

12 messages in com.mysql.lists.clusterRe: Row Size Calculations

From	Sent On	Attachments
Adam Dixon	26 Jul 2005 21:44
Matthew Glubb	27 Jul 2005 01:39
Mikael Ronström	27 Jul 2005 01:51
Matthew Glubb	27 Jul 2005 01:58
Mikael Ronström	27 Jul 2005 02:02
Mikael Ronström	27 Jul 2005 02:08
Matthew Glubb	27 Jul 2005 02:09
Matthew Glubb	27 Jul 2005 02:12
Mikael Ronström	27 Jul 2005 03:42
Mikael Ronström	27 Jul 2005 03:44
Adam Dixon	27 Jul 2005 19:58
Mikael Ronström	28 Jul 2005 02:34

Subject:	Re: Row Size Calculations
From:	Adam Dixon (adam...@gmail.com)
Date:	07/27/2005 07:58:23 PM
List:	com.mysql.lists.cluster

So would the following be correct for MySQL 4.1.12;

INT = 13 * 4 = 52 BIGINT = 1 * 8 = 8 SMALLINT = 2 * 2 = 4 VARCHAR Bytes: 16, 32, 16, 24, 16, 16, 16, 6, 10, 50, 32, 10 = 244 (244+ (12x 2overhearbytes) = 268 Row overhead: 16

Total: 348bytes per row

Pagesize / Rowsize = Rows Per Page 32768 / 348 per row = 94 (Say 90)

100,000 rows / 90 = 1111.12 1million rows / 90 = 11111.12 15million rows / 90 = 166666.66

100,000 records = 1112 pages * (32768 + 128) = 36,580,352 bytes (36mb) 1 million records = 11112 * (32768 + 128) = 365,540,352 bytes (365mb) 15 million records = 166667 * (32768 + 128) = 5,482,677,632 (5.5gb)

Index Memory Requirements:

---------------------------------------------- No primary key on table; so; 1 * (25 + 8) = 33 bytes 3 OrderedIndex * (10) = 30 bytes

---------------------------------------------- Total: 63 bytes

100,000 records = 100000 * 63 bytes = 6,300,000 bytes. (6mb) 1 million records = 1,000,000 * 63 bytes = 63,000,000 bytes. (64mb) 15 million records = 15,000,000 * 63 bytes = 945,000,000 bytes. (950mb)

I think only the PK will get stored in IndexMemory, and the OrderedIndex's in DataMemory?. The 3 ordered indexes are; char 32. datetime, and smallint if they need to be included in the size of the 10byte ordered index.

Total DB Size: 5482677632 + 945000000 = 6,427,677,632 (6.5gb)

(SizeofDatabase * NumberOfReplicas * 1.1 ) / NumberOfDataNodes = Total memory.

(6427677632 * 2 * 1.1) / 4 = 3,535,222,697.6 (3.6gb)

Thanks, Adam

On 7/27/05, Mikael Ronström <mik...@mysql.com> wrote:

Hi, Size of VARCHAR(n)= ((n+2)+3)>>2)<<2 In normal english 2 bytes overhead plus the max size of the VARCHAR and aligned to a 4 byte boundary. Size (Int(11)) = 4 bytes (unsigned or not doesn't matter) Size(datetime) = 4 bytes (not 100% sure on this, could be 8 bytes) Size(timestamp) 4 bytes (not 100% sure on this, could be 8 bytes)

IndexMemory size is dependent on version 4.1: 25 + size of PK fields (8 if no PK) per record 5.0: 25 bytes per record

Normal indexes (not unique nor PK) is stored in DataMemory 10 bytes per record

Unique Indexes are stored as an ordinary table with unique key as PK and PK as fields in the table

When defining a PK you actually get both the PK hash index stored in IndexMemory and an ordinary ordered index, the same is true for unique indexes. If only the hash index is needed then USING HASH can be used to dismiss these extra indexes.

2005-07-27 kl. 06.45 skrev Adam Dixon:

I need to take a more serious look at rowsizes for a particular table in our database, It consists of; 12 varchars, 14 int (11) some unsigned, 3 smallint, a datetime and a timestamp fields. Indexes on 1 varchar, 1 int and 1 datetime fields.

Does someone have a clear cut definition of how I can calculate row size, so I can correctly and accuratly predict DataMemory and IndexMemory requirements of our existing database.

I have had a look at ./ndb_desc -d clust1 table (However results dont mean that much to me) Does K Value = the size of the row in kilobytes?

Waiting... -- table -- Version: 2

Change version number of table

Fragment type: 2

Internal variable describing the fragmentation

K Value: 6

Internal variable part of distributed hash algorithm (is always = 6)

Min load factor: 78 Max load factor: 80

Fill factors in the PK hash index

Temporary table: no

Whether table is to be logged to disk or not (temporary tables are not) Currently not available to set from SQL i/f

Number of attributes: 32

Number of fields

Number of primary keys: 1

Number of fields in primary key

Length of frm data: 909

Size of frm file

TableStatus: Retrieved

Not sure

I understand that max rowsize is 8kb, as this is how cluster stores data in memory, I just need a definition on how to ensure that our tables abide by these limitations.

Rgrds Mikael

Mikael Ronstrom, Senior Software Architect MySQL AB, www.mysql.com

Jumpstart your cluster: http://www.mysql.com/consulting/packaged/cluster.html