Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.6Mb
PDF (A4) - 34.6Mb
PDF (RPM) - 32.2Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 147.5Kb
Man Pages (Zip) - 208.8Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb

MySQL 8.0 Reference Manual  /  ...  /  Temporary Tablespace

Pre-General Availability Draft: 2017-12-13

15.4.11 Temporary Tablespace

The temporary tablespace is used for InnoDB temporary tables and related objects. The innodb_temp_data_file_path configuration option defines a relative path for the temporary tablespace data file. If innodb_temp_data_file_path is not defined, a single auto-extending 12MB data file named ibtmp1 is created in the data directory. The temporary tablespace is recreated on each server start and receives a dynamically generated space ID, which helps avoid conflicts with existing space IDs. The temporary tablespace cannot reside on a raw device. Startup is refused if the temporary tablespace cannot be created.

The temporary tablespace is removed on normal shutdown or on an aborted initialization. The temporary tablespace is not removed when a crash occurs. In this case, the database administrator may remove the temporary tablespace manually or restart the server with the same configuration, which removes and recreates the temporary tablespace.

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO provides metadata about active InnoDB temporary tables. With the exception of optimized internal temporary tables used by InnoDB, INNODB_TEMP_TABLE_INFO reports on all user and system-created temporary tables that are active within a given InnoDB instance.

Temporary Table Undo Logs

Temporary table undo logs are used for temporary tables and related objects. This type of undo log is not a redo log, as temporary tables are not recovered during crash recovery and do not require redo logs. Temporary table undo logs are, however, used for rollback while the server is running. This special type of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and related objects. Temporary table undo logs reside in the temporary tablespace.

The innodb_rollback_segments configuration option defines the number of rollback segments used by the temporary tablespace.

User Comments
Sign Up Login You must be logged in to post a comment.