Physical Storage: Files, Blocks, Pages

Imagine our OnlineStore database is experiencing slowdowns, especially when many users are browsing products or when we’re trying to generate large sales reports. We’ve optimized our queries, and our logical schema seems fine. So, what could be the bottleneck? Often, the answer lies deeper, in how the data is physically stored and accessed from the disk. Understanding this physical layer, the files, blocks, and pages, is like a mechanic understanding the engine parts of a car. It’s essential for diagnosing performance issues, planning for future growth, and ensuring our database runs smoothly and efficiently. This lesson will demystify the physical storage of data in MySQL.

By the end of this lesson, we will be able to:

• Understand how databases physically store data on disk.

• Learn about the crucial roles of files, blocks, and pages in data storage.

• Recognize how these physical structures directly impact database performance and management.

Let’s get started and uncover how MySQL handles data at its most fundamental level!

The indispensable foundation: Why physical storage matters

When we work with databases, we often interact with logical structures like tables, rows, and columns – for example, the Products table in our OnlineStore database with its ProductName and Price columns. These are convenient abstractions. However, underneath this logical layer, the data has to actually live somewhere. This “somewhere” is the physical storage, usually a hard disk drive (HDD) or a solid-state drive (SSD).

Understanding physical storage is incredibly important for several reasons. Firstly, it’s key to performance tuning. How quickly data can be read from or written to the disk is often the biggest factor in database speed. If we know how data is arranged physically, we can make better decisions about indexing, query writing, and database configuration. Secondly, it’s vital for capacity planning. Knowing how much space our Customers table or Orders table takes up, and how that space is organized, helps us predict future storage needs. Finally, it’s crucial for backup and recovery. Physical backups involve copying the actual files where data resides. A solid grasp of the physical storage architecture ensures we can reliably protect and restore our valuable data.

In essence, while we create and query tables, the database management system (DBMS), like MySQL, is busy managing files on a disk, reading and writing data in chunks. Let’s explore these components.

The building blocks: Database files

Our MySQL database, including all its tables, indexes, and other objects, is ultimately stored in files on the server’s file system. These files are the containers that hold all the data. The specific types of files used can depend on the storage engine chosen for each table. MySQL supports various storage engines, but the two most commonly discussed are InnoDB (the default and most widely used) and MyISAM (an older engine).

InnoDB files

InnoDB is the default storage engine in MySQL, and for good reason. It supports transactions, foreign keys, and crash recovery, making it robust for most applications, including our OnlineStore database.

• .frm files: For every table, regardless of the storage engine (including InnoDB), MySQL creates a .frm file (e.g., Products.frm, Customers.frm). This file stores the table definition or schema, such as column names, data types, and constraints. It doesn’t store the actual row data.

• .ibd files (Tablespace files): If the innodb_file_per_table system variable is enabled (which is the default and highly recommended setting), InnoDB stores the data and indexes for each table in its own file with an .ibd extension (e.g., Products.ibd, Orders.ibd). This makes managing individual tables, like backing them up or reclaiming space, much easier.

• System ...