Rechercher dans le manuel MySQL
15.6.3.2 File-Per-Table Tablespaces
Historically, InnoDB
tables were stored in the
system tablespace.
This monolithic approach was targeted at machines dedicated to
database processing, with carefully planned data growth, where any
disk storage allocated to MySQL would never be needed for other
purposes. The file-per-table
tablespace feature provides a more flexible alternative,
where each InnoDB
table is stored in its own
tablespace data file (.ibd
file). This
feature is controlled by the
innodb_file_per_table
configuration option, which is enabled by default.
Advantages
You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablespace. Truncating or dropping tables stored in the shared system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new
InnoDB
data.Similarly, a table-copying
ALTER TABLE
operation on table that resides in a shared tablespace can increase the amount of space used by the tablespace. Such operations may require as much additional space as the data in the table plus indexes. The additional space required for the table-copyingALTER TABLE
operation is not released back to the operating system as it is for file-per-table tablespaces.The
TRUNCATE TABLE
operation is faster when run on tables stored in file-per-table tablespaces.You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes by specifying the location of each table using the syntax
CREATE TABLE ... DATA DIRECTORY =
, as explained in Section 15.6.3.6, “Creating a Tablespace Outside of the Data Directory”.absolute_path_to_directory
You can run
OPTIMIZE TABLE
to compact or recreate a file-per-table tablespace. When you run anOPTIMIZE TABLE
,InnoDB
creates a new.ibd
file with a temporary name, using only the space required to store actual data. When the optimization is complete,InnoDB
removes the old.ibd
file and replaces it with the new one. If the previous.ibd
file grew significantly but the actual data only accounted for a portion of its size, runningOPTIMIZE TABLE
can reclaim the unused space.You can move individual
InnoDB
tables rather than entire databases.You can copy individual
InnoDB
tables from one MySQL instance to another (known as the transportable tablespace feature).Tables created in file-per-table tablespaces support features associated with compressed and dynamic row formats.
You can enable more efficient storage for tables with large
BLOB
orTEXT
columns using the dynamic row format.File-per-table tablespaces may improve chances for a successful recovery and save time when a corruption occurs, when a server cannot be restarted, or when backup and binary logs are unavailable.
You can back up or restore individual tables quickly using the MySQL Enterprise Backup product, without interrupting the use of other
InnoDB
tables. This is beneficial if you have tables that require backup less frequently or on a different backup schedule. See Making a Partial Backup for details.File-per-table tablespaces are convenient for per-table status reporting when copying or backing up tables.
You can monitor table size at a file system level without accessing MySQL.
Common Linux file systems do not permit concurrent writes to a single file when
innodb_flush_method
is set toO_DIRECT
. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction withinnodb_flush_method
.The system tablespace stores the data dictionary and undo logs, and is limited in size by
InnoDB
tablespace size limits. See Section 15.6.1.6, “Limits on InnoDB Tables”. With file-per-table tablespaces, each table has its own tablespace, which provides room for growth.
Potential Disadvantages
With file-per-table tablespaces, each table may have unused space, which can only be utilized by rows of the same table. This could lead to wasted space if not properly managed.
fsync
operations must run on each open table rather than on a single file. Because there is a separatefsync
operation for each file, write operations on multiple tables cannot be combined into a single I/O operation. This may requireInnoDB
to perform a higher total number offsync
operations.mysqld must keep one open file handle per table, which may impact performance if you have numerous tables in file-per-table tablespaces.
More file descriptors are used.
innodb_file_per_table
is enabled by default in MySQL 5.6 and higher. You may consider disabling it if backward compatibility with earlier versions of MySQL is a concern.If many tables are growing there is potential for more fragmentation which can impede
DROP TABLE
and table scan performance. However, when fragmentation is managed, having files in their own tablespace can improve performance.The buffer pool is scanned when dropping a file-per-table tablespace, which can take several seconds for buffer pools that are tens of gigabytes in size. The scan is performed with a broad internal lock, which may delay other operations. Tables in the system tablespace are not affected.
The
innodb_autoextend_increment
variable, which defines increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full, does not apply to file-per-table tablespace files, which are auto-extending regardless of theinnodb_autoextend_increment
setting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.
The innodb_file_per_table
option is enabled by default.
To set the
innodb_file_per_table
option at
startup, start the server with the
--innodb_file_per_table
command-line option, or add this line to the
[mysqld]
section of
my.cnf
:
[mysqld]
innodb_file_per_table=1
You can also set
innodb_file_per_table
dynamically, while the server is running:
With innodb_file_per_table
enabled, you can store InnoDB
tables in a
file. Unlike the tbl_name
.ibdMyISAM
storage engine, with
its separate
and
tbl_name
.MYD
files for indexes and data, tbl_name
.MYIInnoDB
stores the
data and the indexes together in a single
.ibd
file.
If you disable
innodb_file_per_table
in your
startup options and restart the server, or disable it with the
SET GLOBAL
command, InnoDB
creates new tables inside the system tablespace unless you have
explicitly placed the table in file-per-table tablespace or
general tablespace using the
CREATE TABLE ...
TABLESPACE
option.
You can always read and write any InnoDB
tables, regardless of the file-per-table setting.
To move a table from the system tablespace to its own
tablespace, change the
innodb_file_per_table
setting
and rebuild the table:
Tables added to the system tablespace using
CREATE TABLE ...
TABLESPACE
or
ALTER TABLE ...
TABLESPACE
syntax are not affected by the
innodb_file_per_table
setting.
To move these tables from the system tablespace to a
file-per-table tablespace, they must be moved explicitly using
ALTER TABLE ...
TABLESPACE
syntax.
InnoDB
always needs the system tablespace
because it puts its internal
data dictionary
and undo logs there. The
.ibd
files are not sufficient for
InnoDB
to operate.
When a table is moved out of the system tablespace into its
own .ibd
file, the data files that make
up the system tablespace remain the same size. The space
formerly occupied by the table can be reused for new
InnoDB
data, but is not reclaimed for use
by the operating system. When moving large
InnoDB
tables out of the system tablespace,
where disk space is limited, you may prefer to enable
innodb_file_per_table
and
recreate the entire instance using the
mysqldump command. As mentioned above,
tables added to the system tablespace using
CREATE TABLE ...
TABLESPACE
or
ALTER TABLE ...
TABLESPACE
syntax are not affected by the
innodb_file_per_table
setting. These tables must be moved individually.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-multiple-tablespaces.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.