14.10 InnoDB Disk I/O and File Space Management

14.10.1 InnoDB Disk I/O
14.10.2 File Space Management
14.10.3 InnoDB Checkpoints
14.10.4 Defragmenting a Table
14.10.5 Reclaiming Disk Space with TRUNCATE TABLE

As a DBA, you must manage disk I/O to keep the I/O subsystem from becoming saturated, and manage disk space to avoid filling up storage devices. The ACID design model requires a certain amount of I/O that might seem redundant, but helps to ensure data reliability. Within these constraints, InnoDB tries to optimize the database work and the organization of disk files to minimize the amount of disk I/O. Sometimes, I/O is postponed until the database is not busy, or until everything needs to be brought to a consistent state, such as during a database restart after a fast shutdown.

This section discusses the main considerations for I/O and disk space with the default kind of MySQL tables (also known as InnoDB tables):