MySQL/Mariadb per-record overhead?

Question

what, if any, is the per-record disk overhead for mysql/mariadb? (i'm mostly interested in InnoDB, but any engine would interesting)

lets say you just have a

CREATE TABLE tbl (
    id BIGINT NOT NULL PRIMARY KEY
);

obviously this will use at least 8 bytes (BIGINT) per record, but will it use more than that per record?

I can't quite see the point of this question - if you need bigint then you need bigint..Is this about sizing your physical server? performance? or just an I'm curious question? — P.Salmon
– P.Salmon, Commented Jan 30, 2021 at 7:29
Nearly every table I have encountered does not need BIGINT; a 4-byte INT UNSIGNED is more than enough; an even smaller datatype usually suffices. — Rick James
– Rick James, Commented Jan 30, 2021 at 8:21

Rick James · Accepted Answer · 2021-01-30 02:29:42Z

There's a lot of overhead.

Per 16KB block: several pointers, flags, etc.
Per row: about 2 dozen bytes of links, transaction stuff, etc. Even more, if a row is being touched by multiple transactions at the same time.
Per column: 1- or 2-byte length and NULLness. Yes even for that 8-byte BIGINT.
PRIMARY KEY: This is quite low because the PK is clustered with the data; it adds up to about 1% of the entire table.
Secondary keys: Each secondary key (INDEX(...)) contains the columns indicated, plus a copy of the PK column(s). It is a separate BTree. So, for this reason, there may be multiple copies of your BIGINT occupying disk space.
InnoDB initially allocates in 16KB block chunks (data and each secondary index separately). Later it pre-allocates in bigger chunks. I think your sample table will use 16KB until you have several hundred rows in it.
InnoDB does not release space back to the OS. That is, if you insert a thousand rows, then the table will be expanded to several blocks. Then, if you delete all the rows, the disk space won't shrink.

Aside from the indexes, that 8 bytes for the bigint, plus similar computations for other columns can be added up. But then you must multiply by 2 to 3 to get the disk space needed for an InnoDB table. (Of course, there are exceptions with less than 2x or more than 3x.)

I'll be happy to discuss other examples, but I cannot be very precise.

Collectives™ on Stack Overflow

MySQL/Mariadb per-record overhead?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related