Search This Blog

Thursday, November 22, 2018

Minimize the overall size of MySQL InnoDB data files

To minimize the overall size of InnoDB data files, consider enabling the MySQL configuration option innodb_file_per_table. This option can minimize data size for InnoDB tables in several ways:

  • It prevents the InnoDB system tablespace from ballooning in size, allocating disk space that can afterwards only be used by MySQL. For example, sometimes huge amounts of data are only needed temporarily, or are loaded by mistake or during experimentation. Without theinnodb_file_per_table option, the system tablespace expands to hold all this data, and never shrinks afterward.
  • It immediately frees the disk space taken up by an InnoDB table and its indexes when the table is dropped or truncated. Each table and its associated indexes are represented by a .ibd file that is deleted or emptied by these DDL operations.
  • It allows unused space within a .ibd file to be reclaimed by the OPTIMIZE TABLE statement, when substantial amounts of data are removed or indexes are dropped.
  • It enables partial backups where you back up some InnoDB tables and not others