What is the relation between Tablespaces, .frm & .ibd files and database pages in MySQL?

Question

I looked in the documentation but there was not a clear answer regarding their relation, can someone give an real life example of their relation in MySQL?

Database pages are smallest read write unit in databases and is usually 8KB, but:

• What is the relation between MySQL pages and .frm & .ibd files?
• Is .frm files consists of many pages coming together?
• If so then does the .frm files have any type of info on the pages that are inside them?
• Like the location of each page and stuff like that?
• Where can i read more about this?
• And what about tablespaces?
• Is every .frm file a tablespace?

I read their documentation for hours but it got me even more confused

(I'm talking about the InnoDB storage engine BTW)

Answer 1

(@jynus has good answers; hopefully, I am adding to his answer, not repeating too much, and not disagreeing.)

Part of the confusion comes from the evolution of InnoDB.

Keep in mind that 90+% of MySQL users can totally ignore "tablespaces" and do just fine. Understanding them is either an academic exercise or, in a few cases, important for disk management.

In the beginning, on 32-bit machines, there was one tablespace, ibdata1. However, if that was not big enough (remember, some machines had a limit of 2GB for a file), there was ibdata2, etc. Technically, this was a set of "tablespaces", but it was not touted as such.

In any case, the concept of ibdata1 as the main place for InnoDB stuff lives on.

Initially, all blocks of all tables and indexes lived in ibdata1. A problem was, and still is, that space freed up was left as free blocks in ibdata1, and could not be given back to the OS.

To allow for putting a table on a different disk, and for allowing you to DROP or ALTER a table and give space back to the Operating System, innodb_file_per_table. So, at this point, ibdata1 contained miscellany stuff, plus (optionally) some tables. Various .ibd files each contained one table (including indexes), or one PARTITION of a table.

PARTITIONing allows for breaking a table into 'subtables', each acts almost entirely like a table.

This still does not allow for good disk management. A thousand tiny tables, stored as file_per_table, wastes a lot of disk space. Putting a different database on a different filesystem is a hassle. Etc. So, in comes a more generic "tablespace".

About the same time, the handling of "tmp" and "undo" space became important. In the past, those were handled somewhat monolithically in ibdata1. But there were mutex slowdowns, etc, so they got spread around.

So what to do with "tablespaces" in 5.7 and later?

Plan A: Ignore them. The defaults will probably be good enough.

Plan B: One tablespace per database. Unfortunately, there is not 'automatic' way to do this.

Plan C: Follow the 80-20 rule. For the 20% of the tables that will consume 80% of the disk space, use file_per_table. For the rest, have them go to ibdata1 (or tablespace-per-databse).

The history of the meta info is simpler:

Pre-8.0: The .frm file contained the schema, no data; ibdata1 contained some more info.

8.0+: The new "Data Dictionary" is in InnoDB tables. (The bootstrapping is scary.) It contains the schema and lots of other stuff. There is a file on disk in the unlikely event that the DD gets hosed and you need to reconstruct the schema.

Blocks:

Each tablespace (ibdata*, .ibd) has always been broken into blocks. Initially, there was one choice: 16KB. You may hear of "extents" of 1MB(?) or 8MB(?) -- this is merely pre-allocating a bunch of blocks. This was slightly beneficial for HDDs, but probably useless for SSDs.

While it is possible to have 4/8/16/32/64KB InnoDB blocks, 99+% of users stick with the default of 16KB. They are organized in a B+Tree based on some index (the PRIMARY KEY for the data, or the secondary key for secondary indexes). A convenient Rule of Thumb: 100 items are stored in a BTree block. Correlary: a million-row table (or index) will be about 3 levels deep.

Answer 2

• What is the relation between MySQL pages and .frm & .ibd files? Is .frm files consists of many pages coming together?

.frm used to contain metadata about tables- that means, the structure (think of the output of SHOW CREATE TABLE- columns and data types) in its own format. Now a days (Since MySQL 8.0) that data is stored on InnoDB tables on the mysql database to allow transactional schema changes. .ibd contained mostly data (not that innodb also had its own internal data dictionary, supporting MySQL one, but different), organized in namespaces>extents>pages, and inside pages, records "rows".

There is other stuff (structures) related to InnoDB for it to work, but the main take away is that there are 2 different planes, the SQL layer, which takes care of connection handling, query parsing and optimization for execution, and the Engine layer (InnoDB, MyISAM, MyRocks, ...) which decides how data is actually stored. Both layers communicate with common API called Handler API, which contains very basic row-level actions like "Search the first record in Primary Key order" or "get the following record".

.frms were an SQL-level functionality (so found for every engine), .ibds were an InnoDB-only structure. Because InnoDB also had its own internal dictionary, aside from the MySQL one, if you deleted a .frm improperly (from the filesystem), InnoDB got confused as the table was still there from its point of view.

If so then does the .frm files have any type of info on the pages that are inside them? Like the location of each page and stuff like that?

No, the MySQL data dictionary, be it on .frm or in its new form only contains basic SQL-level information (what is the table definition, which columns are there, which types, etc.). InnoDB information is stored on InnoDB related files- ibdata1, .ibds, transactiona logs. When InnoDB engine receives, through the Handler API the need to read a particular row, it does that independently of the SQL later- data may be on cache (buffer pool, or it may be on disk only, or it may be on disk but not available because it is hidden for the given session). To find a particular page, it implements a series of memory and disk structures (headers on files, tree-like structures, lookup tables, hashes that will let InnoDB access the right page). This is very little space to give an proper overview of InnoDB internals, but think of it like a very complex set of pointers and references that allow accessing quickly data while maintaining consistency to disk and access control.

Where can i read more about this?

I already shared with you the InnoDB: A journey to the core which describes more in detail how it works. Aside from that, probably the best way is the source code itself- MySQL source code is available and you can check the details of how InnoDB implements its own data structures. This is not simple- and almost impossible to summarize here. For a very simplistic approach, each table creates a B+tree structure organized in 16K pages ordered by the primary key (there is always a primary key, even if one has not been explicitly created). Other indexes are implemented as separate tree structures that record the value of the PK of the row they index. If you just want a simplified approach about the logical view of this, without the technical details, you can read my slides on Query Optimization for MySQL/InnoDB.

And what about tablespaces? Is every .frm file a tablespace?

No, as we said, .frm is a MySQL table structure. However, InnoDB usually creates a different tablespace per table (there are exceptions, like partitioned tables), and each tablespace normally is on a separate file (.ibd). However, this is not necessarily true, there could be several tables on the tablespace0 (it's complicated :-)).