Default value for Innodb_files_per_table parameter

MySQL & MariaDB Percona XtraDB Cluster 5.x
2

Prior to MySQL 5.6.6 innodb_file_per_table is OFF by default. So in your MySQL version 5.5.23 is OFF by default. You can enable it by setting it dynamically which means without restarting MySQL server and to make it permanent declare it in my.cnf too. After enabling innodb_file_per_table it will affect new tables not existing tables. You can check for details here [url]MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables

3

Thanks for your reply Irfan. However, I see the parameter in my.cnf just blank “Innodb_files_per_table” no value. How can I confirm if it is either ON or OFF. Because, we have tried to delete few records and optimize the table and could see table space got free. Here is the link which i followed: http://www.mysqlperformanceblog.com/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/
If I got to know whether it is ON or OFF, I can try to add it dynamically and proceed otherwise directly proceed through above link. Is there a way I can find out the value of innodb_files_per_table if not available in my.cnf file.

4

Setting innodb_file_per_table in the my.cnf turns this setting on, if it is there, it is on, if it does not exist, it is off. I had to add it on all nodes to enable the feature. I have never set it using mysql commands though, so YMMV. This is my understanding, I always explicitly enable it in my,cnf.

5

You can run

show variables like 'innodb_file_per_table';

in MySQL to see is the setting enabled.

You should also check the filesystem in the folder where DB data resides. If enabled, every table in your database folder should have an appropriate .ibd file.

6

Thankyou very much Mark.