Rechercher dans le manuel MySQL
15.6.3.5 Temporary Tablespaces
InnoDB
uses session temporary tablespaces and a
global temporary tablespace.
Session Temporary Tablespaces
Session temporary tablespaces store user-created temporary
tables and internal temporary tables created by the optimizer
when InnoDB
is configured as the storage
engine for on-disk internal temporary tables. Beginning with
MySQL 8.0.16, the storage engine used for on-disk internal
temporary tables is always InnoDB
.
(Previously, the storage engine was determined by the value of
internal_tmp_disk_storage_engine
.)
Session temporary tablespaces are allocated to a session from a
pool of temporary tablespaces on the first request to create an
on-disk temporary table. A maximum of two tablespaces is
allocated to a session, one for user-created temporary tables
and the other for internal temporary tables created by the
optimizer. The temporary tablespaces allocated to a session are
used for all on-disk temporary tables created by the session.
When a session disconnects, its temporary tablespaces are
truncated and released back to the pool. A pool of 10 temporary
tablespaces is created when the server is started. The size of
the pool never shrinks and tablespaces are added to the pool
automatically as necessary. The pool of temporary tablespaces is
removed on normal shutdown or on an aborted initialization.
Session temporary tablespace files are five pages in size when
created and have an .ibt
file name
extension.
A range of 400 thousand space IDs is reserved for session temporary tablespaces. Because the pool of session temporary tablespaces is recreated each time the server is started, space IDs for session temporary tablespaces are not persisted when the server is shut down and may be reused.
The innodb_temp_tablespaces_dir
variable defines the location where session temporary
tablespaces are created. The default location is the
#innodb_temp
directory in the data
directory. Startup is refused if the pool of temporary
tablespaces cannot be created.
shell> cd BASEDIR/data/#innodb_temp
shell> ls
temp_10.ibt temp_2.ibt temp_4.ibt temp_6.ibt temp_8.ibt
temp_1.ibt temp_3.ibt temp_5.ibt temp_7.ibt temp_9.ibt
In statement based replication (SBR) mode, temporary tables created on a slave reside in a single session temporary tablespace that is truncated only when the MySQL server is shut down.
The INNODB_SESSION_TEMP_TABLESPACES
table provides metadata about session temporary tablespaces.
The
INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO
table provides metadata about user-created temporary tables that
are active in an InnoDB
instance.
The global temporary tablespace (ibtmp1
)
stores rollback segments for changes made to user-created
temporary tables.
The innodb_temp_data_file_path
variable defines the relative path, name, size, and attributes
for global temporary tablespace data files. If no value is
specified for
innodb_temp_data_file_path
, the
default behavior is to create a single auto-extending data file
named ibtmp1
in the
innodb_data_home_dir
directory.
The initial file size is slightly larger than 12MB.
The global temporary tablespace is removed on normal shutdown or on an aborted initialization, and recreated each time the server is started. The global temporary tablespace receives a dynamically generated space ID when it is created. Startup is refused if the global temporary tablespace cannot be created. The global temporary tablespace is not removed if the server halts unexpectedly. In this case, a database administrator can remove the global temporary tablespace manually or restart the MySQL server. Restarting the MySQL server removes and recreates the global temporary tablespace automatically.
The global temporary tablespace cannot reside on a raw device.
INFORMATION_SCHEMA.FILES
provides
metadata about the global temporary tablespace. Issue a query
similar to this one to view global temporary tablespace
metadata:
By default, the global temporary tablespace data file is autoextending and increases in size as necessary.
To determine if a global temporary tablespace data file is
autoextending, check the
innodb_temp_data_file_path
setting:
- +------------------------------+
- | @@innodb_temp_data_file_path |
- +------------------------------+
- | ibtmp1:12M:autoextend |
- +------------------------------+
To check the size of global temporary tablespace data files,
query the INFORMATION_SCHEMA.FILES
table using a query similar to this one:
- *************************** 1. row ***************************
- FILE_NAME: ./ibtmp1
- TABLESPACE_NAME: innodb_temporary
- INITIAL_SIZE: 12582912
- TotalSizeBytes: 12582912
- DATA_FREE: 6291456
- MAXIMUM_SIZE: NULL
TotalSizeBytes
shows the current size of the
global temporary tablespace data file. For information about
other field values, see Section 25.11, “The INFORMATION_SCHEMA FILES Table”.
Alternatively, check the global temporary tablespace data file
size on your operating system. The global temporary tablespace
data file is located in the directory defined by the
innodb_temp_data_file_path
variable.
To reclaim disk space occupied by a global temporary tablespace
data file, restart the MySQL server. Restarting the server
removes and recreates the global temporary tablespace data file
according to the attributes defined by
innodb_temp_data_file_path
.
To limit the size of the global temporary tablespace data file,
configure
innodb_temp_data_file_path
to
specify a maximum file size. For example:
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
Configuring
innodb_temp_data_file_path
requires restarting the server.
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-temporary-tablespace.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.