Buy Percona ServicesBuy Now!

Impact of innodb_file_per_table Option On Crash Recovery Time

 | July 23, 2019 |  Posted In: MySQL, Open Source Databases

MySQL Logo Starting at version MySQL5.6+ by default innodb_file_per_table is enabled and all data is stored in separate tablespaces.

It provides some advantages. I will highlight some of them:

  • You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablespace. Truncating or dropping tables stored in the shared system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new InnoDB data.
  • You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes.
  • You can monitor table size at a file system level without accessing MySQL.
  • Backups taken with Percona XtraBackup takes less space (compared with the physical backup of ibdata files)

Problem

There are disadvantages described on MySQL man page but I found another one that is not mentioned: if you have a huge number of tables, the crash recovery process may take a lot of time.

During crash recovery the MySQL daemon scans .ibd files:

During startup time I checked MySQL behavior and found that MySQL opens files one by one. In my test case it was 1400000+ tables and it took 02:46:48 just to scan ibd files.

To prevent such a long downtime we decided to move all the tables to shared tablespaces.

Solution – moving tables to shared tablespaces

  1. Make sure that you have enough space on disk.
  2. Modify my.cnf and add the files.
  3. Restart MySQL and wait until it creates the data files.
  4. Move your InnoDB tables to shared tablespaces.

You can use this script:

What the script does:

  1. Retrieves all tables that are occupying their own tablespace
  2. Generates SQL code in this pattern USE DB_X; ALTER TABLE TBL_Y engine=innodb;
  3. Applies the SQL scripts in parallel.

After changing file_per_table to 0 and moving the InnoDB tables:

Conclusion

Using the default value of innodb_file_per_table (ON) is not always a good choice. In my test case: 4000+ databases, 1400000+ tables. I reduced recovery time from 02:46:48 to 00:08:58 seconds.

That’s 18 times less!

Remember, there is no “golden my.cnf config”, and each case is special. Optimize MySQL configuration according to your needs.


The content in this blog is provided in good faith by members of the open source community. The content is not edited or tested by Percona, and views expressed are the authors’ own. When using the advice from this or any other online resource, please test ideas before applying them to your production systems, and always secure a working back up.

Cartoon source https://imgur.com/

Timur Solodovnikov

For the last 10 years, Timur has worked in various telco companies. He has hands-on experience in databases, system and network engineering. Currently Timur works as a Senior DBA for Five9, Inc., the leading provider of cloud contact center software. He lives in California, US with wife and daughter. LinkedIn

3 Comments

  • Timur, you seem to be trying to fix a problem in 5.6 when MySQL/Percona upstream is on 8.0. Are you aware there was work done to improve this crash recovery performance in 5.7 and later?

    I don’t remember the details now, but do no know that this was something that I noticed and reported in the past and I’m pretty certain that MSQL 5.7 recovers much more quickly than 5.6 did. MySQL 8.0’s data dictionary is also supposed to handle things better.

    So fixing an issue in 5.6 as you describe is good but I’d be most interested if you have a copy of the data you were working on to know if (1) upgrading to 5.7 and then 8.0 and doing the same recovery scenario would lead to the same long time for recovery to take place using the default innodb_file_per_table settiing.

    If it does it makes sense to report that this problem [still] exists. If it’s faster in 5.7 or 8.0, if you have time to test would be good to see any differences, then you could report how much improvement you notice.

    Many people often are not aware that many performance problems have been resolved in newer releases. Some users may not want to upgrade, but showing them that the “issue” really is fixed in newer versions may convince them that the upgrade process is not “that” hard and the benefit really is worth it.

    In this particular case I’m pretty sure things are better in 5.7 so you’d benefit from the upgrade.

    Please if you have time compare the numbers for 5.7 and 8.0 as it would be interesting to see what difference the upgrades actually make.

  • Simon, thanks for pointing out that there are improvements in new releases.

    Before MySQL 5.7, InnoDB crash recovery would construct a mapping from numeric tablespace identifiers to *.ibd file names by scanning through the data directory.

    In MySQL 5.7 (and MariaDB Server 10.2), I changed the InnoDB redo log format so that the mapping from numeric identifiers to file names will be included in the redo log file. For some reason, my blog post about that is no longer available at the original location, but you can view it at http://web.archive.org/web/20180123191716/http://mysqlserverteam.com/innodb-crash-recovery-improvements-in-mysql-5-7/

    Side note: I believe that this work is necessary for the correct crash recovery of CREATE TABLESPACE feature that appeared in MySQL 5.7 later, for InnoDB tables. (MariaDB does not support this feature for InnoDB, because it does not play well together with other features, such as exporting or importing tablespaces.)

    In MariaDB, I have modified the redo log format further. I believe that neither MySQL Enterprise Backup nor XtraBackup can cope with a situation where a TRUNCATE TABLE operation is being executed on the 5.7 server. This works with MariaDB. For backward compatibility, in the 10.2 series there is the option innodb_safe_truncate=OFF. Bigger changes are planned for the MariaDB Server 10.5 release. MySQL 8.0 implements TRUNCATE in a better way, based on my design for the Global Data Dictionary. In MariaDB, we do TRUNCATE by a combination of crash-safe RENAME (with an undo log format change), DROP and CREATE.

    When it comes to DDL improvements, the recently released MariaDB Server 10.4 supports not only instant ADD COLUMN (which debuted in MariaDB Server 10.3 before MySQL 8.0), but also instant DROP COLUMN and a few more instant ALTER TABLE operations.

Leave a Reply