0

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 ?

1
  • what size you have set in your configurations for innodb_data_file? the default ibdata1 has MySQL instance native data used for undo/redo operations and for innodb_file_per_table would split data table wise. Any table created with this option would let you have two files .frm and .ibd..hope it helps Nov 6 '17 at 15:49
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.