Can you grow an ibdata file per table?
If the ibdata1 grows just for doing that, then you have proven undeniably that dropping and creating tables alone can grow ibdata1 with innodb_file_per_table enabled. GIVE IT A TRY!!! I wish add something to the answer of Rolando.
How big is the file size of ibdata1?
Now, the ibdata1 file size will be around 100GB+. After few days, you deleted around 50GB worth of data from all those tables. The ibdata1 file size will not be reduced to around 50GB+, it will still stay at around 100GB+.
Why is MySQL ibdata1 file still too big?
Because, the file still has the unused space inside from the above 50GB of deleted data. The problem is, you can’t reclaim those unused space after you delete the 50GB of data from ibdata1 file. There is a way to do it, but is too complicated (explained below), and involves taking the MySQL database down.
How to shrink MySQL ibdata1 size using InnoDB?
How to Shrink MySQL ibdata1 Size using innodb_file_per_table. In MySQL, when you are using InnoDB, all the tables and indexes are stored under the MySQL system tablespace. The single ibdata1 file contains all the tables and indexes in your MySQL database. So, if you have a big database, this file size will grow really big.
How big is the table size in InnoDB?
The manual page is Storing InnoDB Undo Logs in Separate Tablespaces. Recreated the datadir and checked the size of system and undo tablespaces: Fresh ibdata1 (with my configuration) is around 12M. I launched a simple script to create 200000 tables like this: The 200000 tables occupy less than 129M.
How big is the ibdata file in MySQL?
But Still I m seeing huge ibdata file ( ~50GB). All tables are having .ibd file as well. This is a machine I have created by taking dump and loading. What is a reason for this. You probably have lots of large transactions or one really big transaction. If you look inside the system table (ibdata1), you see rollback segments and undo logs.