Important INNODB storage related parameter innodb_file_per_table

The data files that you define in the configuration file form the InnoDB system tablespace. The files are logically concatenated to form the tablespace. There is no striping in use. Currently, you cannot define where within the tablespace your tables are allocated. However, in a newly created tablespace, InnoDB allocates space starting from the first data file.

To avoid the issues that come with storing all tables and indexes inside the system tablespace, you can turn on the innodb_file_per_table configuration option, which stores each newly created table in a separate tablespace file (with extension .ibd). For tables stored this way, there is less fragmentation within the disk file, and when the table is truncated, the space is returned to the operating system rather than still being reserved by InnoDB within the system tablespace.


[root@vmxdb01 test]# grep innodb_file_per_table /etc/my.cnf
innodb_file_per_table = 1


[root@vmxdb01 test]# mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.13-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)


[root@vmxdb01 test]# ls -l /var/lib/mysql/test
total 216
-rw-rw----. 1 mysql mysql 9106 Jun 15 23:39 customers.frm
-rw-rw----. 1 mysql mysql 98304 Jun 15 23:42 customers.ibd
-rw-rw----. 1 mysql mysql 8556 Jun 16 00:02 user.frm
-rw-rw----. 1 mysql mysql 98304 Jun 16 00:02 user.ibd


This parameter can be dynamically changed:

mysql> set global innodb_file_per_table=on;
Query OK, 0 rows affected (0.00 sec)