MySQL Forums
Forum List  »  InnoDB

Why is there a GB discrepancy between innodb_file_per_table=ON vs innodb_file_per_table=OFF
Posted by: Brad Howe
Date: November 20, 2020 09:33PM

Hi just a little context.

We had a database server that was configured with the default innodb_file_per_table=ON. It was unnoticed but as the server schemas grew to over 1000, Backups were really so as there as many,many files. Additionally, we had over 522,350 .ibd files due to the nature of the application and the table requirements which in turn had sporadic open file limits hit .

This is not the reason for my post. 😊

To convert it in the ibdata1 file, I put the server in READ_LOCK and took a mysqldump.

After restoring that file to another server clean server with innodb_file_per_table=OFF, the ibdata1 file grew to only 30 GB.

innodb_file_per_table=ON
DATA Folder = 93.6 GB (522,350 .ibd files)

Mysqldump resulted in a 8.7 GB db.sql file

innodb_file_per_table=OFF
DATA Folder = 30 GB (1 ibdata1 file)

Why is there a 63 GB discrepancy between innodb_file_per_table=ON vs innodb_file_per_table=OFF

Options: ReplyQuote


Subject
Views
Written By
Posted
Why is there a GB discrepancy between innodb_file_per_table=ON vs innodb_file_per_table=OFF
36
November 20, 2020 09:33PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.