InnoDB row metadata size (minimum row size) I'm trying to decide whether to normalize a thing further, and need to know what the smallest a row in InnoDB can be (I've got MySQL 5.6).

This is not exactly my case, but for simplicity, let's assume I just have a surrogate PK (bigint) and two TINYINT columns, neither of which can be null. How many bytes can I expect each row to need for storage if I'm gonna have 1 billion of these?

I'd imagine each row requires some metadata (I know it does at least for when there are NULLable columns), but can't seem to find the documentation on how to calculate it.

Then, how much can I expect to be eaten by the free space in the page, and the metadata in the page (or is that all together)?

I want to know so that I can better judge when I would have a long table that is narrow enough to avoid proper normalization.
Each row in InnoDB (let assume COMPACT format) has these headers:


Offsets - one or two byte per variable length type field.
NULL flags - one bit per NULL-able field, aligned to a whole number of bytes.
So called "Extra bytes" - a bunch of flags like is_deleted, pointer to next record ect. Five bytes in COMPACT format, fixed.


Then comes primary key field(s).
Then 6 bytes transaction id and 7 bytes rollback pointer.
Then remaining field values.

For the given row (BIGINT, TINYINT, TINYINT) it's size will be:


0 bytes offsets
0 bytes NULL flags
5 bytes "Extra bytes"
8 bytes BIGINT
6 bytes trx_id
7 bytes roll_ptr
1 byte TINYINT
1 byte TINYINT


So, 28 bytes per record.

But to calculate how much space the table will take you need to take into account:


PAGE header (38+56 bytes)
page trailer
page fill factor
infinum, supremum records
space used by non-leaf pages


Check these slides for more details
http://www.slideshare.net/akuzminsky/data-recovery-talk-on-pluk