Estimating size of a MySQL Heap table?

[lorax]

I'm considering using a HEAP table to do some select statements on. My only concern is how big the table will be once it is created. I found this equation for the size of one row:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

But I'm not sure I understand how to use it. It seems to me that what I need are:

A. get the size of all of the keys I intend to use from the tables (simply use MYSQL to get these)

B. max key length - not sure how to get this

C. sizeof(char*) - not sure what to do with this

D. length of row (max?) - again where to get this

E. Align - not a clue what this is for

[DaveAtIFG]

I did a little searching and found the same formula, but no explanation. My database experience is very limited but common sense suggests to estimate memory needs I must know how many characters are in a row, then double it to determine the number of bytes. If memory serves, when you define a table structure you define a max length for each field. Add 'em up! Hopefully, someone who knows what they are talking about will add some additional comments. In other words this post is just a long winded BUMP. :)

[DaveAtIFG]

After a bit more thought, ALIGN may be referring to "slack" space in memory. For example, a hard disk may use a 4K cluster to write a one byte file leaving a lot of unused space, "slack." Memory operates on 8, 16, 32, 64 (etc) bit chunks. If a field is 25 characters (50 bytes) the remaining memory space up to 64 bytes may be "slack" and the ALIGN portion of the formula may be to estimate this. I'm guessing here, don't flame me too badly...

[lorax]

DaveAtIFG,
I appreciate the response. It seems like such a simple thing doesn't it - yet I can't for the life of me find any good documentation/help on it! I'll post what I find here when I find it.

[seindal]

I read it like this (guessing along the way :-)

First, the formula doesn't give the size requirement for a row, it gives the overhead for a row, so the real formula is

length_of_row
+ SUM_OVER_ALL_KEYS(max_length_of_key) + sizeof(char*) * 2
+ ALIGN(length_of_row+1, sizeof(char*))

All records in a HEAP table is of fixed size, so the length_of_row is the same for all rows.

It appears (I haven't read the source code) that records are organised as a linked list, with a pointer to the row data. The data is aligned on a four (or eight) byte boundary, possibly leaving a gap.

This means each record takes up:

the primary key (max size allocated for all)
pointer to where data is stored (four or eight bytes)
pointer to next record (four or eight bytes)
length_of_row (that's your data - all row are the same size)
alignment overhead for row (0-3 bytes or 0-7 bytes)

Lets say the largest primary key is 27 bytes, and the row length is 169 bytes, and we use a 32bit computer, then each row takes up

27 (primary key key)
+ 4 (row data pointer)
+ 4 (next row pointer)
+ 169 (row data)
+ 3 (alignment to make 169+3=172 a multiplum of 4)
= 207 bytes.

That's my guess, anyway.

René.

[seindal]

I'll follow up on myself :)

A. get the size of all of the keys I intend to use from the tables (simply use MYSQL to get these)

You know the type of the primary key.

If your key is an int, it is four bytes.
If your key is a char(32), then it is 32 bytes.

The size requirement for each datatype is described in the MySQL manual. See [mysql.com...] and click through to the explanation of the different column types.

B. max key length - not sure how to get this

Same as A.

C. sizeof(char*) - not sure what to do with this

Depends on the CPU. It is programmer's talk, meaning the memory requirement for a pointer to some data in memory. It is usually four bytes on a 32bit maching, and 8 bytes on a 64bit machine.

D. length of row (max?) - again where to get this

You know the type of each field from the MySQL manual. Just add them together.

E. Align - not a clue what this is for

Most hardware requires data to be aligned on a 4 or 8 byte boundary, so you cannot just store 17 bytes, you have to use 20 or 24 bytes. The overhead is for this alignment.

[lorax]

Ah...now I understand. Thanks so much Rene.

[seindal]

You're welcome. I posted your question to the mysql mailing list, but there have been no replies. Maybe nobody really knows for sure!

René.