MySQL InnoDB Files – Structure and Page Composition

innodbMySQL

Definition of InnoDB page is here : https://dev.mysql.com/doc/internals/en/innodb-page-structure.html

InnoDB stores all records inside a fixed-size unit which is commonly called a "page" (though InnoDB sometimes calls it a "block" instead). Currently all pages are the same size, 16KB.
A page contains records, but it also contains headers and trailers. I'll start this description with a high-altitude view of a page's parts, then I'll describe each part of a page. Finally, I'll show an example. This discussion deals only with the most common format, for the leaf page of a data file.

And based on their documentation, database tables and their tuples are stored in pages, for example a part of each page contains the row data which is the tuples

what i don't understand is :

which files inside the mysql folder are made of these pages? only the .ibd files or..? and do these files contain any meta data at the beginning of them to show info about these pages and their addresses?
when i want to read a particular row, lets say with the PRIMARY id of 1, what are the files that DBMS reads and which parts of them? I'm asking about the DBMS interaction with files, i want to know for example does it first read the .frm file to know the metadata of that table then reads the .ibd file of that table? and then goes down in the B+tree aka that .ibd file and finds the correct page in the clustered Index? or am i wrong?

Best Answer

With innodb_file_per_table = ON and 5.7 or older:

Each DATABASE is manifested on disk as a directory (with the database name).
In that directory are a few files for each TABLE in that Database.
The .frm file contains (effectively) the schema for the table, and essentially nothing else.
The .ibd file contains one or more B+Tree. One B+Tree contains the data, organized by the PRIMARY KEY. Each secondary key lives in a similarly structured B+Tree, but organized by the key, and containing a copy of the PK in the leaf nodes.

Meanwhile, there is some meta information about the table in the common ibdata1 file. (This implies that moving the .ibd file by itself will screw up the integrity of the database.)

File_per_table=OFF and "tablespaces" are variants on the above. In the old days, before .ibd files, all tables were in ibdata1.

MySQL 8 moves a lot of the meta info, plus the schema, into the "Data Dictionary". This is implemented using InnoDB. (I cringe at the bootstrap issues.)

To find the row uniquely identified by PRIMARY KEY = 1234, here is what happens:

If not already opened, the table is "opened" and put in a cache of open tables. Some information from the .frm (or DD) is copied there. The .ibd file is located (or the table is found in ibdata1), etc.
Locate the PK in the table. Get its 'root' node.
Drill down that B+Tree until you find the 'row' in the leaf node (aka page, aka block) that has id=1234. On average, there will be 100 rows in that block, some before 1234, some after. So, in a million-row table, the "drill-down" might hit 3 blocks. Since blocks are cached in the buffer_pool, this involves between 0 and 3 disk hits (usually toward the 0 end).
Deliver all the columns (or whatever is requested).

Note: If the lookup is by a secondary key (that is, not 'clustered'), there are more steps.

Also contained in each data block are a few things to facilitate traversing the rows and the blocks. (Think the + in B+Tree; read Wikipedia if necessary.)

Within each row in the block there are other things:

Fields, and their lengths, possibly pointers to off-record large chunks for TEXT and BLOB. (This gets around the 8K pseudo-limit on a row.)
Transaction ids for MVCC
A "history" of the revisions (again think MVCC) of each row that has been modified, but has not yet been commited/resolved (think ROLLBACK and transaction_isolation). The Undo log is also involved in these things.

Each block anywhere in the running MySQL system is uniquely identified identified by a rather large number. Part of it the 'tablespace' (ibdata1, foo.ibd, etc.) Part of it is the block number.

("Tuples" is a rather general term; I don't know what you are referring to here.)

Related Solutions

Mysql – InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”

You have 6 indexes

  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),

The PRIMARY KEY is in the gen_clust_index (aka Clustered Index). All secondary index entries include a corresponding PRIMARY KEY entry.

I would mysqldump that table and reload it into a test DB server

Next, I would run CHECKSUM TABLE db1.tableABC; or mysqlchk against db1.tableABC in production and the test DB.

If the checksum values match, you should be OK.

If they do not match or you are not sure, run this on the production server

ALTER TABLE db1.tableABC ENGINE=InnoDB;

This will rebuild the table and its indexes.

If that error ever materializes after this, there may be a data dictionary problem inside ibdata1. Your final solution would be to dump all databases, shutdown mysql, delete ibdata1, ib_logfile0, ib_logfile1, start mysql, reload all data.

I posted this InnoDB Cleanup Process in StackOverflow back on Oct 29, 2010

MySQL – How to Flush InnoDB Table, Copy Files, and Unlock Tables from Windows Bat File

Your problem is to maintain two sessions

Session #1 : Lock the table
Session #2 : Copy .ibd file

Note the Following

Session #1 must remain alive in order to hold the lock
Session #1 must remain alive long enough for Session #2 to copy the .ibd file

Steps to Perform

Session #1 sleeps for 30 seconds after issuing the lock
Pause 5 seconds to give Session #1 time to lock the table
Session #2 copies the .ibd file
When Session #1 terminates, the lock is implicitly released (unlocked)

This gives Session #2 25 seconds to copy the .ibd file

Here is the Windows BAT file

@echo off
rem
rem Setup Path to MySQL Folders
rem
set BASE_FOLDER="C:\Program Files\MySQL\MySQL Server 5.6"
set DATADIR=%BASE_FOLDER%\data
rem
rem Setup Path to .ibd file and Destination Folder
rem
set COPY_FROM_DB=mydata
set TABLE_TO_COPY=mytable
set IBD_TO_COPY=%DATADIR%\%COPY_FROM_DB%\%TABLE_TO_COPY%.ibd
rem
rem Setup Destination Folder
rem
set TARGET_FOLDER=D:\BackupFolder
rem
rem Setup MySQL Username and Password
rem
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
rem
rem Setup Session to Lock Table and Hold Lock for 30 seconds in the Background
rem Pause 5 Seconds
rem Perform Physical Copy
rem
set TIME_TO_SLEEP=30
set TIME_TO_PAUSE=5
set SQL=FLUSH TABLES %TABLE_TO_COPY% FOR EXPORT ; DO SLEEP(%TIME_TO_SLEEP%)
start mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"%SQL%"
mysql %MYSQL_CONN% -D%COPY_FROM_DB% -ANe"DO SLEEP(%TIME_TO_PAUSE%)"
copy %IBD_TO_COPY% %TARGET_FOLDER%\.

GIVE IT A TRY !!!

Note : You can change TIME_TO_SLEEP and TIME_TO_PAUSE if it takes more than 30 Seconds to copy

Best Answer

Related Solutions

Mysql – InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”

MySQL – How to Flush InnoDB Table, Copy Files, and Unlock Tables from Windows Bat File

Related Question