Mysql – How to find if the innodb row uses overflow/off pages

innodbMySQLpage-splits

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?

Best Answer

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.

Related Question