1

I use INNODB engine.

My row format is Dynamic.

For variable-length columns like TEXT, BLOB and varchar etc. , data is stored in off page and not in the same page itself.

Do I have a way to determine if my INNODB row uses additional overflow pages?

If yes, can I also get the total number of off pages used?

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
kanaga sangeetha
  • 11
  • 2

2 Answers2

3

SHOW TABLE STATUS LIKE 'tablename'; -- There are 3 length columns (Data, Index, Free). These are in bytes; divide by 16KB to get pages.

It is more complex to discover on-record vs off-record storage.

If all you want is whether it uses off-record pages, then that can probably be determined by the definition of the ROW_FORMAT. For DYNAMIC, I think these are the rules:

  1. Any column up to 40 bytes is stored on-record.
  2. If all the rest of the columns will fit (in about 8KB), then put them on-record.
  3. Spin off the longest rows first until the rest fit.

The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. ("all or none")

When a column is stored off-record, a 20-byte "pointer" is left on-record. (There may be a 2-byte length in addition; I am not sure.)

Depending on what version of MySQL/MariaDB/Percona you are using, there are detailed statistics on the data and index BTrees.

Rick James
  • 80,479
  • 5
  • 52
  • 119
0

Please try hexdump utility. This will help you to see if there are any 20 bytes row pointer in the ibd file and if this does exist then yes, your row is being stored as off page or overflow ( as says in PG ). But remember that it will only be visible once you cross the size of the column being defined.

simplifiedDB
  • 679
  • 6
  • 18
  • 36