How does page overflow with columns (InnoDB) work in MySQL?

user_12758565 0
Viewed 263

The documentation says: "Whether columns are stored off-page depends on the page size and the total size of the row."

1.- This means that if I have a page size of 16KB, the maximum size of the row would be 8KB, therefore, if I have 4 columns, will the maximum size of each column be 2KB (approximately)?

2.- When the documentation says "Whether columns are stored off-page depends on the page size and the total size of the row.", it is only valid for the DYNAMIC and COMPRESS column format, or it is also for the format of REDUNDANT and COMPACT columns, or when REDUNDANT and COMPACT are used, ALWAYS so that the excess data of a column goes to an overflow page, the maximum size per row is 768 bytes? Does it not occupy the free space of the rest of the row ?.

3.- The size of the indexes is affected by the size of the row and the columns, that is, going back to what I asked in question 1, each row would be 2KB (approximately), and if it has the type of DYNAMIC column format, the maximum index size would be 2KB (2000 bytes approx.) and not 3072 bytes, right?

mariadb data-structures mysql sql database
1 Answers

Forget the 2KB in your example; it is misleading and mostly irrelevant.

Yes, there is approximately an 8KB limit on what is fit into a 16KB data block. (Index blocks are covered later.)

Columns either go into the up-to-8KB, or into another block ("off-record" storage).

MySQL decides whether to put a column in that 8KB limit based on

  • Datatype -- Numeric columns always go in; strings and blobs depend.
  • Row format and column size -- smallish strings and blobs may go inline or may spill off-record. Certainly any text/blob bigger than 8KB would have to go off-record.

Let's look at another example: 4 columns of 3KB each and 4 "small" strings. First the 4 small strings would be allowed to go on-record. Then, I think two of them would be assigned to be on-record, but the last two would not fit, so...

  • For COMPACT, The first 768 bytes would be on-record, the rest would be off-record.
  • For DYNAMIC, none of the column is on-record.
  • Text shorter than 40 bytes is 'always' stored on-record.

(COMPRESSED adds another wrinkle -- the compressed block is smaller than 16KB.)

For a text/blob that needs to go off-record, a 20-byte 'pointer' is left on-record so that it can be found. This effectively says that you can't have more than about 400 (8K/20) big text columns (not the 1017 mentioned below).

Indexes are stored in a separate BTree. It acts very much like the Data BTree. However, with the 3072 limit (per column in the index), there is no concept of "off-record". (A side note: That is 3072 bytes, which is what `VARCHAR(768) CHARACTER SET utf8mb4 takes. The 768 is _characters.) The max key length is 3500 bytes.

Back to one of your first points... The maximum size of a row is (I think) 4GB. The 8KB is merely what can be held in the on-record part of it. More "limits": http://mysql.rjweb.org/doc.php/limits

What some people hit is "too many columns". The limit in InnoDB is 1017, which comes close to matching the 8KB limit if all 1017 are BIGINTs. But with big DECIMALs or medium-sized VARCHARs, you could build a row with fewer than 1017 columns that won't fit, regardless of Row Format.

Back to your Question 1. No. Every one of your 4 text columns could be many megabytes.

Question 2 is much more complicated. I answered some of it above.

"Free space" -- There's a lot of unclaimed free space floating around in InnoDB BTrees. So much that I have a Rule of Thumb that says that a simple-minded computation of row size (4 bytes per INT, etc) needs to be multiplied by between 2 and 3 to accommodate all the overhead and "free" space. Yes, some of that "free space" is reclaimed some of the time. But the details are long and involved.

The net effect of all that is that most tables, whether holding big columns or not, perform reasonably well.

Sorry, but that is only the simplified version of what is going on.

user_17668310
Related