Reasons to use innodb_file_per_table

左手的ㄟ右手 2021-10-01 03:28 160阅读 0赞

When working with InnoDB, you have two ways for managing the tablespace storage:

1.  Throw everything in one big file (optionally split).
2.  Have one file per table.

I will discuss the advantages and disadvantages of the two options, and will strive to convince that **innodb\_file\_per\_table** is preferable.

#### A single tablespace ####

Having everything in one big file means all tables and indexes, from *all schemes*, are 'mixed' together in that file.

This allows for the following nice property: free space can be shared between different tables and different schemes. Thus, if I purge many rows from my **log** table, the now unused space can be occupied by new rows of any other table.

This same nice property also translates to a not so nice one: data can be greatly fragmented across the tablespace.

An annoying property of InnoDB's tablespaces is that they never shrink. So after purging those rows from the **log** table, the tablespace file (usually **ibdata1**) still keeps the same storage. It does not release storage to the file system.

I've seen more than once how certain tables are left unwatched, growing until disk space reaches 90% and SMS notifications start beeping all around.

There's little to do in this case. Well, one can always purge the rows. Sure, the space would be reused by InnoDB. But having a file which consumes some 80-90% of disk space is a performance catastrophe. It means the disk needle needs to move large distances. Overall disk performance runs very low.

The best way to solve this is to setup a new slave (after purging of the rows), and dump the data into that slave.

#### InnoDB Hot Backup ####

The funny thing is, the **ibbackup** utility will copy the tablespace file as it is. If it was 120GB, of which only 30GB are used, you still get a 120GB backed up and restored.

#### mysqldump, mk-parallel-dump ####

mysqldump would be your best choice if you only had the original machine to work with. Assuming you're only using InnoDB, a dump with **--single-transaction** will do the job. Or you can utilize [mk-parallel-dump][] to speed things up (depending on your dump method and accessibility needs, mind the locking).

#### innodb\_file\_per\_table ####

With this parameter set, a **.ibd** file is created per table. What we get is this:

*  Tablespace is not shared among different tables, and certainly not among different schemes.
 *  Each file is considered a tablespace of its own.
 *  Again, tablespace never reduces in size.
 *  It is possible to regain space per tablespace.

Wait. The last two seem conflicting, don't they? Let's explain.

In our **log** table example, we purge many rows (up to 90GB of data is removed). The **.ibd** file does not shrink. But we *can* do:

> ALTER TABLE log ENGINE=InnoDB

What will happen is that a new, temporary file is created, into which the table is rebuilt. Only existing data is added to the new table. Once comlete, the original table is removed, and the new table renamed as the original table.

Sure, this takes a long time, during which the table is completely locked: no writes and no reads allowed. But still - it allows us to regain disk space.

With the new InnoDB plugin, disk space is also regained when execuing a **TRUNCATE TABLE log** statement.

Fragmentation is not as bad as in a single tablespace: the data is limited within the boundaries of a smaller file.

#### Monitoring ####

One other nice thing about **innodb\_file\_per\_table** is that it is possible to monitor table size on the file system level. You don't need access to MySQL, to use SHOW TABLE STATUS or to query the INFORMATION\_SCHEMA. You can just look up the top 10 largest files under your MySQL data directory (and subdirectories), and monitor their size. You can see which table grows fastest.

#### Backup ####

Last, it is not yet possible to backup single InnoDB tables by copying the **.ibd** files. But hopefully work will be done in this direction.

There is a negative to innodb\_file\_per\_table:

With single file, table space can be shared between rows of different tables and schemas. This means less wasted tablespace.

With innodb\_file\_per\_table, each table may have unused tablspace, which can only be utilized by rows of the same table. This means (sometimes much) more wasted tablespace.

参考：

[http://code.openark.org/blog/mysql/reasons-to-use-innodb\_file\_per\_table][http_code.openark.org_blog_mysql_reasons-to-use-innodb_file_per_table]

转载于:https://www.cnblogs.com/xiaotengyi/p/3572642.html

[mk-parallel-dump]: http://www.maatkit.org/
[http_code.openark.org_blog_mysql_reasons-to-use-innodb_file_per_table]: http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table