Default value for Innodb_files_per_table parameter

  • Filter
  • Time
  • Show
Clear All
new posts

  • Default value for Innodb_files_per_table parameter

    What is the default value of Innodb_Files_per_table. Does this parameter gets installed during the general mysql install process or need to do explicitly. When I observe in the my.cnf, we see that though parameter exist in the file but not able to see the parameter value. Does that mean it is On or Off. Mysql version is 5.5.23. Please suggest!
    Last edited by krishna; 11-15-2013, 03:02 PM.

  • #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 http://dev.mysql.com/doc/refman/5.5/...file_per_table


    • #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.