Mysql – innodb_file_per_table option enabled but ibdata1 file still grows up

innodbmariadbMySQL

I installed mariadb 10.2.10 with innodb_file_per_table option enabled.

Then I create table with Innodb engine and inserted 1 M row into.

Now I look and local (..data/mydbname/table_name.ibd) file is grown, but grown system ibdata1 file also. Why? I expected that because of innodb_file_per_table is enabled, only local .ibd should grown, but not ibdata1.

Why size of ibdata1 also grows up? What I misunderstood ?

Best Answer

A number of operations use ibdata1 temporarily. The actual data for the given table will be entirely in the .ibd file.

It's rather normal for ibdata1 to be at least tens of MB, even with all tables in their .ibd files.

Do you have the following case?

  1. innodb_file_per_table=OFF
  2. Create and populate table1 -- this will go into ibdata1
  3. change to innodb_file_per_table=ON
  4. Create and populate table2 -- this will go into table2.ibd
  5. insert into table1 -- this will still go into ibdata1

The way to move an existing table to ibd is to set ON, then do

ALTER TABLE table1 ENGINE=InnoDB;

If this does not explain your situation, please keep an eye on (1) what operations are being performed on any table, and (2) the size of ibdata1. If it grows again, the (1) may give us some more clues.

Note especially any operations involving large tables.

Related Question