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
Subject
Views
Written By
Posted
Why is there a GB discrepancy between innodb_file_per_table=ON vs innodb_file_per_table=OFF
666
November 20, 2020 09:33PM
Sorry, you can't reply to this topic. It has been closed.
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.